Filter values in combo box A based on selected value in combo box B in DataGridView
I know it is quite popular question but I cannot find the solution to my problem. There are two combo boxes comboFrom and comboTo in DataGridView dgvTransfer - here is how their are populated:
private void PopComboFrom(DataGridViewComboBoxColumn combo, string valMember, int parValue1, int parValue2)
{
combo.DataSource = null;
DataTable dt = Helper.ExecuteDataTable("pp_sp_MachineAndOp",
new SqlParameter("@MachineAndOpID", SqlDbType.Int) { Value = parValue1 },
new SqlParameter("@Seq", SqlDbType.Int) { Value = parValue2 });
//Add field to be displayed
dt.Columns.Add("ToShow", typeof(string), "'Seq: ' + Seq + ' ID: ' + MachineAndOpID + ' (' + OperationID + ') ' + Operation + ' + ' + Machine");
// bind data table into combo box.
combo.DisplayMember = "ToShow";
combo.ValueMember = valMember;
combo.DataSource = dt;
}
private void PopComboTo(DataGridViewComboBoxColumn combo, string valMember, int parValue1, int parValue2, string seqFilter)
{
//combo.DataSource = null;
DataTable dt = Helper.ExecuteDataTable("pp_sp_MachineAndOp",
new SqlParameter("@MachineAndOpID", SqlDbType.Int) { Value = parValue1 },
new SqlParameter("@Seq", SqlDbType.Int) { Value = parValue2 });
//Add field to be displayed
dt.Columns.Add("ToShow", typeof(string), "'Seq: ' + Seq + ' ID: ' + MachineAndOpID + ' (' + OperationID + ') ' + Operation + ' + ' + Machine");
// bind data table into combo box.
DataView dv = new DataView(dt);
dv.RowFilter = "Seq > " + seqFilter;
combo.DisplayMember = "ToShow";
combo.ValueMember = valMember;
combo.DataSource = dv;
}
When comboFrom is changed the datasource of comboTo is filtered in this event handler:
private void dgvTransfers_EditingControlShowing(object sender, DataGridViewEditingControlShowingEventArgs e)
{
if(dgvTransfers.CurrentCell.ColumnIndex == dgvTransfers.Columns[controlFrom].Index && e.Control is ComboBox)
{
ComboBox comboBox = e.Control as ComboBox;
comboBox.SelectedIndexChanged -= ComboFromSelectionChanged;
comboBox.SelectedIndexChanged += ComboFromSelectionChanged;
}
}
private void ComboFromSelectionChanged(object sender, EventArgs e)
{
PopComboTo(cmbMachineAndOpIDTo, "MachineAndOpID", 0, 0, "7");
}
Filter works on comboTo but the problem is that values not visible in filtered comboTo also disappears from DataGridView!
Not filtered:
Filtered:
And this error raised for every row with empty data as a filter result:
How to show values in GridViewData and have filtered them in comboTo at same time?
In this example, we have two DataGridViewComboBoxColumns. Each column contains the same data source and obviously contain the same values. The goal is such that if the user selects one value in one combo box on a particular row, then, when the user selects the other combo box on the same row, then, the selected value in the first combo box will NOT be available in the second combo box. In other words, no two combo boxes on the same row in the grid will contain the same value.
I am sure there are numerous ways to do this. It may help to keep in mind that this is in reference to two DataGridViewComboBoxColumnss. Dealing with ComboBoxes in a column in a DataGridView can be challenging and it is not forgiving like a regular ComboBox is. Compound this with the idea that “each” combo box in each cell MAY contain a “different” DataSource only exacerbates things more and makes it easier for the grid to throw its dreaded DataError.
One BIG issue when using the combo box columns is if (somehow) a combo box cell’s value gets set to a value that is NOT in the combo boxes list of items. A regular ComboBox just ignores this, a DataGridViewComboBoxCell on the other hand, will complain about this and cause the grid to throw up its DataError. Or more precisely… a code crashing problem. Therefore, it is imperative that you take care and make sure that no combo box value is improperly set.
Given that we do not want the two combo boxes on a row to have duplicate values, there is one situation where it IS possible for the two combo box values to be the same… when both values are “empty,” or… not selected yet. In other words, it IS possible for the two combo boxes to have the same “NON” value. This is a “special” case and we will look for it, however, I want to stress that simply using the combo box cells null value for this “empty/non-choice” can be problematic. SO… to help, I suggest you ADD an “empty/non-choice” value as one of the items in each combo box. It may not be obvious at this point; however, this simple “non-choice” item in the combo boxes list of items is going to simplify things later.
To start, we need to break this down in to two (2) parts. Part one (1) is dealing with the user interaction with the combo boxes. If we have an “empty” grid with one row, then, when the user changes one of the combo boxes, the other combo box gets its data source filtered and vise versa. Part two (2) deals with what to do after the grid is loaded with existing data. If we simply load the data, then each combo box will be set properly, however, if the user clicks on one of the combo boxes, then the other combo boxes value will be available to duplicate. Once the user changes the combo boxes value, part one kicks in and filters the other combo box. In other words, we will need to do something AFTER the data is loaded into the grid to filter each of the combo box cells that were loaded.
If you create a new winforms solution and drop a DataGridView onto the form, you should be able to follow along in the steps below to demonstrate this and proceed in a step-by-step fashion. First we will create some simple combo box data to display in the combo boxes list of items. Then, use that data in both combo boxes. After this, both combo boxes will contain the same data and the user will be able to duplicate values on a row.
The combo box data will be a DataTable with two properties/fields… an int MachineID and a string MachineOPID. The data table will have 11 rows of data. It should be noted, that here we also want to add our “non” choice item with…
dt.Rows.Add(0, "-");
It should be noted, that I did NOT use an “empty” string or null for the MachineOPID value. We WANT “something” there. In this case it is a simple dash character. We will look for the MachineID of zero (0) later to know that the user selected the “non-selected” value.
private DataTable GetComboData() {
DataTable dt = new DataTable();
dt.Columns.Add("MachineID", typeof(int));
dt.Columns.Add("MachineOPID", typeof(string));
dt.Rows.Add(0, "-");
for (int i = 1; i < 11; i++) {
dt.Rows.Add(i, "Mac_" + i);
}
return dt;
}
Next we need to add the combo box columns to the grid. For this a helper method is created that takes four parameters. A column name, colName; header text, a data source and finally a DataPropertyName which will be utilized in part two (2).
private DataGridViewComboBoxColumn GetComboCol(string colName, string headerText, DataTable data, string dpn) {
DataGridViewComboBoxColumn col = new DataGridViewComboBoxColumn();
col.Name = colName;
col.HeaderText = headerText;
col.ValueMember = "MachineID";
col.DisplayMember = "MachineOPID";
col.DataPropertyName = dpn;
col.DataSource = data;
return col;
}
That should get the combo boxes working as shown below. The combo boxes each have the same values and the user can select the same value for both combo boxes on any row.
DataTable ComboData;
public Form1() {
InitializeComponent();
dataGridView1.EditMode = DataGridViewEditMode.EditOnEnter;
}
private void Form1_Load(object sender, EventArgs e) {
ComboData = GetComboData();
dataGridView1.Columns.Add(GetComboCol("FromMachine", "From Machine", ComboData, "FromMachine"));
dataGridView1.Columns.Add(GetComboCol("ToMachine", "To Machine", ComboData, "ToMachine"));
}
This obviously is not doing anything in relation to checking for duplicate combo box values. In this case, I will use the grids CellValueChanged event to “filter” the other combo box. Example, if the user selects a value in the “FromMachine” column and then leaves the cell, then we will then filter the “ToMachine” combo box. The same idea applies if the changed cell is the “ToMachine” cell. Therefore, a helper method is created that takes three parameters, an int grid row index; a string column name of the column that was changed and finally the name of the column of the cell we want to filter. This way, we can use this same method for both combo box cells. It may look something like…
private void SetComboData(int rowIndex, string curColName, string targetColName) {
if (dataGridView1.Rows[rowIndex].Cells[curColName].Value != null) {
string selectedValue = dataGridView1.Rows[rowIndex].Cells[curColName].Value.ToString();
DataGridViewComboBoxCell cell = (DataGridViewComboBoxCell)dataGridView1.Rows[rowIndex].Cells[targetColName];
DataView dv = new DataView(ComboData);
// we do not want to filter out the "empty" value - i.e. BOTH From and To can be empty
if (selectedValue != "0") {
dv.RowFilter = "MachineID <> '" + selectedValue + "'";
}
cell.DataSource = dv;
}
}
First a simple check to see if the cell is not null. Then, grab that cells value as this will be the item we want to filter out of the other combo box. Grab the other combo box cell and cast it to a DataGridViewComboBoxCell so we can set its data source. Create a new DataView, set the row filter unless the selected item is our “non-selected” item which we do not want to filter out. Finally set the cells data source.
This helper method should simplify the code in the grids CellValueChanged event to something like…
private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e) {
if (dataGridView1.Columns[e.ColumnIndex].Name == "FromMachine") {
SetComboData(e.RowIndex, "FromMachine", "ToMachine");
}
else {
if (dataGridView1.Columns[e.ColumnIndex].Name == "ToMachine") {
SetComboData(e.RowIndex, "ToMachine", "FromMachine");
}
}
}
// updated constructor to subscribe to the grids `CellValueChanged` event
public Form1() {
InitializeComponent();
dataGridView1.EditMode = DataGridViewEditMode.EditOnEnter;
dataGridView1.CellValueChanged += new DataGridViewCellEventHandler(dataGridView1_CellValueChanged);
}
This change should work as shown below. No two combo boxes on the same row can be set to the same value. This should update if either combo box is changed and the combo boxes can both be set to the same “non-selected” values.
This should finish part one with the user’s interaction with the combo boxes. Part two is what to do when data is loaded into the grid. IMO, if you use a combo box column in a grid that is data bound… it is wise to “check” the combo box data bound to the combo box column. Any bad data is going to cause the grid to throw its code crashing DataError. Even in a simplistic approach, checking the grids data BEFORE binding the data to the grid is simply a CYA approach.
So… the question will always be…
”what do you do if a row in the data has offending data in the combo boxes… i.e.…. a non-existent value (out of bounds)… or in this case, duplicate
FromMachineandToMachinevalues?” …
Unfortunately this is something we can NOT ignore. You either have to remove the offending data or change it. Both ideas are bad ideas, but what choice do you have? In this example, if both values are the same, then is what we will do is “change” the “ToMachine” to our “non-selected” value. If the value is out of bounds, we will change it to the non-selected value. Obviously this “change” may be some needed info in a log file. However, in this example… we just want to change it, log it and move on.
To test this, below is some test data we can use to bind to the grid. It has three (3) rows with duplicate values. Also, it has three rows of bad data such that the MachineID is out of range (12, -1). It may look something like…
private DataTable GetGridData() {
DataTable dt = new DataTable();
dt.Columns.Add("FromMachine", typeof(int));
dt.Columns.Add("ToMachine", typeof(int));
dt.Rows.Add(1, 1);
dt.Rows.Add(4, 1);
dt.Rows.Add(5, 5);
dt.Rows.Add(0, 9);
dt.Rows.Add(4, 0);
dt.Rows.Add(0, 0);
dt.Rows.Add(12, 0);
dt.Rows.Add(0, -1);
dt.Rows.Add(-1, 12);
return dt;
}
If we bind this table to the grid, we will get the grids DataError. In addition, the duplicate values will be displayed in the grid. The duplicate values are a secondary problem to the obvious and constant DataError because of the out of bounds values in the data… namely the 12 and -1 values. This is why we need to check the grids combo box data values BEFORE we bind the data to the grid. Do NOT think you can simply catch the DataError and ignore/swallow the error… You MUST do something.
For this, we will create a simple method that takes the original grid data table and loops through the table’s rows. We will check for the out of bounds values and since we are doing this, we can check for “duplicate” combo box values and act accordingly. In both cases, if the value is out of bounds, we will set that value to the “non-selected” value 0. The same will apply if any two values on the same row are equal.
After we make these changes we “should” be able to breath a little easier knowing that the grids data error is NOT going to get thrown because the combo box values are out of bounds. In addition, this will fix any row that had the same combo box values. This fixer method may look something like…
private void FixDuplicateComboData(DataTable originalData) {
int curRowIndex = 0;
int fromValue;
int toValue;
foreach (DataRow row in originalData.Rows) {
fromValue = (int)row["FromMachine"];
toValue = (int)row["ToMachine"];
if (fromValue < 0 || fromValue > 10) {
Debug.WriteLine("Row: " + curRowIndex + " From: " + row["FromMachine"] + " value is out of range - setting to no-choice");
row["FromMachine"] = 0;
}
if (toValue < 0 || toValue > 10) {
Debug.WriteLine("Row: " + curRowIndex + " To: " + row["ToMachine"] + " value is out of range - setting to no-choice");
row["ToMachine"] = 0;
}
if (fromValue == toValue) {
Debug.WriteLine("Row: " + curRowIndex + " From: " + row["FromMachine"] + " and To: " + row["ToMachine"] + " - Machines are the same... Setting to no-choice");
row["ToMachine"] = 0;
}
curRowIndex++;
}
}
This should quiet down the data error and the duplicates have been fixed so no duplicates are displayed in the grid after the data is loaded. HOWEVER, there is still a little problem. The problem is that when the data is loaded into the grid, each combo box cell was NOT filtered. So, after the data is loaded, if we click on a combo box row with two values, you will see that the “other” combo box value IS in the combo boxes list of items. Further, if we select that item… our data error friend will start complaining.
Therefore, we have one last step to complete this. After the good data is loaded into the grid, we need to loop through ALL the rows in the grid and set each combo box cell’s data source to the properly set filter. Fortunately, our previous SetComboData method should make this relatively simple… like…
private void SetComboDataAfterDataLoad() {
foreach (DataGridViewRow row in dataGridView1.Rows) {
SetComboData(row.Index, "FromMachine", "ToMachine");
SetComboData(row.Index, "ToMachine", "FromMachine");
}
}
The final product should work something like shown below…
DataTable ComboData;
DataTable GridTable;
public Form1() {
InitializeComponent();
dataGridView1.EditMode = DataGridViewEditMode.EditOnEnter;
dataGridView1.CellValueChanged += new DataGridViewCellEventHandler(dataGridView1_CellValueChanged);
dataGridView1.DataError += new DataGridViewDataErrorEventHandler(dataGridView1_DataError);
}
private void Form1_Load(object sender, EventArgs e) {
ComboData = GetComboData();
dataGridView1.Columns.Add(GetComboCol("FromMachine", "From Machine", ComboData, "FromMachine"));
dataGridView1.Columns.Add(GetComboCol("ToMachine", "To Machine", ComboData, "ToMachine"));
GridTable = GetGridData();
FixDuplicateComboData(GridTable);
dataGridView1.DataSource = GridTable;
SetComboDataAfterDataLoad();
}
private void dataGridView1_DataError(object sender, DataGridViewDataErrorEventArgs e) {
MessageBox.Show("Data Error: " + e.Exception.Message);
Debug.WriteLine("Data Error: " + e.Exception.Message);
}
This should complete the example. I hope it makes sense.