Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

[jmcilhinney]

How do you filter child data in one control based on a selection from parent data in another control? Like this:

1. Create a new Windows Forms Application project.
2. Add two ComboBoxes to the form.
3. Add two BindingSources to the form.
4. Add the following code:

Private Sub Form1_Load(ByVal sender As Object, _
                       ByVal e As EventArgs) Handles MyBase.Load
    'Get the data.  The DataSet must contain a Parent table,
    'a Child table and a ParentChild relation between them
    Dim data As DataSet = Me.GetDataSet()

    'Bind the parent source to the parent table.
    Me.BindingSource1.DataSource = data
    Me.BindingSource1.DataMember = "Parent"

    'Bind the child source to the relationship.
    Me.BindingSource2.DataSource = Me.BindingSource1
    Me.BindingSource2.DataMember = "ParentChild"

    'Bind the parent control to the parent source.
    Me.ComboBox1.DisplayMember = "Name"
    Me.ComboBox1.ValueMember = "ID"
    Me.ComboBox1.DataSource = Me.BindingSource1

    'Bind the child control to the child source.
    Me.ComboBox2.DisplayMember = "Name"
    Me.ComboBox2.ValueMember = "ID"
    Me.ComboBox2.DataSource = Me.BindingSource2
End Sub

Private Function GetDataSet() As DataSet
    Dim data As New DataSet
    Dim parent As DataTable = Me.GetParentTable()
    Dim child As DataTable = Me.GetChildTable()

    data.Tables.Add(parent)
    data.Tables.Add(child)

    'Add a relationship between the ID of the parent
    'table and the ParentID of the child table.
    data.Relations.Add("ParentChild", _
                       parent.Columns("ID"), _
                       child.Columns("ParentID"))

    Return data
End Function

Private Function GetParentTable() As DataTable
    Dim table As New DataTable("Parent")

    With table.Columns
        .Add("ID", GetType(Integer))
        .Add("Name", GetType(String))
    End With

    With table.Rows
        .Add(1, "Parent1")
        .Add(2, "Parent2")
        .Add(3, "Parent3")
    End With

    Return table
End Function

Private Function GetChildTable() As DataTable
    Dim table As New DataTable("Child")

    With table.Columns
        .Add("ID", GetType(Integer))
        .Add("ParentID", GetType(Integer))
        .Add("Name", GetType(String))
    End With

    With table.Rows
        .Add(1, 1, "Child1")
        .Add(2, 1, "Child2")
        .Add(3, 1, "Child3")
        .Add(4, 2, "Child4")
        .Add(5, 2, "Child5")
        .Add(6, 2, "Child6")
        .Add(7, 3, "Child7")
        .Add(8, 3, "Child8")
        .Add(9, 3, "Child9")
    End With

    Return table
End Function

Now run the project and make selections from the parent list in the first ComboBox. Watch the child list filter automatically as you do.

You can use DataGridView controls instead of ComboBoxes if you like. In fact, because the work is done by the BindingSource components, you can use any controls at all that you can bind to a BindingSource.

Now, before anyone says that that's all well and good but I'm creating the DataTables myself and they are getting their data from a database, that makes absolutely no difference whatsoever. Note that in my Load event handler I call GetDataSet to get a DataSet. All that matters at that point is that a DataSet with the appropriate format is returned. Where that DataSet comes from is irrelevant to the code that uses it, so you can implement the GetDataSet function in any way you like. Obviously, if your tables and relation have different names then you should adjust my code to use those names. I feel that this disclaimer is required because it happens with monotonous regularity that I post example code an someone just copies and pastes it and then wonders why it doesn't work. This is an example only to illustrate a principle. If you want to implement that principle then you can either write your own code from scratch or else use mine as a starting point and adjust it as needed.

[Jenner]

Nice trick. I didn't know you could bind a control to a relation like that.

[sunhpj]

Hi jmcihinney, your link really helps.

However, it is not exactly what I want. It just seems a filter

1. Adding the relation for parent and child require unique id in parent table. But I don't want this constraint. Since the parent id is only used to filter the rows in child, i don't know why it need to be unique

2. In your case, user click a row in Parent combobox (I try datagridview1 here), and then only rows match the parent id will be displayed in "child" datagridview2. But what I want is display all in dgv2(child), but set the row(s) in dgv2 that match the parent id in selected row in dgv1(parent) as selected.
At the same time, if user select a row in dgv2, the selection in dgv1 is also located accordingly.

[jmcilhinney]

Hi jmcihinney, your link really helps.

