IDbDataParameter error in .NET






2.40/5 (7 votes)
How to solve the error when assigning a DateTime to a IDbDataParameter.
Introduction
I wanted to update a project of mine, which eases database access using Generics, Attributes, and Reflection. My original project used database classes like SqlConnection
, SqlCommand
, and SqlDataReader
, but to support more database types (MS SQL, MS MDB, and MS SDF), I needed to isolate database dependent code and use the general interfaces like IDbConnection
, IDbCommand
, and IDataReader
.
When I write data back to the database, I now use code like this:
IDbCommand command = connection.CreateCommand();
// ... adding each parameter like this
IDbDataParameter param = command.CreateParameter();
param.DbType = DbType.DateTime;
param.ParameterName = <Field Name>;
param.Value = DateTime.New;
command.Parameters.Add(param);
command.CommandText = <some SQL code with @-codes>;
command.Transaction = null;
command.CommandTimeout = 2000;
command.ExecuteNonQuery();
Data type mismatch in criteria expression
When I assigned a DateTime
value to the param.Value
, I got the "Data type mismatch in criteria expression" exception.
After spending some hours on the Internet, I found a solution where a guy converted the DateTime
object to a string
, like this:
param.DbType = DbType.DateTime;
param.ParameterName = <Field Name>;
param.Value = DateTime.New.ToString();
Error in the .NET Framework
Funny though, the DateTime
assignment worked in one place in my application, but not in other places. After trying some things out, I discovered this to be an error in the .NET Framework.
If the assigned DateTime
contains a milliseconds value other then 0 (zero), an exception is thrown. If the milliseconds is 0, then no exception is thrown.
So this will work:
param.Value = new DateTime(2006, 05, 04);
because the constructed DateTime
has a milliseconds value of 0.