views:

254

answers:

1

Original Question:

Why do I get an Arithmetic overflow error converting numeric to data type numeric in ADO.NET code using the Money Data Type when amount is greater than $999,999,99?


Just as the question says... I have a bit of ADO.net code in the data access layer that talks to a Sql Server 2008 database. There is an Amount column in the table that is of data type "Money". The code works fine when inserting a record with an amount < $1,000,000 but throws this error when amount is >= $1,000,000:

"Arithmetic overflow error converting numeric to data type numeric"

I can manually run t-sql against the database updating the amount to a value larger than $1,000,000 so the database can except the amount fine... what is it about the following SqlCommand that causes the error to fire?

        MyCommand.Parameters.Add(New SqlParameter("@Amount", SqlDbType.Money))
        If IsNothing(Amount) Then
            MyCommand.Parameters("@Amount").Value = Convert.DBNull
        Else
            MyCommand.Parameters("@Amount").Value = Amount
        End If
A: 

If an error occurs calling a stored procedure with a value, but no error occurs when directly updating the table with that same value, then that casts suspition upon the data type of the @Amount parameter. Make sure it is also defined as Money.

Jeffrey L Whitledge
As you pointed out through your question... Amount was a decimal but the sql column was money. Once I converted the value before calling the Stored Procedure it worked perfect: CType(Amount, Data.SqlTypes.SqlMoney). Thanks!
theminesgreg