OleDbException | Exception on Inserting a Date

The exception in question is an OleDbException with the message: "The fractional part of the provided time value overflows the scale of the corresponding SQL Server parameter or column. Increase bScale in DBPARAMBINDINFO or column scale to correct this error."

I was receiving this error when inserting a row that contained a DateTime in Sql Server.

Firstly, Some Context
The context is that I have an application that I'm volunteering some time on that has OleDbConnections/OleDbCommands scattered through many pages. It used to connect to a Microsoft Access database, but I moved it to Sql Server and ended up using a connection string that uses the SQLNCLI10 provider (Originally the SQLNCLI provider, but that wasn't provided on the host that we signed up for) I didn't want to change all the occurrences of OleDbConnection etc obviously.

I received the above mentioned exception and had a quick look, but didn't find anything obvious as to why this might be happening. The exception is somewhat clear, but why this was happening in my case was not so clear.

The Test Code
I made a simple test application that talked to a database to simply insert a date. The code that fails is thus:

string query = "INSERT INTO DateTimeTest ([Date]) VALUES (?)";

var date = DateTime.Now;

OleDbConnection connection = new OleDbConnection(
    connectionString );

OleDbCommand command = new OleDbCommand( query, connection );
command.Parameters.Add(new OleDbParameter( "Date", date));

connection.Open();

command.ExecuteNonQuery();

Fairly simple stuff. Note that the connection string is stored in a variable called connectionString and looks something like this:

string connectionString = "PROVIDER=SQLNCLI10;database=TESTING;Server=localhost\\instance_name; trusted_connection=yes";

The Solution
The solution to my problem was to change the way that I was adding the date as a parameter to the command so that the command knew that it was a DateTime; like this:

command.Parameters.Add( "Date", OleDbType.Date ).Value = date;

I now no longer get the exception and my code works. Yay! :oD

Explanation
I'm not sure exactly why the failing code failed, but I suspect that it might be something along the lines of that the provider doesn't know or try to determine the type that the OleDb type that the parameter should be and therefore might just call .ToString() on the date time and try and insert it in this manner, and thus the error. I've not verified that this is what is happening though.

Inserting a Date Time into Microsoft Access in Microsoft .NET (C#)

I just had an issue, in a project that I've inherited, where dates are are formatted for insertion into a query by calling .ToString() on the DateTime structure. This worked fine until I changed the culture, which caused the date to be formatted in a way that Microsoft Access didn't like... which resulted in me finding out that this was going on. 

I'm going to show two ways, the quick way, and the parameterized query way.

Query Contatenation Method

Please note that I (and probably a lot of other people) would not generally recommend this as a solution. If you concatenate strings together to form your queries you are opening yourself up to Sql injection attacks if you don't know or aren't careful with what you are doing.

Say we have a query like this, which is a simplified example of what was causing the error mentioned:

string sql = "INSERT INTO TEST (MyDateTime) VALUES(#" 
    + DateTime.Now + "#)"

By default ToString() will be called on the DateTime.Now value (which returns a DateTime structured) and the ToString() method will format the DateTime based on the current culture of the thread that you are working on. This is not what we want because Microsoft Access might either 1. Not be able to parse the date resulting in an error or 2. Could pass the date wrongly resulting in odd dates when you retrieve them from the database later.

The quickest/dirties/simplest way to fix this is to do something like this instead of DateTime.Now:

DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

So as as simple example the query could be:

string sql = "INSERT INTO TEST (MyDateTime) VALUES(#" 
    + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "#)";

Parameterized Query Method
A better way to structure the code is to create a Parameterized Query which will mean

1. No need to format the date, we just pass it as a parameter to the query
2. We don't need to worry about correctly quoting the date when it is converted to a string (In the above example I've used "#" characters each side of the date string. You can use single quotes also though.)
3. We don't need to do any concatenation as the query is considered essentially a template string
4. This method avoids places where you might accidently or ignorantly open yourself up to Sql injections attacks.

The code would be something like the following. (Note that this code doesn't include error handling code, I just made as simple an example as possible)

OleDbConnection connection = null;
string sql= "INSERT INTO TEST (MyDateTime) VALUES(?)";
OleDbCommand command = new OleDbCommand(sql, connection);
CmdCheck.Parameters.Add( new OleDbParameter("MyDateTime"
    , DateTime.Now));
connection.Dispose();

In the above example the date will be converted to a correctly formatted string and the ? inside the VALUES() clause of the query will be replaced with this date (with correct escaping etc.)