C# Datagridview - Combobox With Different Values Per Row

Question

I've done my usual google searching but all of the topics I came across didn't seem to be doing this in the same way I am doing, or looking to do it in the same way. The closest I could find was this

https://www.experts-exchange.com/questions/23555139/How-to-filter-values-in-DataGridViewComboBoxColumn.html

Ignoring that this is VB and I'm working in C#, they have the dataset/source etc set at the top and creating new columns on a line by line basis, whereas I'm creating my data in the grid via a SQL query. In a nutshell, I'm looking to do this:

have a datagridview, with the information populated via a SQL query, with a combobox having different values based on another value per line.

I already have everything done and setup, the issue I'm running into is getting it to be a single combobox with different values on a line-by-line check of the end value (screenshot below to make it easier to understand)

http://i67.tinypic.com/1zdqafd.jpg

The "exp grade" should be different values based on what the ID column reads as. I've tried a few different methods of getting it to be one column with different grades, but I either get the first set or second set (if I leave the if column doesn't exist) or I get multiple columns (if I remove it for id = 2 onwards). This first piece is using a query that checks the value of the ID column per row

foreach (DataGridViewRow rw in this.dgvDDL.Rows)
                    {
                        if (Convert.ToString(rw.Cells["ID"].Value) == "1")
                        {
                            if (!dgvDDL.Columns.Contains("Exp_Grade"))
                            {

                                try
                                {
                                    DataGridViewComboBoxColumn cmbGrade = new DataGridViewComboBoxColumn();
                                    SqlConnection conn = new SqlConnection(@"");

                                    SqlCommand cmd = new SqlCommand("SELECT AREA_CODE, GRADE [Grade] "
                                                                  + "FROM RICH_DDL_TEST "
                                                                  + "WHERE IDENTIFIER = '1' "
                                                                  + "UNION "
                                                                  + "SELECT AREA_CODE, GRADE2[Grade] "
                                                                  + "FROM RICH_DDL_TEST "
                                                                  + "WHERE IDENTIFIER = '1' "
                                                                  + "UNION "
                                                                  + "SELECT AREA_CODE, GRADE3[Grade] "
                                                                  + "FROM RICH_DDL_TEST "
                                                                  + "WHERE IDENTIFIER = '1' "
                                                                  + "ORDER BY 2", conn);

                                    cmd.CommandType = CommandType.Text;
                                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                                    DataSet ds = new DataSet();
                                    da.Fill(ds);
                                    cmbGrade.DataSource = ds.Tables[0];
                                    this.dgvDDL.Columns.Insert(2, cmbGrade);
                                    this.dgvDDL.Columns[2].HeaderText = "Exp_Grade";
                                    this.dgvDDL.Columns[2].Name = "Exp_Grade";
                                    cmbGrade.ValueMember = "Grade";
                                    cmbGrade.DisplayMember = "Grade";
                                }

                                catch (Exception ex)
                                {
                                    MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OKCancel,
                                    MessageBoxIcon.Warning);
                                }
                            }
                        }

                        if (Convert.ToString(rw.Cells["ID"].Value) == "2")
                        {
                            if (!dgvDDL.Columns.Contains("Exp_Grade"))
                            {

                                try
                                {
                                    DataGridViewComboBoxColumn cmbGrade = new DataGridViewComboBoxColumn();
                                    SqlConnection conn = new SqlConnection(@"");

                                    SqlCommand cmd = new SqlCommand("SELECT AREA_CODE, GRADE [Grade] "
                                                                  + "FROM RICH_DDL_TEST "
                                                                  + "WHERE IDENTIFIER = '2' "
                                                                  + "UNION "
                                                                  + "SELECT AREA_CODE, GRADE2[Grade] "
                                                                  + "FROM RICH_DDL_TEST "
                                                                  + "WHERE IDENTIFIER = '2' "
                                                                  + "UNION "
                                                                  + "SELECT AREA_CODE, GRADE3[Grade] "
                                                                  + "FROM RICH_DDL_TEST "
                                                                  + "WHERE IDENTIFIER = '2' "
                                                                  + "ORDER BY 2", conn);

                                    cmd.CommandType = CommandType.Text;
                                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                                    DataSet ds = new DataSet();
                                    da.Fill(ds);
                                    cmbGrade.DataSource = ds.Tables[0];
                                    this.dgvDDL.Columns.Insert(2, cmbGrade);
                                    this.dgvDDL.Columns[2].HeaderText = "Exp_Grade";
                                    this.dgvDDL.Columns[2].Name = "Exp_Grade";
                                    cmbGrade.ValueMember = "Grade";
                                    cmbGrade.DisplayMember = "Grade";
                                }

                                catch (Exception ex)
                                {
                                    MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OKCancel,
                                    MessageBoxIcon.Warning);
                                }
                            }
                        }

