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:
Now run the project and make selections from the parent list in the first ComboBox. Watch the child list filter automatically as you do.
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
Private Function GetDataSet() As DataSet
Dim data As New DataSet
Dim parent As DataTable = Me.GetParentTable()
Dim child As DataTable = Me.GetChildTable()
'Add a relationship between the ID of the parent
'table and the ParentID of the child table.
Private Function GetParentTable() As DataTable
Dim table As New DataTable("Parent")
Private Function GetChildTable() As DataTable
Dim table As New DataTable("Child")
.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")
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.