Tips on Related Data Binding and ComboBoxes

I’ve been noticing a lot of questions on the forums related to Winforms data binding and the ComboBox and I thought I’d post something up here to help people out. In fact data binding, or what we call “Windows Forms over Data” is a huge, sometimes misunderstood, topic. Because of this I’m putting together a “how-to” video series on a variety of topics in this area. Two of the videos are dedicated to data binding the ComboBox in a couple very common scenarios:

1. To display information from a lookup table and send the selected value into another table’s field.

2. To display a list of parent table’s records and use that as a filter to display related child records. For instance, as the user selects a record in the ComboBox, you want to display all the related child records in a grid.

The trick is setting up the data binding properly using the BindingSource. I wrote about how to set up related lists in this post in order to get this second scenario to work with BindingSources managing lists of objects. Here, I’ll put it into the context of using DataSets.

In the first case it’s not necessary to set up a data relation in your DataSet between the lookup table and the table you’re editing, but it doesn’t hurt. In the second case it is necessary to create a relation between your parent and child tables. Let’s take an example from our beloved Northwind.

First we’ll use the Data Source Configuration Wizard to create a DataSet with Regions and Territories. In VS 2005, go to the Data menu and select “Show Data Sources” then select “Add New Data Source”.

If we take a look at the DataSet through the DataSet Designer, we see that these tables are related on RegionID:

In the first scenario we want to select a Region from the ComboBox and have that value populated into the Territorries record. In this case we set up the RegionBindingSource with the following properties:

Me.RegionBindingSource.DataSource = Me.NorthwindDataSet

Me.RegionBindingSource.DataMember = “Region”

Then you use set the BindingSource as the Datasource of the ComboBox and set the display member and value member properties:

Me.ComboBox1.DataSource = Me.RegionBindingSource

Me.ComboBox1.DisplayMember = “RegionDescription”

Me.ComboBox1.ValueMember = “RegionID”

These properties control what items are displayed in the ComboBox and what value is used when the user makes a selection. Now to get that value into the Territories table, we use the TerritoriesBindingSource when we create the binding to the RegionID:

Me.TerritoriesBindingSource.DataMember = “Territories”

Me.TerritoriesBindingSource.DataSource = Me.NorthwindDataSet

Dim b AsNew System.Windows.Forms.Binding(“SelectedValue”, _

Me.TerritoriesBindingSource, “RegionID”, True)

Me.ComboBox1.DataBindings.Add(b)

Okay we’re all set, right? Well… almost! You’ll also need to call EndEdit on the TerritoriesBindingSource at some point in order to write the value back to the DataSet. If you’re using the designers to do this then it takes care of the code for you when you click save on the BindingNavigator’s save button. So depending on the style of your form you could do this from an “Update” button (similarly you could call CancelEdit from a Cancel button).

The cool thing about EndEdit/CancelEdit on the BindingSources is that they cancel or commit only the fields in which they have bindings for, where as the DataSet rows’ AcceptChanges/RejectChanges works on the whole row regardless of the data bindings.

Now let’s take our second scenario where we want to use the ComboBox as a row filter. In this case we have to have a relation set up between our parent and our child; in the example this is FK_Territories_Region in our DataSet. So the BindingSources in this case need to be set up so that they are related as well. If you use the Data Sources Window, make sure you are working with the Region and the related Territories, just like if you were creating a One-to-Many form.

So the BindingSources will be set up like so:

Me.RegionBindingSource.DataSource = Me.NorthwindDataSet

Me.RegionBindingSource.DataMember = “Region”

Me.TerritoriesBindingSource.DataSource = Me.RegionBindingSource

Me.TerritoriesBindingSource.DataMember = “FK_Territories_Region”

Notice that the main difference here is that the TerritoriesBindingSource’s DataSource property is set to the parent BindingSource, RegionBindingSource and its DataMember is the relation name. This sets up automatic filtering on the TerritoriesBindingSource as the position changes on the RegionBindingSource. Also notice how the BindingSources decouple the data from the actual controls, making it very easy to switch controls or change the data sources.

Now the ComboBox properties can then be set up just like the first example:

Me.ComboBox1.DataSource = Me.RegionBindingSource

Me.ComboBox1.DisplayMember = “RegionDescription”

Me.ComboBox1.ValueMember = “RegionID”

Technically we don’t need to specify the ValueMember property this time because we’re not writing it anywhere, but it doesn’t hurt to specify it. Next all we need to set up is the DataSource property of the DataGridView setting it to the TerritoriesBindingSource.

