DbType.DateTime not equivalent to OleDbType.Date

[Roy Soltoff]

Using Framework 3.5: I've converted a data base access project that was
written against the OleDbProvider to now be provider independent using a
DbProviderFactory. In doing so, I had to migrate my update and insert
commands from OleDbCommand to DbCommand created using the factory. This
meant that I changed the Parameter items to use DbType rather than
OleDbType.

The program uses a Vb Date type that is equivalent to a System.DateTime. I
previously the OleDbType.Date type for updating date columns; the database
is an Access Jet 4.0 database. So I used the DbType.DateTime type in the
provider independent code. This updates the database date columns okay only
when the date/time value is no more precise than minutes (i.e. #4/15/2009
11:45:00 AM#). If the date/time value has a precision of seconds (i.e.
#4/15/2009 11:45:25 AM#), the update fails with a "Data type mismatch in
criteria expression" error. It acts as if the mapping of DbType.DateTime
does not have the precision needed for the database date column where, in
fact, the OleDbProvider using OleDbType.Date was fine. There is a
DbType.DateTime2 that appears to reflect the same precision of a System.Date
however that type has no mapping to the OldeDbType (that was the error
message).

Does anyone know if there is a bug in the framework's conversion of
DbType.DateTime to the OleDb provider?

 

[Lingzhi Sun [MSFT]]

Hi Roy,

Thank you for your report. This problem, "OleDbParameter with
DbType.DateTime throws 'Data type mismatch in criteria expression'", has
been confirmed as a product issue by the corresponding product unit. For
detail, please see
http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?Feedbac
kID=94377.

In your post, you have mentioned that you are currently using
DbProviderFactory to do data access. To make your application work
correctly, you can refer to the following two recommended workarounds.

====================
1. Use OleDbType.Date instead of DbType.DateTime when we encounter an
OleDbParameter

For detail, please see this code snippet:
===================
// OleDb connection string templete
string ConnectionTemplate = "Provider=Microsoft.Jet.OLEDB.4.0;
Data Source={0};Persist Security Info=False;";
// OleDb data provider
string DbProvider = "System.Data.OleDb";
try
{
// Data source path
string path = Path.GetFullPath("...\\DB.mdb");
// Create the connection string
string connectionString = string.Format(ConnectionTemplate, path);

// Create the DbProviderFactory
DbProviderFactory df = DbProviderFactories.GetFactory(DbProvider);
// Create the corresponding DbConnection
DbConnection conn = df.CreateConnection();
conn.ConnectionString = connectionString;
using (DbCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO TestTable (Inserted) VALUES (?)";

// Create the corresponding DbDataParameter
IDbDataParameter pInserted = cmd.CreateParameter();
// Try to convert the DbDataParameter to OleDbParameter
OleDbParameter p = pInserted as OleDbParameter;
// If the convert fails, use DbType.DateTime
if (null == p)
pInserted.DbType = DbType.DateTime;
// Else set the OleDbType.Date
else
p.OleDbType = OleDbType.Date;
// Set the DateTime.Now value
pInserted.Value = DateTime.Now;
cmd.Parameters.Add(pInserted);

// Insert the record
conn.Open();
int recordsAffected = cmd.ExecuteNonQuery();
conn.Close();

Console.WriteLine("{0} records affected", recordsAffected);
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
===================

2. Insert DateTime.ToString() to the DbDataParameter's value

For detail, please see this code snippet:
===================
...
IDbDataParameter pInserted = cmd.CreateParameter();
pInserted.DbType = DbType.DateTime;
// Set the DateTime.Now.ToString() to the
// DbDataParameter's value
pInserted.Value = DateTime.Now.ToString();
cmd.Parameters.Add(pInserted);
...
===================

If you have any further questions related to this case, please be free to
post here.

Have a nice day!

Regards,
Lingzhi Sun ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

MSDN Managed Newsgroup support offering is for non-urgent issues where an
initial response from the community or a Microsoft Support Engineer within
2 business day is acceptable. Please note that each follow up response may
take approximately 2 business days as the support professional working with
you may need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.