CODEWITHSUNDEEP
c#winformsfilterdatagridview
Imdad Miran
Imdad Miran

Reputation: 1

How to filter data in DataGridView in C#?

I am building a windows form application. In this application, I want to use a filter according to a combo box. I have tried some code but it's giving me an exception, Invalid Column 'drink'. drink is one of my categories. How can I solve this problem? And I am confused about where I should use the code in the form. combo box method or page load method

private void populateCat()
{
    try
    {
        Con.Open();
        string CatValue = CatCb2.SelectedValue.ToString();
        string query = $"select * from ProductTbl where ProdCat={CatValue};";
        SqlDataAdapter sda = new SqlDataAdapter(query, Con);
        SqlCommandBuilder builder = new SqlCommandBuilder(sda);
        var ds = new DataSet();
        sda.Fill(ds);
        ProdDGV.DataSource = ds.Tables[0];
        Con.Close();
    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Upvotes: 0

Views: 242

Answers (2)

Yong Shun
Yong Shun

Reputation: 51450

ISSUE(s):

Invalid Column 'drink'

The error was when you pass 'drink' value into query.

string query = $"select * from ProductTbl where ProdCat={CatValue};";

Through the statement above, your SQL query will become

select * from ProductTbl where ProdCat=drink

Thus you will get this error:

Invalid Column 'drink'

Suppose your SQL query have to be:

select * from ProductTbl where ProdCat='drink'

RECOMMENDATIONS

  1. Use parameterized query for adding value into parameter(s) and prevent SQL Injection attack.

  2. You don't need a DataSet. Seems you are query result from 1 table only. Hence you should use DataTable.

  3. Would recommend applying "using block/declaration" for SqlConnection, SqlCommand and SqlDataAdapter to dispose resources and connection when the process ended.

private void populateCat()
{
    try
    {
        using (SqlConnection Con = new SqlConnection(/* Connection string */))
        {
            Con.Open();

            using (SqlCommand command = new SqlCommand("select * from ProductTbl where ProdCat= @ProdCat", Con))
            {
                string catValue = CatCb2.SelectedValue.ToString();
                command.Parameters.Add("@ProdCat", SqlDbType.NVarchar).Value = catValue;

                using (SqlDataAdapter sda = new SqlDataAdapter(command))
                {
                    DataTable dt = new DataTable();
                    sda.Fill(dt);

                    ProdDGV.DataSource = dt;
                }
            }
        }
    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Note: This solution is for Post Owner to understand the root cause and how to fix it. To filter DataGridView without the need to fetch from the database, may refer to @OlivierRogier's recommendation on filtering DataGridView.

Upvotes: 2

Miad BayaniRad
Miad BayaniRad

Reputation: 76

First go to event of your combo box and in selectindexChange double click then

 Con.Open();
        string CatValue = CatCb2.SelectedValue.ToString();
        string query = $"select * from ProductTbl where ProdCat={CatValue};";
        SqlDataAdapter sda = new SqlDataAdapter(query, Con);
        SqlCommandBuilder builder = new SqlCommandBuilder(sda);
        var ds = new DataSet();
        sda.Fill(ds);
        ProdDGV.DataSource = ds.Tables[0];
        Con.Close();

Upvotes: -1

Related Questions