Janine Henstock Janine Henstock - 6 months ago 80
Vb.net Question

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

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

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))
Comments