How to datagridview combobox use data source show like

Question

0.00/5 (No votes)

See more:

i have DataGridView for Invoive Save but i Have Problem
if i press Key "A" Item Show which Start "A"
But i Want when i Press "A" Item Show All the Words have "A", Such as SQL Query %Like% Statement

Can Someone Help Me this Problem

What I have tried:

C#

public SaleWindow()
{
    InitializeComponent();
    dataGridView1.ColumnCount = 6;

    dataGridView1.Columns[0].Name = "ProductName";
    dataGridView1.Columns[1].Name = "QTY";
    dataGridView1.Columns[2].Name = "Price";
    dataGridView1.Columns[3].Name = "GST%";
    dataGridView1.Columns[4].Name = "GST(Rs)";
    dataGridView1.Columns[4].DefaultCellStyle.Format = "0.00";
    dataGridView1.Columns[5].Name = "Total";
    dataGridView1.Columns[5].DefaultCellStyle.Format = "0.00";
    dataGridView1.Rows.Add();
}

public AutoCompleteStringCollection Cilentlistdropdown()
{
    AutoCompleteStringCollection asc = new AutoCompleteStringCollection();
    try
    {
        string Query;
        Query = "Select Product_Name From Product";
        SqlDataReader dr;
        con.Open();
        cmd = new SqlCommand(Query, con);
        dr = cmd.ExecuteReader();
        if ((dr != null) && (dr.HasRows))
            while (dr.Read())
                asc.Add(dr.GetValue(0).ToString());
        dr.Close();
        cmd.Dispose();
        con.Close();
    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
    return asc;
}

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DataConnectionString"].ConnectionString);
SqlCommand cmd;

private void dataGridView1_EditingControlShowing(object sender, DataGridViewEditingControlShowingEventArgs e)
{
    if (dataGridView1.CurrentCell.ColumnIndex == 0)
    {
        TextBox TB = e.Control as TextBox;
        if (TB != null)
        {
            TB.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
            TB.AutoCompleteCustomSource = Cilentlistdropdown();
            TB.AutoCompleteSource = AutoCompleteSource.CustomSource;
        }
        
    }
    else
    {
        TextBox TB = e.Control as TextBox;
        if (TB != null)
        {
            TB.AutoCompleteMode = AutoCompleteMode.None;
        }
    }
    if (dataGridView1.CurrentCell.ColumnIndex == 1)
    {
        e.Control.KeyPress += new KeyPressEventHandler(Control_KeyPrass);
    }
}

private void Control_KeyPrass(object sender,KeyPressEventArgs e)
{
    if (dataGridView1.CurrentCell.ColumnIndex == 1)
    {
        if (!char.IsControl(e.KeyChar) && !char.IsDigit(e.KeyChar) && e.KeyChar != '.')
        {
            e.Handled = true;
        }
        // Only Allow one Decimal Point
        if (e.KeyChar == '.' && (sender as TextBox).Text.IndexOf('.') > -1)
        {
            e.Handled = true;
        }
    }
}
    
private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
    if (e.ColumnIndex == 0)
    {
        if (dataGridView1[e.ColumnIndex, e.RowIndex].Value != null)
        {
            string newvalue;
            newvalue = (dataGridView1[e.ColumnIndex, e.RowIndex].Value).ToString();
            if (newvalue != "")
            {
                con.Open();

                SqlDataAdapter da = new SqlDataAdapter("select Product_Name, Product_MRP, Product_GST_Rate from Product where Product_Name = '" + newvalue + "'", con);
                DataTable dt = new DataTable();
                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    dataGridView1.Rows[e.RowIndex].Cells[0].Value = dt.Rows[0][0].ToString();
                    dataGridView1.Rows[e.RowIndex].Cells[2].Value = dt.Rows[0][1].ToString();
                    dataGridView1.Rows[e.RowIndex].Cells[3].Value = dt.Rows[0][2].ToString();
                    con.Close();
                    //dataGridView1.Rows.Add();
                }
                else
                {
                    SqlDataAdapter da1 = new SqlDataAdapter("select top 1 Product_Name, Product_MRP, Product_GST_Rate from Product", con);
                    DataTable dt1 = new DataTable();
                    da1.Fill(dt1);

                    if (dt1.Rows.Count > 0)
                    {
                        dataGridView1.Rows[e.RowIndex].Cells[0].Value = dt1.Rows[0][0].ToString();
                        dataGridView1.Rows[e.RowIndex].Cells[2].Value = dt1.Rows[0][1].ToString();
                        dataGridView1.Rows[e.RowIndex].Cells[3].Value = dt1.Rows[0][2].ToString();
                        con.Close();
                        // dataGridView1.Rows.Add();
                    }
                }
            }
            
        }
        else
        {
            SqlDataAdapter da1 = new SqlDataAdapter("select top 1 Product_Name, Product_MRP, Product_GST_Rate from Product", con);
            DataTable dt1 = new DataTable();
            da1.Fill(dt1);

            if (dt1.Rows.Count > 0)
            {
                dataGridView1.Rows[e.RowIndex].Cells[0].Value = dt1.Rows[0][0].ToString();
                dataGridView1.Rows[e.RowIndex].Cells[2].Value = dt1.Rows[0][1].ToString();
                dataGridView1.Rows[e.RowIndex].Cells[3].Value = dt1.Rows[0][2].ToString();
                con.Close();
                // dataGridView1.Rows.Add();
            }
        }
        if (dataGridView1[0, e.RowIndex].Value != null)
        {
            int a = Convert.ToInt32(dataGridView1[1, e.RowIndex].Value);
            if (dataGridView1[1, e.RowIndex].Value == null || a <= 0 || dataGridView1[1, e.RowIndex].Value.ToString() == "" || dataGridView1[1, e.RowIndex].Value.ToString() == "0" || string.IsNullOrWhiteSpace(dataGridView1[1, e.RowIndex].Value.ToString()))
            {
                dataGridView1["QTY", e.RowIndex].Value = "1";
                dataGridView1.CurrentCell = dataGridView1.Rows[e.RowIndex].Cells[1];
                dataGridView1.BeginEdit(true);
            }

        }
    }

    if (e.ColumnIndex == 1)
    {
        if (dataGridView1[0, e.RowIndex].Value != null)
        {
            decimal a = Convert.ToDecimal(dataGridView1[1, e.RowIndex].Value);
            if (dataGridView1[1, e.RowIndex].Value == null || a <= 0 || dataGridView1[1, e.RowIndex].Value.ToString() == "" || dataGridView1[1, e.RowIndex].Value.ToString() == "0" || string.IsNullOrWhiteSpace(dataGridView1[1, e.RowIndex].Value.ToString()))
            {
                dataGridView1["QTY", e.RowIndex].Value = "1";
                dataGridView1.CurrentCell = dataGridView1.Rows[e.RowIndex].Cells[1];
                dataGridView1.BeginEdit(true);
            }

        }
    }
    
    foreach (DataGridViewRow row in dataGridView1.Rows)
    {
        row.Cells[dataGridView1.Columns["Total"].Index].Value = (Convert.ToDouble(row.Cells[dataGridView1.Columns["Price"].Index].Value) * Convert.ToDouble(row.Cells[dataGridView1.Columns["QTY"].Index].Value));
        row.Cells[dataGridView1.Columns["GST(Rs)"].Index].Value = (Convert.ToDouble(Convert.ToDouble(row.Cells[dataGridView1.Columns["Total"].Index].Value) * (Convert.ToDouble(row.Cells[dataGridView1.Columns["GST%"].Index].Value)) / (100 + (Convert.ToDouble(row.Cells[dataGridView1.Columns["GST%"].Index].Value)))));
    }
}

