How do I fill a single ComboBoxCell from a database?

Question

I use this code so I can fill my combobox.

MySqlDataAdapter sqlDa = new MySqlDataAdapter(query, bd);
DataTable dtbl = new DataTable();
sqlDa.Fill(dtbl);
box.MinimumWidth = 200;
box.ValueMember = idFromTable;
box.DisplayMember = "Nome";
box.DataSource = dtbl;

I use this code So I can fill my dataGridView with all the data that I have in mysql

MySqlDataAdapter sqlDa = new MySqlDataAdapter("SELECT * FROM fosseis", bd);
DataTable dtbl = new DataTable();
sqlDa.Fill(dtbl);
FosseisDtGridVw.DataSource = dtbl;

This code works, but the problem is that I cannot fill a combobox after I populate my DataGridView, and neither I can fill just one combobox in the whole datagridview (in a specific row, like an if statement).

I had tried to do to make the DataSource from a comboboxColumn null before I fill it up again, tried to make something like

datagridview.Row[1].Cells[1].DataGridView.DataSource = dtbl;

And other stuff that I don't find wise enough to resolve this problem. Most of the time when I tried to solve this problem, it gets me an Exception from another code

"This object is not set to an instance"

Answer 1

I am confident there may be more than one way to do this. In the example below I have two DataGridViewComboBoxColumns. One is for a list of “States” and the other is for a list of “Cities.” The city combo box is filled based on what state is selected. There was minimal testing and I am guessing a different event(s) may also be a better approach. But this may work, in my test it worked without errors.

One problem I am guessing you may be having is when the code is “loading” the data. You need to keep in mind, as you stated, that you can not really “set up” each combo box cell until you know what value the “State” (first combo box) is. And you will not know what “State” that row has until AFTER the data has loaded.

Given this, it should appear obvious, that if you want the combo boxes to be set properly when the data is initially loaded… then, you better make sure the “State/City” combo box has the correct “State/City” items. Either that or add the rows to the grid one at a time, which is not a good idea for numerous reasons.

In addition, unlike a regular combo box, a DataGridViewComboBoxCell/Column is a different monster and it is notorious for throwing the DataError when invalid items are set in the grid’s combo cells. The grid will throw the DataError anytime the code attempts to set a cells value to a value that is NOT in the combo box/column items list. Example… when the data is loaded. When this happens, unless caught… it is an app crashing event and should be avoided.

Therefore, when loading the data using a DataSource for the grid, it is a MUST that we have a “full” list of the “Cities” since we do not know what “State” is in what row. So, a “full” list of the cities will fix this problem when “loading” the data and we can do something “after” the data has finished loading to filter the newly added rows.

In order to set an Individual combo box, we simply “filter” the original list with ALL the “Cities” to contain only the “Cities” from that selected state. IF we “FILTER” the original list as opposed to creating a new one, then the chances of getting a DataError decline significantly.

In other words, the combo columns data source contains the full list… then each comb box cell in that column could be “subset” of the original list. The grid will not throw an error if only SOME of the items in list are displayed, it only cares if it is NOT in its list.

The other main issue which you have not described, is “how” the data is organized. If the data is stored in a different way, then you may need to do some adjustments, however, the main idea should still apply. So, let’s see how this would work is general terms using six (6) steps.

A general approach with six (6) steps...

1. First is to load the data for both combo boxes (States data and Cities data). In this example there are two DataTables called “States” and “Cities.” This data is “strictly” for the combo boxes data sources… not the grid itself.
2. Create two (2) DataGridViewComboBoxColumns called “States” and “Cities.” Use the States table in step 1 as a data source to the States combo box and obviously the Cities table as a data source to the “Cities” combo box column. Also setting the combo boxes necessary property values to mate each column to the grids data source.
3. Add the columns from step 2 to the grid “BEFORE” you set the grids data source.
4. Set the grids data source. If properly set, the combo box columns should match the loaded data. However, the cities combo box will display ALL the cities.
5. After the data is loaded, we need to call a method that loops through each row in the grid and “filters” each “Cities” combo box list to the values of the selected state.
6. After this, it is all UI. For this we need to subscribe to a few grid events to maintain each combo boxes “filtered” state such as when the user changes a “State” value or adds a new row.

Below is a full example. Create a new winforms solution, drop a DataGridView onto the form and follow along. We should end up with something that looks like…

To start, lets look at the sample data. There will be two DataTables called “States” and “Cities.”

The “States” schema would be two (2) columns…

1. a unique int ID “StateID”
2. a string “StateName”

The “Cities” table would have three (3) columns…

1. a unique int ID “CityID”
2. an int “StateID" and
3. a string “CityName”

Example… State data using a CSV… I pasted this so you can use this for test data in the code below. The code reads this data from a simple CSV file.

1,Alaska
2,California
3,Texas
4,Colorado

And City data using a CSV

