Hi nauna,
I have created sample that full-fill your requirement. Here I have use split function to search multiple category(i.e. Dell,HP). Refer the below article to know more about split function.
SQL
CREATE FUNCTION [dbo].[SplitString]
(
@Input NVARCHAR(MAX),
@Character CHAR(1)
)
RETURNS @Output TABLE (
Item NVARCHAR(1000)
)
AS
BEGIN
DECLARE @StartIndex INT, @EndIndex INT
SET @StartIndex = 1
IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
BEGIN
SET @Input = @Input + @Character
END
WHILE CHARINDEX(@Character, @Input) > 0
BEGIN
SET @EndIndex = CHARINDEX(@Character, @Input)
INSERT INTO @Output(Item)
SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
END
RETURN
END
GO
CREATE PROCEDURE GetProduct
@Brand VARCHAR(15) = NULL
,@Processor VARCHAR(15) = NULL
,@Ram VARCHAR(15) = NULL
,@ScreenSize VARCHAR(15) = NULL
,@HardDrive VARCHAR(15) = NULL
AS
BEGIN
DECLARE @Product AS TABLE(ProductID INT IDENTITY,Brand VARCHAR(15),Processor VARCHAR(15),Ram VARCHAR(15),ScreenSize VARCHAR(15),HardDrive VARCHAR(15))
INSERT INTO @Product VALUES('Dell','i3','4GB','15','1TB')
INSERT INTO @Product VALUES('Dell','i3','8GB','15','1TB')
INSERT INTO @Product VALUES('Dell','i5','4GB','15','1TB')
INSERT INTO @Product VALUES('Dell','i5','8GB','15','1TB')
INSERT INTO @Product VALUES('Dell','i3','4GB','15','2TB')
INSERT INTO @Product VALUES('Dell','i3','8GB','15','2TB')
INSERT INTO @Product VALUES('Dell','i5','4GB','15','2TB')
INSERT INTO @Product VALUES('Dell','i5','8GB','15','2TB')
INSERT INTO @Product VALUES('HP','i3','4GB','15','1TB')
INSERT INTO @Product VALUES('HP','i3','8GB','15','1TB')
INSERT INTO @Product VALUES('HP','i5','4GB','15','1TB')
INSERT INTO @Product VALUES('HP','i5','8GB','15','1TB')
INSERT INTO @Product VALUES('HP','i3','4GB','15','2TB')
INSERT INTO @Product VALUES('HP','i3','8GB','15','2TB')
INSERT INTO @Product VALUES('HP','i5','4GB','15','2TB')
INSERT INTO @Product VALUES('HP','i5','8GB','15','2TB')
INSERT INTO @Product VALUES('Sony','i3','4GB','15','1TB')
INSERT INTO @Product VALUES('Sony','i3','8GB','15','1TB')
INSERT INTO @Product VALUES('Sony','i5','4GB','15','1TB')
INSERT INTO @Product VALUES('Sony','i5','8GB','15','1TB')
INSERT INTO @Product VALUES('Sony','i3','4GB','15','2TB')
INSERT INTO @Product VALUES('Sony','i3','8GB','15','2TB')
INSERT INTO @Product VALUES('Sony','i5','4GB','15','2TB')
INSERT INTO @Product VALUES('Sony','i5','8GB','15','2TB')
SELECT * FROM @Product
WHERE (Brand IN(SELECT ITEM FROM dbo.SplitString(@Brand, ',')) OR ISNULL(@Brand,'') = '')
AND (Processor IN(SELECT ITEM FROM dbo.SplitString(@Processor, ',')) OR ISNULL(@Processor,'') = '')
AND (Ram IN(SELECT ITEM FROM dbo.SplitString(@Ram, ',')) OR ISNULL(@Ram,'') = '')
AND (ScreenSize IN(SELECT ITEM FROM dbo.SplitString(@ScreenSize, ',')) OR ISNULL(@ScreenSize,'') = '')
AND (HardDrive IN(SELECT ITEM FROM dbo.SplitString(@HardDrive, ',')) OR ISNULL(@HardDrive,'') = '')
END
GO
HTML
<div>
<table>
<tr>
<td>
<table>
<tr>
<td>
Brand
</td>
</tr>
<tr>
<td>
<asp:CheckBoxList ID="cblBrand" runat="server" AutoPostBack="true" OnSelectedIndexChanged="PopulateProductByCategory">
<asp:ListItem Text="HP" Value="1" />
<asp:ListItem Text="Dell" Value="2" />
<asp:ListItem Text="Sony" Value="3" />
</asp:CheckBoxList>
</td>
</tr>
<tr>
<td>
Processor
</td>
</tr>
<tr>
<td>
<asp:CheckBoxList ID="cblProcessor" runat="server" AutoPostBack="true" OnSelectedIndexChanged="PopulateProductByCategory">
<asp:ListItem Text="i3" Value="1" />
<asp:ListItem Text="i5" Value="2" />
<asp:ListItem Text="i7" Value="3" />
</asp:CheckBoxList>
</td>
</tr>
<tr>
<td>
Ram
</td>
</tr>
<tr>
<td>
<asp:CheckBoxList ID="cblRam" runat="server" AutoPostBack="true" OnSelectedIndexChanged="PopulateProductByCategory">
<asp:ListItem Text="4GB" Value="1" />
<asp:ListItem Text="8GB" Value="2" />
</asp:CheckBoxList>
</td>
</tr>
<tr>
<td>
Screen Size
</td>
</tr>
<tr>
<td>
<asp:CheckBoxList ID="cblScreenSize" runat="server" AutoPostBack="true" OnSelectedIndexChanged="PopulateProductByCategory">
<asp:ListItem Text="14" Value="1" />
<asp:ListItem Text="15" Value="2" />
</asp:CheckBoxList>
</td>
</tr>
<tr>
<td>
Hard Drive
</td>
</tr>
<tr>
<td>
<asp:CheckBoxList ID="cblHardDrive" runat="server" AutoPostBack="true" OnSelectedIndexChanged="PopulateProductByCategory">
<asp:ListItem Text="500GB" Value="1" />
<asp:ListItem Text="750GB" Value="2" />
<asp:ListItem Text="1TB" Value="3" />
<asp:ListItem Text="2TB" Value="4" />
</asp:CheckBoxList>
</td>
</tr>
</table>
</td>
<td rowspan="10">
<asp:GridView runat="server" ID="gvProducts" EmptyDataText="No record for this search criteria.">
</asp:GridView>
</td>
</tr>
</table>
</div>
Code
string str = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
PopulateProduct();
}
}
private void PopulateProduct()
{
using (SqlConnection con = new SqlConnection(str))
{
using (SqlCommand cmd = new SqlCommand("GetProduct", con))
{
cmd.CommandType = CommandType.StoredProcedure;
if (cblBrand.SelectedValue != "")
{
cmd.Parameters.AddWithValue("@Brand", GetCheckedItems(cblBrand));
}
if (cblProcessor.SelectedValue != "")
{
cmd.Parameters.AddWithValue("@Processor", GetCheckedItems(cblProcessor));
}
if (cblRam.SelectedValue != "")
{
cmd.Parameters.AddWithValue("@Ram", GetCheckedItems(cblRam));
}
if (cblScreenSize.SelectedValue != "")
{
cmd.Parameters.AddWithValue("@ScreenSize", GetCheckedItems(cblScreenSize));
}
if (cblHardDrive.SelectedValue != "")
{
cmd.Parameters.AddWithValue("@HardDrive", GetCheckedItems(cblHardDrive));
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
gvProducts.DataSource = ds;
gvProducts.DataBind();
}
}
}
protected void PopulateProductByCategory(object sender, EventArgs e)
{
PopulateProduct();
}
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
Private str As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
Protected Sub Page_Load(sender As Object, e As EventArgs)
If Not IsPostBack Then
PopulateProduct()
End If
End Sub
Private Sub PopulateProduct()
Using con As New SqlConnection(str)
Using cmd As New SqlCommand("GetProduct", con)
cmd.CommandType = CommandType.StoredProcedure
If cblBrand.SelectedValue <> "" Then
cmd.Parameters.AddWithValue("@Brand", GetCheckedItems(cblBrand))
End If
If cblProcessor.SelectedValue <> "" Then
cmd.Parameters.AddWithValue("@Processor", GetCheckedItems(cblProcessor))
End If
If cblRam.SelectedValue <> "" Then
cmd.Parameters.AddWithValue("@Ram", GetCheckedItems(cblRam))
End If
If cblScreenSize.SelectedValue <> "" Then
cmd.Parameters.AddWithValue("@ScreenSize", GetCheckedItems(cblScreenSize))
End If
If cblHardDrive.SelectedValue <> "" Then
cmd.Parameters.AddWithValue("@HardDrive", GetCheckedItems(cblHardDrive))
End If
Dim da As New SqlDataAdapter(cmd)
Dim ds As New DataSet()
da.Fill(ds)
gvProducts.DataSource = ds
gvProducts.DataBind()
End Using
End Using
End Sub
Protected Sub PopulateProductByCategory(sender As Object, e As EventArgs)
PopulateProduct()
End Sub
Private Function GetCheckedItems(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
