Help woth SQL Command Text

Question

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

Ken · Answer

try this for access...... 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 Griffiths · Answer

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

Ken · Answer

Looks like a couple of issues ...assuming this is MS SQL 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 ' '