OleDbException: Data type mismatch in criteria expression.

Question

I read data from MS Access using C#. But get the OleDbException trying to execute such query:

SELECT * FROM Flats 
WHERE Flats.VersionStamp <= [theDate] AND Flats.Flat=[theFlat]

OleDbException:

Data type mismatch in criteria expression.

On the other side, any one of the following queries works fine:

SELECT * FROM Flats 
WHERE Flats.VersionStamp <= [theDate] AND Flats.Flat=1

SELECT * FROM Flats 
WHERE Flats.VersionStamp <= #1/1/2009# AND Flats.Flat=[theFlat]

The C# code stays the same all the time:

DbParameter theFlat = new OleDbParameter("theFlat", 1);
DbParameter theDate = new OleDbParameter("theDate", new DateTime(2009, 1, 1));

using (DbDataReader reader = dbHelper.ExecuteReader(sqlText, theFlat, theDate))
{ }

Finally, the query can be successfully executed directly in the MS Access UI.

What is wrong here?

Answer 1

Where are you defining/using the parameters in your SQL String; I don't see them.

Try this:

SELECT * From Flats WHERE VersionStamp = @theDate AND Flat = @theFlat

DbParameter = new OleDbParameter ("@theDate", someDate);

Answer 2

I am not sure but I don't think the OleDb classes support named parameters. Try the following SQL instead:

SELECT * FROM Flats WHERE Flats.VersionStamp <= ? AND Flats.Flat=?

The parameters must be added to the command object in the right order (I don't see you adding the parameters in your code).

Answer 3

http://support.microsoft.com/default.aspx?scid=kb;en-us;316744

Contrary to what the preceding documentation error describes, the OleDbCommand parameters are positional when they are used with the Microsoft SQL Server OLE DB provider. The names of the parameters can be arbitrary... The order of the parameters that you add to the OleDbParameterCollection must match the order of the parameters in your stored procedure.