Me.TerritoriesDataGridView.DataSource = Me.TerritoriesBindingSource

(By the way, all the code up to this point could all be written by the designers by using drag-and-drop from the Data Sources Window. You’ll see this in the videos ;-))

Okay we’re all set, right? Well… almost! Unfortunately a ComboBox won’t move the BindingSource’s position for you like list controls do (Grids, ListBoxes). So the trick is to simply set the position of the RegionBindingSource by handling the ComboBox’s SelectedIndexChanged event. So we need to write some code:

Because we have related BindingSources the grid will automatically filter its rows based on the selected parent row in the ComboBox.

And notice that this code would work with other controls as well, for instance, instead of a ComboBox we could have used a ListBox or have set up two related grids.

Because we’re using DataSets as the source of our data, the BindingSources are maintaining DataViews so you can easily access the current DataRowView and cast it to the Typed DataSets’s row for typed access to the fields. For instance it’s handy to be able to get the current row when working on your form. You can do this by adding a property to your form. Just make sure to check for Nothing because there may not be a selected row:

The BindingSources are your friends. They decouple the data from the actual controls, making it very easy to switch controls or change the data sources without affecting the bindings. Use the Data Sources Window and it will save you from writing 90% of your data binding code. Have fun and keep a look out for the Windows Forms over Data video series that will be published soon!

I understand that this is a beginner topic but traditionally VB6 had very different support of data binding than VFP :-) so the questions come up in the forums because data binding in .NET is very different than VB6. And remember 90% of the code I showed is actually created by the designers, I just wanted to put it in the post so people could understand. What do you see as being difficult, specifically? Thanks for the input!

I don’t see it as being particularly difficult – well it is easier in VFP that’s for sure. I was commenting on the fact that you were seeing lots of questions regarding data binding and the combobox. I thought if there’s lots of questions related to that, there’s a lot of people out there finding data handling in .NET difficult.

A combobox lookup is one of the most basic and common elements out there I would have thought.

Cool stuff, but how about two related comboboxes rather than a combobox and a grid?

Replace the grid in your second scenario with a combobox. Changing the value of the parent does update the child (at least visually), but the child’s SelectedIndexChanged event doesn’t fire.

I want to do something based on the value of the child. In order to this I need this event to fire or its SelectedValue to get updated. No matter what I do, I can’t seem to get either to happen. Any ideas?

I’m not sure I understand your problem. When you select an option in a combobox and then drop it down again, it will be positioned on that selection but you can still scroll to the other items in the list.

ok. I found out the problem i had. here is the problem now. hard to explain, but i will try.

I have a main form, that shows all job orders. you click on a job order and it takes you to view the details of that job order.

On the details view, all information comes up correct, there is a binding navigator that shows you how many jobs for that customer, and you can even navigate through them all.

When i add dropdowns for the lookup items. they default view the first item in the dropdown, they do not show what is in the database until i use the binding navigator to go up a record then back down a record. Then the dropdown displays correctly.

I have a combobox where the DataSource is bound to a datatable and the SelectedValue is bound to a class object property. The class object is not a list, but a simple business object. The combobox will not update the class objects property no matter what I do, unless I set the property value in the SelectionChangeCommitted event. The class object bindings work on text boxes, but not with the combobox SelectedValue. I’m thinking the class is missing the implementation of some interface, but it does work with textboxes, etc.

I’m actually working in c# rather than VB but this is the best tutorial I’ve seen on what I’m trying to achieve. I have a couple of questions though…

My scenario is that I have two comboboxes (Countries and Offices), some text fields (contain info about the currently selected office) and a datagridview (contains opening time info about the currently selected office).

I have not set it up to use DataSets at this point, as I wasn’t sure if it would be able to do what I want. I have a DataTable (contains CountryCode and Country) bound to the Countries combobox and just before I bind it, I programmatically add another row at the top for "All Countries". I have a SelectedIndexChanged event handler on Countries that sets a filter on the bindingsource for the Offices combobox or if the "All Countries" option is selected, it clears the Filter. The down side is that when the filter is applied, I haven’t managed to work out a way to get the first displayed item in the Office combobox to be selected and as such, all the info shown in the textboxes (and the datagridview) is for the office that was previously selected rather than the currently displayed office. Obviously, this is not good and so I was thinking of switching it over to use a DataSet in order to get the select working but if I do that, can I get it to display all offices in the Combobox when "All Countries" is selected rather than no offices (as obviously no offices are associated with "All Countries")?

