Bind combobox to Parent DataRelation and update when 2nd Parent changes

Question

I've looked around, and can't quite find what I need.

I have a DB with (amoung others) 3 tables.
SchemeType
Scheme
Type

SchemeType contains foreign keys to the primary keys of both Scheme and Type

I have a .NET 3.5 WinForm containing 2 comboboxes.
One displays the Schemes
I would like the other to display the distinct Types that exist in the SchemeType table for that selected Scheme.

I have a DataSet which contains all the entries for all 3 tables, and have set up DataRelations for the Primary-Foreign key relations.

I am using BindingSources to populate the comboboxes, but can't work out how to get the Type combobox to refresh it's contents when I change the Scheme combobox.

I can do this with a straight parent-child relation, but can't work out how to code the parent-child-parent relation.
Here is my code, with unnecessary stuff stripped out

    Dim DS As New DataSet("myDS")
    Dim SchemeBndSrc As New BindingSource
    Dim TypeBndSrc As New BindingSource

    Using cmd As New SqlCommand("myStroedProc", _conn)
        cmd.CommandType = CommandType.StoredProcedure
        Using adp As New SqlDataAdapter(cmd)
            adp.Fill(DS)
        End Using
    End Using

    ' Name the tables
    DS.Tables(0).TableName = "Scheme"
    DS.Tables(1).TableName = "Type"
    DS.Tables(2).TableName = "SchemeType"

    Dim rel As New DataRelation("Scheme-SchemeType", _
                                 DS.Tables("Scheme").Columns("SchemeID"), _
                                 DS.Tables("SchemeType").Columns("SchemeID"), _
                                 True)

    Dim rel2 As New DataRelation("Type-SchemeType", _
                                DS.Tables("Type").Columns("TypeID"), _
                                DS.Tables("SchemeType").Columns("TypeID"), _
                                True)

    DS.Relations.Add(rel)
    DS.Relations.Add(rel2)



    ' Scheme
    ' Set up the binding source
    SchemeBndSrc.DataSource = DS
    SchemeBndSrc.DataMember = "Scheme"

    ' Bind the bindingsource to the combobox
    cboScheme.ValueMember = "SchemeId"
    cboScheme.DisplayMember = "SchemeName"
    cboScheme.DataSource = SchemeBndSrc
    cboScheme.SelectedIndex = -1

    ' Type
    ' Set up the binding source
    TypeBndSrc.DataSource = SchemeBndSrc
    TypeBndSrc.DataMember = "Type-SchemeType"

    ' Bind the bindingsource to the combobox
    cboType.ValueMember = "TypeID"
    cboType.DisplayMember = "TypeDesc"
    cboType.DataSource = TypeBndSrc
    cboType.SelectedIndex = -1

The Type combobox does not contain any items, even though there should be at least 1 item in it. If I swap the DataRelation around, it won't add it to the DataSet as the parent in this case (SchemeType) does not have unique entries for TypeID.

Can anyone help me, please?

Answer 1

You're not going to accomplish that automatically via data-binding. Data-binding can handle filtering a child list based on a selected parent, so you can get the SchemeType list to filter automatically based on the selected Scheme. What you then want is to get all the parent Type records based on those child records, which data-binding won't do. That will have to be manual.

Bind your Scheme and SchemeType tables to BindingSources as parent and child as you normally would, with the child BindingSource bound to the DataRelation through the parent BindingSource. Once the the Scheme is selected and the child BindingSource filters automatically, you can loop through it to get all the IDs for the Type records and use that to build a Filter value for a third BindingSource, e.g.

Dim typeIDs = schemeTypeBindingSource.Cast(Of DataRowView)().
                                      Select(Function(drv) CInt(drv("TypeID")))

typeBindingSource.Filter = String.Fomrat("TypeID IN ({0})",
                                         String.Join(", ", typeIDs))