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

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 St开发者_如何学Cudio 2010 and mostly design view.

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 = null;
countryComboBox.DisplayMember = "CountryName";
countryComboBox.ValueMember = "CountryID";
countryComboBox.DataSource = countriesTable;

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

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.

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

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.

This is my new answer. It turns out I read the question wrong (sorry about that). In my example I am using an OleDb connection to an Access Database. This is a code snippet from an app that I am currently using (names of comboboxes and tables have been changed for the example). All it does is query the database whenever the selection is changed on comboBox1 and add the result to comboBox2.

            private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
    {
        try
        {
            //Open connection to database...
            connection.Open();
            OleDbCommand command = new OleDbCommand();
            command.Connection = connection;
            string query1 = "select * from Your_Table where Title='" + comboBox1.Text + "'";
            command.CommandText = query1;

            OleDbDataReader reader1 = command.ExecuteReader();
            while (reader1.Read())
            {
                comboBox2.Items.Add(reader1["ColumnName"].ToString());
            }
            connection.Close();
        }
        catch (System.Exception ex)
        {
            MessageBox.Show("Error " + ex);
        }
    }

This will query your database based on comboBox1 and put the results in comboBox2 based on your selection.

Hope this helps!