This second set also checks, but hard codes the values instead:

/*foreach (DataGridViewRow rw in this.dgvDDL.Rows)
                        {
                            if (Convert.ToString(rw.Cells["ID"].Value) == "1")
                            {
                                if (!dgvDDL.Columns.Contains("Exp_Grade"))
                                {
                                    try
                                    {
                                        DataGridViewComboBoxColumn a = new DataGridViewComboBoxColumn();
                                        this.dgvDDL.Columns.Insert(2, a);
                                        this.dgvDDL.Columns[2].HeaderText = "Exp_Grade";
                                        this.dgvDDL.Columns[2].Name = "Exp_Grade";
                                        string[] aList = new[] { "A", "B", "C" };
                                        foreach (string str in aList)
                                        {
                                            a.Items.Add(str);
                                        }
                                    }

                                    catch (Exception ex)
                                    {
                                        MessageBox.Show(ex.Message, "Error at 1", MessageBoxButtons.OKCancel,
                                        MessageBoxIcon.Warning);
                                    }
                                }
                            }

                            if (Convert.ToString(rw.Cells["ID"].Value) == "2")
                            {
                                if (!dgvDDL.Columns.Contains("Exp_Grade"))
                                {
                                    try
                                    {
                                        DataGridViewComboBoxColumn b = new DataGridViewComboBoxColumn();
                                        this.dgvDDL.Columns.Insert(2, B)/>/>/>;
                                        this.dgvDDL.Columns[2].HeaderText = "Exp_Grade";
                                        this.dgvDDL.Columns[2].Name = "Exp_Grade";
                                        string[] bList = new[] { "D", "E", "F" };
                                        foreach (string str in bList)
                                        {
                                            b.Items.Add(str);
                                        }
                                    }

                                    catch (Exception ex)
                                    {
                                        MessageBox.Show(ex.Message, "Error at 2", MessageBoxButtons.OKCancel,
                                        MessageBoxIcon.Warning);
                                    }
                                }
                            }

                            if (Convert.ToString(rw.Cells["ID"].Value) == "3")
                            {

                            }

                            if (Convert.ToString(rw.Cells["ID"].Value) == "4")
                            {

                            }*/

Is anybody able to point me in the right direction, or maybe point out what I'm doing wrong?

Thanks

-edit full code below updated:

