Change Windows Form DataGridView row cell value based on AutoComplete selection 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,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