Tom Goff's .Net Musings

Tidbits of information regarding .Net, C#, and SQL Server.

Decimal Troubles

leave a comment »

I ran into an annoyance relating to the Decimal data today. While testing my .Net application, I was getting an OverflowException when I was trying to run a query on SQL Server. The text of the exception was (simply) “Conversion overflows” and was coming from SqlBuffer (a private ADO.Net class) via SqlDataReader.

Specifically, the exception was coming from the get accessor of SqlBuffer’s Decimal property. Using Lutz Roeder’s .Net Reflector, I could see that this property was hard coded to throw an OverflowException if the scale of a decimal value was greater than 28. The SQL query I was running did include a column whose datatype was decimal and had a scale larger than 28.

Looking at the SQL Server documentation, the decimal data type can have a precision from 1 to 38 and a scale from 0 to the precision. So “decimal(38, 38)” is a valid data type in SQL Server. Looking at the .Net documentation, the Decimal data type can have a scale between 0 and 28. So I was receiving an OverflowException, because I was trying to select data that was supported by SQL Server, but not by .Net.

If you try to get the results of the following query using ADO.Net, then you will get the same exception:

    DECLARE @testVar DECIMAL(38, 29) 
    SET @testVar = 1.1 
    SELECT @testVar AS TestCol

As usual, I’ve put together a sample application if you would like to see this in action.

Advertisements

Written by Tom

April 26, 2007 at 3:29 pm

Posted in .Net, Ado.Net, C#, Gotcha

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: