Click here to Skip to main content
15,302,644 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Updated 8-Dec-20 0:08am
v2
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

Have a look at solution 6 to this CodeProject QA question - How to filter a datagridview by entering a text in textbox[^]
   
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[^]

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900