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

Tinsae Telu 21 Reputation points
2022-01-19T13:28:47.757+00:00

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

ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,077 questions
ASP.NET
ASP.NET
A set of technologies in the .NET Framework for building web applications and XML web services.
3,219 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,483 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,097 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Bruce (SqlWork.com) 53,426 Reputation points
    2022-01-19T16:22:35.993+00:00

    while you can dynamically build the sql and add parameters, a common approach is to use null as an no indicator

    select *
    from myTable
    where (id = @id or @id is null)
    and (sex = @purty or @purty is null)
    and (age = @age or @age is null)
    and (grade = @grade or @grade is null)


  2. AgaveJoe 25,866 Reputation points
    2022-01-19T19:47:09.183+00:00

    This forum can only review code that we can see. We do know you and have no idea how to explain a concept in a way you understand. If you want a more accurate response then ask a detailed question.

    We need sample code that runs and illustrates how your code works. This will help us help you as we will be able to determine, from the source code, if you are using ADO.NET or something else. Next, explain the steps to reproduce the problem, the expected results, and the actual results.


  3. Karen Payne MVP 35,016 Reputation points
    2022-01-19T21:07:15.45+00:00

    Well hopefully my code sample now makes sense, make sure data is clean and in expected condition. When a property does not allow null, using data annotations prevents null values and like shown in the user name password we can get into more than simple annotations.

    We use generic validators in class projects, the one below I created for teaching purposes while the same is true for enterprise level validation.

    166535-validators.png


  4. Albert Kallal 4,566 Reputation points
    2022-12-04T11:22:48.407+00:00

    Ok, this is a fantastic question. One we should keep as an interview question!

    I have since Foxpro, VB, Access, SQL server and more systems have ALWAYS had to provide some kind of filter system.

    This is really a business "101" type of question! Anyone writing software for a company? You will encounter this type of problem!

    And of course, this issue looms:

    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.

    yes, sort of!!!

    And there is a great way to deal with this issue.

    First up, one can write SQL or even a stored procedure, and setup to have "optional" parameters. It is however still messy.

    That approach involves setting a default for the paramter - like say -1, and then where (@City = '-1' OR City = @City).

    But, there is a better way.

    As you note, if the user does NOT enter a value/select a check box/set a dropdown, then we are to "ignore" those choices.

    In other words, we are "ADD UP" the filter choices so to speak.

    So, say I have this filter - just plain jane markup:

    266896-image.png

    Now, if you look closely, I have 6 possible choices (6! = 720 possible combinations).
    (and love that you introduced n! (factorial) here - reminds me of high school math)

    However, the trick is to keep in mind that the choices are cumulative!

    Thus, all we have to do is write our filter code to "add" each control if filter, or ignore.

    So, we can rather easy build up a filter (sql where clause).

    the 2nd VERY important issue is we STILL need to use SQL parameters (no sql injection allowed - right!!!).

    so, the way the filter code looks is like this:

    Now, for the check boxes, we actually don't need SQL paramters. And I don't have any input text box, but for safety, the follow code shows I can BOTH add SQL parmaters on the fly and also direct sql condiations (for when I don't need a paramter).

    Most don't realize that the Sqlcommnd allows you to add parmaters, and it DOES NOT care about the SQL it has (yet!!!).

    So, the code looks like this. We can have 2, or 20 filters. All you do is just keep on appending/adding to the where clause string.

    And for any case to avoid sql injeciton then simple add/use a @ paramter.

    So, the code looks like this:

        protected void cmdSearch_ServerClick(object sender, EventArgs e)  
            {  
            string strSQL = "SELECT * FROM tblHotels ";  
            string strOrderBy = " ORDER BY HotelName";  
            string strWhere = "";  
    
            SqlCommand cmdSQL = new SqlCommand();  
    
            if (chkDescripiton.Checked)  
            {  
                if (strWhere != "") strWhere += " AND ";  
                strWhere += "(Description is not null)";  
            }  
    
            if (chkActiveOnly.Checked)  
            {  
                if (strWhere != "") strWhere += " AND ";  
                strWhere += "(Active = 1)";  
            }  
    
            if (chkBalconyOnly.Checked)  
            {  
                if (strWhere != "") strWhere += " AND ";  
                strWhere += "(Balcony = 1)";  
            }  
    
            if (chkWaterOnly.Checked)  
            {  
    
                if (strWhere != "") strWhere += " AND ";  
                strWhere += "(LakeView = 1)";  
            }  
    
            if (cboCity.SelectedIndex >0)  
            {  
                if (strWhere != "") strWhere += " AND ";  
                strWhere += "(City = @City)";  
                cmdSQL.Parameters.Add("@City", SqlDbType.NVarChar).Value = cboCity.Text;  
            }  
            if (cboProvince.SelectedIndex >0)  
            {  
                if (strWhere != "") strWhere += " AND ";  
                strWhere += "(Province = @Province)";  
                cmdSQL.Parameters.Add("@Province", SqlDbType.NVarChar).Value = cboProvince.Text;  
            }  
    
            cmdSQL.CommandText = strSQL;  
            if (strWhere != "")  
                cmdSQL.CommandText += " WHERE " + strWhere;  
    
            cmdSQL.CommandText += strOrderBy;  
            LoadGrid(cmdSQL);  
        }  
    

    Note how we written the code. We just add/have/write a code stub for each filter control. We just keep on appending (building up) the where clause.

    And the code works if NO filter is selected.

    And note how we handle the optional @ parmaters. In those cases, you add BOTH to the "where clause" we are building up, and also add the paramter to the sql where clause!!

    So, after the above code runs, we call loadgrid, and that is this simple code:

        void LoadGrid(SqlCommand cmdSQL)  
        {  
            DataTable rstData = MyRstP(cmdSQL);  
            GridView1.DataSource = rstData;  
            GridView1.DataBind();  
        }  
    

    And of course, our little helper routine MyRstP (it just returns a table on passed SqlCommand object)

            public DataTable MyRstP(SqlCommand cmdSQL)  
            {  
                DataTable rstData = new DataTable();  
                using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))  
                {  
                    using (cmdSQL)  
                    {  
                        cmdSQL.Connection = conn;  
                        conn.Open();  
                        rstData.Load(cmdSQL.ExecuteReader());  
                    }  
                }  
                return rstData;  
            }  
    

    And that is quite much it.

    The results thus look like this:

    266897-coolfilter.gif

    0 comments No comments