Syntax for "OR" in WHERE clause passing parameters from C# ASP.NET

  • Thread starter Thread starter JB
  • Start date Start date
J

JB

Hello Community

I am passing parameters SQL Server from an C# ASP.NET app. When
creating the query I have need to use an “or†operator after the Where clause
when comparing values. Does anyone know the syntax for this type of thing?

Below is the code:

public DataSet getCustomer(string Addr, int PhoneNo)
{

string strSQL = " select distinct a.firstname + ' ' +
a.lastname as Name";

strSQL += " from table1 a Join Table2 b ";

strSQL += " on a.id = b.id ";

strSQL += " WHERE a.Addr = " + Addr;

strSQL += " AND (b.PhoneNo = + PhoneNo OR b.PhoneNo <>
PhoneNo);



strSQL += " order by a.lastname, a.firstname ";

}


If I only wanted to check whether the PhoneNo was equal the following code
works:

strSQL += " AND c.PhoneNo = " + PhoneNo;

But I want to get a result returned whether the PhoneNo is equal or the
PhoneNo is not
That means the question is:

When passing variable and comparing values what is the syntax for the “orâ€
operator within the Where clause and are the parenthesis necessary?

Thanks
Jeff
 
JB said:
Hello Community

I am passing parameters SQL Server from an C# ASP.NET app.
When
creating the query I have need to use an “or†operator after the Where
clause
when comparing values. Does anyone know the syntax for this type of
thing?

Below is the code:

public DataSet getCustomer(string Addr, int PhoneNo)
{

string strSQL = " select distinct a.firstname + ' ' +
a.lastname as Name";

strSQL += " from table1 a Join Table2 b ";

strSQL += " on a.id = b.id ";

strSQL += " WHERE a.Addr = " + Addr;

strSQL += " AND (b.PhoneNo = + PhoneNo OR b.PhoneNo <>
PhoneNo);



strSQL += " order by a.lastname, a.firstname ";

}


If I only wanted to check whether the PhoneNo was equal the following code
works:

strSQL += " AND c.PhoneNo = " + PhoneNo;

But I want to get a result returned whether the PhoneNo is equal or the
PhoneNo is not
That means the question is:

When passing variable and comparing values what is the syntax for the “orâ€
operator within the Where clause and are the parenthesis necessary?

Thanks
Jeff
Click to expand...

You use or and you might need brackets to group the two alternative
conditions.
 
Andy said:
You use or and you might need brackets to group the two alternative
conditions.
Click to expand...

Please don't confuse my fellow American. To us, "brackets" = "square
brackets". We group with "parentheses".
 
Hello Harlan

Do you know the correct syntax for this "OR" statement in the WHERE claus:

public DataSet getCustomer(string Addr, int PhoneNo)
{

string strSQL = " select distinct a.firstname + ' ' +
a.lastname as Name";

strSQL += " from table1 a Join Table2 b ";

strSQL += " on a.id = b.id ";

strSQL += " WHERE a.Addr = " + Addr;

strSQL += " AND (b.PhoneNo = + PhoneNo OR b.PhoneNo <>
PhoneNo);



strSQL += " order by a.lastname, a.firstname ";

}


Thanks
Jeff
 
Hello Andy

Do you know the correct syntax for this "OR" statement in this WHERE
claus:

public DataSet getCustomer(string Addr, int PhoneNo)
{

string strSQL = " select distinct a.firstname + ' ' +
a.lastname as Name";

strSQL += " from table1 a Join Table2 b ";

strSQL += " on a.id = b.id ";

strSQL += " WHERE a.Addr = " + Addr;

strSQL += " AND (b.PhoneNo = + PhoneNo OR b.PhoneNo <>
PhoneNo);



strSQL += " order by a.lastname, a.firstname ";

}


Thanks
Jeff
 
Hi,
Do you know the correct syntax for this "OR" statement in this WHERE
claus:

public DataSet getCustomer(string Addr, int PhoneNo)
{

string strSQL = " select distinct a.firstname + ' ' +
a.lastname as Name";

strSQL += " from table1 a Join Table2 b ";

strSQL += " on a.id = b.id ";

strSQL += " WHERE a.Addr = " + Addr;

strSQL += " AND (b.PhoneNo = + PhoneNo OR b.PhoneNo <>
PhoneNo);
Click to expand...

Several issues here :
- it would be something such as : strSQL+=" AND (b.PhoneNo="+PhoneNo+" OR
b.PhoneNo<>"+PhoneNo+")";

BUT :
- If PhoneNo (and Addr ?) is not just a number but a string you'll need to
add additional quotes
- not sure how it is usefull, if you want those lines that both match and
don"t match, then just drop this condition.
- it's better to use parameters
(http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson06.aspx) as it :
- help to avoid SQL injection attacks
- takes care of details such as doubling ' when ' is found inside a
string and transports values correctly regardless of the country (else
you'll have to do that yourself)

Finally the query seems useless. You join both table both the distinct will
just return a unique value for fistname, lastname, basically joining b adds
no value (or is ti just a starting point ?)...
 
Hello Patrice

Thank you for your response. Actually I am using parameters, I posted
this question as an "short example" of the actual "long query", because I
need the syntax for one of the parameters that I passed to the query.
Fortunately if I have to I can test that parameter in C# before I pass it so
that it will be decided before it gets to the query.

Thank you
Jeff

JB
 
Back
Top