Ignore empty textboxes when executing sql search query

Question

0.00/5 (No votes)

See more:

I'm using an intensive search feature with around 16 text boxes to search in 16 columns of the database
the problem is that when I try to search through more than 1 text box, it usually shows all the rows in the database

what I want to achieve :
is to simply ignore all the empty fields \ text boxes in the search query itself
without dedicating a separate command for each empty text box per se
OR
In other words, I want to filter my results to show only search results of the text boxes with search data in them and ignore the others

My code till now :

C#

sqlcmd = new SqlCeCommand("Select * from Cases WHERE CaseNum LIKE @S1 "
+ "AND DisposalProsecutor LIKE @S10 AND CaseYear LIKE @S2 AND CaseRegNum LIKE @S3 AND CaseRegYear LIKE @S4 "
+ "AND CaseSubject LIKE @S5 AND AccusedNames LIKE @S6 AND ArrestOfficer LIKE @S7 AND InvestigationsOfficer LIKE @S8 "
+ "AND InterrogatorProsecutor LIKE @S9 AND InterrogationDate LIKE @S11 AND RemainingReasons LIKE @S13 AND MandateDate LIKE @S16", sqlcon);
if (S1.Text != string.Empty)
{sqlcmd.Parameters.AddWithValue("@S1", "%" + S1.Text + "%");}

if (S2.Text != string.Empty)
{sqlcmd.Parameters.AddWithValue("@S2", "%" + S2.Text + "%");}

if (S3.Text != string.Empty)
{sqlcmd.Parameters.AddWithValue("@S3", "%" + S3.Text + "%");}

if (S4.Text != string.Empty)
{sqlcmd.Parameters.AddWithValue("@S4", "%" + S4.Text + "%");}

if (S5.Text != string.Empty)
{sqlcmd.Parameters.AddWithValue("@S5", "%" + S5.Text + "%");}

if (S6.Text != string.Empty)
{sqlcmd.Parameters.AddWithValue("@S6", "%" + S6.Text + "%");}

if (S7.Text != string.Empty)
{sqlcmd.Parameters.AddWithValue("@S7", "%" + S7.Text + "%");}

if (S8.Text != string.Empty)
{sqlcmd.Parameters.AddWithValue("@S8", "%" + S8.Text + "%");}

if (S9.Text != string.Empty)
{sqlcmd.Parameters.AddWithValue("@S9", "%" + S9.Text + "%");}

if (S10.Text != string.Empty)
{sqlcmd.Parameters.AddWithValue("@S10", "%" + S10.Text + "%");}

if (S11.Text != string.Empty)
{sqlcmd.Parameters.AddWithValue("@S11", "%" + S11.Text + "%");}

if (S13.Text != string.Empty)
{sqlcmd.Parameters.AddWithValue("@S13", "%" + S13.Text + "%");}

if (S16.Text != string.Empty)
{sqlcmd.Parameters.AddWithValue("@S16", "%" + S16.Text + "%");}

    listView1.Items.Clear();

    sqldr = sqlcmd.ExecuteReader();

Posted 12-Dec-15 8:09am

Sherif Kamel

Add a Solution

3 solutions

Solution 3

I suggest you create a data structure to hold the instances of TextBoxes you use:

List<textbox> ParameterTextBoxes = new List<textbox>;

Somewhere in your code add the TextBox instances to this list.

Then, when you want to only add SQL Parameters when the TextBoxes have active content you can use Linq to filter that list:

C#

// required
using System.Linq;

foreach(TextBox tbx in ParameterTextBoxes.Where(tb => ! string.IsNullOrEmpty(tb.Text)))
{
    sqlcmd.Parameters.AddWithValue(tbx.Name, "%{0}%", tbx.Text);
}

Note: I have tested a simulation to confirm it compiles and works as expected.

I assume (hope) you are doing something to validate the Text the user enters in the TextBoxes.

Posted 12-Dec-15 8:57am

BillWoodruff

Updated 12-Dec-15 9:10am

v3

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)

Rajdeep Debnath · Accepted Answer · 2015-12-12T08:47:00

Please try the below way....

Format the sql query with stringbuilder and add where clauses as required...

var sqlcmd = new SqlCommand();

StringBuilder sb = new StringBuilder();
sb.Append("Select * from Cases WHERE ");
if (S1.Text != string.Empty)
{
sb.Append("CaseNum LIKE @S1 AND");
sqlcmd.Parameters.AddWithValue("@S1", "%" + S1.Text + "%");
}
if (S2.Text != string.Empty)
{
sb.Append("CaseYear LIKE @S2 AND");
sqlcmd.Parameters.AddWithValue("@S2", "%" + S2.Text + "%");
}

sqlcmd.Connection = connectionString;
sqlcmd.CommandText = sb.ToString();
sqlcmd.CommandType = CommandType.Text;

listView1.Items.Clear();

sqldr = sqlcmd.ExecuteReader();

Rajdeep Debnath · Accepted Answer · 2015-12-12T09:41:00

I have added a bool switch to take care of that....

compacted the code

C#

static void Main()
        {
            var S1 = new System.Web.UI.WebControls.TextBox();
            var S2 = new System.Web.UI.WebControls.TextBox();
            var S3 = new System.Web.UI.WebControls.TextBox();
            var S4 = new System.Web.UI.WebControls.TextBox();

            S1.Text = "abcd";
            S2.Text = "abcd";
            S3.Text = "abcd";
            S4.Text = "abcd";

            //this bool switch will take care that
            bool isSetAnd = false;

            var sqlcmd = new SqlCommand();

            StringBuilder sb = new StringBuilder();
            sb.Append("Select * from Cases WHERE ");
            generateQueryText(S1.Text, "S1", ref sb, ref isSetAnd, ref sqlcmd);
            generateQueryText(S2.Text, "S2", ref sb, ref isSetAnd, ref sqlcmd);
            generateQueryText(S3.Text, "S3", ref sb, ref isSetAnd, ref sqlcmd);
            generateQueryText(S4.Text, "S4", ref sb, ref isSetAnd, ref sqlcmd);
            
            Console.WriteLine(sb.ToString());
            sqlcmd.CommandText = sb.ToString();
            sqlcmd.CommandType = CommandType.Text;

            //listView1.Items.Clear();

            //sqldr = sqlcmd.ExecuteReader();
        }

        private static void generateQueryText(string textBoxText, string textBoxID, ref StringBuilder sb, ref bool isSetAnd, ref SqlCommand sqlcmd)
        {
            if (textBoxText != string.Empty)
            {
                if (isSetAnd)
                {
                    sb.Append(" AND ");
                }
                isSetAnd = true;
                sb.Append("CaseRegYear LIKE @" + textBoxID);
                sqlcmd.Parameters.AddWithValue("@" + textBoxID, "%" + textBoxText + "%");
            }
        }

Ignore empty textboxes when executing sql search query

3 solutions

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0