Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#3.5
I am facing a problem in executing the below code (Syntax Error). Basically i want to query for a multiple OR statements for SQL server. Please help me what should i do. Thanks.
 
Database Fields: pId, pAltId, pMobile, pDate, pName
 
Variables:argID, alt_ID, p_mobile, p_date, p_Name
 
String selectStr = "select * from " + thisTable + " where pId = " + argID + " " + OR + " pAltId = " + alt_ID + " " + OR + " pMobile = " + p_mobile + " "  + OR + " pDate = '" + p_date + "' " + OR + " pName = '" + p_Name + "'";
Posted 26-Mar-13 13:59pm
Edited 26-Mar-13 14:15pm
v3
Comments
ThePhantomUpvoter at 26-Mar-13 19:09pm
   
To begin with, you have mismatched single quote characters in your query. Which could have been entirely avoided if you used a parameterized query instead of this SQL injection attack waiting to happen stuff.
Sergey Alexandrovich Kryukov at 26-Mar-13 19:17pm
   
Right. I provided a detailed answer. Please see my solution — you may find the first link very interesting. :-)
Your comment is of course credited.
—SA
Kenneth Haugland at 26-Mar-13 19:09pm
   
you seem to have confused the + signs, they shouldnt be includeed in the "+" but + " " etc.
Sergey Alexandrovich Kryukov at 26-Mar-13 19:18pm
   
May be; it's pain in the eyes to look thoroughly :-). I provided a detailed answer. Please see my solution — you may find the first link very interesting. :-)
Your comment is of course credited.
—SA
apurba001 at 26-Mar-13 19:13pm
   
Error is "The name 'OR' does not exist in the current context"
 
String selectStr = "select * from " + thisTable + " where pId = " + argID + " "
+ OR + " pAltId = " + alt_ID + " " + OR + " pMobile = " + p_mobile + " "
+ OR + " pDate = '" + p_date + "' " + OR + " pName = '" + p_Name + "'";
Sergey Alexandrovich Kryukov at 26-Mar-13 19:18pm
   
Stop doing it all. I explained what to do in my answer.
—SA
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You already got a good advice, in the comments to the question, by ThePhantomUpvoter and Kenneth Haugland.
 
This is a great example of this problem: http://xkcd.com/327/[^].
 
I'll explain what to do instead. Please see my past answer:
EROR IN UPATE in com.ExecuteNonQuery();[^],
hi name is not displaying in name?[^].
 
—SA
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

String selectStr = "select * from " + thisTable + " where pId = " + argID + "  OR   pAltId = " + alt_ID +" OR  pMobile = " + p_mobile +" OR pDate =" + p_date +" OR 
pName = '"+ p_Name +"'"
 
Something better to use would be
SqlDataReader selectCommandResult = null;
try{
SqlCommand selectCommand = new SqlCommand("SELECT * FROM @table WHERE pId = @paramId OR pAltId = @paramAltId OR pMobile = @paramMobile OR pDate = @paramDate OR pName = @paramName", SqlConnectionHolder) { CommandType = CommandType.Text };
selectCommand.Parameters.AddWithValue("@table", thisTable);
selectCommand.Parameters.AddWithValue("@paramId", argID);
selectCommand.Parameters.AddWithValue("@paramAltId", alt_ID);
selectCommand.Parameters.AddWithValue("@paramMobile", p_mobile);
selectCommand.Parameters.AddWithValue("@paramDate", p_date);
selectCommand.Parameters.AddWithValue("@paramName", p_Name);
 
selectCommandResult = selectCommand.ExecuteReader();
                var returnValue = "0";
//You should state what columns you wish to returns instead of using wild cards as table structures can change
                while (selectCommandResult.Read())
                {
                    columnOne = selectCommandResult.GetValue(0).ToString().Trim();
                    columnTwo = selectCommandResult.GetValue(1).ToString().Trim();
                    columnThree = selectCommandResult.GetValue(2).ToString().Trim();
//Etc...
                }
 

 
}
catch (Exception ex){
throw ex;
}
 
Might I also suggest you make the habit of keeping one naming convention per project. IE the below variable names you've given :
thisTable
p_mobile
alt_ID
argID
 
Should all be one convention like:
tbTableName
pMobileParam
pAltIdParam
pArgIdParam
 
It will make it easier to read in the future.
 
Further reading: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.aspx[^]
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 195
1 Afzaal Ahmad Zeeshan 152
2 ProgramFOX 130
3 Maciej Los 130
4 den2k88 80
0 OriginalGriff 6,564
1 Sergey Alexandrovich Kryukov 6,048
2 DamithSL 5,228
3 Manas Bhardwaj 4,657
4 Maciej Los 4,150


Advertise | Privacy | Mobile
Web04 | 2.8.1411022.1 | Last Updated 26 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100