Development Central

I’m by no means an expert on ADO.NET. Most of the time I go through an O/RM (Gentle.NET) to handle persistent storage. (It’s a nice product, but it’s no longer being maintained.) A recent project needed to read an Access database, and we had built some SQL Server-specific layers on top of Gentle, so I dropped to ADO.NET and used OleDb to hit the Jet drivers.

Everything was working in my tests. When I tried it on a different machine, though, it failed with “Data type mismatch in criteria expression.” The Jet DLLs were the same. The base MDAC version was the same. The relevant schema in the database was the same.

I still don’t know what caused it, but I know how to fix it. I was using DbType to set the type of the SQL parameters. This was not mapping correctly to the OleDbType. For instance, DbType.String was mapping to OleDbType.VarWChar when the actual type was WChar (similarly with date types).

To fix it, I used View/Server Explorer in Visual Studio, connected to the database, then checked the column properties. I copied and pasted the OleDbType values into the code and set OleDbType directly for the parameters. Then I removed the connection in Server Explorer.

Note that this was all hand-coded C# - I was porting working code over from Delphi, and was not using any visual tools.