Binding Multiple Combo Boxes to the Same Data Source

Recently the VB Team received a customer bug submitted through Connect that had to do with binding multiple combo boxes to the same data source. The customer was reporting that once a selection was made in one combo, the other combos were also changing. The resolution was “By Design” because of the way the customer was setting up the data binding. We thought it would benefit the community if I posted information on how we helped the customer resolve the issue, so let’s talk about what was happening and the solution. But first some architectural background on data binding.

Data binding on Windows Forms involves a few different objects depending on where your data is coming from (i.e. an object or the database directly) but we’re going to focus on the database scenario. The TableAdapter is responsible for talking to the database and pulling in result sets into DataTables contained in a DataSet and for sending back the updates — this is referred to sometimes as CRUD — Create, Retrieve, Update, Delete. The DataSet maintains the client-side representation of that data (and can also perform validation) by placing the data in DataRow objects that are contained in DataTables. The BindingSource is responsible for maintaining currency, that’s the position of the DataRow in the DataTable in which the controls are displaying data from. Here’s a simple diagram pulled from my How Do I video on Understanding Data.

Once you get your DataSet and TableAdapters set up you’re ready to start data binding to your controls. You use the Data Sources Window to drag fields and tables onto your form and I’ve shown how to do this in many videos in the Forms Over Data series. When you do this, the DataSet and TableAdapters are dropped onto your form and then a new BindingSource object is created for you. It’s this BindingSource that is providing the “glue” between the DataSet and your controls.

There’s one video in particular that shows you how to create lookup lists. In this scenario, the data binding is a bit more complex. When you bind a textbox to a BindingSource it’s a simple binding to a single field in the current row. When you bind a combo box that acts as a lookup list however, you need two binding sources because you need to be able to display the data from one lookup table and place the value into the table that you’re editing.

It’s very easy to set this up through the Data Sources Window. First drag all the fields you want to edit onto the from from your edit table by selecting the drop down next to the table and choosing “Details”. You can change the lookup fields to combo boxes by selecting the drop down next to the fields and changing the control to a combo box:

Once you have the controls bound to your edit table you can easily bind the combo box list with data from your lookup table. Just drag the lookup table from the Data Sources Window directly over the combo box. Repeat this process for each combo box you want to display that lookup data. Then use the property window or the smart tags on the combo boxes to adjust the ValueMember (the field who’s value you want placed in your edit table) and DisplayMember (the field who’s value you want displayed in the combo) properties.

This will create THREE separate LookupBindingSources, (four total BindingSources once you count the edit table’s BindingSource). This is the key to binding the same lookup data to multiple combo box controls. If you use the same LookupBindingSource instance for the combo boxes then you will end up in the same situation as the customer who reported the bug. If you don’t use separate BindingSource instances then as a selection is made in one combo, the others also change to that value, and that’s probably not the desired behavior you want.

Why this works this way is because each BindingSource object manages it’s own position within the data source that is bound to it. If you use one LookupBindingSource in this example for multiple combos, then as one selection is made, the other will also change. (Keep in mind that using multiple LookupBindingSources does not load multiple instances of the LookupTable’s data from the database, it just simply creates multiple BindingSource objects.)

So the customer easily fixed this issue and was very happy with the VB Team’s quick response.