Say you have 2 ComboBox controls on a Windows forms. Those 2 ComboBox are related in a parent-child (or master-detail) relationship. For example, you could have a ComboBox that contains a list of countries for the user to choose from (the parent) and another ComboBox (the child) that contains the list of states or provinces related to the countries.

This month, I will show you 3 solutions to this problem I have seen in the last few months: a very bad solution, a popular solution, and a (I personally consider) very good solution.

The goal

Intuitively, you will say that you will need to filter the second combobox to display on valid values according to what is selected into the first combobox. Sadly, it seems that not all programmers I met think that way!

The demo application

The demo application is, as always, very simple. I have separated it so that you don’t need to ask yourself if the portion of the code is related to example you are focusing on or not.

First, there is a class (named cShared) that contains a single (shared) method to create a dataset that contains two tables (one for the countries and a second for the states). This method will be used in the three examples. Frequent readers of my articles will find that this method has been reused in at least times. You will need to download the demo application to be able to see that method.

A first form (named fMenu) displays a menu to ease the transition from one example to another. The only code you will find in there is to display the test forms.

Finally, there are three other forms (named fExample1, fExample2, and fExample3) implementing each solution. Each of these three forms is showing exactly the same GUI as shown in Figure 1.

Figure 1: The GUI

The button titled “Show selected values” is exactly the same into each example forms so I will show you the code here once for all. The code behind this button is the following:

Dim intSelectedCountry As Integer
Dim intSelectedState As Integer

If cboCountry.SelectedValue Is Nothing Then
    intSelectedCountry = -1
Else
    Try
        intSelectedCountry = Convert.ToInt32(cboCountry.SelectedValue)
    Catch ex As Exception
        intSelectedCountry = -1
    End Try
End If

If cboState.SelectedValue Is Nothing Then
    intSelectedState = -1
Else
    Try
        intSelectedState = Convert.ToInt32(cboState.SelectedValue)
    Catch ex As Exception
        intSelectedState = -1
    End Try
End If

MessageBox.Show("The ID of the selected Country is : " + _
                intSelectedCountry.ToString + Environment.NewLine + _
                "The ID of the selected State/Province is : " + _
                intSelectedState.ToString, _
                "Demo application", _
                MessageBoxButtons.OK, _
                MessageBoxIcon.Information)

Solution 1: The bad solution

The first method I have seen to implement this problem is to show all available values into the child ComboBox. The control shows all the rows of the DataTable no matter which country is selected into the parent ComboBox. After the user has made is inputs, you normally have to validate if the selected child (the state in this example) is related to the parent (the country). I have seen one application that is not even validating the relation. The programmer relies on the user to input related data. You and I know that we cannot rely that much on user!

Even if I would never implement (and I would deny you if you do that), I will give you here the code to load and initialize the controls (this code can be found into the Load event of the form):

'Loads the data
Dim dsData As DataSet = cShared.CreateDataSet()

'Binds the combos
With cboCountry
    .DisplayMember = "Description"
    .ValueMember = "ID"
    .DataSource = dsData.Tables("Countries")
End With

With cboState
    .DisplayMember = "Description"
    .ValueMember = "ID"
    .DataSource = dsData.Tables("States")
End With

First, the CreateDataSet method is called to create the dataset that contains the two DataTables we are interested in.

The first ComboBox control is then bound to a table. You first need to set the DisplayMember property to the field name of the bound table that you want the user to see in ComboBox. You also need to set the ValueMember property which is the value you normally want as a programmer to store into a database (it is often the primary key of that value).

Finally, you need to set the DataSource property. In my example, I set this property to the Countries DataTable of the DataSet object.

The second ComboBox is initialized exactly the same way except that it is using the States DataTable. Remember that this ComboBox will always show all the states no matter what is selected into the parent ComboBox.

Solution 2: The popular solution

So the previous solution is far from perfect because the values of the child ComboBox are not filtered to show only the values related to the selected parent. Ok but how could we filter those values?

There are many ways of doing it but the solution you have probably seen the most is to handle an event of the parent ComboBox like the SelectedIndexChanged event, filter the DataTable to show with the value of the parent ComboBox and initialized the child ComboBox with these filtered rows.

The code to load and initialized the parent ComboBox is almost the same as of the previous example:

'Loads the data
mdsData = cShared.CreateDataSet()

'Bind the countries combo
With cboCountry
    .DisplayMember = "Description"
    .ValueMember = "ID"
    .DataSource = mdsData.Tables("Countries")
End With

That means that we will need to initialize that child ComboBox elsewhere. This place has to be in an event of the parent ComboBox that is triggered when it is modified. I suggest you to use either the SelectedIndexChanged event or the SelectionChangeCommited event. This is the code you can find there:

Dim dtvStates As DataView = mdsData.Tables("States").DefaultView
dtvStates.RowFilter = "IDParent = " + cboCountry.SelectedValue.ToString

With cboState
    .DisplayMember = "Description"
    .ValueMember = "ID"
    .DataSource = dtvStates
End With

I have to admit that I have used this solution very often. But don’t stop reading here, something better is coming!!!

Solution 3: A better solution

So you decided to continue your reading. This is a very great thing because this other solution requires even less code then the second one and the child ComboBox will be filtered.

The good thing with this solution is that it requires less code then every other solution and it works! Isn’t it beautiful?

The idea was given to me by a friend who told me that he was tired of always handle events like the solution 2 to create good entry form for his users. He then asked me if we could use the DataRelation like we do for DataGrids. I honestly answered him that I didn’t know but I couldn’t see why it wouldn’t be working. It is not as easy as for DataGrids but with some “hacks”, it is working correctly.

You first need to create a relation on your DataSet like this:

'Loads the data
Dim dsData As DataSet = cShared.CreateDataSet()

'Creates a relation between the 2 DataTables
dsData.Relations.Add("relCountryStates", _
                dsData.Tables("Countries").Columns("ID"), _
                dsData.Tables("States").Columns("IDParent"))

The relation is created on the other line. This line reads as follows: create a relation and name it relCountryStates (and we will use that name later). In this relation, use the ID column of the Countries table as the parent and relate the rows of the States table using the IDParent column. All the magic lies here so be very careful when you establish your DataRelation. This syntax can be a bit unpleasing at first but it is finally pretty easy when you spend 2 minutes trying to understand how to read it.

We are now ready to bind the ComboBox controls. Let’s start with the first one. Here is the code:

With cboCountry
    .DisplayMember = "Countries.Description"
    .ValueMember = "ID"
    .DataSource = dsData
End With

Last but not least, we can bind the child ComboBox using this code:

With cboState
    .DisplayMember = "Countries.relCountryStates.Description"
    .ValueMember = "ID"
    .DataSource = dsData
End With

Don’t look any further for more code, there isn’t anymore. No need to handle any kind of events! Simply establish a relation and bind correctly and your set.

Conclusion

I concur that the way Microsoft has implemented the relation between ComboBox controls is really not obvious. It seems to me like a patch they applied the day before going RTM.

If you have ever worked with the DataGrid, you will surely remember that you can set the DataMember property of the child control directly to the relation object which is much clearer and nicer.

Anyway, now that you know how to relate them, let’s just use it!

I hope you appreciated the topic and see you next month.