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
