ASP.Net single quotes embedded SQL multiple conditions

J

JB

Hello Community

I am using ASP.Net with C# with embedded SQL query using single quotes.
I am passing mulitple parameters with multiple conditions such that the "AND"
and the "OR" conditions need to be on one line or atleast be considered as
one statement:

strSql = " Select * " ;
strSql += " From table1 " ;
strSql += " Where qty = " + amt ;
strSql += " AND color = ' " + color1 + " ' " ;
strSql += " OR material = ' " + silk + " ' " ;

In the past I could satisfy the condition in the ASP.Net C# portion
using codebehind but in this case I don't have that option.

Can anyone tell me how I can put these 2 conditions on one line or use
parenthesis around them the same as you would in a mathematical expression
that and make the "AND" and "OR" (2 conditions ) considered as one condition
or statement ?

Thanks
Jeff

JB
 
H

Harlan Messinger

JB said:
Hello Community

I am using ASP.Net with C# with embedded SQL query using single quotes.
I am passing mulitple parameters with multiple conditions such that the "AND"
and the "OR" conditions need to be on one line or atleast be considered as
one statement:

strSql = " Select * " ;
strSql += " From table1 " ;
strSql += " Where qty = " + amt ;
strSql += " AND color = ' " + color1 + " ' " ;
strSql += " OR material = ' " + silk + " ' " ;

In the past I could satisfy the condition in the ASP.Net C# portion
using codebehind but in this case I don't have that option.

Can anyone tell me how I can put these 2 conditions on one line or use
parenthesis around them the same as you would in a mathematical expression
that and make the "AND" and "OR" (2 conditions ) considered as one condition
or statement ?

You seem to be under the impression that it matters in the slightest how
many lines you use in building the string. There is no significance to
this at all. You can just as well have

strSql = "Select * from table1 where ";
strSql += " qty = amt AND color = " ;
strSql += "'" + color1 + "' OR material = 'silk'";

You can put parentheses for grouping in the WHERE clause anywhere in the
string they belong.
 
J

JB

Hello Harlan

Thanks! Your solution pointed out something that I didn't understand
when using this method of embedded sql.

Jeff
 
G

Guest

Hello Harlan

    Thanks!  Your solution pointed out something that I didn't understand
when using this method of embedded sql.

    Jeff

Most likely you will love

strSql = @"Select * from table1 where
qty ='" + amt + "' AND color =
'" + color1 + "' OR material = '"silk"'";

or

strSql = string.Format(@"Select * from table1 where
qty = '{0}' AND color =
'{1}' OR material = '{2}' ",
amt,
color,
silk);

Hope this helps
 
P

Patrice

Hello,

Unrelated to this specific issue, but you may want to consider using
parameters :
http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx

Not listed but IMO one of the key benefit is that values embedded in the SQL
string could cause problems if not well done i..e :
- you have to replace ' with '' in strings
- you have to use a date format that match your server language (or better
use a format such as YYYMMDD that works regardless of the server settings)
- to take extra care if your code runs in a country that doesn't use . as a
decimal separator (else you'll get 2,5 rather than 2.5 in your SQL
statement).

With parameters you'll just work with the actual data type...
 
G

Guest

Hello,


Unrelated to this specific issue, but you may want to consider using
parameters :http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx

Not listed but IMO one of the key benefit is that values embedded in the SQL
string could cause problems if not well done i..e :
- you have to replace ' with '' in strings
- you have to use a date format that match your server language (or better
use a format such as YYYMMDD that works regardless of the server settings)
- to take extra care if your code runs in a country that doesn't use . as a
decimal separator (else you'll get 2,5 rather than 2.5 in your SQL
statement).

With parameters you'll just work with the actual data type...

I agree with suggestion of Patrice.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top