Ignoring null textboxes in SQL query

Question

1.00/5 (1 vote)

See more:

Currently I'm trying to filter a query to my database, but how would I avoid causing an error if my variable for the where clause was left blank.

Right now it's currently working to support the min and max price values, but I plan allowing all variables to be used.

For instance if I had "where make like @make" and left the textbox for make empty how would I ignore that clause from the query?

What I have tried:

I tried to append the additional statements if textboxes were not empty but I'd run into problems if select boxes were still missing..

}

C#

using (SqlConnection sqlCon = new SqlConnection(con))
            {
                sqlCon.Open();
                DataTable dt = new DataTable();
                if (string.IsNullOrEmpty(make) && (string.IsNullOrEmpty(model)) && (string.IsNullOrEmpty(color)) && (string.IsNullOrEmpty(min)) && (string.IsNullOrEmpty(max)) && (string.IsNullOrEmpty(miles)))
                {
                    SqlCommand updateDataGridViewCmd = new SqlCommand("select m.make, m.model, car.price, color.color, car.mileage, carlot.lotid, car.pic from car join makemodel as m ON m.mmid = car.mmid join color ON  car.colorid = color.colorid join carlot ON  carlot.carid = car.carid; ", sqlCon);
                    
                    dt.Load(updateDataGridViewCmd.ExecuteReader());
                    
                }
                else
                {
                    StringBuilder sqlCommandText = new StringBuilder();
                    sqlCommandText.Append("select m.make, m.model, car.price, color.color, car.mileage, carlot.lotid, car.pic from car join makemodel as m ON m.mmid = car.mmid join color ON  car.colorid = color.colorid join carlot ON  carlot.carid = car.carid");
                    string commandtext = sqlCommandText.ToString();
                    SqlCommand updateDataGridViewCmd = new SqlCommand(commandtext, sqlCon);
                    
                    //updateDataGridViewCmd.Parameters.AddWithValue("@model", model);
                    //updateDataGridViewCmd.Parameters.AddWithValue("@min", min);
                    //updateDataGridViewCmd.Parameters.AddWithValue("@max", max);
                    //updateDataGridViewCmd.Parameters.AddWithValue("@mileage", miles);
                    //updateDataGridViewCmd.Parameters.AddWithValue("@color", color);
                    dt.Load(updateDataGridViewCmd.ExecuteReader());

                    if(!string.IsNullOrEmpty(make))
                    {
                        sqlCommandText.Append(" where make like @make");
                        updateDataGridViewCmd.Parameters.AddWithValue("@make", make);
                        commandtext = sqlCommandText.ToString();

                    }

                    if (!string.IsNullOrEmpty(model))
                    {
                        sqlCommandText.Append(" OR @model");
                        updateDataGridViewCmd.Parameters.AddWithValue("@model", model);
                        commandtext = sqlCommandText.ToString();

                    }

                    sqlCommandText.Append(";");
                    commandtext = sqlCommandText.ToString();
                }
                dataGridView1.DataSource = dt;
            }
        }

Posted 27-Apr-21 16:52pm

Mike Mattera (Mike Mattera)

Updated 28-Apr-21 8:36am

Add a Solution

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

Wendelius · Answer 1 · 2021-04-27T18:33:00

You didn't specify the problems you ran into but I take it one was the location of keyword WHERE

When you build the conditions dynamically you can for example use a constant condition to ensure that WHERE is always present. For example

C#

sqlCommandText.Append(@"select m.make, m.model, car.price, color.color, car.mileage, carlot.lotid, car.pic 
from car 
join makemodel as m ON m.mmid = car.mmid 
join color ON  car.colorid = color.colorid 
join carlot ON  carlot.carid = car.carid
where 1 = 1");

Another observation from your code is that the model condition seems malformed since it's missing the column name. Should it be

C#

if (!string.IsNullOrEmpty(model))
{
    sqlCommandText.Append(" OR m.model = @model");

Third thing is that if you're using OR's you most likely need to use parenthesis to ensure that proper group of conditions are handled with OR's. Otherwise you may end up getting rows that would not belong to the result set.

And last one is that while it's okay to put a semicolon to the end of the statement, you actually don't need to do this since your command has only one statement to execute.

Hope these help.

W∴ Balboos, *GHB* · Answer 2 · 2021-04-28T08:36:00

Solution 2

If you're building the query string, you can do it in a stored procedure.

then you can use conditionals and CASE statements, as appropriate, to handle your empty string and NULL values. The DBA that uses my Generic Reporting web pages handles user input (builds filters) on a routine basis. Also, you can make good use of ISNULL() and match values in tables to input values to your SP.

Cleverly done, it can even be done with "LIKE '%'" to get unfiltered results for appropriate filters although if you build the string and just skip them performance will be better. If the table's don't get very large then you can cheap-out and use the wild-card LIKE.

Posted 28-Apr-21 8:36am

W∴ Balboos, GHB

Comments

Richard Deeming 29-Apr-21 4:28am

The down-side of that approach is that SQL Server often can't choose a query plan that works well for all combinations of values.
Optional Parameters and Missing Index Requests - Brent Ozar Unlimited®[^]

W∴ Balboos, GHB 29-Apr-21 8:05am

I wasn't even going near indices. Just the simple coding with a bunch of like clauses that may or may not have content along with the %'s vs. building the query piece-by-piece and just not adding the WHERE bits that aren't used.

That second path became necessary for one of our queries, indices and all, but it was heavily but necessarily over-stuffed with optional user filters. Even then, it took a while for the tables to grow large enough for it to show (i.e., a timeout). It's been preemptively applied to a few sister queries.

Mostly, tuning is done with indices and such. My boss (DBA) knows the inner workings and fixes these. Mainly, there's a boiler-plate root design because we most of this is done by the two of us - SQL mainly by him - and productivity takes a back seat to performance at times - since no one else, here, can do this.