IDbDataParameter error in .NET

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.

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)