Populate cells based on ComboBox change inside Windows Form DataGridView using C#

Hi alhamd,

Refer below code.

Database

I have made use of the following table Customers with the schema as follows.

I have already inserted few records in the table.

You can download the database table SQL by clicking the download link below.

Download SQL file

Code

using System.Data;
using System.Data.SqlClient;

public partial class Form1 : Form
{
    private const string ConnectionString = @"Data Source=.;Initial Catalog=AjaxSamples;UID=sa;PWD=pass@123;";
    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(ConnectionString);
        SqlCommand cmd = new SqlCommand("SELECT CustomerId,Name FROM Customers", con);
        SqlDataAdapter paging = new SqlDataAdapter(cmd);
        paging.SelectCommand = cmd;
        SqlCommandBuilder sBuilder = new SqlCommandBuilder(paging);
        DataSet ds = new DataSet();
        paging.Fill(ds, "tblItemSold");
        DataTable sTable = ds.Tables["tblItemSold"];
        dataGridView1.DataSource = ds.Tables["tblItemSold"].DefaultView;
        //lblTotal.Text = dataGridView1.Rows.Count.ToString();
        if (sTable.Rows.Count < 1)
        {
            MessageBox.Show("No Record Found", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
            return;
        }

        DataGridViewComboBoxColumn dgvCmb = new DataGridViewComboBoxColumn();
        dgvCmb.Name = "cmbGName";
        dgvCmb.Width = 95;
        dataGridView1.Columns.Add(dgvCmb);

        foreach (DataGridViewRow row in dataGridView1.Rows)
        {
            DataGridViewComboBoxCell comboBoxCell = row.Cells[2] as DataGridViewComboBoxCell;
            comboBoxCell.Items.Add("Select Country");
            comboBoxCell.Value = "Select Country";
            using (SqlConnection con1 = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd1 = new SqlCommand("SELECT DISTINCT CustomerId,Country FROM Customers", con1))
                {
                    cmd1.CommandType = CommandType.Text;
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd1))
                    {
                        using (DataTable dt = new DataTable())
                        {
                            sda.Fill(dt);
                            foreach (DataRow drow in dt.Rows)
                            {
                                string customerId = drow[0].ToString();
                                comboBoxCell.Items.Add(drow[1]);
                            }
                        }
                    }
                }
            }
        }
    }

    private void dataGridView1_EditingControlShowing(object sender, DataGridViewEditingControlShowingEventArgs e)
    {
        ComboBox combo = e.Control as ComboBox;
        if (combo != null)
        {
            combo.SelectedIndexChanged -= new EventHandler(ComboBox_SelectedIndexChanged);
            combo.SelectedIndexChanged += new EventHandler(ComboBox_SelectedIndexChanged);
        }
    }

    private void ComboBox_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (((ComboBox)sender).SelectedIndex > 0)
        {
            string country = ((ComboBox)sender).SelectedItem.ToString();

            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("SELECT CustomerId,Name FROM Customers WHERE Country=@Country", con))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.AddWithValue("@Country", country);
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        using (DataTable dt = new DataTable())
                        {
                            sda.Fill(dt);
                            if (dt.Rows.Count > 0)
                            {
                                dataGridView1.Rows[dataGridView1.CurrentCell.RowIndex].Cells[0].Value = dt.Rows[0]["CustomerId"];
                                dataGridView1.Rows[dataGridView1.CurrentCell.RowIndex].Cells[1].Value = dt.Rows[0]["Name"];
                            }
                        }
                    }
                }
            }
        }
    }
}

Screenshot