Question Populate Cascading/Dependent Comboboxes

[daniness]

Hello All,

I'm new here and am a novice VB.Net programmer.

Could someone please advise on how I can populate a combobox (combobox2) based on the selection made from another combobox (combobox1)? They are on different forms. Combobox1 lists locations, while combobox2 lists offices for each location. I would like it to work so that when the user clicks on combobox1 and makes a selection, the 2nd form loads with combobox2 showing all the offices for the selected location.

Your assistance with this would be GREATLY appreciated!

 

[jmcilhinney]

You shouldn't load the second form when the user makes a selection. You should require them to click a button. Otherwise you'll end up opening the second form at times that the user doesn't want to, e.g. they couldn't scroll through the items in the list using the keyboard.

In your second form, you can add you own constructor, i.e. Public Sub New, that takes the value from the first ComboBox as an argument. You can then pass the value from the first form when you create the second. It can then use that value to filter a database query to get just the appropriate data.

 

[daniness]

Hi jmcilhinney and everyone,

Thanks for the reply. I forgot to mention that I am having the user click on a button from the first form after making the selection from the combobox1, which then loads a second form. On this second form, I'm able to successfully populate textboxes with the corresponding data, but the 3 other comboboxes are not populating correctly.

I am using the following to populate the textboxes:

Me.txtLocation.DataBindings.Add("text", frmEditDel.LocationsBindingSource, "site")
Me.txtAAC.DataBindings.Add("text", frmEditDel.LocationsBindingSource, "aac")​

,but for one of the comboboxes (I'm trying to tackle this 1 at a time) on this 2nd form, the following code does not seem to work:

Me.DepotTableAdapter.FillByDepotName(Me.Dos_trackDataSet.depot)
Me.cboDepot.DataBindings.Add("text", Me.DepotBindingSource, "depot_name")
​

I am using a database query as you suggested on the table which I constructed in the DataSet Designer to filter data based on a foreign key...but when the 2nd form loads, it's only showing the first value in the combobox, which is also not listing the complete list of values.

I really hope I haven't lost you and your help is immensely appreciated. Please let me know if you have any questions.

 

[jmcilhinney]

The ComboBoxes are not populating properly because you're not populating them. You aren't adding any items. You need to populate the list of items by setting the DataSource of the control.

 

[daniness]

Hi jmcilhinney,

I appreciate your suggestion. I thought I was setting the datasource of the control. I have it set to a bindingsource of a table. However when this form loads, it just shows the record whose value member is 0, instead of the corresponding record based on the selection from combobox1.

I'm using the following code on the form load after binding the other controls:

        Dim dataset As New DataSet
        Using connection As New SqlClient.SqlConnection("Data Source=F03D3S-DEV01; Initial Catalog=dos_track;" _
        & "Integrated Security=True")
            Dim adapter As New SqlClient.SqlDataAdapter()
            adapter.SelectCommand = New SqlClient.SqlCommand("SELECT DISTINCT depot.depot_name FROM depot INNER JOIN locations ON depot.depot_refnbr = locations.depot_refnbr", connection)
            adapter.Fill(dataset)


        End Using
        cboDepot.DataSource = dataset.Tables(0)
        cboDepot.DisplayMember = "depot_name"

Your insight is appreciated.

 

[jmcilhinney]

Generally speaking, if you want to bind parent and child data to a ComboBox it should look something like this:

myComboBox.DisplayMember = "ParentName"
myComboBox.ValueMember = "ParentID"
myComboBox.DataSource = parentTable

myComboBox.DataBindings.Add("SelectedValue", childTable, "ParentID")

This is a database design issue first. You should NOT have a Depot table containing depot names and then a Location table containing depot names. What happens if a depot name changes? You then have to change it in the Depot table and throughout Location table. What you should have is a Depot table with a depot name and a depot ID, then a Location table with a depot ID. The ID of the depot will never change. You can then change the name of a depot in one place and every location will be inherently updated because, when you get location data, you are getting the depot name from the Depot table via the foreign key. You can see that it's that key that's the link in the example above: ParentID is the primary key in the parent table and ParentID is the foreign key in the child table.

 

[daniness]

Thanks so much for your input, jmcilhinney. I tried everything you suggested, but using the

myComboBox.DataBindings.Add("SelectedValue", childTable, "ParentID")

is giving me an "ArgumentException was unhandled error. This causes two bindings in the collection to bind to the same property. Parameter name: binding"

I've checked for any other bindings going on, but there are none for Selected Value. Any ideas?