Filter ASP.Net GridView based on DropDownList and CheckBoxList using C# and VB.Net

Last Reply 2 months ago By pandeyism

Posted 2 months ago

Hello Sir,

I'm trying to display the data in gridview based on if condition.

For example:

I have 7 checkboxlist for 7 days and a dropdown for service.

The user has to select what type of service and check the checkboxlist for days.

assume service A is selected on days(monday,wednesday , friday), so here in service column it should display only one value for service not all.

 

Thanks

Posted 2 months ago

Hey democloud,

Please refer below sample.

In this sample i have used split function to search multiple columns, Refer the below article to know more about split function.

Split function in SQL Server Example: Function to Split Comma separated (Delimited) string in SQL Server 2005, 2008 and 2012

SQL

CREATE TABLE ServiceDay(Service VARCHAR(100), Day VARCHAR(50))
INSERT INTO ServiceDay VALUES('A','Monday')
INSERT INTO ServiceDay VALUES('A','Tuesday')
INSERT INTO ServiceDay VALUES('B','Wednesday')
INSERT INTO ServiceDay VALUES('B','Friday')
INSERT INTO ServiceDay VALUES('C','Monday')
INSERT INTO ServiceDay VALUES('C','Wednesday')
INSERT INTO ServiceDay VALUES('C','Friday')
GO
CREATE PROCEDURE ServiceDay_getServiceDays
	@Service VARCHAR(100) = NULL,
	@Day VARCHAR(100) = NULL
AS
BEGIN
	SELECT * FROM ServiceDay
	WHERE (Service LIKE @Service + '%' OR @Service IS NULL) AND (Day IN(SELECT ITEM FROM dbo.SplitString(@Day, ',')) OR ISNULL(@Day,'') = '')
END
GO 
EXEC ServiceDay_getServiceDays

HTML

Services :
<asp:DropDownList ID="chkServices" runat="server">
    <asp:ListItem Text="Select Services" Value="0" />
    <asp:ListItem Text="A" Value="A" />
    <asp:ListItem Text="B" Value="B" />
</asp:DropDownList>
<br />
Days :
<asp:CheckBoxList ID="chkDays" runat="server">
    <asp:ListItem Text="Monday" Value="Monday" />
    <asp:ListItem Text="Tuesday" Value="Tuesday" />
    <asp:ListItem Text="Wednesday" Value="Wednesday" />
    <asp:ListItem Text="Thursday" Value="Thursday" />
    <asp:ListItem Text="Friday" Value="Friday" />
    <asp:ListItem Text="Saturday" Value="Saturday" />
    <asp:ListItem Text="Sunday" Value="Sunday" />
</asp:CheckBoxList>
<br />
<asp:Button Text="Search" runat="server" OnClick="Search" />
<br />
<asp:GridView runat="server" ID="gvServices" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="Service" HeaderText="Service" />
        <asp:BoundField DataField="Day" HeaderText="Day" />
    </Columns>
</asp:GridView>

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 Search(object sender, EventArgs e)
{
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand("ServiceDay_getServiceDays", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Service", chkServices.SelectedItem.Value);
            cmd.Parameters.AddWithValue("@Day", GetCheckedItems(chkDays));
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                DataTable dt = new DataTable();
                da.Fill(dt);
                gvServices.DataSource = dt;
                gvServices.DataBind();
            }
        }
    }
}

private string GetCheckedItems(CheckBoxList checkBoxList)
{
    string checkedItems = string.Empty;
    foreach (ListItem item in checkBoxList.Items)
    {
        if (item.Selected)
        {
            checkedItems += item.Text + ",";
        }
    }
    checkedItems = checkedItems.Remove(checkedItems.Length - 1);
    return checkedItems;
}

VB.Net

Protected Sub Search(ByVal sender As Object, ByVal e As EventArgs)
    Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
        Using cmd As SqlCommand = New SqlCommand("ServiceDay_getServiceDays", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@Service", chkServices.SelectedItem.Value)
            cmd.Parameters.AddWithValue("@Day", GetCheckedItems(chkDays))
            Using da As SqlDataAdapter = New SqlDataAdapter(cmd)
                Dim dt As DataTable = New DataTable()
                da.Fill(dt)
                gvServices.DataSource = dt
                gvServices.DataBind()
            End Using
        End Using
    End Using
End Sub

Private Function GetCheckedItems(ByVal checkBoxList As CheckBoxList) As String
    Dim checkedItems As String = String.Empty

    For Each item As ListItem In checkBoxList.Items

        If item.Selected Then
            checkedItems += item.Text & ","
        End If
    Next

    checkedItems = checkedItems.Remove(checkedItems.Length - 1)
    Return checkedItems
End Function

Screenshot