Populate cells based on ComboBox change inside DataGridView in Windows Application

alhamd
 
on Jul 29, 2021 01:55 AM
769 Views

I have a tblItem which contains the following data

ItemID     ItemName     Qty   Price     Alert

1                 abc            10       100        10 

2                 sky            10       100         10

3                 xyz             10       100       10

I wrote the following code to display ItemName in the combo box inside DataGridview

private void frmDairy_Load(object sender, EventArgs e)
{
    this.GetData();
    dGVStore.AutoGenerateColumns = false;
}
private void GetData()
{
    con = new SqlDbConnect();
    con.SqlQuery("select ItemID,ItemName from tblMedicine order by MID desc;");
    paging.SelectCommand = con.Cmd;
    DataTable Medi = new DataTable();
    paging.Fill(Medi);
    this.cmbGName.DisplayMember = "ItemName";
    this.cmbGName.ValueMember = "ItemID";
    this.cmbGName.DataSource = Medi;
}

Required output

When I select ItemName abc from combo box inside datagridviw in colomn no. 2 then the colomn no. 3 , 4 and 5 should be auto fill by fetching data from database against ItemID = '1' and datagridview like that

ItemID     ItemName     Qty   Price     Alert

1                 abc            10       100        10 

and when in second row when i select ItemID= '2' sky from combo box then it should be

ItemID     ItemName     Qty   Price     Alert

2                 sky            10       100         10

this process will continue untill i selects ItemName from combo box which may be 2 or 5 or many. how to get solution

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Aug 05, 2021 07:34 AM
on Aug 10, 2021 06:37 AM

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