C# WinForms - filtering one combobox based on the value of another combobox in a databound datagridview

Question

Hey,

I have 4 tables - Agents, Customers, Counties and Towns. Agents and Customers both have a Town field and a County field. I have a DataGridView for each table. These are working great. I have the Town and County as comboboxes using the Towns and Counties table as the datasource.

The problem is that it does not filter the Town based on the selected County. I would like it to do this, but there is no option to filter a combobox field based on the value of another field.

I have searched this up for a while but cant find anything useful.

Can anyone talk me through how to do this, please?

Thanks in advance.

Regards,

Richard

PS I am using Visual Studio 2010 and mostly design view.

Answer 1

How is your data bound? If you use a DataView you can specify the RowFilter property and then refresh the underlying data. The rowfilter property works like a where clause and only returns a subset of the actual data.

A little background on DataView

Answer 2

To be able to do this you should have a Country foreign key field in you Towns table.

If you have it, the problem is probably in how your Towns combobox is databound, i.e. choosing Datasource property. You should't bind it directly to a Towns table but to a Towns "foreign key" of a Country table. You can do this in design view I think.

Answer 3

You can use DataView as a data source for your comboboxes, since this allows you to filter rows based on a criterion (via the RowFilter property). I'll show a simple example involving two comboboxes used for selecting a country and a town in that country.

---

First, set up some data to be used:

// set up DataTable with countries:
countriesTable = new DataTable("Countries");
countriesTable.Columns.Add("CountryID", typeof(int));
countriesTable.Columns.Add("CountryName", typeof(string));
countriesTable.Rows.Add(1, "England");
countriesTable.Rows.Add(2, "Spain");
...

// set up DataTable with towns:
townsTable = new DataTable("Towns");
townsTable.Columns.Add("TownID", typeof(int));
townsTable.Columns.Add("TownName", typeof(string));
townsTable.Columns.Add("CountryID", typeof(int));   // <-- this is a foreign key
townsTable.Rows.Add(1, "London", 1);
townsTable.Rows.Add(2, "Brighton", 1);
townsTable.Rows.Add(3, "Barcelona", 2);
...

---

Next, data-bind the comboboxes to the data:

// bind countries to country combobox:
countryComboBox.DataSource = countriesTable;
countryComboBox.DisplayMember = "CountryName";
countryComboBox.ValueMember = "CountryID";

// bind towns to town combobox:    
townsView = new DataView(townsTable, "CountryID = 1", ...);  // use foreign key
townComboBox.DataSource = townsView;                         // in a row filter
townComboBox.DisplayMember = "TownName";
townComboBox.ValueMember = "TownID";

---

Finally, whenever another country is selected in the country combobox, update the row filter:

private void countryComboBox_SelectedIndexChanged(object sender, EventArgs e)
{
    ...
    townsView.RowFilter = string.Format("CountryID = {0}",
                                            countryComboBox.SelectedValue);
}

I believe you could automate this last step using databinding and a custom Format event handler, but I won't go into details.