c# - How to make a column in a DataTable be a column of comboboxes?

Question

So I have a datagrdview whose data is dynamically changed and filled using datatables. The reason for using DataTable is because I change up what is in the DataGridView throughout the course of the code. What I'm trying to accomplish is by using a DataTable I want the user to be able to Update changes made to the database. This works if I leave it as a textbox, however, as the data is a set amount of options, I would like to be able to make the columns be comboboxes.

Curtrently this is what I have:

this.StoresDataGrid.Rows.Clear();
StoresTable = new DataTable("ItemsTable");
StoresTable.Columns.Add("Meter Number", typeof(String));
StoresTable.Columns.Add("Manufacturer", typeof(ComboBox));
StoresTable.Columns.Add("Meter Size", typeof(String));
StoresTable.Columns.Add("Model Number", typeof(String));
StoresTable.Columns.Add("Body Type", typeof(String));
StoresTable.Columns.Add("Location", typeof(String));
StoresTable.Columns.Add("Account Number", typeof(String));
StoresTable.Columns.Add("Premise ID", typeof(String));
StoresTable.Columns.Add("Date Created", typeof(String));
StoresTable.Columns.Add("Created By", typeof(String));
StoresTable.Columns.Add("Invoice Number", typeof(String));
StoresTable.Columns.Add("Seal Number", typeof(String));
StoresTable.Columns.Add("Meter Status", typeof(String));
StoresTable.Columns.Add("Stolen", typeof(String));
StoresTable.Columns.Add("Verified", typeof(String));                    

