How can I search only checked items from database [ASP.NET]

Let me explain my issue using a simple example: I have a table with attributes 'id' , 'age', 'sex' and 'grade'. Then I have many student's data list. I have already managed to insert specific data (For example: '0001', '12', 'male' and 'B+') into the checkbox. Now if '12' and 'male' are checked, I want my gridview to display only students whose sex are male and age is 12. If I checked three attributes, I want data with three of the attributes, not just one or two of them. To do that I have to use if...else and I am expected to do something like if(age && sex) or if(age && sex && grade) or if(grade) ... But the issue is I got so many columns (attributes). In order to address all combinations of 8 columns, I need to have 8! (8*7*6*5*4*3*2*1) i.e. 40,000+ if-else scenarios. That is like impossible. So is there any solution I can deal with this? I have already built a beautiful system and left with this issue only. Your help means a lot!

What I have tried:

I have already tried to use checkbox list. But that is only for list items of one column. But I need to take the items from different columns (4 in my previous example).

1 Reply

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

Nitin Sontakke4y ago
Hello,
You forgot to mention where this data is persisted. Which database system you are using?

Is it is safe to assume that it is Microsoft SQL Server?

If yes, are you using stored procedure? Or there are select statements in your code?

Please do let us know these details as it helps answer the question.

Assuming it is a SQL Server and assuming, again that there is stored procedure, this is very simple with a trick.

Given below is the HINT, not the actual code.
create procedure [dbo].[DemoProcedure]
(
   @Age   integer = null
  ,@Sex   varchar(20) = null
  ,@Grade varchar(20) = null
)
as
begin

-- execute [dbo].[DemoProcedure] null, 'male', null
-- execute [dbo].[DemoProcedure] 12, 'male', null
-- execute [dbo].[DemoProcedure] 12, null, null
-- execute [dbo].[DemoProcedure] null, null, 'B+'

  set nocount on;

  select *
  from [DemoTable]
  where 1 = 1
    and [Age] = isnull(@Age, [Age])
    and [Sex] = isnull(@Sex, [Sex])
    and [Grade] = isnull(@Grade, [Grade])

end
go
Hope it helps!