However, it is not exactly what I want. It just seems a filter

1. Adding the relation for parent and child require unique id in parent table. But I don't want this constraint. Since the parent id is only used to filter the rows in child, i don't know why it need to be unique

2. In your case, user click a row in Parent combobox (I try datagridview1 here), and then only rows match the parent id will be displayed in "child" datagridview2. But what I want is display all in dgv2(child), but set the row(s) in dgv2 that match the parent id in selected row in dgv1(parent) as selected.
At the same time, if user select a row in dgv2, the selection in dgv1 is also located accordingly.

1. The ParentID column in the Child table has no unique constraint. This is a one-to-many relationship. Each ParentID value can appear only once in the Parent table but can appear many times in the Child table. If the same ParentID appeared more than once in the Parent table then how could you tell which record was the actual parent of a Child record with that value? The whole point of the ParentID is to uniquely identify a Parent record. The relationship is to uniquely identify a Child record's parent.

2. There's no way to do that automatically. You'd basically have to bind the two grid's to the DataTable's independently. When the user makes a selection in the parent grid you can get the selected row, call its GetChildRows method to get all the related rows, then loop through the child grid and select those records. To go the other way you can call GetParentRow.

[mungle_back]

I used the above code and it worked great - very straightforward and easy to use. I'm new to Visual Studio 2005, so I'm still trying to figure out how everything connects.

One question though. On my form, I want to have multiple datagridviews which are based off of the selection in the child combo box. Would you use similar code to what you have above, creating a relation between the child table and the datagrid tables, and could you do this within the same function (GetDataset, in your example)? Thanks.

[jmcilhinney]

I used the above code and it worked great - very straightforward and easy to use. I'm new to Visual Studio 2005, so I'm still trying to figure out how everything connects.

One question though. On my form, I want to have multiple datagridviews which are based off of the selection in the child combo box. Would you use similar code to what you have above, creating a relation between the child table and the datagrid tables, and could you do this within the same function (GetDataset, in your example)? Thanks.

Not a problem. The controls used to display the parent and child data don't have to be the same. Also, you can have as many levels of relationship as you like. You can have a parent table, a child, a grandchild, a great grandchild and so on. This thread demonstrates the principle between one pair of tables. There can be as many pairs as you like with as much overlap as you like, all in the same DataSet.

[szlamany]

@jm - hope you don't mind me putting this question into this thread - if you do I'll delete it and start a new thread...

At any rate - I've got a more complex relationship and I was wondering if this would be possible with the method you describe here. I'll attach a mock-up of the form we are considering using for this.

Basically a binding navigator is used to flip through all the VENDOR's in a table. Each vendor has child records in another table - that fall into 1 of 4 categories - Translater, Proofreader, Interpretor and Foreign associate.

There can be many child records for a VENDOR for each of these categories based on the "language pair" being represented (they can translate from English-to-Spanish and from Eng-to-Portuguese, for example).

We were thinking of having a tab control for each of these categories - with it's own bind-nav control.

Would a WHERE clause simply put the right category onto the correct tab control?

Am I headed down the right path here?

Thanks in advance

[jmcilhinney]

@jm - hope you don't mind me putting this question into this thread - if you do I'll delete it and start a new thread...

At any rate - I've got a more complex relationship and I was wondering if this would be possible with the method you describe here. I'll attach a mock-up of the form we are considering using for this.

Basically a binding navigator is used to flip through all the VENDOR's in a table. Each vendor has child records in another table - that fall into 1 of 4 categories - Translater, Proofreader, Interpretor and Foreign associate.

There can be many child records for a VENDOR for each of these categories based on the "language pair" being represented (they can translate from English-to-Spanish and from Eng-to-Portuguese, for example).

We were thinking of having a tab control for each of these categories - with it's own bind-nav control.

Would a WHERE clause simply put the right category onto the correct tab control?

Am I headed down the right path here?

Thanks in advance

If I'm reading that correctly then you just have what I've described but four times. You would fill one parent table and four child tables. Each of the child tables would be filled from the same source table with, as you say, a WHERE clause to limit the result set to a single category.

Your DataSet will then have four DataRelations that you'll bind each of the child BindingSources to.

[trevorjeaton]

just a quick shout out to jmcilhinney - as per the suggestions, I had to tweak this code a little to get it pulling from a sql database, and after doing so, it does exactly what i was after - two thumbs up and a big thanks!

[trevorjeaton]

Here's a question to add to this by the way if anyone can contribute - I'm looking to figure out how to deal with a selectedindexchanged event on the child of this binding so i can pull additional values instead of being restricted to the single value member......

