CODEWITHSUNDEEP
c#winformsdatagridview
imamage597
imamage597

Reputation: 95

Filtering DataGridView Winforms

I am attempting to create a button which takes 2 user inputs and then filters the datagridview depending on the two inputs. I am using winforms and Sql. Here is some code I found and tried implementing but it doesn't populate the datagridview with the filtered data.

        private void button3_Click(object sender, EventArgs e) {
        dataSet31.Personal_Details.Clear();
        using (SqlDataAdapter sqlDataAdapter =
new SqlDataAdapter(sqlCommand2.CommandText = "select * from Personal_Details WHERE '" + comboBox2 + "' LIKE '" + textBox1 + "'",
    "Data Source=Z46308;Initial Catalog=VSTest;Integrated Security=True"))
        {
            using (DataTable dataTable = new DataTable())
            {
                sqlDataAdapter.Fill(dataTable);
                this.DataGridView01.DataSource = dataTable;
            }
        }
    }

Upvotes: 2

Views: 2585

Answers (4)

Reza Aghaei
Reza Aghaei

Reputation: 125197

If you load data in form load, then you don't need to run a query to filter grid, you can simply use a DataView to filter the data table.

In the below sample I suppose that you use comboBox2 to select the column name that you want to filter based on.

//Create a data table to store data when you load them in form load
private DataTable dataTable;
private void Form1_Load(object sender, EventArgs e)
{
    dataTable = new DataTable();
    string constring = @"Data Source=Z46308;Initial Catalog=VSTest;Integrated Security=True";
    using (SqlConnection con = new SqlConnection(constring))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter("select * from Personal_Details", con))
        {
            //Fill the data table here
            sda.Fill(dataTable);
        }
    }
    //Set the data source of grid
    this.DataGridView01.DataSource = new DataView(dataTable);
}
private void button3_Click(object sender, EventArgs e)
{
    //Get the datasource from grid
    var dv = (DataView)this.DataGridView01.DataSource;

    //comboBox2.SelectedItem or comboBox2.SelectedValue based on your settings
    //Apply filter to data source
    dv.RowFilter = string.Format("{0} Like '%{1}%'",comboBox2.SelectedItem,   textBox1.Text);
}

EDIT

You can also use this.DataGridView01.DataSource = dataTable; to set data source, and then when filtering simply use dataTable.DefaultView.RowFilter = ....

Upvotes: 3

danish
danish

Reputation: 5600

You do not need to run the query on database all the time. Here is an alternate approach.

You can make use of DataView.RowFilter property to filter the data in memory. Once you have done this, you can rebind data to your grid. Assuming the data table object name is table, this is what you can do in button click.

        table.DefaultView.RowFilter = combobox1.SelectedText + " LIKE '%" + textbox1.Text + "%'";
        dataGridView1.DataSource = null;
        dataGridView1.DataSource = table.DefaultView;

Upvotes: 1

user1500633
user1500633

Reputation: 

 private void button2_Click(object sender, EventArgs e)
    {
        //dataSet31.Personal_Details.Clear();
        SqlCommand cmd = new SqlCommand();
        using (SqlDataAdapter sqlDataAdapter =
new SqlDataAdapter(cmd.CommandText = "select * from vtypes WHERE VoucherType ='" + comboBox1.Text + "' AND VouOrder = '" + textBox1.Text + "'",
    "Data Source=SQLEXPRESS;Initial Catalog=DB50A0;Integrated Security=True"))
        {
            using (DataTable dataTable = new DataTable())
            {
                sqlDataAdapter.Fill(dataTable);
                this.dataGridView1.DataSource = dataTable;
            }
        }
    }

Upvotes: 0

ArtleMaks
ArtleMaks

Reputation: 171

Try changing comboBox2 to comboBox2.SelectedValue and textBox1 to textBox1.Text. I don't think the query will return any results the way you have it at the moment.

I am using similar thing in my application, have a look at my code and see if you can adjust it to meet your needs:

SqlCommand cmd = new SqlCommand("SELECT o.OrderID AS ID,o.ProductID AS ProductID, p.ProductName AS 'ProductName', s.CompanyName AS Supplier, c.CategoryName AS Category, o.UnitPrice AS 'Unit Price', o.Quantity AS Quantity, (o.UnitPrice * o.Quantity) AS 'Sub-Total'" +
          "FROM [Order Details] o INNER JOIN Products p ON o.ProductID = p.ProductID " +
          "INNER JOIN Suppliers s ON p.SupplierID = s.SupplierID INNER JOIN Categories c ON p.CategoryID = c.CategoryID WHERE OrderID = '" + DropDownList1.SelectedValue + "'", new SqlConnection(ConfigurationManager.ConnectionStrings["databaseConnection1"].ConnectionString));
    cmd.Connection.Open();
    SqlDataReader sdrEmp = cmd.ExecuteReader();
    try
    {
        if (sdrEmp.HasRows)
        {
            DataTable dt = new DataTable();
            dt.Load(sdrEmp);
            //Response.Write(dt);
            ViewState["CurrentTable"] = dt;
            dataGridView1.DataSource = dt;
            dataGridView1.DataBind();        // BIND DATABASE TABLE WITH THE GRIDVIEW.
        }
    }

Upvotes: 0

Related Questions