Filter data using multiple ASP.Net CheckBox in C# and VB.Net

Last Reply one month ago By pandeyism

Posted one month ago

I am trying to filter data using dropdown list (skills, state, city) but in skills dropdown i am having check box so user can select multiple skills to filter out the data in code behind

I am not getting how to write code to filter the above data.

<div class="dropdown ">
    <button class="dropbtn">
        --Skills--</button>
    <div class="dropdown-content" id="drp">
        <a href="#"><asp:CheckBox ID="CheckBox1" runat="server" Text="Asp.Net C#" CssClass="styled" /></a>
        <a href="#"><asp:CheckBox ID="CheckBox2" runat="server" Text="Python" CssClass="styled" /></a>
        <a href="#"><asp:CheckBox ID="CheckBox3" runat="server" Text="Android" CssClass="styled" /></a>
        <a href="#"><asp:CheckBox ID="CheckBox4" runat="server" Text="Php" CssClass="styled" /></a>
        <a href="#"><asp:CheckBox ID="CheckBox5" runat="server" Text="Java" CssClass="styled" /></a>
        <a href="#"><asp:CheckBox ID="CheckBox11" runat="server" Text="HTML" CssClass="styled" /></a>
        <a href="#"><asp:CheckBox ID="CheckBox6" runat="server" Text="Accounting" CssClass="styled" /></a>
        <a href="#"><asp:CheckBox ID="CheckBox7" runat="server" Text="Account Management" CssClass="styled" /></a>
        <a href="#"><asp:CheckBox ID="CheckBox8" runat="server" Text="Machine Learning (ML)" CssClass="styled" /></a>
        <a href="#"><asp:CheckBox ID="CheckBox9" runat="server" Text="Graphic Design" CssClass="styled" /></a>
        <a href="#"><asp:CheckBox ID="CheckBox10" runat="server" Text="Travel and Tourism" CssClass="styled" /></a>
        <a href="#"><asp:CheckBox ID="CheckBox12" runat="server" Text="Human Resource (HR)" CssClass="styled" /></a>
        <a href="#"><asp:CheckBox ID="CheckBox13" runat="server" Text="Software Development" CssClass="styled" /></a>
        <a href="#"><asp:CheckBox ID="CheckBox14" runat="server" Text="Auditing" CssClass="styled" /></a>
    </div>
</div>
<br />
<b>State:</b>
<asp:DropDownList ID="state" AutoPostBack="True" runat="server" class="form-control input-lg"
    Width="50%" DataSourceID="SqlDataSource2" DataTextField="State" DataValueField="SId"
    AppendDataBoundItems="true" OnSelectedIndexChanged="state_SelectedIndexChanged">
    <asp:ListItem Value="0">--Select--</asp:ListItem>
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
    SelectCommand="SELECT * FROM [State]"></asp:SqlDataSource>
<br />
<b>City:</b>
<asp:DropDownList ID="City" AutoPostBack="True" runat="server" class="form-control input-lg"
    Width="50%" DataSourceID="SqlDataSource3" DataTextField="City" DataValueField="Id"
    AppendDataBoundItems="false">
    <asp:ListItem Value="0">--Select--</asp:ListItem>
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
    SelectCommand="SELECT [City], [Id] FROM [City] WHERE ([SId] = @SId)">
    <SelectParameters>
        <asp:SessionParameter Name="SId" SessionField="sid" Type="Int32" />
    </SelectParameters>
</asp:SqlDataSource>
<br />
<asp:Button ID="Button2" runat="server" Text="Search" OnClick="Button2_Click" CssClass="btn btn-primary" />

 