in my iteration of this concept, i'd like to be able to process on a valuemember in addition to ID.

normally with a table adapter i would just call the following:

TableAdapter.GetData.Item(combobox2.SelectedIndex).Field_I_Want_To_Work_With

but in this case its not a tableadapter nor is the selected index of the combobox reflective of the record position as a whole......so it will pull inaccurate data....

any help would be appreciated...

[trevorjeaton]

okay i "sorta" figured it out.....i just changed me.combobox2.valuemember to the field i need to do the processing on - but i'm of the opinion that's messy....(what if i had 100 fields to process on?) - does anybody else have alternatives?

[jmcilhinney]

Here's a question to add to this by the way if anyone can contribute - I'm looking to figure out how to deal with a selectedindexchanged event on the child of this binding so i can pull additional values instead of being restricted to the single value member......

in my iteration of this concept, i'd like to be able to process on a valuemember in addition to ID.

normally with a table adapter i would just call the following:

TableAdapter.GetData.Item(combobox2.SelectedIndex).Field_I_Want_To_Work_With

but in this case its not a tableadapter nor is the selected index of the combobox reflective of the record position as a whole......so it will pull inaccurate data....

any help would be appreciated...

The SelectedItem of the ComboBox is the entire bound item, so you simply cast it as the appropriate type and then get whatever property of column value you want. For instance, if you've bound a DataTable then each item is a DataRowView, therefore the SelectedItem is a DataRowView. As such you can get the SelectedItem, cast it as type DataRowView and then get any field you want, e.g.

Dim row As DataRowView = DirectCast(myComboBox.SelectedItem, DataRowView)
Dim firstName As String = CStr(row("FirstName"))
Dim lastName As String = CStr(row("LastName"))

[trevorjeaton]

as usual, you're spot on.....my compliments on your extensive knowledge......you are a great help to people "coming up" in VB.....don't ever leave vbforums - we love ya

[trevorjeaton]

okay - last question, i promise ;-).....how can the selectedindexchanged event of the parent control trigger the selectedindexchanged event of the child control in this example?

here's an example using twist ties:

my parent control is a master material name and my child control is a list of different sizes of that master material (ie master material = "twist tie 1" and child material = "twist tie 1, one inch length", "twist tie 1, two inch length", "twist tie 1, three inch length", etc etc)

i have a selectedindexchanged event on the child combobox that works great (it changes a length textbox.text value to reflect the length of the material - and that is thanks to the directcast solution above). But when i switch the master material combobox (the parent) to "twist tie 2", of course the child combobox default entry now is "twist tie2, 4 inch length" for example - and the length textbox.text will still reflect the previously selected child combobox value.

my proposed solution is that in the selectedindexchanged event of the parent combobox, i want to call the selectedindexchanged event of the child combobox and that way regardless of which combobox is changed, the textbox.text will be updated to reflect the current child value....but i'm not sure if that's possible......

sorry its a little long winded and i'm trying to keep this thread as clean and as relevant possible.....

[jmcilhinney]

You don't call an event. If you want to do something on the SelectedIndexChanged of both ComboBoxes then that's exactly what you should do: handle both SelectedChangedIndex events and do the same thing on both. You might have a single method that handles both events and performs the required task or you might have two separate methods that then both call the same third method that does the work.

[trevorjeaton]

does the selectedindexchanged event fire on the child combobox when the relationship to the child combobox is refreshed?

i'm deducing that because a different value is now displayed in the child combobox after the change to the parent combobox via the relationship, that 'some' event must be triggered.....and in that event is where i'd like to place my code to fill my textbox.text based on the new child value.....and of course i'm not quite experienced enoughto put all that together on what that event is.....

right now it appears as though selectedindexchanged on the child only fires via a user initiated click and not when the parent combobox updates the relationship. would that fall under the displaymemberchanged event?

[trevorjeaton]

okay i'm half way there - its "working" when i add my code to the selectedvaluechanged event as well as the selectedindexchanged event, but on form launch, my try catch statement grabs "object reference not set to an instance of an object"

[trevorjeaton]

okay fixed - selectedvalue was the key - the object reference errors were because originally my form was using table adapters and i forgot to set their databindings back to nothing when i switched to this code, so they were calling empty items, hence generating the null references - thanks once again to jmcilhinney

[ZoeWashburn]

Hi there!