using (SqlConnection GetItemCon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConfigString"].ToString()))
    using (SqlCommand GetItemCom = GetItemCon.CreateCommand())
    {
          GetItemCom.CommandText = GetItemsQuery;
          adapter = new SqlDataAdapter(GetItemsQuery, GetItemCon);
          GetItemCon.Open();

          using(SqlDataReader GetItemsRead = GetItemCom.ExecuteReader())
          {
                while (GetItemsRead.Read())
                {
                            manuBox.SelectedItem = GetItemsRead["meter_make"].ToString();
                            StoresTable.Rows.Add(GetItemsRead["meter_number"].ToString(), manuBox, GetItemsRead["meter_size"].ToString(), GetItemsRead["model_number"].ToString(),
                                GetItemsRead["body_type"].ToString(), GetItemsRead["current_location"].ToString(), GetItemsRead["acct_num"].ToString(), GetItemsRead["premise_id"].ToString(),
                                GetItemsRead["date_created"].ToString(), GetItemsRead["created_by"].ToString(), GetItemsRead["batch_number"].ToString(), GetItemsRead["seal_number"].ToString(),
                                GetItemsRead["meter_status"].ToString(), GetItemsRead["stolen"].ToString(), GetItemsRead["verified"].ToString());
                        }
                        
                        adapter = new SqlDataAdapter(GetItemsQuery, GetItemCon);
                        StoresDataGrid.DataSource = StoresTable;
                        StoresDataGrid.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
                    }
                    GetItemCon.Close();
                    ((DataTable)StoresDataGrid.DataSource).AcceptChanges();
                }

I have no errors however the assigned column does not become a "ComboBox" like I'd like. How can I achieve this using DataTable? Can I achieve this using DataGridView and still be able to tie it to a DataTable?

You will need to add a `DataGridViewComboBoxColumn` to the grid. Then fill it with the items you want the combo box drop down to display. The combo column will also accept a data source. Then mate that column to the `DataTable` column by setting the combo box columns `DataPropertyName` to match the column name in the data table. — JohnG, Nov 09 '20 at 23:07
*Data* goes into a `DataTable`. As such the data type can never be `ComboBox`. Chances are pretty good though that the datatype for `Date Created` will be `DateTime`. There are thousands of posts here ( [For example](https://stackoverflow.com/q/39245628/1070452) ) on how to set up a `ComboBox` column in a `DataGridView` — Ňɏssa Pøngjǣrdenlarp, Nov 09 '20 at 23:11

Kristin Vernon · Accepted Answer · 2020-11-12T19:15:34.553

After creating the DataGridViewComboBoxColumn, set the .DataPropertyName to the same name as the Data Column I want, and filling it with the Items to select:

StoresDataGrid.DataSource = StoresTable;
StoresDataGrid.Columns.Remove(StoresDataGrid.Columns[1]);
StoresDataGrid.Columns.Add(manuBox); //manuBox is how I named the DataGridViewComboBoxColumn
manuBox.DisplayIndex = 1;

EDIT: Pasting the full event code here for clarity

private void StoresDataGrid_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
    {
        int selectedIndex = StoresDataGrid.SelectedCells[0].RowIndex;
        string tableName = StoresDataGrid.DataSource.ToString();
        if(tableName == "GroupTable")
        {
            StoresDataGrid.ReadOnly = false;
            try
            {
                GetItemsQuery = "SELECT meter_number, meter_make, meter_size, model_number, body_type, current_location, acct_num, premise_id, date_created, created_by " +
                                        ", batch_number, seal_number, meter_status, stolen, verified " +
                                        "FROM meters " +
                                        "WHERE meter_make = '" + StoresDataGrid.Rows[selectedIndex].Cells["Manufacturer"].Value.ToString() + "' AND " +
                                        "meter_size = '" + StoresDataGrid.Rows[selectedIndex].Cells["Meter Size"].Value.ToString() + "' AND " +
                                        "model_number = '" + StoresDataGrid.Rows[selectedIndex].Cells["Model Number"].Value.ToString() + "' AND " +
                                        "body_type = '" + StoresDataGrid.Rows[selectedIndex].Cells["Body Type"].Value.ToString() + "' ";

                string manufactQuery = "SELECT manufacturer FROM meter_manufacturers";
                DataGridViewComboBoxColumn manuBox = new DataGridViewComboBoxColumn();
                manuBox.HeaderText = "Manufacturer";
                manuBox.DataPropertyName = "Manufacturer";
                using (SqlConnection Con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConfigString"].ToString()))
                using (SqlCommand Com = Con.CreateCommand())
                {
                    Con.Open();
                    Com.CommandText = manufactQuery;
                    using (SqlDataReader GetManuRead = Com.ExecuteReader())
                    {
                        while (GetManuRead.Read())
                        {
                            manuBox.Items.Add(GetManuRead["manufacturer"]);
                        }
                    }
                    Con.Close();
                }
                
                this.StoresDataGrid.DataSource = null;
                this.StoresDataGrid.Rows.Clear();
                StoresTable = new DataTable("ItemsTable");
                StoresTable.Columns.Add("Meter Number", typeof(String));
                StoresTable.Columns.Add("Manufacturer", typeof(String));
                StoresTable.Columns.Add("Meter Size", typeof(String));
                StoresTable.Columns.Add("Model Number", typeof(String));
                StoresTable.Columns.Add("Body Type", typeof(String));
                StoresTable.Columns.Add("Location", typeof(String));
                StoresTable.Columns.Add("Account Number", typeof(String));
                StoresTable.Columns.Add("Premise ID", typeof(String));
                StoresTable.Columns.Add("Date Created", typeof(String));
                StoresTable.Columns.Add("Created By", typeof(String));
                StoresTable.Columns.Add("Invoice Number", typeof(String));
                StoresTable.Columns.Add("Seal Number", typeof(String));
                StoresTable.Columns.Add("Meter Status", typeof(String));
                StoresTable.Columns.Add("Stolen", typeof(String));
                StoresTable.Columns.Add("Verified", typeof(String));

                StoresDataGrid.DataSource = StoresTable;
                StoresDataGrid.Columns.Remove(StoresDataGrid.Columns[1]);
                StoresDataGrid.Columns.Add(manuBox);
                manuBox.DisplayIndex = 1;

                using (SqlConnection GetItemCon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConfigString"].ToString()))
                using (SqlCommand GetItemCom = GetItemCon.CreateCommand())
                {
                    GetItemCom.CommandText = GetItemsQuery;
                    adapter = new SqlDataAdapter(GetItemsQuery, GetItemCon);
                    GetItemCon.Open();

                    using(SqlDataReader GetItemsRead = GetItemCom.ExecuteReader())
                    {
                        while (GetItemsRead.Read())
                        {                                
                            StoresTable.Rows.Add(GetItemsRead["meter_number"].ToString(), GetItemsRead["meter_make"].ToString(), GetItemsRead["meter_size"].ToString(), GetItemsRead["model_number"].ToString(),
                                GetItemsRead["body_type"].ToString(), GetItemsRead["current_location"].ToString(), GetItemsRead["acct_num"].ToString(), GetItemsRead["premise_id"].ToString(),
                                GetItemsRead["date_created"].ToString(), GetItemsRead["created_by"].ToString(), GetItemsRead["batch_number"].ToString(), GetItemsRead["seal_number"].ToString(),
                                GetItemsRead["meter_status"].ToString(), GetItemsRead["stolen"].ToString(), GetItemsRead["verified"].ToString());
                        }
                        
                        adapter = new SqlDataAdapter(GetItemsQuery, GetItemCon);
                        StoresDataGrid.DataSource = StoresTable;
                        StoresDataGrid.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
                    }
                    GetItemCon.Close();
                    ((DataTable)StoresDataGrid.DataSource).AcceptChanges();
                }
            }
            
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
    }

Is there some reason you are removing the column from the grid? Is this some other column? — JohnG, Nov 10 '20 at 01:33
Yea because I want the column to be a DataGridViewComboBoxColumn but it was a DataTable column when I filled the rows — Kristin Vernon, Nov 12 '20 at 16:23
But doesn’t the data table column "contain" data that "matched" one of the values in the combo box? This will leave the entire column with blank values, and when the user selects a combo box item, your code will have to do "extra" work to add the value from the combo box column back to the table. This appears to be counter intuitive to how it is supposed to work. If it works for you, then go for it. However, this appears to simply “create” more work for yourself. — JohnG, Nov 12 '20 at 17:31
It doesn't leave the combobox blank, because I fill in the values after adding the column, and I named the .DataPropertyName the same as the DataTable Column. The problem I am now having is that I had previosuly performed an update using the DataTable.GetChanges() method and that no longer seems to work when the combobox changes value. Thinking of opening a new question for this. — Kristin Vernon, Nov 12 '20 at 18:15
So initially, when you get the data table from the DB... ALL the "manufacture" fields are empty? — JohnG, Nov 12 '20 at 18:26
Your posted code looks like it is adding the data from the DB first, removing the column and then adding the combo box column. I am obviously missing something. I am just saying that your solution is not how it is supposed to work. But if it works for you, I say go for it even though it is going to “create” more work for yourself. Good Luck. — JohnG, Nov 12 '20 at 18:30
@JohnG I pasted the entire event for more clarity, your input is appreciated. — Kristin Vernon, Nov 12 '20 at 19:16
So, from what I can decipher, when the user “double clicks” on a cell in the grid. That the code queries the DB to collect all the rows that have the matching “Manufacture”, “meter size” etc. as the selected row. Then the code also “re-loads” the combo box column data. This is odd, for starters, the combo box items have not changed, so reloading its items appears unnecessary. — JohnG, Nov 12 '20 at 22:01
In addition, re-querying the DB for the specific data also appears unnecessary. What the query is doing is simply “filtering” the existing data. Instead of re-querying the DB, a better approach would be to “filter” the existing `DataTable` to show only the rows that match the “Manufacture” etc. of the selected row. — JohnG, Nov 12 '20 at 22:02
Lastly, it is perfectly legal to manually (through code) “create” the table, add the columns, then execute the query, then read the result from the query line by line and add each row from the query into the table you just created… — JohnG, Nov 12 '20 at 22:02
However, I am confident, the query can be used to “FILL” a table with the same columns and also FILL that table’s rows in “one” line of code. Something like… `adapter.Fill(DataSet)` or `adapter.Fill(DataTable, GetItemsReader)` … If you are adjusting/changing the table “after” you get it back from the DB, then you need to “adjust/change” the “query” not the table. — JohnG, Nov 12 '20 at 22:03

c# - How to make a column in a DataTable be a column of comboboxes?

1 Answers1