selecting from different condition

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

hi All,

i have 6 columns
like col1,col2,col3,......col6

now i need to fetch records based on conditions

C#

if (txt1.text!="" &&txt2.text!="" && txt3.text!="" && txt4.text!="" &&txt5.text!="" && txt6.text!="")
{
query="select * from table1 where col1='" + txt1.text + " '  and col2='" + txt2.text + " '  and col3='" + txt3.text + " ' and  col4='" + txt4.text + " ' and col5=' "+ txt5.text + "' and col6='" + txt6.text + "'";
}
else if (txt1.text!="" &&txt2.text!="" &&txt3.text!="" && txt4.text!="" &&txt5.text!="" && txt6.text="")

{
 query="select * from table1 where col1='" + txt1.text + "' and col2='" + txt2.text + ' " and col3='" + txt3.text +"' and  col4=' "+txt4.text+"' and col5='"+txt5.text+"'" ;
}

else if (txt1.text!="" &&txt2.text!="" &&txt3.text!="" && txt4.text!="" &&txt5.text="" && txt6.text="")
{
query="select * from table1 where col1=" ' + txt1.text + '" and col2='" +  txt2.text + "'  and col3=' " +txt3.text + "' and  col4=' " +txt4.text+ "' ";

}
else if (txt1.text="" &&txt2.text!="" &&txt3.text!="" && txt4.text!="" &&txt5.text="" && txt6.text!="")
{
query="select * from table1 where  col2='" + txt2.text + "'  and col3=' " +txt3.text + "' and  col4=' "+ txt4.text + "'  and col6='"+txt6.text +"'";

}
else if.........
:
:
:
:
:
:
:

else
{
}

continues all the condtions

this means it has about 6! =720 conditions
is there any shortcut to implement in this type of situations

Thank You.

Posted 4-Jan-15 17:37pm

Member 10476757

Updated 4-Jan-15 21:31pm

v4

Add a Solution

Comments

deepakdynamite 4-Jan-15 23:43pm

Can you write down your complete query as well ??? Also in below condition what would be the value of txt6.text

else if (col1=txt1.text &&col2=txt2.text &&col3=txt3 && col4=txt4.text &&col5=txt5.text && col6="")

Member 10476757 5-Jan-15 3:53am

hi jus updated pls check

Suvendu Shekhar Giri 4-Jan-15 23:46pm

You need to provide the sample query too so that one can suggest you appropriate solution.

Member 10476757 5-Jan-15 3:53am

hi just now edited check the updated

Richard Deeming 19-Feb-15 7:45am

Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

3 solutions

Solution 1

There is a another way to do this. here is the Simple example i give you

for Instance ,you want to select data by 4 different Conditions

SQL

select Col1,Col2,COl3,Col4 from table where col1=1 and col2=2 and col3=3 and col4=4

SQL

Query="Select  Col1,Col2,COl3,Col4 from table where 1=1 ";
if(col1!='')
Query=Query+ " and Col1=1";

if(Col2!='')
Query=Query+" and Col2=2";

if(Col3!="")
Query=Query+" and Col3=3";

if(Col4!="")
Query=Query+" and Col4=4";

//run Your Query
I suggest you to create this as Stored Procedure

Edited:

Building Dynamic SQL In a Stored Procedure[^]

Posted 4-Jan-15 18:01pm

King Fisher

Updated 4-Jan-15 18:05pm

v4

Comments

Member 10476757 5-Jan-15 3:49am

it is not restricted to only 5 to 10 conditions the conditions go on like a loop upto 720 conditions

King Fisher 5-Jan-15 4:08am

you don't need any Loop or 720 Conditions here.

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)

Suvendu Shekhar Giri · Accepted Answer · 2015-01-04T22:25:00

Oh dear ! No need to write 720 if else conditions. Only a single line of code can do the same job. A small trick to be implemented using logical OR operator.

C#

query="select * from table1 where ('"+txt1.Text+"'='' OR col1='"+txt1.Text+"') and ('"+txt2.Text+"'='' OR col2='"+txt2.Text+"') and ('"+txt3.Text+"'='' OR col3='"+txt3.Text+"') and ('"+txt4.Text+"'='' OR col4='"+txt4.Text+"') and ('"+txt5.Text+"'='' OR col5='"+txt5.Text+"') and ('"+txt6.Text+"'='' OR col6='"+txt6.Text+"')";

Don't put any if else condition around this.

Hopefully, I have saved your time wasting in writing if else conditions.
In case you want to use this in a stored procedure, you can check my blog post on SQL Server: An optimal way to create procedure with multiple optional parameters[^]

In case this doesn't help, please let me know :)

Update (Preventing SQL Injection)
As @CHill60 suggested, you should consider improving your querying method to prevent SQL Injection. Either convert it to a stored procedure or following parametrized query may help.

C#

query="select * from table1 where (@txt1='' OR col1=@txt1) and (@txt2='' OR col2=@txt2) and (@txt3='' OR col3=@txt3) and (@txt4='' OR col4=@txt4) and (@txt5='' OR col5=@txt5) and (@txt6='' OR col6=@txt6)";
//SqlCommand cmd = new SqlCommand(Query);
cmd.Parameters.AddWithValue("@txt1", txt1.Text.Trim());
cmd.Parameters.AddWithValue("@txt2", txt2.Text.Trim());
cmd.Parameters.AddWithValue("@txt3", txt3.Text.Trim());
cmd.Parameters.AddWithValue("@txt4", txt4.Text.Trim());
cmd.Parameters.AddWithValue("@txt5", txt5.Text.Trim());
cmd.Parameters.AddWithValue("@txt6", txt6.Text.Trim());
//execute the command

:)

deepakdynamite · Accepted Answer · 2015-01-05T01:57:00

You can write your Query as

Select * from table1 where col1=ISNULL(@Col1,col1) and Col2=ISNULL(@Col2,Col2) and Col3=ISNULL(@Col3,Col3)

and later on you can write as below:

C#

cmd.Parameters.AddWithValue("@txt1", txt1.Text.Trim()== string.Empty ? null :txt1.Text.Trim());

In any case it will give you correct number of Records and no need to write any other code.

selecting from different condition

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0