Error in my WHERE clause - please help

[Alex]

Why am I getting the following error?

/*
TYPE: System.Data.OleDb.OleDbException
MESSAGE: Operator/operand type mismatch.
*/

It happens at the ExecuteReader() statement.

I am trying to access a dbf file and it works when I don’t include any
dates in my WHERE clause (i.e. my clause looks like: WHERE termcd =
‘SA’). Here is the relevant code:

/*
string sql = "SELECT tel FROM results WHERE termcd = 'SA' AND lcdate

= " + dateFrom.Value.ToString(@"MM/dd/yyyy") + " AND lcdate <= " +

dateTo.Value.ToString(@"MM/dd/yyyy");
OleDbConnection con = new OleDbConnection(resultsFolder);
OleDbCommand cmd = new OleDbCommand(sql, con);
OleDbDataReader r = null;

con.Open();
r = cmd.ExecuteReader();
*/

Do I need to surround the dates with hashes or something for them to
register as dates?

 

[Roy Fine]

Alex,

The two date literal values need to be enclosed in the appropriate literal
string delimiter.

Your SQL string gets expanded into something like this:

SELECT tel FROM results
WHERE termcd = 'SA'
AND lcdate >= 05/05/2004 AND lcdate <= 05/06/2004

Also - you should consider using the BETWEEN clause - try something like
this

SELECT tel FROM results
WHERE termcd = 'SA'
AND lcdate BETWEEN '05/05/2004' AND '05/06/2004'

regards

roy fine

 

[Jon Skeet [C# MVP]]

The two date literal values need to be enclosed in the appropriate literal
string delimiter.

Your SQL string gets expanded into something like this:

SELECT tel FROM results
WHERE termcd = 'SA'
AND lcdate >= 05/05/2004 AND lcdate <= 05/06/2004

Also - you should consider using the BETWEEN clause - try something like
this

SELECT tel FROM results
WHERE termcd = 'SA'
AND lcdate BETWEEN '05/05/2004' AND '05/06/2004'

Rather than specify the dates in the SQL itself, you should also
consider (very strongly!) using command parameters. See
OleDbCommand.Parameters for an example.

 

[Alex]

Alex,

The two date literal values need to be enclosed in the appropriate literal
string delimiter.

Your SQL string gets expanded into something like this:

SELECT tel FROM results
WHERE termcd = 'SA'
AND lcdate >= 05/05/2004 AND lcdate <= 05/06/2004

Also - you should consider using the BETWEEN clause - try something like
this

SELECT tel FROM results
WHERE termcd = 'SA'
AND lcdate BETWEEN '05/05/2004' AND '05/06/2004'

Thanks that's what I needed to do but with {} braces.