private void DDLTest_Load(object sender, EventArgs e) { Cursor.Current = Cursors.WaitCursor;

        this.dgvDDL.Focus();

        try
        {
            SqlConnection cnSQL;
            DataSet dsData;
            String strQuery = "SELECT uio.course_occurrence_code [CourseCode], uio.long_description [CourseDesc], '' [Grade], uio.owning_organisation [Area], ddl.IDENTIFIER [ID] "
                            + "FROM unit_instance_occurrences [uio] LEFT JOIN RICH_DDL_TEST [ddl] ON uio.UIO_ID = ddl.UIO_ID "
                            + "WHERE uio.calocc_occurrence_code lIKE '16%' AND uio.owning_organisation IS NOT NULL "
                            + "ORDER BY 3, 1";

            //If using database class
            cnSQL = database.AcquireConnection();

            //If using database class
            dsData = database.GetData(cnSQL, strQuery);

            cnSQL.Close();
            cnSQL.Dispose();

            if (dsData.Tables.Count > 0)
            {
                dgvDDL.DataSource = dsData.Tables[0];

                this.dgvDDL.Columns["Grade"].Visible = false;

                this.dgvDDL.AutoGenerateColumns = false;

                /*try
                {
                    DataGridViewComboBoxColumn cmbGrade = new DataGridViewComboBoxColumn();
                    SqlConnection conn = new SqlConnection(@"Data Source = EBS - DB - REPORTSE; Initial Catalog = ebslive; Persist Security Info = True; User ID = sa; Password = W0rk1ngMen5");

                    SqlCommand cmd = new SqlCommand("SELECT AREA_CODE, 'A'[Grade] "
                                                  + "FROM RICH_DDL_TEST "
                                                  + "UNION "
                                                  + "SELECT AREA_CODE, 'B'[Grade] "
                                                  + "FROM RICH_DDL_TEST "
                                                  + "UNION "
                                                  + "SELECT AREA_CODE, 'C'[Grade] "
                                                  + "FROM RICH_DDL_TEST "
                                                  + "UNION "
                                                  + "SELECT AREA_CODE, 'D'[Grade] "
                                                  + "FROM RICH_DDL_TEST "
                                                  + "UNION "
                                                  + "SELECT AREA_CODE, 'E'[Grade] "
                                                  + "FROM RICH_DDL_TEST "
                                                  + "UNION "
                                                  + "SELECT AREA_CODE, 'F'[Grade] "
                                                  + "FROM RICH_DDL_TEST "
                                                  + "UNION "
                                                  + "SELECT AREA_CODE, 'G'[Grade] "
                                                  + "FROM RICH_DDL_TEST "
                                                  + "UNION "
                                                  + "SELECT AREA_CODE, 'H'[Grade] "
                                                  + "FROM RICH_DDL_TEST "
                                                  + "UNION "
                                                  + "SELECT AREA_CODE, 'I'[Grade] "
                                                  + "FROM RICH_DDL_TEST "
                                                  + "UNION "
                                                  + "SELECT AREA_CODE, 'J'[Grade] "
                                                  + "FROM RICH_DDL_TEST "
                                                  + "UNION "
                                                  + "SELECT AREA_CODE, 'K'[Grade] "
                                                  + "FROM RICH_DDL_TEST "
                                                  + "ORDER BY 2", conn);

                    cmd.CommandType = CommandType.Text;
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    cmbGrade.DataSource = ds.Tables[0];
                    this.dgvDDL.Columns.Insert(2, cmbGrade);
                    this.dgvDDL.Columns[2].HeaderText = "Exp_Grade";
                    this.dgvDDL.Columns[2].Name = "Exp_Grade";
                    cmbGrade.ValueMember = "Grade";
                    cmbGrade.DisplayMember = "Grade";
                }

                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OKCancel,
                    MessageBoxIcon.Warning);
                }*/
            }

            Int32 intRows = dsData.Tables[0].Rows.Count;

            try
            {
                foreach (DataGridViewRow row in dgvDDL.Rows)
                {
                    if (row.Index < intRows)
                    {
                        // row.Cells["Exp_Grade"].Value = dsData.Tables[0].Rows[row.Index]["Grade"].ToString();
                    }
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error detected",
                MessageBoxButtons.OKCancel,
                MessageBoxIcon.Warning);
            }

            dsData.Tables[0].Rows.Count.ToString();
        }

        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error detected",
            MessageBoxButtons.OKCancel,
            MessageBoxIcon.Warning);
        }
    }

    private void dgvDDL_CellContentClick(object sender, DataGridViewCellEventArgs e)
    {

    }

    private void dgvDDL_CellBeginEdit(object sender, DataGridViewCellCancelEventArgs e)
    {
       /* foreach (DataGridViewRow rw in this.dgvDDL.Rows)
        {
            bool flaga = false;
            if (Convert.ToString(rw.Cells["ID"].Value) == "1")
                flaga = true;

            if (flaga)
            {
                this.dgvDDL.Columns["Grade"].Visible = false;

                if (!dgvDDL.Columns.Contains("Exp_Grade"))
                {
                    try
                    {
                        DataGridViewComboBoxColumn a = new DataGridViewComboBoxColumn();
                        this.dgvDDL.Columns.Insert(2, a);
                        this.dgvDDL.Columns[2].HeaderText = "Exp_Grade";
                        this.dgvDDL.Columns[2].Name = "Exp_Grade";
                        string[] aList = new[] { "C", "D", "E" };
                        foreach (string str in aList)
                        {
                            a.Items.Add(str);
                        }
                    }

                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "Error at 1", MessageBoxButtons.OKCancel,
                        MessageBoxIcon.Warning);
                    }
                }
            }

            else if (Convert.ToString(rw.Cells["ID"].Value) == "2")

            {
                this.dgvDDL.Columns["Grade"].Visible = false;

                if (!dgvDDL.Columns.Contains("Exp_Grade"))
                {
                    try
                    {
                        DataGridViewComboBoxColumn a = new DataGridViewComboBoxColumn();
                        this.dgvDDL.Columns.Insert(2, a);
                        this.dgvDDL.Columns[2].HeaderText = "Exp_Grade";
                        this.dgvDDL.Columns[2].Name = "Exp_Grade";
                        string[] aList = new[] { "D", "E", "F" };
                        foreach (string str in aList)
                        {
                            a.Items.Add(str);
                        }
                    }

                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "Error at 2", MessageBoxButtons.OKCancel,
                        MessageBoxIcon.Warning);
                    }
                }
            }

            else if (Convert.ToString(rw.Cells["ID"].Value) == "3")
            {
                this.dgvDDL.Columns["Grade"].Visible = false;

                if (!dgvDDL.Columns.Contains("Exp_Grade"))
                {
                    try
                    {
                        DataGridViewComboBoxColumn a = new DataGridViewComboBoxColumn();
                        this.dgvDDL.Columns.Insert(2, a);
                        this.dgvDDL.Columns[2].HeaderText = "Exp_Grade";
                        this.dgvDDL.Columns[2].Name = "Exp_Grade";
                        string[] aList = new[] { "G", "H", "I" };
                        foreach (string str in aList)
                        {
                            a.Items.Add(str);
                        }
                    }

                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "Error at 3", MessageBoxButtons.OKCancel,
                        MessageBoxIcon.Warning);
                    }
                }
            }

            else if (Convert.ToString(rw.Cells["ID"].Value) == "4")
            {
                this.dgvDDL.Columns["Grade"].Visible = false;

                if (!dgvDDL.Columns.Contains("Exp_Grade"))
                {
                    try
                    {
                        DataGridViewComboBoxColumn a = new DataGridViewComboBoxColumn();
                        this.dgvDDL.Columns.Insert(2, a);
                        this.dgvDDL.Columns[2].HeaderText = "Exp_Grade";
                        this.dgvDDL.Columns[2].Name = "Exp_Grade";
                        string[] aList = new[] { "J", "K", "L" };
                        foreach (string str in aList)
                        {
                            a.Items.Add(str);
                        }
                    }

                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "Error at 4", MessageBoxButtons.OKCancel,
                        MessageBoxIcon.Warning);
                    }
                }
            }
        }*/
    }

    private void dgvDDL_CellEndEdit(object sender, DataGridViewCellEventArgs e)
    {

    }

    private void dgvDDL_CellValueChanged(object sender, DataGridViewCellEventArgs e)
    {
        try
        {
            if (e.ColumnIndex == 4)
            {
                if (dgvDDL.Rows[e.RowIndex].Cells[4].Value != null)
                {
                    DataGridViewComboBoxCell comboCell = dgvDDL.Rows[e.RowIndex].Cells[0] as DataGridViewComboBoxCell;

                    if (dgvDDL.Rows[e.RowIndex].Cells[4].Value.ToString() == "1")
                    {
                        SetCombo(1, comboCell);
                    }

                    if (dgvDDL.Rows[e.RowIndex].Cells[4].Value.ToString() == "2")
                    {
                        SetCombo(2, comboCell);
                    }

                    if (dgvDDL.Rows[e.RowIndex].Cells[4].Value.ToString() == "3")
                    {
                        SetCombo(3, comboCell);
                    }

                    if (dgvDDL.Rows[e.RowIndex].Cells[4].Value.ToString() == "4")
                    {
                        SetCombo(4, comboCell);
                    }
                }
            }
        }

        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OKCancel,
            MessageBoxIcon.Warning);
        }
    }

    private void SetCombo (int comboType, DataGridViewComboBoxCell comboCell)
    {
        comboCell.Value = "";
        comboCell.Items.Clear();

        if (comboType == 1)
        {
            comboCell.Items.Add("A");
            comboCell.Items.Add("B");
            comboCell.Items.Add("C");
            return;
        }

        if (comboType == 2)
        {
            comboCell.Items.Add("D");
            comboCell.Items.Add("E");
            comboCell.Items.Add("F");
            return;
        }

        if (comboType == 3)
        {
            comboCell.Items.Add("G");
            comboCell.Items.Add("H");
            comboCell.Items.Add("I");
            return;
        }

        if (comboType == 4)
        {
            comboCell.Items.Add("J");
            comboCell.Items.Add("K");
            comboCell.Items.Add("L");
            return;
        }

        comboCell.Items.Add("A");
        comboCell.Items.Add("B");
        comboCell.Items.Add("C");
        comboCell.Items.Add("D");
        comboCell.Items.Add("E");
        comboCell.Items.Add("F");
        comboCell.Items.Add("G");
        comboCell.Items.Add("H");
        comboCell.Items.Add("I");
        comboCell.Items.Add("J");
        comboCell.Items.Add("K");
        comboCell.Items.Add("L");
    }

