Combobox Databinding Woes

UPDATE: This post is for Visual Studio 2003. For Visual Studio 2005 content please see this post.

I've been noticing a lot of questions on the newsgroups related to Winforms databinding and the combobox and I thought I'd post something up here to help people out. There are a couple very common scenarios in which people use the combobox:

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.

As usual, the trick is setting up the data binding properly and using the currency managers. 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:

Private Const SQL_CONNECTION_STRING As String = _
 "Data Source=localhost;" & _
 "Initial Catalog=Northwind;" & _
 "Integrated Security=SSPI"

Try
 Dim ds As New DataSet
 Dim cnn As New SqlConnection(SQL_CONNECTION_STRING)

 Dim da As New SqlDataAdapter("SELECT * FROM Region", cnn)
 da.Fill(ds, "Region")

 da = New SqlDataAdapter("SELECT * FROM Territories", cnn)
 da.Fill(ds, "Territories")

 ds.Relations.Add("Region_Territories", _
  ds.Tables("Region").Columns("RegionID"), _
  ds.Tables("Territories").Columns("RegionID"))

 ds.DataSetName = "RegionTerritories"

Catch Exp As Exception
 MessageBox.Show(Exp.Message)
End Try

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 you'll need to set up the following properties on your Combobox:

Me.ComboBox1.DataSource = ds.Tables("Region")
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, you'll need to set up a data binding:

Me.ComboBox1.DataBindings.Add("SelectedValue", ds, "Territories.RegionID")

Okay we're all set, right? Well... not exactly. You'll also need to call EndCurrentEdit on the territories currency manager at some point in order to write the value back to the dataset. Depending on the style of your form you could do this from an "Update" button (similarly you could call CancelCurrentEdit from a Cancel button). However, when working with datasets I find it much easier to use the dataset methods for Accepting/Rejecting row changes. So 99.99% of the time I just call EndCurrentEdit from the SelectedIndexChanged event handler of the combobox itself:

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, _
  ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
 '-- This forces the comboxbox's value to be written to the dataset.
 Dim cm As CurrencyManager = DirectCast(Me.BindingContext(ds, "Territories"), CurrencyManager)
 cm.EndCurrentEdit()
End Sub

The cool thing (or anoying thing depending on how you look at it) about EndCurrentEdit/CancelCurrentEdit on the currency managers 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. (It would be *really* nice if the currency manager had a property for this like "AlwaysCommitChanges" so we wouldn't have to call EndCurrentEdit all over the place.)

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 Region_Territories. The combobox properties can be set up just like the first example:

Me.ComboBox1.DataSource = ds.Tables("Region")
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 you'll need to set up the Datasource and DataMember properties of the DataGrid using the relation path. It is very important to get the path right otherwise the datagrid will not filter automatically as we move the position in the parent:

Me.DataGrid1.DataSource = ds
Me.DataGrid1.DataMember = "Region.Region_Territories"

Okay we're all set, right? Well... not exactly (I knew you were going to say that <g>). Unfortunately a combo box won't move the CurrencyManager's position for you like list controls do (Grids, Listboxes). So the trick is to get a hold of the parent currency manager and move the position manually by handling the combobox's SelectedIndexChanged event:

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, _
  ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
 Dim cmParent As CurrencyManager = DirectCast(Me.BindingContext(ds, "Region"), CurrencyManager)
 cmParent.Position = Me.ComboBox1.SelectedIndex
End Sub

Because you set up the datagrid to display the related territories by specifying the relation path Region.Region_Territories as the DataMember, the grid will automatically filter it's rows based on the selected parent row in the combobox.

The currency managers are your friends. You can obtain currency managers for any table/path in your dataset even if there are no control bindings set. You can also use the currency managers to disable controls when the position moves to -1 (no records) Here's an example. The currency managers maintain dataviews so you can easily access the current DataView as well as the current DataRowView:

Dim dv As DataView = DirectCast(cmParent.List, DataView)
Dim dvr As DataRowView = DirectCast(cmParent.Current, DataRowView)

Complex winforms databinding can take some practice, but once you get the hang of it you can create some very cool forms. Have fun!