Copy ComboBox Dataset to DataGridViewComboBoxColumn

Question

(Using C# Visual Studio 2015, Windows Forms)

I have a list of strings that I want to show in a DataGridView (DGV).

Each row in the DGV will have:

• column[0]: String value from list (ColumnType: DataGridViewTextBoxColumn)
• column[1]: A blank cell (user will enter data here) (ColumnType: DataGridViewTextBoxColumn)
• column[2]: A ComboBox (ColumnType: DataGridViewComboBoxColumn)

Every ComboBox in each record will have identical items (the same DataSet) and I retrieve these from a SQL query to my Access Database (accdb).

My main problem is that I need to identify the ValueMember and DisplayMember of each ComboBox, and I can't figure out how to do that. My current code just tries to copy the DataTable results into the ComboBox, but I'm getting the error:
System.ArgumentException.DataGridViewComboBoxCell value is not valid.

And it repeats for every value of every ComboBox in the DataGridView. I cannot figure out what I'm doing wrong.

Any help would be greatly appreciated. Here's the code:

     DataTable results = new DataTable();

     //Identify the Connection String
     connection.ConnectionString = dbQuery.connStr;

     //SQL Statement to retrieve ComboBox Items
     string sql = @"SELECT ID, DESCRIP FROM tbl_setpoints_categories ORDER BY DESCRIP ASC";

     //Create a new ComboBox (this is for testing purposes)
     ComboBox cb = new ComboBox();

        try
        {
            connection.Open();
            OleDbCommand command = new OleDbCommand();
            command.Connection = connection;
            command.CommandText = sql;
            OleDbDataReader reader = command.ExecuteReader();

            results.Columns.Add("ID", typeof(int));
            results.Columns.Add("DESCRIP", typeof(string));
            results.Load(reader);

            //For testing purposes...
            cb.ValueMember = "ID";
            cb.DisplayMember = "DESCRIP";
            cb.DataSource = results;

            if (!reader.IsClosed)
            {
                reader.Close();
            }
        }
        finally
        {
            connection.Close();
        }


        //Loop through the list and add each into an array of objects
        //This array will be added as a DataGridView row
        foreach (string spName in spList)
        {

            //My Latest Edit, but still produces same error
            DataGridViewComboBoxCell cbCell = new DataGridViewComboBoxCell();
            cbCell.ValueMember = "ID";
            cbCell.DisplayMember = "DESCRIP";
            cbCell.DataSource = results;
            //End Latest Edit

            object[] row = new object[3];
            row[0] = spName.ToString();
            row[1] = "";
            row[2] = cbCell;  //From Latest Edit (was: results)
            dataGridView1.Rows.Add(row);
        }

EDIT: I just checked to see if my query was pulling records by adding this line:
MessageBox.Show(results.Rows.Count.ToString());
And I received the correct number of records

Answer 1

Hopefully this may clear things up. Unfortunately a DataGridViewComboBoxColumn is NOT a column of ComboBoxcontrols. So the lines below won’t work as a DataGridView column.

ComboBox cb = new ComboBox();
cb.ValueMember = "ID";
cb.DisplayMember = "DESCRIP";
cb.DataSource = results;

The DataGridView has a DataGridViewComboBoxColumn which unfortunately is a little more challenging to implement than a regular ComboBox. So to help visualize this, I made a drawing to show what I am describing.

Above you can see the output from the code below. I used the button click to place a breakpoint to display the two DataTables. The DataGridViewComboBoxColumn DataSource is a DataTable with the different “Set Point Types” and is displayed on the right showing “Id” and “Set Point Types” 0-14. These are the items that the DataGridViewComboBoxColumn uses as a DataSource.

The convenient part is that once you set the DataSource for this column you do not have to update this with every new/existing cell. The column will build the ComboBoxCells automatically. When you create the DataTable for the main DataGridView with all the data, you would assume that the cells where the combo box column is needs to have ALL the values. But these values would simply be a single string that matches one of the items in combo box (hopefully). So the DataGridView DataTable (not the DataGridView column) needs to make this column a string (not a combo box) as it can contain only ONE value from the different “Set Point Types” types. The convenient aspect is that even though it is a string and not a combo box, if it matches an existing item in the combo box then it will display that item.

Below is the code that was used for the picture above.

First set the DataGridView columns with two text columns then the last column is the DataGridViewComboBoxColumn. The GetComboColumn method returns a DatGridViewComboBoxColumn with the different items for the combo box. Here the pertinent properties are DataPropertyName, DisplayMemeber and ValueMember, these identify which columns to use from the DataSource. Below that is a method to add a text column to the DataTable.

Finally, we get a DataTable for the DataGridView with some test data for testing. Hope this helps.

DataTable allData;
DataTable comboTable;

public Form1() {
  InitializeComponent();
}

//-------------------------------------------------------------------------
private void Form1_Load(object sender, EventArgs e) {
  SetDGVColumns();
  allData = GetAllData();
  dataGridView1.DataSource = allData;
}

private DataTable GetAllData() {
  DataTable data = new DataTable();
  data.Columns.Add("Set Point Name", typeof(string));
  data.Columns.Add("Description", typeof(string));
  data.Columns.Add("Set Point Type", typeof(string));  // <-- NOTE: this is a string- not a combo box

  data.Rows.Add("nai_m2_no2", "Description 0", "Set Point Type 4");
  data.Rows.Add("nao_enth_no3", "Description 1", "Set Point Type 13");
  data.Rows.Add("nai_m2_no4", "Description 2", "Set Point Type 4");
  data.Rows.Add("nai_m2_no5", "Description 3", "Set Point Type 11");
  data.Rows.Add("nai_m2_no6", "Description 4", "Set Point Type 3");
  data.Rows.Add("nai_m2_no7", "Description 5", "Set Point Type 2");
  return data;
}

//-------------------------------------------------------------------------
private void SetDGVColumns() {
  AddTextCol("Set Point Name");
  AddTextCol("Description");
  dataGridView1.Columns.Add(GetComboColumn()); // <-- THIS DataGridView Column needs to be a combo box column
}

//-------------------------------------------------------------------------
private DataGridViewComboBoxColumn GetComboColumn() {
  comboTable = new DataTable();
  comboTable.Columns.Add("ID", typeof(int));
  comboTable.Columns.Add("Set Point Type", typeof(string));
  for (int i = 0; i < 15; i++) {
    comboTable.Rows.Add(i, "Set Point Type " + i);
  }
  // we now have a data table to use as a data source for the DataGridViewComboBoxColumn
  // make a DataGridViewComboBoxColumn and set it properties
  DataGridViewComboBoxColumn typeCol = new DataGridViewComboBoxColumn();
  typeCol.Width = 150;
  typeCol.DataPropertyName = "Set Point Type";  //<-- needs to match the DataTable column name you want to display - in this case 'Set Point Type'
  typeCol.HeaderText = "Set Point Type";
  typeCol.ValueMember = "Set Point Type";
  typeCol.DisplayMember = "Set Point Type";
  typeCol.Name = "Set Point Type";
  typeCol.DataSource = comboTable;
  return typeCol;
}

//-------------------------------------------------------------------------
private void AddTextCol(string colInfo) {
  DataGridViewTextBoxColumn TextCol = new DataGridViewTextBoxColumn();
  TextCol.DataPropertyName = colInfo;
  TextCol.HeaderText = colInfo;
  TextCol.Name = colInfo;
  dataGridView1.Columns.Add(TextCol);
}