private void dataGridView1_CellLeave(object sender, DataGridViewCellEventArgs e)
{
    if(dataGridView1[0, e.RowIndex].Value != null)
    {
        decimal a = Convert.ToDecimal(dataGridView1[1, e.RowIndex].Value);
        if (dataGridView1[1, e.RowIndex].Value == null || a <= 0 || dataGridView1[1, e.RowIndex].Value.ToString() == "" || string.IsNullOrWhiteSpace(dataGridView1[1, e.RowIndex].Value.ToString()))
        {
            dataGridView1["QTY", e.RowIndex].Value = "1";
        }
    }
}

Posted 7-Dec-20 19:41pm

Amar chand123

Updated 8-Dec-20 0:08am

Richard Deeming

v2

Add a Solution

Comments

Richard Deeming 8-Dec-20 5:43am

SqlDataAdapter da = new SqlDataAdapter("select Product_Name, Product_MRP, Product_GST_Rate from Product where Product_Name = '" + newvalue + "'", con);

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

SqlDataAdapter da = new SqlDataAdapter("select Product_Name, Product_MRP, Product_GST_Rate from Product where Product_Name = @ProductName", con);
da.SelectCommand.Parameters.AddWithValue("@ProductName", newvalue);

You should also avoid storing the SqlConnection and SqlCommand instances in class-level fields. Instead, create them where they're needed, and wrap them in using blocks to ensure they are always cleaned up properly.

using statement - C# Reference | Microsoft Docs[^]

Amar chand123 8-Dec-20 7:40am

My problem is not datagridview filters my problem is who i use datagridview combobox autocomplete List show data. like if my custom datasource have Apple, Banana and i press
"A" in datagridview combobox cell than autocomplete suggestion show Apple and Banana, only only Apple

Richard Deeming 8-Dec-20 7:42am

No, your problem is that, as your code stands, your database will soon be destroyed. Either accidentally or deliberately.

SQL Injection is trivial to exploit, and can have extremely serious consequences. For example, TalkTalk was hit with a £400,000 fine for failing to fix a SQLi vulnerability.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2020-12-08T00:08:00

Solution 1

Have a look at solution 6 to this CodeProject QA question - How to filter a datagridview by entering a text in textbox[^]

Posted 8-Dec-20 0:08am

CHill60

Comments

Amar chand123 8-Dec-20 7:40am

My problem is not datagridview filters my problem is who i use datagridview combobox autocomplete List show data. like if my custom datasource have Apple, Banana and i press
"A" in datagridview combobox cell than autocomplete suggestion show Apple and Banana, only only Apple

CHill60 8-Dec-20 8:23am

Have you actually set the AutoCompleteMode property of the Combobox?

Edit: Here is how to do that c# - How to set AutoComplete for a DataGridView Combobox Column[^]