Question for you... I'm trying to expand the concept of this code to multiple parent/child comboboxes. In a nutshell, I have one parent combobox that has one child, and a second that has three children that are unrelated to one another, and all of these are on a single form. Do I need to do each parent and its offspring separately? Or can I get the data for both parent boxes in a single datatable, and all the children in another datatable?


Thanks in advance,
Zoe

[jmcilhinney]

Hi there!

Question for you... I'm trying to expand the concept of this code to multiple parent/child comboboxes. In a nutshell, I have one parent combobox that has one child, and a second that has three children that are unrelated to one another, and all of these are on a single form. Do I need to do each parent and its offspring separately? Or can I get the data for both parent boxes in a single datatable, and all the children in another datatable?


Thanks in advance,
Zoe

You have six separate tables and four separate relationships. You don't have to do anything differently. You simply do as I've done multiple times.

[ZoeWashburn]

You have six separate tables and four separate relationships. You don't have to do anything differently. You simply do as I've done multiple times.

Ok, I've set all that up and I'm getting an error "Parent columns and Child columns don't have type-matching columns." I'm pulling these from a SQL database, so all the parent tables have integer or tinyint ID columns, and I've made sure the columns added to the child tables are integer as well.

Here's the code where I set up the relationships:

  Private Function GetDataSet() As DataSet
            connection.Open()
            Dim ds As New DataSet
            Dim parent_cp As DataTable = Me.GetParentTable_CP()
            Dim child_market As DataTable = Me.GetChildTable_Market()
            Dim parent_etype As DataTable = Me.GetParentTable_EType()
            Dim child_ecat As DataTable = Me.GetChildTable_ECat()
            Dim child_prov As DataTable = Me.GetChildTable_Prov()
            Dim child_crr As DataTable = Me.GetChildTable_CRR()
            ds.Tables.Add(parent_cp)
            ds.Tables.Add(child_market)
            ds.Tables.Add(parent_etype)
            ds.Tables.Add(child_ecat)
            ds.Tables.Add(child_prov)
            ds.Tables.Add(child_crr)


            'Add a relationship between the ID of the parent
            'table and the ParentID of the child table.
            ds.Relations.Add("ParentChild", _
                                       parent_cp.Columns("CPID"), _
                                       child_market.Columns.Add("ParentID", GetType(Integer)))
            ds.Relations.Add("ParentChild", _
                                        parent_etype.Columns("ETypeID"), _
                                        child_ecat.Columns.Add("ParentID", GetType(Integer)))
            ds.Relations.Add("ParentChild", _
                                        parent_etype.Columns("ETypeID"), _
                                        child_prov.Columns.Add("ParentID", GetType(Integer)))
            ds.Relations.Add("ParentChild", _
                                        parent_etype.Columns("EtypeID"), _
                                        child_crr.Columns.Add("ParentID", GetType(Integer)))

            Return ds
            connection.Close()
    End Function

Is there a vb.net data type that matches tinyint? Or do I need to change all my tinyint ID columns to integer? Or am I missing something else entirely?

Thanks much in advance,
Zoe

[jmcilhinney]

@ZoeWashburn

bigint = Long
int = Integer
smallint = Short
tinyint = Byte

If your tables have relations between them in the database then all the columns must be compatible data types. That means that either you don't have foreign keys in the database, you're populating your DataTables incorrectly or you're creating your DataRelations incorrectly. The fact that you're using the same name for all four relations is a problem for a start.

Maybe you should just create a Data Source. That way, all the adapters, tables and relations are created for you.

[ZoeWashburn]

@jmcilhinney,

I've modified the relationships to use the keys in the database (sorry, that was a total dumbass moment, should've set it up that way to begin with). I've verified all the data types match, and all the relationships are set up but one that the database isn't cooperating with me on. It keeps telling me I can't create the relationship because it conflicts with the relationship I'm trying to create. Grrr.

Anyway... yeah, a data source might be a simpler solution here. I'm getting really tired of it telling me the data types don't match when I've gone to painstaking lengths to ensure that they do match.

Thanks,
Zoe

[lechip]

What if I what to view the PARENT of an actual record?

[jmcilhinney]

What if I what to view the PARENT of an actual record?

This data-binding technique is about filtering child records. There's nothing built in that I aware of that will display or select a parent record based on selection of a child.

It's not too hard to do with a small amount of code though. Once you've got a child DataRow, you can call its GetParent method to get the parent DataRow. Assuming that you have two BindingSources bound to two DataTables with a DataRelation between them, that might look like this:

