Change DataGridView row cell value based on AutoComplete selection using C# in Windows Application

alhamd
 
on Aug 12, 2021 03:12 AM
610 Views

I have a table like that

Id

Item

Avl Qty

Price

1

ABC

50

70

2

SKY

50

65

3

XYZ

50

70

4

MNO

50

55

I take a datagridview and define column like that

ItemId

ItemName

Avl Qty

Price

Qty

NewPrice

Discount

D_Cost

Total

I use autocomplete textbox inside datagridview under column 1 [ItemName] like that 

Now when I enter ItemName ABC then the next column of datagridview AvlQty Price must be filled by getting data from database like that

ItemName

Avl Qty

Price

Qty

NewPrice

Discount

D_Cost

Total

ABC

50

70

 

 

 

 

 

When I enter 2 under Qty column inside datagridview then the value of NewPrice should be 140 automatically and when I enter discount % it should calculate remaining values automatically.

Now when I enter ItemName SKY then the next column of datagridview AvlQty Price must be filled by getting data from database like that

ItemName

Avl Qty

Price

Qty

NewPrice

Discount

D_Cost

Total

SKY

50

65

 

 

 

 

 

When I enter 2 under Qty column inside datagridview then the value of NewPrice should be 130 automatically and when I enter discount % it should calculate remaining values automatically.

How to get solution pls.

private void dGV_EditingControlShowing(object sender, DataGridViewEditingControlShowingEventArgs e)
{
    if (dGV.Columns[1].HeaderText.Equals("ItemName"))
    {
        TextBox textBox = e.Control as TextBox;
        if (textBox != null)
        {
            textBox.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
            textBox.AutoCompleteSource = AutoCompleteSource.CustomSource;
            textBox.AutoCompleteCustomSource = AutoCompleteItems();
        }
    }
}
 
public AutoCompleteStringCollection AutoCompleteItems()
{
    AutoCompleteStringCollection acsc = new AutoCompleteStringCollection();
    con = new SqlDbConnect();
    con.SqlQuery(@"select Distinct ItemName from tblStores where Qty > 0;");
    con.RdrEx();
    while (con.Rdr.Read())
    {
        acsc.Add(con.Rdr["ItemName"].ToString());
    }
    return acsc;
}

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Aug 13, 2021 05:38 AM
on Aug 14, 2021 01:17 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,Country 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;
        if (sTable.Rows.Count < 1)
        {
            MessageBox.Show("No Record Found", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
            return;
        }
    }

    private void dataGridView1_EditingControlShowing(object sender, DataGridViewEditingControlShowingEventArgs e)
    {
        if (dataGridView1.Columns[2].HeaderText.Equals("Country"))
        {
            TextBox textBox = e.Control as TextBox;
            if (textBox != null)
            {
                textBox.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
                textBox.AutoCompleteSource = AutoCompleteSource.CustomSource;
                textBox.AutoCompleteCustomSource = AutoCompleteItems();
                textBox.TextChanged += new EventHandler(AutoCompleteSelectedIndexChanged);
            }
        }
    }

    public AutoCompleteStringCollection AutoCompleteItems()
    {
        AutoCompleteStringCollection acsc = new AutoCompleteStringCollection();
        using (SqlConnection con1 = new SqlConnection(ConnectionString))
        {
            using (SqlCommand cmd1 = new SqlCommand("SELECT DISTINCT Country FROM Customers", con1))
            {
                cmd1.CommandType = CommandType.Text;
                con1.Open();
                SqlDataReader sdr = cmd1.ExecuteReader();
                while (sdr.Read())
                {
                    acsc.Add(sdr["Country"].ToString());
                }
                con1.Close();
            }
        }

        return acsc;
    }

    private void AutoCompleteSelectedIndexChanged(object sender, EventArgs e)
    {
        string country = ((TextBox)sender).Text;
        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