Tips on Related Data Binding and ComboBoxes

Tips on Related Data Binding and ComboBoxes

Rate This
  • Comments 47

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 As New 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).

Private Sub TerritoriesBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TerritoriesBindingNavigatorSaveItem.Click

 

    Me.Validate()

    Me.TerritoriesBindingSource.EndEdit()

    Me.TerritoriesTableAdapter.Update(Me.NorthwindDataSet.Territories)

 

End Sub

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:

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged

     

    Me.RegionBindingSource.Position = Me.ComboBox1.SelectedIndex

End Sub

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:

Public ReadOnly Property CurrentRegion() As NorthwindDataSet.RegionRow

    Get

        If Me.RegionBindingSource.Position > -1 Then

            Return CType(CType(Me.RegionBindingSource.Current, DataRowView).Row, NorthwindDataSet.RegionRow)

        Else

            Return Nothing

        End If

    End Get

End Property

We can use this property like so:

Dim region As NorthwindDataSet.RegionRow = Me.CurrentRegion

 

If region IsNot Nothing Then

    MsgBox("The selected Region is:" & region.RegionDescription)

End If

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!

Leave a Comment
  • Please add 2 and 3 and type the answer here:
  • Post
  • As someone looking towards .NET from VFP, I can't believe that using lookup lists from data is still a "difficult" issue!

    No matter how you look at it, .NET still has a long way to go with data handling!

  • Hi Jamie,

    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.

  • Hello! Good Site! Thanks you! hzanrkhjvggq

  • 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?

    Huge thanks if you've got any.

  • How come when using the you select something in the dropdown, you then can not see it in the list.

    What could i be doing wrong to cause this?

    dropdown example:

    item1

    item2

    item3

    then when i select item 3 and look at the dropdown again it will show

    item3

    item2

    item3

    --Joseph

  • Hi Eric,

    You can handle the CurrentChanged event on the child's BindingSource in order to react to the filter changing.

    -B

  • Hi Joseph,

    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.

    -B

  • 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.

    Any ideas?

  • Dear Beth,

    I have 2 forms and on each form datagridview and when I press enter on grid2 that pick data from rows and put them in grid 1.

    The form2 is like search form!

  • Hi Beth,

    This is a great Tutorial for someone like me coming back to VB after a couple of versions away.  How would you feel about re-doing it within the context of LINQ and Visual Studio 2008?

    Regards,

    Pete

    http://www.channelcomputing.co.uk

  • Hello,

    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.

    Thanks

    Barry

  • In a previous post I showed how to set up related data binding using ComboBoxes against DataSets and

  • In a previous post I showed how to set up related data binding using ComboBoxes against DataSets and

  • Two recent LINQ databinding with ComboBoxes posts from Beth Massi of the Visual Basic team. Creating

Page 1 of 4 (47 items) 1234