Filter ASP.Net GridView based on multiple ListBox values using C# and VB.Net

Hi RayMilhon,

Refer below example.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

<table>
    <tr>
        <td>
            <asp:ListBox ID="lstCountries" runat="server" AutoPostBack="true"
                OnSelectedIndexChanged="OnSelectedIndexChanged" Height="200" />
        </td>
        <td>
            <asp:ListBox ID="lstColumns" runat="server" Height="200" />
        </td>
    </tr>
    <tr>
        <td>
            <asp:TextBox runat="server" ID="txtSearch" /></td>
        <td>
            <asp:Button ID="btnSubmit" Text="Submit" runat="server" OnClick="Submit" />
        </td>
    </tr>
</table>
<hr />
<asp:GridView runat="server" ID="gvCustomers"></asp:GridView>

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.BindListBox("SELECT DISTINCT TOP 12 Country FROM Customers", lstCountries);
    }
}

protected void OnSelectedIndexChanged(object sender, EventArgs e)
{
    this.BindListBox("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'Customers'", lstColumns);
}

protected void Submit(object sender, EventArgs e)
{
    this.BindGridView();
}

private void BindListBox(string query, ListBox lstControl)
{
    lstControl.Items.Clear();
    using (SqlConnection con = new SqlConnection())
    {
        con.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            con.Open();
            using (SqlDataReader sdr = cmd.ExecuteReader())
            {
                while (sdr.Read())
                {
                    lstControl.Items.Add(new ListItem { Text = sdr[0].ToString(), Value = sdr[0].ToString() });
                }
            }
            con.Close();
        }
    }
}

private void BindGridView()
{
    string query = string.Format("SELECT {0} FROM Customers WHERE {0} LIKE '{1}%' AND Country = '{2}'",
        lstColumns.SelectedValue, txtSearch.Text, lstCountries.SelectedValue);
    using (SqlConnection con = new SqlConnection())
    {
        con.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    gvCustomers.DataSource = dt;
                    gvCustomers.DataBind();
                }
            }
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Me.BindListBox("SELECT DISTINCT TOP 12 Country FROM Customers", lstCountries)
    End If
End Sub

Protected Sub OnSelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
    Me.BindListBox("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'Customers'", lstColumns)
End Sub

Protected Sub Submit(ByVal sender As Object, ByVal e As EventArgs)
    Me.BindGridView()
End Sub

Private Sub BindListBox(ByVal query As String, ByVal lstControl As ListBox)
    lstControl.Items.Clear()
    Using con As SqlConnection = New SqlConnection()
        con.ConnectionString = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using cmd As SqlCommand = New SqlCommand(query, con)
            con.Open()
            Using sdr As SqlDataReader = cmd.ExecuteReader()
                While sdr.Read()
                    lstControl.Items.Add(New ListItem With {
                        .Text = sdr(0).ToString(),
                        .Value = sdr(0).ToString()
                    })
                End While
            End Using
            con.Close()
        End Using
    End Using
End Sub

Private Sub BindGridView()
    Dim query As String = String.Format("SELECT {0} FROM Customers WHERE {0} LIKE '{1}%' AND Country = '{2}'",
                                        lstColumns.SelectedValue, txtSearch.Text, lstCountries.SelectedValue)
    Using con As SqlConnection = New SqlConnection()
        con.ConnectionString = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using cmd As SqlCommand = New SqlCommand(query, con)
            Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
                Using dt As DataTable = New DataTable()
                    sda.Fill(dt)
                    gvCustomers.DataSource = dt
                    gvCustomers.DataBind()
                End Using
            End Using
        End Using
    End Using
End Sub

Screenshot