I am trying to use an SQL command in my code but i am having trouble using WHERE & AND in the statement. I am using an OLEDB connection to a database.
The command text i am using is,
string commandText = "SELECT * FROM Table1 WHERE Date = " + selDate + " AND Location = " + selLocation + " AND Route = " + selRoute;
I just get an OLEDB Exception "Syntax error (missing operator) in query expression 'Date = 12/05/2008 00:00:00 AND Location = UK AND Route = 20'"
The command will run if i just use "SELECT * FROM Table1" but when i add a WHERE or AND i get exceptions.
I use this statement in the following: OleDbDataAdaptor adaptor = new OleDbDataAdaptor(commandText, conn)
How do i get the statement to work with WHERE & AND?
Many Thanks
KenPosted Jul 25, 2008, 3:47 PM
select table1.* from table1 where ((table1.date) =#01/01/2008# ) and ((table1.location) ="UK") and ((table1.route) = "20")
not sure about the (()) but see if that works
Antony GriffithsPosted Jul 25, 2008, 3:25 PM
Hi
I am using VS2005 and the data comes from an MS Access file. I have put those changes into the code but now i get a different exception,
'Data type mismatch in criteria expression'
The fields in the Access file are
Date = Date/Time
Depot No = Text
Route = Text
In the C# code the types are
Date = Date/Time
Depot No = string
Route = string
Do you know where its going wrong.
Many Thanks
KenPosted Jul 25, 2008, 2:57 PM
output should read
select * from table1 where [date]='01/01/2008' and location ='UK' and route = 20
the column name date is a reserved name in SQL thus you need the []
I am also assuming that the data type for date is date/time.... for location is varchar ....and route integer
for date/time and varchar data type you will need the ' '