You are really something. I think you are the most popular vb programmer. Good job. So, question: Is there a way that i can use lookup on combo which is in a datarepeater??? This would save me from a lot of trouble

If you set up the binding like in the second scenario in the above example then when you add a new row it looks at the RegionID to determine what to display in the combobox. If the RegionID is System.DBNull by default then you need to make sure the "blank" row you are adding to the combobox’s datasouce has a RegionID of System.DBNull as well.

I really enjoyed reading this article, hoping it would give me a clue to my dilemma. It is a start, but I’m not any closer. Here is the issue: I am giving users a form to enter a new customer record. Once they complete the record (it is validated on that form), they hit a Record button that appends the record to the underlying dataset table. The combo box on the order form is then supposed to be updated with the CustomerID and the CustomerName they have just added, but no such luck. I have verified that the record is making it into the SQL table, I have verified that the new record is getting into the BindingSource for the combo box, but I cannot figure out how to get that value to appear in the drop-down list! At this point I am assuming it requires the sacrifice of two or more small animals….

Here is the code I am using to update the record. Any advice or suggestions you can offer would be GREATLY appreciated! Thanks!

This is an AWESOME article, especially for somebody coming from a strong vba/vb6 background, who is used to data application dev in MS Access, and trying to get adapted to the .NET paradigm. This really cleared up alot of questions for me about working with bindings in .NET, and getting them to perform similar to Access. Thanks alot for including your code too. Kudos!

I’d like a combobox to display the data from a database but the dropdownlist only some predefine elements. I have not figure out how to do that because if I bound the combobox to the database it displays the data fine but the dropdownlist is bound to the database as well.

First Thanks for all the great help. Your videos are a great reference for me. I have a problem when using multiple comboboxes in a datagridview where the first combobox selection is used to filter the second comboboxes selections and the first and second combo boxes are used to filter the third comboboxes selections.

An example would be selecting general ledger account numbers, where the account numbers are made up of 3 fields. Account Number, Sub Account Number and Cost Center.

When adding records to the detail check dispersement file each record requires a valid GL account. In the Datagridview for entering the check detail records I can get everything to work I can even get the filtering for the 3 comboboxes to work. The problem is when adding or changing records and moving from one record to a different record. The Datagridview will crash with an error.

Here is a little more detail:

GL account records:

Acct# Sub# CC Description

3001 001 account 1

3001 002 account 2

3001 003 account 3

3002 account 4

3003 004 401 account 5

3003 005 account 6

When adding records to the check detail datagridview everything works as long as I don’t try and go back to a previous record. If for example I added the first record and used “account 2” and for the second record I used “account 5”. After adding the second record and try to go back to the first record the datagridview crashes with an error. The problem is when you start to enter the first record from the second record the datagridview uses account 5’s Acct#(3003) to filter the Sub# combobox on the first line. As you can see the first detail line has Account 2 Acct#(3001) that does not have a Sub# of “004”, so the filter/relation fails and the datagridview fails.

I have Researched this problem and have not found a viable solution as of yet. If you can help point me in the right direction it would be much appreciated.

What’s the error and stack trace? Have you tried debugging? You’re going to need to make sure that the value that the row has in its field that the combobox value is bound to exists in the list and you aren’t filtering that out.

You may also want to ask your question in the forums where many Microsoft customer support and other people are there to help troubleshoot.

Firstly thanks for your superb videos. For a newby like me they have helped me progress far quicker than would otherwise have been the case.

Been trying out your example of using a lookup dataset with a combo box in a datagrid and everything works great until I close the form when I get the following error:

The following exception occurred in the DataGridView:

System.ArgumentException: DataGridViewComboBoxCell value is not valid

To replace this default dialog please handle the DataError event.

I can add, delete and edit to my heart’s content and save all the changes without a problem until I close the form.

There is no code other than that generated by the designer and curiously I have no problems using lookups on a second laptop where it all works flawlessly. Unfortunately my 12" touch screen tablet is the one I’m trying to write an app for and also the one throwing a wobbler!

Any ideas?

Thanks again for sharing your knowledge and time. I listen to you on your videos so often it’s almost like I know you :-)

I’m not really sure why that’s happening sometimes. You may want to just try adding a handler to the Form’s Close event and setting the BindingSource.DataSource property to Nothing before the form closes.

I encountered the same problem as Joseph said on July 5, 2007, I also found the fix and the cause (in this order).