protected void Button2_Click(object sender, EventArgs e)
{
    string joinskill = "";
    if (CheckBox1.Checked)
    {
        joinskill += CheckBox1.Text + ",";
    }
    if (CheckBox2.Checked)
    {
        joinskill += CheckBox2.Text + ",";
    }
    if (CheckBox3.Checked)
    {
        joinskill += CheckBox3.Text + ",";
    }
    if (CheckBox4.Checked)
    {
        joinskill += CheckBox4.Text + ",";
    }
    if (CheckBox5.Checked)
    {
        joinskill += CheckBox5.Text + ",";
    }
    if (CheckBox6.Checked)
    {
        joinskill += CheckBox6.Text + ",";
    }
    if (CheckBox7.Checked)
    {
        joinskill += CheckBox7.Text + ",";
    }
    if (CheckBox8.Checked)
    {
        joinskill += CheckBox8.Text + ",";
    }
    if (CheckBox9.Checked)
    {
        joinskill += CheckBox9.Text + ",";
    }
    if (CheckBox10.Checked)
    {
        joinskill += CheckBox10.Text + ",";
    }
    if (CheckBox11.Checked)
    {
        joinskill += CheckBox11.Text + ",";
    }
    if (CheckBox12.Checked)
    {
        joinskill += CheckBox12.Text + ",";
    }
    if (CheckBox13.Checked)
    {
        joinskill += CheckBox13.Text + ",";
    }
    if (CheckBox14.Checked)
    {
        joinskill += CheckBox14.Text + ",";
    }
    string conString = ConfigurationManager.ConnectionStrings["Q_SS_regnConnectionString1"].ConnectionString;
    if (City.SelectedValue != "" && state.SelectedValue != "")
    {
        string query = "SELECT * FROM Post_job_details where Skills='" + joinskill.ToString() + "' and City='" + City.SelectedItem.ToString() + "' and State='" + state.SelectedItem.ToString() + "' and  Id NOT IN (Select JobId from JS_Resume where email='" + Session["mail"].ToString() + "') and Last_Apply_date>=CURRENT_TIMESTAMP ";
        SqlCommand cmd = new SqlCommand(query);
        using (SqlConnection con = new SqlConnection(conString))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataSet ds = new DataSet())
                {
                    sda.Fill(ds);
                    DataList1.DataSource = ds;
                    DataList1.DataBind();
                }
            }
        }
    }
    else
    {
        string query = "SELECT * FROM Post_job_details where Id NOT IN (Select JobId from JS_Resume where email='" + Session["mail"].ToString() + "') and Last_Apply_date>=CURRENT_TIMESTAMP";
        SqlCommand cmd = new SqlCommand(query);
        using (SqlConnection con = new SqlConnection(conString))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataSet ds = new DataSet())
                {
                    sda.Fill(ds);
                    DataList1.DataSource = ds;
                    DataList1.DataBind();
                }
            }
        }
    }
}

 

Posted one month ago

Hi itsme,

Refer below sample.

HTML

<div class="dropdown ">
    <button class="dropbtn">
        --Country--</button>
    <div class="dropdown-content" id="drp" runat="server">
        <asp:CheckBox Text="India" runat="server" ID="chkInd" />
        <br />
        <asp:CheckBox Text="United States" runat="server" ID="chkUSA" />
        <br />
        <asp:CheckBox Text="France" runat="server" ID="chkFrance" />
        <br />
        <asp:CheckBox Text="Russia" runat="server" ID="chkRussia" />
        <br />
        <asp:Button Text="Search" runat="server" OnClick="FilterData" />
        <br />
        <asp:GridView runat="server" ID="gvCustomers" />
    </div>
</div>

Namespaces

C#

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

VB.Net

Imports System.Data.SqlClient
Imports System.Data

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataTable dt = GetData("SELECT CustomerId, Name,Country FROM Customers");
        gvCustomers.DataSource = dt;
        gvCustomers.DataBind();
    }
}

private static DataTable GetData(string query)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    SqlConnection con = new SqlConnection(constr);
    SqlCommand cmd = new SqlCommand(query, con);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);
    return dt;
}

protected void FilterData(object sender, EventArgs e)
{
    string query = "SELECT CustomerId, Name,Country FROM Customers";
    query += " WHERE Country IN (";
    foreach (CheckBox chk in drp.Controls.OfType<CheckBox>())
    {
        if (chk.Checked)
        {
            query += "'" + chk.Text + "'" + ",";
        }
    }
    query = query.Substring(0, query.Length - 1);
    query += ")";
    DataTable dt = GetData(query);
    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
        Dim dt As DataTable = GetData("SELECT CustomerId, Name,Country FROM Customers")
        gvCustomers.DataSource = dt
        gvCustomers.DataBind()
    End If
End Sub

Private Shared Function GetData(ByVal query As String) As DataTable
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim con As SqlConnection = New SqlConnection(constr)
    Dim cmd As SqlCommand = New SqlCommand(query, con)
    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
    Dim dt As DataTable = New DataTable()
    da.Fill(dt)
    Return dt
End Function

Protected Sub FilterData(ByVal sender As Object, ByVal e As EventArgs)
    Dim query As String = "SELECT CustomerId, Name,Country FROM Customers"
    query += " WHERE Country IN ("

    For Each chk As CheckBox In drp.Controls.OfType(Of CheckBox)()
        If chk.Checked Then
            query += "'" & chk.Text & "'" & ","
        End If
    Next
    query = query.Substring(0, query.Length - 1)
    query += ")"
    Dim dt As DataTable = GetData(query)
    gvCustomers.DataSource = dt
    gvCustomers.DataBind()
End Sub

Screenshot