Monday, April 25, 2011

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));



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

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.

1 comment:

  1. You have to use DataTypeCompatibility=80 in yout connectionstring.