Problem description: Using VB 2008, I followed similar steps to the first scenario (using the designer instead of writing text). As a result, when navigating the Territories table the values in the Region ComboBox started duplicating. The number of elements in the drop down remained but they were not distinct anymore (eg: Western, Western, Northern, Western). After some debugging I found out that the values in the RegionDescription column of the Region table were actually being modified when I navigated.

The fix: In the properties for the ComboBox go to (DataBindings) and click “…” at the end of the (Advanced) row. In the “Formatting and Advanced Binding” you will see – under Common properties – that you have 2(?!) Bindings: one for the SelectedValue property and another for the Text property. Select the Text property and set the Binding to None.

The cause: When I stared I didn’t drag a “clean” ComboBox from the Toolbox, I dragged the RegionDescription column from the DataSources window (this already created a binding between the Text property and the RegionDescription column). I then used the smart tags, enabled “Use data bound items” and added another binding (the correct one). Result: 2 bindings and the strange behavior.

[Note: I actually encountered the problem on my own DataSet, not a Northwind based one]

Your videos are amazing. I am facing a problem on combobox. I have a form for saving data to db. On that form I have a combobox. The field for the combobox is FK field. The combobox should be field with the respective name rather than ID from the PK table. My problem comes when I add data to the PK table the combobox wont refresh. I tried many ways an I came up updating the combobox. But when I select the dropdown, the form freezes and wont be accessable rather than the combobox. Am using the graphical wizard with dataset, databinding source and table adapter with detailsview. Hoping to help me solve this problem.

Firstly many thanks for all your videos. I have not watched a lot of them, but only the ones when I was in trouble and u made sure that I was bailed out of trouble. And I hope that u do the same this time.

The problem is, I have a database with 6 tables of which 3 are main course namely Tab1, Tab2, Tab3 and each of the other 3 tables namely Tab1Rel, Tab2Rel, Tab2Rel relate to one of Tab1, Tab2, Tab3 respectively in that order. That is data base structure. I have created a winforms application with a tab control holding 3 tab pages TabPage1, TabPage2, TabPage3 and each page displays one main course table data with a related table. For example TabPage1 displays 2 grid views for Tab1 and Tab1Rel tables and like wise for TabPage2 and TabPage3. Now I have achieved this through custom business objects as well, separating data layer , biz logic and presentation layer. Everything is working fine except the fact that I cannot use one single binding navigator for different tab pages and different binding sources. I mean my binding navigator just has one binding source and it seems you cannot change it. I tried the following, without any success:

‘like wise for the remaining tabs…And I am calling these methods in the tabpage_click events for respective tabs. The application runs without any problems and even pulls out and displays the data correctly on the respective tabs. But the binding navigator’s binding source is not changed …no matter what. Also, is tabpage_click the event that is fired when I select a particular tab page or is it something different? That apart, I also tried using 3 different binding navigators for 3 different binding sources and I also have a sample some where on the web that successfully hides/shows either of them based on code at run time. However, the sample does that on the same form without a tabbed interface; which brings me to the question…are the tab pages being notified of the binding navigator outside the tab control? are the tab pages receiving the notifications from the respective binding navigators outside the tab control? I also thought of implementing something like factory design pattern that returns appropriate binding source using a single instance…But that would require frequent reallocation of the objects when the user switches tabs. So having explained my case, I would really appreciate if you could flash a ray of light and save me from wandering in to all possible directions but the right one. I have tried to find some thing on web…but it is just too much for very little something some where….

Great article. I’m working on a general-level Database application using MS Access. I want to list the tables in the Access Database since I might not know all of the tables in a new Access Database. I have an example using SQL Server, but can’t get it translated into OleDb from Sql. One of the probelms I’m having is that the SQL example is able to show the "Database File Name" using the "Connection.Database" property, but the OleDb version just shows "blank" when I try to display the "Connection.Database" property?

The Sql version uses the "Command String": "SELECT table_names FROM Information_Schema.Tables" to get the tables in the SQL Datasbase. Using this Command String results in an error messsage for the OleDb version. Is there an equivalent Command String for OleDb for MS Access databases (using teh OleDb Connection)?

I have attempted to create 6 combo boxes which lookup locational relational data such as Country, Region, Province, City, Town & Street. The selected value from these 6 comboboxes are stored in one master table. The relational structure of this locational dataset is heirachal ie. One country can have many regions, One regions can have many Province etc etc. I need to be able to populate the child records retrieved in each of the successive combo box selections and attempted this using the selected index changed event for each combo to retrive the appropriate records for its child in the lower combo. Each time i new to a new master record the child combos fail to retrieve the exact display member selected in the master. Any ideas would be appreciated.