i see your updated code. I am guessing the CellValueChanged event is not firing? — JohnG
If you are using visual studio, click on the forms designer and select the DataGridView. In the properties window, click on the “events” (lightning bolt) button, and then scroll down to the CellValueChanged event. To the right of this event, click into the empty box, a list should drop down and allow you to select your CellValueChanged method. It will add the following line to the “designer’ code: this.dgvDDL.CellValueChanged += new System.Windows.Forms.DataGridViewCellEventHandler(this.dgvDDL_CellValueChanged); — JohnG
If you cannot get that to work, then add the line in the form’s constructor or its load event before you fill the grid. — JohnG
Thanks for that - still no luck on getting the event to fire though (which is odd as I've never had an issue with getting that to fire before). It does fire if I re-enabled the section in the form load event where I add in the combo box for expected grade and it doesn't seem to be specific to the ID. With that, it's reading the "e columnindex == 4" as 2 always so it never gets to the setcombo section — Richard
Without seeing how you register the event, it is pure speculation as to what could be the problem. If you put a breakpoint, in the CellValueChanged event and the code does not stop at that breakpoint, then obviously the event is not being fired. If it does stop at the breakpoint, then step through the code and see what is happening. Looking at the last CellValueChanged event you posted, the problem I see is that SetCombo(5, comboCell) is never called and will leave the combo box empty for cells that do not contain 1, 2, 3 or 4. — JohnG

JohnG JohnG · Accepted Answer · 2017-05-13T11:15:36

I do not think looping through the DaragridView to set the combo boxes is necessarily the best approach. Using this approach, the user will have to press a button or some other mechanism is necessary to fire this combo box update. Using the picture you linked to, it appears you want different values to appear in the combo boxes based on what value is in the “ID” column.

Example, if row 0 “ID” column has a “1” then set the combo box values for that row to “A, B and C”. If the value in “ID” column is 2, then set the combo box values on that row to “D, E and F”. It is unclear what you want to set the combo box values to if the value in “ID” column is not 1 or 2. I assume “A, B, C, D, E, F”

I am sure there are several ways to achieve this and since the specifications are not exactly clear, one possible way to achieve this is to manually set each combo box when the value in the “ID” columns cell changes. In other words, if a cell value in the “ID” column changes, we may need to change the combo box values for that row. This can be achieved using the DataGridViews CellChangedEvent. When this event fires, a check can be made to see if the value changed was in the “ID” column, and if so, then update that rows combo box based on the value in the “ID” column.

Using the CellValueChanged event to update the combo boxes will also happen if the user changes the value in the “ID” column and when data is read into the grid. Hope this makes sense.

The code below demonstrates this. A DataGridView is set up with two columns, the first column is the combo box column the second column is the “ID”. Once the columns are set, a few rows are added. Note the combo box values will not be set until the value in the “ID” column changes. If you change the value in the “ID” column, the combo box on that row should change to the appropriate values. Hope this helps.

private void Form1_Load(object sender, EventArgs e) {
  SetColumns();
  AddRows();
}

private void SetColumns() {
  DataGridViewComboBoxColumn comboColumn = new DataGridViewComboBoxColumn();
  comboColumn.Name = "Grade";
  comboColumn.HeaderText = "Grade";
  comboColumn.ValueMember = "Grade";
  comboColumn.DisplayMember = "Grade";
  dataGridView1.Columns.Add(comboColumn);
  dataGridView1.Columns.Add("ID", "ID");
}

private void AddRows() {
  int newRowIndex = dataGridView1.Rows.Add();
  dataGridView1.Rows[newRowIndex].Cells[1].Value = 1;
  newRowIndex = dataGridView1.Rows.Add();
  dataGridView1.Rows[newRowIndex].Cells[1].Value = 2;
  newRowIndex = dataGridView1.Rows.Add();
  dataGridView1.Rows[newRowIndex].Cells[1].Value = 3;
}

private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e) {
  try {
    if (e.ColumnIndex == 1) {
      if (dataGridView1.Rows[e.RowIndex].Cells[1].Value != null) {
        DataGridViewComboBoxCell comboCell = dataGridView1.Rows[e.RowIndex].Cells[0] as DataGridViewComboBoxCell;
        if (dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString() == "1") {
          SetCombo(1, comboCell);
        } else {
          if (dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString() == "2") {
            SetCombo(2, comboCell);
          } else {
            SetCombo(3, comboCell);
          }
        }
      }
    }
  }
  catch (Exception ex) {
    MessageBox.Show("Error: " + ex.Message);
  }
}

private void SetCombo(int comboType, DataGridViewComboBoxCell comboCell) {
  comboCell.Value = "";
  comboCell.Items.Clear();
  if (comboType == 1) {
    comboCell.Items.Add("A");
    comboCell.Items.Add("B");
    comboCell.Items.Add("C");
    return;
  }
  if (comboType == 2) {
    comboCell.Items.Add("D");
    comboCell.Items.Add("E");
    comboCell.Items.Add("F");
    return;
  }
  comboCell.Items.Add("A");
  comboCell.Items.Add("B");
  comboCell.Items.Add("C");
  comboCell.Items.Add("D");
  comboCell.Items.Add("E");
  comboCell.Items.Add("F");
}

C# Datagridview - Combobox With Different Values Per Row

1 Answers