Private Sub childBindingSource_CurrentChanged(sender As Object, e As EventArgs) Handles childBindingSource.CurrentChanged
    Dim childRow As DataRow = DirectCast(Me.childBindingSource.Current, DataRowView).Row
    Dim parentRow As DataRow = childRow.GetParentRow("ParentChildRelation")
    Dim parentIndex As Integer = Me.parentBindingSource.Find("ParentID", parentRow("ParentID"))
 
    Me.parentBindingSource.Position = parentIndex
End Sub

[lechip]

This data-binding technique is about filtering child records. There's nothing built in that I aware of that will display or select a parent record based on selection of a child.

It's not too hard to do with a small amount of code though. Once you've got a child DataRow, you can call its GetParent method to get the parent DataRow. Assuming that you have two BindingSources bound to two DataTables with a DataRelation between them, that might look like this:

Private Sub childBindingSource_CurrentChanged(sender As Object, e As EventArgs) Handles childBindingSource.CurrentChanged
    Dim childRow As DataRow = DirectCast(Me.childBindingSource.Current, DataRowView).Row
    Dim parentRow As DataRow = childRow.GetParentRow("ParentChildRelation")
    Dim parentIndex As Integer = Me.parentBindingSource.Find("ParentID", parentRow("ParentID"))
 
    Me.parentBindingSource.Position = parentIndex
End Sub

Tnx for the help!
Yet the Cast is not allowed.
I tried using the specific dataTable row too, again, no valid cast.
Tnx for getting off topic for me!

[jmcilhinney]

Tnx for the help!
Yet the Cast is not allowed.
I tried using the specific dataTable row too, again, no valid cast.
Tnx for getting off topic for me!

If the cast is not allowed then that means that the Current property doesn't return a DataRowView, which means that you haven't bound a DataTable.

[lechip]

You're absolutely right, the exceptions actually happens... when I'm binding the DataTable!!! I always get to this loops where the answer gets me back to the problem *facepalm*

[lechip]

Hey I figured it out, I just needed to initialize everything carefully!
Tnx for the help as always jmcilhinney!

[chrisguk]

@jm

Fantastic thread and a great read, I understand the Parent/Child concepts far better than I ever did.

Many thanks for your time and effort in putting your answers together : A five start Contributor

[bodylojohn]

Dear JMC...

I tried your code and it works perfect. Thanks for this.

However I have one small question:

When I select an item in the parent combobox, automatically the first item in the child combobox is shown. Is it possible to set the selected index of the child combobox to -1 (or another solution)?

Thanks in advance!

[jmcilhinney]

Dear JMC...

I tried your code and it works perfect. Thanks for this.

However I have one small question:

When I select an item in the parent combobox, automatically the first item in the child combobox is shown. Is it possible to set the selected index of the child combobox to -1 (or another solution)?

Thanks in advance!

Data-binding always selects the first item by default. You can't stop that happening so you would have to manually set the SelectedIndex to -1 or the SelectedItem to Nothing after selecting the parent.

[bodylojohn]

Thank you for the quick reply JMC...

I tried to put the code in:

 Private Sub cboZoekApplicatie_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboZoekApplicatie.SelectedIndexChanged
    Me.cboModule.SelectedIndex = -1
  End Sub

But with no success.

Is there a specific location in the code?
I tried several but with no success.

Thanks again!!!

[jmcilhinney]

@bodylojohn, try handling the ListChanged event of the child BindingSource. That will work after each time the user selects a different parent. You'll have to also clear the child control manually after setting its DataSource at the start.

[Zubla]

3. Add two BindingSources to the form.

In VS2002, is there some process to reveal the BindingSources widget, so that it shows up in the ToolBox?
Or some code equivalent?

In any case, a really good article. I plan to bookmark it and come back to it when I'm worthy.
Thank-you.

[jmcilhinney]

In VS2002, is there some process to reveal the BindingSources widget, so that it shows up in the ToolBox?
Or some code equivalent?

In any case, a really good article. I plan to bookmark it and come back to it when I'm worthy.
Thank-you.

The BindingSource component was not added until .NET 2.0, which means it is available in VS 2005 and later. Is there a specific reason that you're still using VS.NET 2002? There have been free Express editions available since VS 2005 so, unless you're using some feature in the old paid-for edition that is not available in the newer Express editions, I cannot recommend strongly enough that you upgrade. VS.NET 2002 can only target .NET 1.0 as well, which a lot of users won't have installed. Many users won't have newer .NET versions installed either but at least they will need those for more apps as time goes on, not fewer. You're also missing out on lots of new Framework, VB and VS features that have been added in the last 10 years.