Filter Windows Form DataGridView with multiple ComboBox using C# and VB.Net

Last Reply 3 months ago By pandeyism

Posted 3 months ago
Hi all,

How to filter datagridview using multiple combobox c#

I am very new to coding and am trying to learn C# through little project. I spent a week or more trying to find solution but although there is many threads, none of them made sense for me and couldn't get it to work.

I have a form with datagridview and I want to create 6 comboboxes to filter it. All with same data source. I managed so far to get datagridview working with one combobox thanks to this website but don't know how to add more of combo boxes..

I'd much appreciate you help.

Below is the code:

namespace TM_Hub
{
    public partial class frmEmpList : Form
    {
        public frmEmpList()
        {
            InitializeComponent();
        }

    private void frmEmpList_Load(object sender, EventArgs e)
    {
        this.PopulateCombobox();
        this.PopulateDataGridView();
    }
    private void PopulateCombobox()
    {
        string qry = "SELECT DISTINCT LastName FROM Employees";
        string constr = @"Data Source=DESKTOP-Q2B3UUH\SQLEXPRESS;Initial Catalog=PeopleManager;Integrated Security=True";

        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter(qry, con))
            {
                DataTable dt = new DataTable();
                sda.Fill(dt);

                DataRow row = dt.NewRow();
                row[0] = "";
                dt.Rows.InsertAt(row, 0);

                cbLastName.DataSource = dt;
                cbLastName.DisplayMember = "LastName";
                cbLastName.ValueMember = "LastName";
            }
        }
    }
    private void PopulateDataGridView()
    {
        string query = "SELECT EmpID, FirstName, LastName, Role, Grade, Dept, Shift FROM Employees";
        query += " WHERE LastName = @LastName";
        query += " OR ISNULL(@LastName, '') = ''";
        string constr = @"Data Source=DESKTOP-Q2B3UUH\SQLEXPRESS;Initial Catalog=PeopleManager;Integrated Security=True";
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand(query, con))
            {
                cmd.Parameters.AddWithValue("@LastName", cbLastName.SelectedValue);
                using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                {
                    DataTable dt = new DataTable();
                    sda.Fill(dt);
                    dgEmpList.DataSource = dt;
                }
            }
        }
    }

    private void cbLastName_SelectionChangeCommitted(object sender, EventArgs e)
    {
        this.PopulateDataGridView();
    }
}

Many thanks in advance!

You are viewing reply posted by: pandeyism 3 months ago.
Posted 3 months ago Modified on 3 months ago

Hey rockovo,

Please refer below sample.

Namespaces

C#

using System.Data.SqlClient;

VB.Net

Imports System.Data.SqlClient

Code

C#

private void Form1_Load(object sender, EventArgs e)
{
    this.PopulateComboBox(cbCountries, "SELECT DISTINCT Country FROM Employees", "Country", "Country");
    this.PopulateComboBox(cbCities, "SELECT DISTINCT City FROM Employees", "City", "City");
    this.PopulateDataGridView();
}

private void PopulateComboBox(ComboBox comboBox, string query, string displayMember, string valueMember)
{
    string constr = @"Data Source=.\SQL2005;Initial Catalog=Northwind;User ID=sa;Password=pass@123";
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                DataTable dt = new DataTable();
                sda.Fill(dt);
                DataRow row = dt.NewRow();
                dt.Rows.InsertAt(row, 0);
                comboBox.DataSource = dt;
                comboBox.DisplayMember = displayMember;
                comboBox.ValueMember = valueMember;
            }
        }
    }
}

private void PopulateDataGridView()
{
    string query = "SELECT EmployeeId, Country, City FROM Employees";
    query += " WHERE Country = @Country AND City = @City";
    query += " OR ISNULL(@Country, '') = ''";
    query += " OR ISNULL(@City, '') = ''";
    string constr = @"Data Source=.\SQL2005;Initial Catalog=Northwind;User ID=sa;Password=pass@123";
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            cmd.Parameters.AddWithValue("@Country", cbCountries.SelectedValue);
            cmd.Parameters.AddWithValue("@City", cbCities.SelectedValue);
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                DataTable dt = new DataTable();
                sda.Fill(dt);
                dataGridView1.DataSource = dt;
            }
        }
    }
}

private void Search(object sender, EventArgs e)
{
    this.PopulateDataGridView();
}

VB.Net

Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    Me.PopulateComboBox(cbCountries, "SELECT DISTINCT Country FROM Employees", "Country", "Country")
    Me.PopulateComboBox(cbCities, "SELECT DISTINCT City FROM Employees", "City", "City")
    Me.PopulateDataGridView()
End Sub

Private Sub PopulateComboBox(ByVal comboBox As ComboBox, ByVal query As String, ByVal displayMember As String, ByVal valueMember As String)
    Dim constr As String = "Data Source=.\SQL2005;Initial Catalog=Northwind;User ID=sa;Password=pass@123"
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand(query, con)
            Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
                Dim dt As DataTable = New DataTable()
                sda.Fill(dt)
                Dim row As DataRow = dt.NewRow()
                dt.Rows.InsertAt(row, 0)
                comboBox.DataSource = dt
                comboBox.DisplayMember = displayMember
                comboBox.ValueMember = valueMember
            End Using
        End Using
    End Using
End Sub

Private Sub PopulateDataGridView()
    Dim query As String = "SELECT EmployeeId, Country, City FROM Employees"
    query += " WHERE Country = @Country AND City = @City"
    query += " OR ISNULL(@Country, '') = ''"
    query += " OR ISNULL(@City, '') = ''"
    Dim constr As String = "Data Source=.\SQL2005;Initial Catalog=Northwind;User ID=sa;Password=pass@123"

    Using con As SqlConnection = New SqlConnection(constr)

        Using cmd As SqlCommand = New SqlCommand(query, con)
            cmd.Parameters.AddWithValue("@Country", cbCountries.SelectedValue)
            cmd.Parameters.AddWithValue("@City", cbCities.SelectedValue)
            Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
                Dim dt As DataTable = New DataTable()
                sda.Fill(dt)
                dataGridView1.DataSource = dt
            End Using
        End Using
    End Using
End Sub

Private Sub Search(ByVal sender As Object, ByVal e As EventArgs)
    Me.PopulateDataGridView()
End Sub

Screenshot