ASP.NET 4 MVC Decimal Overflow with Fluent NHibernate and SQL Server 2008 RC2

Question

The following problem is running on a web API application in ASP.NET MVC 4 connected in an SQL Server 2008 R2 by Fluent NHibernate.

So I have this form that can store a decimal number that can hold a value to 15 integers and 14 decimals places. The database column is defined as decimal(29,14) so as the mapped property Map((x) => x.Factor).Column("FACTOR").Precision(29).Scale(14).Not.Nullable().

The data of that field should hold any value in this mask 999999999999999.99999999999999, but it does't. That number causes an OverflowException. I believe that is because of the number limitation, described in its reference: C# and SQL Server.

I really don't understand this notation, -7.9x1028 to 7.9x1028 (from the C# reference) or -10^38 +1 to 10^38 -1 (from the SQL Server reference), but I think that what limits the number are the SQL Server decimals, because the error is on the Transaction commit action. The ViewModel shows the right number.

What precision/scale do I need to set on the table column in order to accept the application value?

jbl jbl · Accepted Answer · 2013-10-22T08:43:09

I really don't understand this notation, -7.9x1028 to 7.9x1028 (from the C# reference)

I prefer the explanation in Decimal Structure (MSDN).

The binary representation of a Decimal value consists of a 1-bit sign, a 96-bit integer number, and a scaling factor used to divide the 96-bit integer and specify what portion of it is a decimal fraction. The scaling factor is implicitly the number 10, raised to an exponent ranging from 0 to 28. Therefore, the binary representation of a Decimal value the form, ((-2⁹⁶ to 2⁹⁶) / 10^{(0 to 28)}), where -(2⁹⁶-1) is equal to MinValue, and 2⁹⁶-1 is equal to MaxValue.

Your decimal number should be written 9 (repeated 29 times) / 10¹⁴.

The problem is that 9 (repeated 29 times), which is greater than 9x10²⁸, is too big to be convertible to 96-bits integer representation (which max value is the 7.9x10²⁸ of your first definition, which you will retrieve in the remarks of the link above)

In fact, if your try to write

decimal d = 99999999999999999999999999999m;

you will get an error stating "Overflow in constant value computation".

I guess the thing to remember is: You can have up to 29 significant digits as long as these significant digits don't form an integer greater than 7.9x10²⁸

It seems that no error is issued when writing

decimal d = 999999999999999.99999999999999m ;

but some rounding happens. Running some tests on your issue with an SQLite database, it happens that the number passed as parameter is 1000000000000000.0000000000000 which is (29,13) and is not convertible to (29,14).

Trying to insert 1000000000000000.0000000000000 in a (29,14) column results in "Arithmetic overflow error converting numeric to data type numeric."

ASP.NET 4 MVC Decimal Overflow with Fluent NHibernate and SQL Server 2008 RC2

1 Answers