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?
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 selectedScheme
. What you then want is to get all the parentType
records based on those child records, which data-binding won't do. That will have to be manual.Bind your
Scheme
andSchemeType
tables toBindingSource
s as parent and child as you normally would, with the childBindingSource
bound to theDataRelation
through the parentBindingSource
. Once the theScheme
is selected and the childBindingSource
filters automatically, you can loop through it to get all the IDs for theType
records and use that to build aFilter
value for a thirdBindingSource
, e.g.