C# How to get Database values to Datagridview Combo box

Question

I have Datagridview and all these data is from my Database(SQL). I have a column "Quantity" and I create a combo box in my datagrid which also "Quantity". On run time Combo box is already there, but i can't get the values of "Quantity" (textbox) to my "Quantity" (Combo box). Here's my code

private DataTable _products()
    {
        using (var con = SQLConnection.GetConnection())
        {
            var ds = new DataSet();
            var select = new SqlCommand("Select * from employee_product", con);
            var sda = new SqlDataAdapter();
            sda.SelectCommand = select;       
            sda.Fill(ds);
            dt = ds.Tables[0];


            return dt;             
        }
    }
    private void fillcombo()
    {
        var combo = new DataGridViewComboBoxColumn();
        ListProduct.Columns.Add(combo);
        combo.HeaderText = "Quantity";
        combo.Name = "combo";
        ArrayList row = new ArrayList();

        foreach (DataRow dr in dt.Rows)
        {
            row.Add(dr["Quantity"].ToString());
        }
        combo.Items.AddRange(row.ToArray());


    }

Answer 1

Below is a small example of what my comment describes.

Some pointers as what to expect when using a DataGridViewComboBoxColumn… First I recommend you wire up the grids DataError. If a value is in the grid that is NOT in the combo box, then the grid will throw a DataError exception. In addition, in most cases when this happen it will make your program non-functional as the grid will continue to throw this error whenever it gains focus or more data is added. This is why you want to wire up the grids DataError… I can almost guarantee you will get this error if you are not careful making sure the combo box has ALL the values that are in the original data.

It is convenient and fortunate that the posted code is filling the combo boxes will “only” the data that is actually in the DataTable. This makes sure that the combo boxes will be good when the data is loaded into the grid. Unfortunately, in the case of the “quantity” values as the code shows… ONLY the values in the DataTable will be in the combo box. This may not always be the case or what you want…

From my small example below with only five (5) different “quantity” values in the data, when the code is run, each combo box only contains five (5) different values. This may be a problem if the user wanted to change the value to “7.5.” That value is not in the original data and it won’t be in the combo box for the user to select; therefore, this approach will possibly miss some needed values.

Using “quantity” as an example I would guess that you may want “quantity” to be values from 1 to 10 in .5 increments. This would be values 1, 1.5, 2, 2.5, 3, 3.5… etc. The limit of 10 is used as an example to demonstrate that often times the combo will contain values that “may” not be in the original data. Setting the combo box data table to these (default) values will display the values described above. However, looking at the original data in my example below, this will crash when it loads the data because there are values in the original data that are NOT in the combo box data table, namely 12.5, 33.5 and 22.5 since these values are greater than 10.

This is an “important” point. When ever you are using a data source on the grid and it has a combo box that is pre-filled with the data… you better make sure that the original data does NOT have a value that is NOT in the combo box. Without this check the DataError will most likely pop up somewhere down the line. So, it is something you would want to ALWAYS address/check for.

Fortunately, the posted code is already doing this, In the GetComboColumn method below, the commented-out line GetFullComboDT will get ALL the values we want in the combo box. Now we have to make sure that the original data doesn’t have any values that are NOT in the combo box. In my example below there are three (3) values that are NOT in the full combo box… 12.5, 33.5 and 22.5. The loop will go ahead and “add” these values to the combo box. This will almost guarantee that you will avoid getting the dreaded DataError.

I hope this makes sense and helps.

public Form1() {
  InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e) {
  DataTable gridDT = GetGridTable();
  FillGridTable(gridDT);
  DataGridViewComboBoxColumn combocol = GetComboColumn(gridDT);
  dataGridView1.Columns.Add(combocol);
  dataGridView1.DataSource = gridDT;
}

private DataTable GetGridTable() {
  DataTable dt = new DataTable();
  dt.Columns.Add("Col1", typeof(string));
  dt.Columns.Add("Col2", typeof(string));
  dt.Columns.Add("Quantity", typeof(decimal));
  return dt;
}

private DataTable GetComboTable() {
  DataTable dt = new DataTable();
  dt.Columns.Add("index", typeof(int));
  dt.Columns.Add("Quantity", typeof(decimal));
  return dt;
}

private void FillGridTable(DataTable dt) {
  dt.Rows.Add("C0R0", "C1R0", 12.5);
  dt.Rows.Add("C0R1", "C1R1", 2);
  dt.Rows.Add("C0R2", "C1R2", 33.5);
  dt.Rows.Add("C0R3", "C1R3", 1);
  dt.Rows.Add("C0R4", "C1R4", 22.5);
  dt.Rows.Add("C0R5", "C1R5", 1);
  dt.Rows.Add("C0R6", "C1R6", 12.5);
}

private DataGridViewComboBoxColumn GetComboColumn(DataTable dt) {
  DataGridViewComboBoxColumn combo = new DataGridViewComboBoxColumn();
  combo.HeaderText = "Quantity";
  combo.Name = "combo";
  combo.DataPropertyName = "Quantity";
  combo.DisplayMember = "Quantity";
  DataTable comboDT = GetComboTable();
  //DataTable comboDT = GetFullcomboDT();
  int index = 0;
  foreach (DataRow dr in dt.Rows) {
    if (NewValue((decimal)dr["Quantity"], comboDT)) {
      comboDT.Rows.Add(index, dr["Quantity"]);
    }
  }
  combo.DataSource = comboDT;
  return combo;
}

private bool NewValue(decimal value, DataTable dt) {
  foreach (DataRow row in dt.Rows) {
    if (((decimal)row["Quantity"]) == value) {
      return false;
    }
  }
  return true;
}

private DataTable GetFullcomboDT() {
  DataTable dt = new DataTable();
  dt.Columns.Add("index", typeof(int));
  dt.Columns.Add("Quantity", typeof(decimal));
  decimal currentValue = 1.0m;
  int index = 0;
  for (int i = 0; i < 20; i++) {
    dt.Rows.Add(index, currentValue);
    currentValue += 0.5m;
    index++;
  }
  return dt;
}

Answer 2

You have to manually add the values to the comboboxes and declare the type of them first. Something like that should do it:

    DataGridViewComboBoxCell dgvcell;
    for (int x = 0; (x <= (DataGridView1.Rows.Count - 1)); x++) 
    {
    SQL_cmd.CommandText = "select something from somethingelse where something = @something ";
        sql_cmd.parameters.addwithvalue("@something", DataGridView1.Rows[x].Cells["something"].Value);
        SQL_reader = SQL_cmd.ExecuteReader;
        while (SQL_reader.Read) {
        dgvcell = ((DataGridViewComboBoxCell)(this.DataGridView1.Rows(x).Cells["something"]));
        dgvcell.Items.Add(SQL_reader("something"));
        }

        SQL_reader.Close();
    }