18. How do I have a combo box column display a sub set of data based upon the value of a different combo box column?
Sometimes data that you want to display in the DataGridView has a relationship between two tables such as a category and subcategory. You want to let the user select the category and then choose between a subcategory based upon the category. This is possible with the DataGridView by using two combo box columns. To enable this, two versions of the filtered list (subcategory) needs to be created. One list has no filter applied while the other one will be filtered only when the user is editing a subcategory cell. Two lists are required due to the requirement described in 3.5.1 section that a combo box cells value must be in the items collection or else a DataError event is raised. In this case, since all combo box cells in the column use the same datasource if you filter the datasource for one row then a combo box cell in another row might not have its value visible in the datasource, thus causing a DataError event.
The below example uses the Northwind database to display related data from the Territory and Region tables (a territory is in a specific region.) Using the category and subcategory concept, the Region is the category and the Territory is the subcategory.
Code:
private void Form1_Load(object sender, EventArgs e)
{
this.territoriesTableAdapter.Fill(this.northwindDataSet.Territories);
this.regionTableAdapter.Fill(this.northwindDataSet.Region);
// Setup BindingSource for filtered view.
filteredTerritoriesBS = new BindingSource();
DataView dv = new DataView(northwindDataSet.Tables["Territories"]);
filteredTerritoriesBS.DataSource = dv;
}
private void dataGridView1_CellBeginEdit(object sender,
DataGridViewCellCancelEventArgs e)
{
if (e.ColumnIndex == territoryComboBoxColumn.Index)
{
// Set the combobox cell datasource to the filtered BindingSource
DataGridViewComboBoxCell dgcb = (DataGridViewComboBoxCell)dataGridView1
[e.ColumnIndex, e.RowIndex];
dgcb.DataSource = filteredTerritoriesBS;
// Filter the BindingSource based upon the region selected
this.filteredTerritoriesBS.Filter = "RegionID = " +
this.dataGridView1[e.ColumnIndex - 1, e.RowIndex].Value.ToString();
}
}
private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
if (e.ColumnIndex == this.territoryComboBoxColumn.Index)
{
// Reset combobox cell to the unfiltered BindingSource
DataGridViewComboBoxCell dgcb = (DataGridViewComboBoxCell)dataGridView1
[e.ColumnIndex, e.RowIndex];
dgcb.DataSource = territoriesBindingSource; //unfiltered
this.filteredTerritoriesBS.RemoveFilter();
}
}