Sharing the goodness…
Beth Massi is a Senior Program Manager on the Visual Studio team at Microsoft and a community champion for business application developers. Learn more about Beth.
More videos »
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.
I found this solution by myself but i think this is not a bug. There are situations in wihch this behavior is required.
I added a similar problem to Connect https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=336451 but the solution proposd made the app too slow to scroll through records.
Crucially, the concept of multiple BindingSources to a single table quickly falls apart if you start using the Sort or Filter properties of the binding sources. For example to sort or filter the various combo boxes in different ways. Only a single Sort and/or Filter can be applied to a datatable at any one time, and only the most recently applied Sort or Filter will apply. I feel that this needs to be made more explicit in the documentation and samples. A solution is to bind thru DataView instances if you need to achieve multiple sorts of filters on a single underlying data table.
You're right regarding the filtering. Just like this post describes, it's better to bind to separate BindingSources. That way you can specify the BindingSource.Filter property independentally. Behind the scenes a DataView is created for each BindingSource so you don't have to bind to the DataView directly.
Hi. Thanks for the response. Forgive me for persisting on this, but in my experience your suggestion, while technically possible at designtime does not atually work at run time as you may expect it to.
Imagine that your lookup table above holds a big list of 'book titles' categorised into types 'fiction', 'non-fiction' and 'childrens' using a BookTypeID field.
You want the first combobox to show only 'fiction' titles, the second combobox to show only 'non-fiction' titles and the third combobox to show only 'childrens' titles.
Easy, you'd say. Load all the titles into a datatable. Create three bindingsources and simply set the three filter properties on the three bindingsources to only show the appropriate titles for its associated combo box. BUT when I run the app all the comboboxes show the SAME filtered list!! Only one bindingsource.filter property actually 'wins' and the other two bindingsource.filter properties simply do not apply. The result is that all the comboboxes show 'fiction' titles, or they all show 'non-fiction' titles or they all show 'childrens' titles. My hypothesis is that rather then constructing an intermediate 'dataview' as you suggest, the bindingsource.filter property does something to directly modify the actual underlying datatable object, and because the same lookup datatable is shared across the 3 bindingsources, only one filter can apply at any one time.
The exact same issue applies to the bindingsource.sort property. Lets assume I clear all the bindingsource.filter properties.
Now I want to sort the full list of titles in the first combobox by 'author', the same full list of titles in the second combobox by 'title' and the same full list of titles in the third combobox by 'publisher'. I can, in theory, do this simply by setting the sort properties of the bindingsources independently. BUT at run time, all the comboboxes end up getting sorted the same way - one bindingsource.sort property 'wins' and the other two do not apply.
The only way I could get this to work as I wanted was to either create some additional copies of the underlying lookuptable and bind each bindingsource to its own isolated copy of the lookuptable. Or to do what you suggest the bindingsource ought to be doing and create several DataViews that sit on in front of the underlying datatable, and bind the comboboxes to those.
If I'm wrong, flame-grill-me and delete my post as I'd hate to inject a load of mis-information into what is a very informative and helpful blog.
Great article Beth, but we really need you to respond to Mike Birtwistle's comment. I think he is right. Certainly I am having all kinds of trouble using multiple BindingSources on the same data table, at least when I use WCF services in n-tier applications. I find that service.wsdll mixes up the datasets.
Sorry for the slow response but I've not been able to post comments to my own blog for a while. Must be an issue with the service provider.
Anyways, just create a separate DataView on the DataTable and as Mike suggests for this scenario. Turns out the DataSet does this to save resources, it will not happen on custom collections. Other option is you can use LINQ to Datasets to completely avoid the Filter property and just bind the results to the BindingSource's DataSource.
I have being browsing the net for about 2 weeks looking for tutorial videos that can help me complete a couple of windows form that I created for my own use. This forms will be pulling and updating information from an access database, so far I have not being very lucky finding any with what I need, until I came across your videos, I was so thrill about, that I decided to drop this note and let you know that you have done a great job with those videos.
How ever my second purpose of this note is to ask you if you assist me in solving a problem that I’m having when on the same matter. Perhaps if you can not help me maybe you guide me in the right direction to find some help to complete my project.
Please disregard this if your time is limited regarding this matters, but if its not, a response will be greatly appreciated.
Thank you very much and happy holidays.
I recently experienced the same problem and arrived at this page after searching for solutions. I have a reservation in this solution though that why do you have to make another table just to ease the binding process when you can fill up all those combo boxes or whatever control using a loop on the datatable.
hi all i m facing trouble using 4 four combo boxes from diffrent tables and fetch them in to one table. i mean i save brand vendor type and location and attach this four to new product where other discriptions stored. when i creat new entry data saved but when i edit or delete data it do nothing and an error comes. pleas help me about this problem.
I used either this video or one simular and it worked fine, now I tried to use this and it gives me tableadater problems as it does not find the table
'A Simple solution to the problem : For 4 Comboboxes create 4 instances of Data set
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
Dim conn As New SqlConnection("Data Source=xxxxxxxxxxxxx")
Dim strSQL As String = "SELECT * FROM Item_tbl "
Dim da As New SqlDataAdapter(strSQL, conn)
Dim ds1, ds2, ds3, ds4 As New DataSet
da.Fill(ds1, "Item_tbl ")
.DataSource = ds1.Tables("Item_tbl ")
.DisplayMember = "Item_list"
.ValueMember = "Item_list"
.SelectedIndex = -1
da.Fill(ds2, "Item_tbl ")
.DataSource = ds2.Tables("Item_tbl ")
da.Fill(ds3, "Item_tbl ")
.DataSource = ds3.Tables("Item_tbl ")
da.Fill(ds4, "Item_tbl ")
.DataSource = ds4.Tables("Item_tbl ")
we want to add in combobox all father name which are same as name in another combobox is selected which are connected with ms access database table in vb.net.Please Tell me answer