1,1,Anchorage
2,1,Bethel
3,1,Fairbanks
4,1,Soldotna
5,2,Los Angeles
6,2,San Francisco
7,2,San Diego
8,2,Sacramento
9,3,San Antonio
10,3,Austin
11,3,Dallas
12,3,Houston
13,4,Denver
14,4,Colorado Springs
15,4,Aurora
16,3,El Paso
17,4,Boulder
18,2,Pleasanton
19,3,Lubbock
20,1,Ketchikan

Step 1

Using this data, the code FillDataSet will read each “States” and “Cities” file and produce a DataSet with two tables called States and Cities. Below is the code that will read the files above and return a DataSet with the two tables.

DataSet StateCityDS;

private void FillDataSet() {
  DataTable dt1 = new DataTable();
  dt1.TableName = "States";
  dt1.Columns.Add("StateID", typeof(int));
  dt1.Columns.Add("StateName", typeof(string));
  DataTable dt2 = new DataTable();
  dt2.TableName = "Cities";
  dt2.Columns.Add("CityID", typeof(int));
  dt2.Columns.Add("StateID", typeof(int));
  dt2.Columns.Add("CityName", typeof(string));
  string line;
  string[] splitArray;
  using (StreamReader sr = new StreamReader(@"D:\Test\CSV\StatesDemo_1.txt")) {
    while ((line = sr.ReadLine()) != null) {
      splitArray = line.Split(',');
      if (splitArray.Length >= 2) {
        dt1.Rows.Add(splitArray[0], splitArray[1]);
      }
    }
  }
  using (StreamReader sr = new StreamReader(@"D:\Test\CSV\CitiesDemo_1.txt")) {
    while ((line = sr.ReadLine()) != null) {
      splitArray = line.Split(',');
      if (splitArray.Length >= 3) {
        dt2.Rows.Add(splitArray[0], splitArray[1], splitArray[2]);
      }
    }
  }
  StateCityDS = new DataSet();
  StateCityDS.Tables.Add(dt1);
  StateCityDS.Tables.Add(dt2);
}

Step 2 and 3

Now we have some test data. Next, we need a method to add the two (2) DataGridViewComboBoxColumns. Below, it should be noted that each column’s DataPropertyName is set to mate with the proper column in the “GRIDS” data source table. Each column’s DataSource is set to one of the tables from the returned DataSet above. Note, the DisplayMember and ValueMember. We want to display the state/city Name, and we want the Value to be the state/city ID.

private void AddCascadeComboBoxes() {
  DataGridViewComboBoxColumn col1 = new DataGridViewComboBoxColumn();
  col1.Name = "States";
  col1.HeaderText = "State";
  col1.DataSource = StateCityDS.Tables["States"];
  col1.DisplayMember = "StateName";
  col1.ValueMember = "StateID";
  col1.DataPropertyName = "States";
  dataGridView1.Columns.Add(col1);

  col1 = new DataGridViewComboBoxColumn();
  col1.Name = "Cities";
  col1.HeaderText = "City";
  col1.DataSource = StateCityDS.Tables["Cities"];
  col1.DisplayMember = "CityName";
  col1.ValueMember = "CityID";
  col1.DataPropertyName = "Cities";
  dataGridView1.Columns.Add(col1);
}

This should take care of the combo box columns. If we call FillDataSet then AddCascadeComboBoxes, from the forms load event, there should be two combo box columns in the grid. The first will list the four (4) states and the second combo box will list ALL the cities for all states. With this configuration, loading the data into the grid should now work successfully. So, lets make some test data for the grid and test this.

Step 4

Below, the GetGridDataSource simulates the data that would be loaded into the grid itself. In this example, it is a simple DataTable with three columns called “Name”, “States” and “Cities.” Each row will have different names with different state and city IDs. Example: State 1 = Alaska, City 1 = Anchorage. This method may look something like…

private DataTable GetGridDataSource() {
  DataTable dt = new DataTable();
  dt.Columns.Add("Name", typeof(string));
  dt.Columns.Add("States", typeof(int));
  dt.Columns.Add("Cities", typeof(int));
  dt.Rows.Add("John", 1, 1);
  dt.Rows.Add("Sally", 2, 5);
  dt.Rows.Add("Mary", 3, 10);
  dt.Rows.Add("Dean", 4, 13);
  dt.Rows.Add("Charlie", 2, 8);
  dt.Rows.Add("Nancy", 3, 12);
  return dt;
}

If we set the above DataTable as a DataSource to the grid, then each combo box column should have the correct “State” and “City” value set to match the original data source. In addition, you should see the extra “Name” column as shown in the previous picture. If you see an extra column like state or city with numbers, then the DataGridViewComboBoxColumn is not set up properly.

Step 5

This appears to fix the “loading” problem, however, when we click on any of the “City” combo boxes, the list is not filtered and we still see all the cities. So, we need to add step 5 from the general description above. After the data is loaded, we need an extra step to “filter” each “City” combo box on each “existing” row. This should not change the cells current value and is relatively straight forward… something like…

