Sql – get an Arithmetic overflow error converting numeric to data type numeric in ADO.NET

ado.netnetsqlsql-server-2008sqlcommand

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

Best Answer

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.

Related Topic