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

Question

0.00/5 (No votes)

See more:

, +

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 point 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 method 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).

Posted 19-Jan-22 2:22am

Tinsae Teku

Updated 19-Jan-22 9:11am

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2022-01-19T02:37:00

Solution 1

Simplest approach - Use the concept of repeated filtering

C#

if(age == 12) {
    if(gender == "male") {
        if(grade == 2) {

            ...
        }
    }
}

You will only have as many ifs as you have attributes

Posted 19-Jan-22 2:37am

CHill60

Comments

Tinsae Teku 19-Jan-22 8:56am

For 4 attributes, we need the following combinations: id, age, sex, grade, id&age, id&grade, id&sex, sex&age, sex&grade, age&grade, id,sex&age, id,sex&grade, id,age&grade, age,sex&grade, id,sex,age&grade. Imagine that for 8 columns, that would be very tiresome.

OriginalGriff · Answer 2 · 2022-01-19T02:40:00

Solution 2

Build it up as a string collection: each attribute calls a method which appends to a List<string> if the associated check is ticked.
When you've assembled them all, use string.Join[^] to combine them:

C#

string sql = $"SELECT * FROM MyTable WHERE {string.Join(" AND ", clausesList)}";

The only complication comes if there are "variable values" you need to compare against (Age = textBoxContent for example) where you will need to create a paramaters collection and use that as well in your final SqlCommand object.

Posted 19-Jan-22 2:40am

OriginalGriff

Comments

Tinsae Teku 19-Jan-22 8:50am

If I use string.Join[^] to combine them, it works. The issue comes when the checkbox isn't clicked. I used a textbox to store the specific item on the checkbox and its value is 'null' by default. So if the checkbox isn't checked, it starts to search null value from the database and displays nothing.

OriginalGriff 19-Jan-22 9:12am

So make the check "is it checked and is the textbox empty?"

Tinsae Teku 19-Jan-22 9:40am

If the textbox is empty, then how am I gonna write the code? The system keep searching for 'null'. But I need it to just search only the checked items (for example 'age' and 'sex') and doesn't search for the empty parts. But it searches for '12', 'male' and 'null'. Then since there is no 'null' value in the database columns value, it displays 'no result'. But I want it to search only '12' and 'male'.

OriginalGriff 19-Jan-22 10:03am

Are you trying to be obtuse here?
What is the problem with checking two values instead of one?

_Asif_ · Answer 3 · 2022-01-19T09:11:00

Its not that difficult. Approach could be like

* HashMap<string, string> filterMaps = new HashMap<string, string>()
* for each column (Id, Age, sex, group, etc)
   filterMap["Id"] = 001
   filterMap["Age"] = 12
* ArrayList list = new ArrayList()
*  foreach element in filterMap
     list.Add(element.key + " = " + element.value)
* string finalWhereClause string.join(" AND ", list)
* Build finalSQL and execute it