private void SetCityComboBoxesDataSource() {
  foreach (DataGridViewRow row in dataGridView1.Rows) {
    if (!row.IsNewRow) {
      DataRowView dr = (DataRowView)row.DataBoundItem;
      int stateID = (int)dr["States"];
      DataView dv = new DataView(StateCityDS.Tables["Cities"]);
      dv.RowFilter = string.Format("StateID = {0}", stateID);
      DataGridViewComboBoxCell cityCell = (DataGridViewComboBoxCell)(row.Cells["Cities"]);
      cityCell.DataSource = dv;
      cityCell.DisplayMember = "CityName";
      cityCell.ValueMember = "CityID";
    }
  }
}

Above, the code loops through each row, grabs the value from the “States” cell and “filters” the cities combo box list. We only need to call this code ONCE after the grids data has loaded.

If we run this code as is, then, the combo boxes will display correctly and if you click on a “City” combo box, it should display a “filtered” list. However, this only gets us up to step 5 in the general description. This is all needed to “load” the data without errors and filter the added rows, however we still need the UI portion. The data is loaded and now we need to subscribe to a couple of events to manage the combo boxes “filtering” properly when the user interacts with the grid (UI).

Step 6

I am betting there is more than one way to do this and there may be a better approach to this. However, in this example, I have wired up (subscribed to) two (2) of the grid’s events.

The grids CellEndEdit event will fire when the user has finished “editing” a cell and is trying to leave the cell. When it fires, we will check to see if the “edited” cell is in the “State” column. If it is, then we will filter the “Cities” cell on that row to display that state’s cities.

private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e) {
  if (e.RowIndex >= 0 && e.ColumnIndex >= 0) {
    if (dataGridView1.Columns[e.ColumnIndex].Name == "States") {
      if (dataGridView1.Rows[e.RowIndex].Cells["States"].Value != null) {
        if (int.TryParse(dataGridView1.Rows[e.RowIndex].Cells["States"].Value.ToString(), out int stateID)) {
          DataView dv = new DataView(StateCityDS.Tables["Cities"]);
          dv.RowFilter = string.Format("StateID = {0}", stateID);
          DataGridViewComboBoxCell cityCell = (DataGridViewComboBoxCell)(dataGridView1.Rows[e.RowIndex].Cells["Cities"]);
          cityCell.Value = DBNull.Value;
          cityCell.DataSource = dv;
          cityCell.DisplayMember = "CityName";
        }
      }
    }
  }
}

The grids EditingControlShowing event is wired up to check if the user changes a “State” value that has already been selected. This will set the “Cities” cell for that row to ‘null’. This is to prevent the “City” combo box value becoming “invalid” if the user changes a “State” value when both have already been set. Without this check and setting the cell to null, then the user could select a different state and the city would remain from the previous state. This will prevent an inconsistent state for the data.

private void dataGridView1_EditingControlShowing(object sender, DataGridViewEditingControlShowingEventArgs e) {
  int colIndex = dataGridView1.CurrentCell.ColumnIndex;
  int rowIndex = dataGridView1.CurrentCell.RowIndex;
  if (dataGridView1.Columns[colIndex].Name == "States") {
    dataGridView1.Rows[rowIndex].Cells["Cities"].Value = DBNull.Value;
  }
}

Wiring up the grids DataError comes in handy while debugging.

private void dataGridView1_DataError(object sender, DataGridViewDataErrorEventArgs e) {
  MessageBox.Show("Error: R" + e.RowIndex + "C" + e.ColumnIndex + " --> " + e.Exception.Message);
}

To put all this together, below is the rest of the code. Note, you will need to save the two CSV files above to your desired location to get the test data.

DataSet StateCityDS;
DataTable GridDT;

public Form1() {
  InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e) {
  GridDT = GetGridDataSource();
  FillDataSet();
  AddCascadeComboBoxes();
  dataGridView1.DataSource = GridDT;
  SetCityComboBoxesDataSource();
}

Lastly, it should be noted, that if the user selects the “Cities” combo box from the grids “new” row, then ALL the cities will be displayed. Granted the user “could” pick any city, however, as soon as the user clicks on the “State” combo box, the selected city value will get removed and the filter will be applied.

A special note on Step 1 from above and setting the initial combo box values. IF you have set up the combo boxes properly, AND, when loading the data, you get the data error stating that the item does not belong to the combo boxes list of items… THEN… it is guaranteed that the data contains something that is not in the combo boxes list of items.

If this is the case, then to debug and find out where the offending value is, you will need to loop through the data and check each of the combo box values. If you find one that is NOT in the list of the combo box items list, then that is one of the offending items that is throwing the error.

The problem you would have in that case is what to do with the offending value… you can NOT simply ignore it. You will either have to remove that row from the data itself, OR, add that new item to the combo boxes list of items. A poison pill you must deal with somehow.

Sorry for the long post, I hope this makes sense.

Answer 2

checks the DataPropertyName property of the DataGridViewComboBoxColumn if it contains the specified field in the table which is the datasource of the datagridview