I dont really know where am going wrong, But, I created a Database with Access 2007 (accdb) did the Relationships (Referential Intergrity…) Did my best to leave nothing out. Values are OK, Autonumber on the Source table (TermID as Autonumber) Data (Term), The Next table has the Foreign Key, FKTermID as Number, I followed the Steps above, Using a ComboBox that Looks up my first table, Exacly as the Steps are. The Combobox brings up the Values Once i select, It just wont save the whole record or loose focus, I wont leave the combobox, Its driving me crazy its now my 4th hour am giving up.

I've read this article, and indeed watched most of your Forms Over Data videos, and they have been a tremendous help. However, I've come up against what looks at first like a simple problem, and is very similar to your opening example, but I've been unable to nail it completely.

Imagine a very simple wine database with three tables – Wines, Regions and Countries. Each Wine belongs to a Region such as "Bordeaux", which in turn belongs to a Country, "France" in this case. The Wines/Regions relationship and the Regions/Countries relationship are both 1-to-Many and enforced by referential integrity in the database. All three tables are in the same Dataset. I'm using VS2005.

Let's say I want to show this data on a simple form using a single TextBox and two ComboBoxes.

I want to select a Wine onto the form and have the Region display in a ComboBox. Easy so far. At the same time I want to display the Country in a ComboBox, but of course the Country is dependent on the Region. Still not complicated yet and easily accomplished with binding, but, I want the ComboBox_Region to only display the Regions for the current Country. If I select a different Country I want the Regions ComboBox to reflect the new Regions in that Country.

Even this I have had working of sorts, but doing the whole thing with Binding results in DataSet.HasChanges() being always true if I move between Wine records (although no data has actually changed). I've tried to move away from using Bindings into the realms of handling the ComboBox events directly, but two weeks later and I feel I'm out of ideas. I also feel like I've read the entire Internet!

Any pointers or suggestions would be gratefully received. I can't believe what I'm trying to do is particularly unusual or complex.

I've read this article, and indeed watched most of your Forms Over Data videos, and they have been a tremendous help. However, I've come up against what looks at first like a simple problem, and is very similar to your opening example, but I've been unable to nail it completely.

Imagine a very simple wine database with three tables – Wines, Regions and Countries. Each Wine belongs to a Region such as "Bordeaux", which in turn belongs to a Country, "France" in this case. The Wines/Regions relationship and the Regions/Countries relationship are both 1-to-Many and enforced by referential integrity in the database. All three tables are in the same Dataset. I'm using VS2005.

Let's say I want to show this data on a simple form using a single TextBox and two ComboBoxes.

I want to select a Wine onto the form and have the Region display in a ComboBox. Easy so far. At the same time I want to display the Country in a ComboBox, but of course the Country is dependent on the Region. Still not complicated yet and easily accomplished with binding, but, I want the ComboBox_Region to only display the Regions for the current Country. If I select a different Country I want the Regions ComboBox to reflect the new Regions in that Country.

Even this I have had working of sorts, but doing the whole thing with Binding results in DataSet.HasChanges() being always true if I move between Wine records (although no data has actually changed). I've tried to move away from using Bindings into the realms of handling the ComboBox events directly, but two weeks later and I feel I'm out of ideas. I also feel like I've read the entire Internet!

Any pointers or suggestions would be gratefully received. I can't believe what I'm trying to do is particularly unusual or complex.

This is great, but would you mind writing the _platform_ in the _header_ of your blog please. I work a lot in .NET but there are some poor souls who have to maintain legacy VB6 projects. While trouble-shooting nothing is more annoying than reading through pages of stuff that only through inference turn out to be for the wrong version/platform. VB6 and VB.NET turn up concurrently in Google searches.

I am having problems with populating Combo Boxes within Data Repeaters. As mentioned in this post, the post at forums.microsoft.com/…/ShowPost.aspx may address my problem; only problem is this post is no longer available. Any clue what this article suggests?

Can you put together a short video of this tutorial please? That would be much easier for some of us to follow. By the way this is really a nice one. Thank you for putting it up for those of us who are just learning.

i create one form in vb.net with the help of msacces data base
in my form i have two combo box one is state and second is city .the city combo box is depended upon state combo box
how can i retrive city name by the choosing state combo box

I bind data from a lookup table to combo box and one field of the lookup table contains Latin,when i select from the list it returns to the first item,but it works fine when i change the field to English,so please help me what is the problem?