Filter Data From DataBase

I want to Filter data from database using CheckboxList

i have four checkboxlist values like january,February,march and April

here i want to filter data from database by clicking these checkboxlist in LINQ.

here i can put 2 values or 3 values or 4 values means i want to retrieve( jan & april)data or (Feb & march & april) like this.

How to do this??

Ex:-

Pass all the selected checkbox value in the database in coma separate and split the value in the database as like I give an example in below

CREATE FUNCTION [dbo].[fn_split]
(
@Input NVARCHAR(MAX),
@Character CHAR(1)
)
RETURNS @Output TABLE (
value 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(value)
SELECT RTRIM(LTRIM(SUBSTRING(@Input, @StartIndex, @EndIndex - 1)))
SET @Input = RTRIM(LTRIM(SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))))
END
RETURN
END

Step 1

DECLARE @tempTableData AS TABLE (id INT IDENTITY(1,1),[monthName] nvarchar(10),[column1] nvarchar(10))
INSERT INTO @tempTableData ([monthName],[column1]) VALUES ('JAN','Row1')
INSERT INTO @tempTableData ([monthName],[column1]) VALUES ('JAN','Row2')
INSERT INTO @tempTableData ([monthName],[column1]) VALUES ('FEB','Row3')
INSERT INTO @tempTableData ([monthName],[column1]) VALUES ('FEB','Row4')
INSERT INTO @tempTableData ([monthName],[column1]) VALUES ('MARCH','Row5')
INSERT INTO @tempTableData ([monthName],[column1]) VALUES ('MARCH','Row6')
INSERT INTO @tempTableData ([monthName],[column1]) VALUES ('MARCH','Row7')
INSERT INTO @tempTableData ([monthName],[column1]) VALUES ('APRIL','Row8')
SELECT * FROM @tempTableData

DECLARE @lSelectedMonthName nvarchar(100) = 'JAN,FEB'

Step 2

DECLARE @tempMonth AS TABLE ([monthName] nvarchar(10))
INSERT INTO @tempMonth ([monthName])
SELECT Value
FROM [dbo].[fn_split] (@lSelectedMonthName,',')
SELECT *
FROM @tempTableData
WHERE [monthName] IN (SELECT [monthName] FROM @tempMonth)

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

sudipta sanyal8y ago
Accepted answer
Pass all the selected checkbox value in the database in coma separate and split the value in the database as like I give an example in below
CREATE FUNCTION [dbo].[fn_split]
(
@Input NVARCHAR(MAX),
@Character CHAR(1)
)
RETURNS @Output TABLE (
value 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(value)
SELECT RTRIM(LTRIM(SUBSTRING(@Input, @StartIndex, @EndIndex - 1)))
SET @Input = RTRIM(LTRIM(SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))))
END
RETURN
END
Step 1
DECLARE @tempTableData AS TABLE (id INT IDENTITY(1,1),[monthName] nvarchar(10),[column1] nvarchar(10))
INSERT INTO @tempTableData ([monthName],[column1]) VALUES ('JAN','Row1')
INSERT INTO @tempTableData ([monthName],[column1]) VALUES ('JAN','Row2')
INSERT INTO @tempTableData ([monthName],[column1]) VALUES ('FEB','Row3')
INSERT INTO @tempTableData ([monthName],[column1]) VALUES ('FEB','Row4')
INSERT INTO @tempTableData ([monthName],[column1]) VALUES ('MARCH','Row5')
INSERT INTO @tempTableData ([monthName],[column1]) VALUES ('MARCH','Row6')
INSERT INTO @tempTableData ([monthName],[column1]) VALUES ('MARCH','Row7')
INSERT INTO @tempTableData ([monthName],[column1]) VALUES ('APRIL','Row8')
SELECT * FROM @tempTableData
DECLARE @lSelectedMonthName nvarchar(100) = 'JAN,FEB'
Step 2
DECLARE @tempMonth AS TABLE ([monthName] nvarchar(10))
INSERT INTO @tempMonth ([monthName])
SELECT Value
FROM [dbo].[fn_split] (@lSelectedMonthName,',')
SELECT *
FROM @tempTableData
WHERE [monthName] IN (SELECT [monthName] FROM @tempMonth)
MN Ambaliya8y ago
Use select statement with AND/OR conditions in your databse.
Pass the value of selected checkboxes in query, you get the filtered data as per your need.
For ex., Select * from YOUR_TABLE_NAME where Month='jan' AND Month='March'
Sujit Sahoo8y ago
Not four checkboxlist value, i have more checkboxbox list item value more than 10

then how to do this??

i also put condition for 2 or 3 value but if i have more value in that case my code is so heavy,so how to do this?

Any suggestion??