Arithmetic overflow error converting varchar to data type numeric in asp.net

Question

I am trying to insert a record that has decimal place, example: 7654.00 and the data type for this column is numeric(10,12) but I am getting arithmetic overflow error and I know I need to do a conversion first but not sure how..

The column I have the issue with is TotalCost.

Here is what I have:

string FullName = row.Cells[1].Text;
string TotalCost = row.Cells[6].ToString(); 

using (SqlConnection myCon = new SqlConnection(myConnStr))
{
    using (SqlCommand myCmd = new SqlCommand())
    {
         myCmd.Connection = myCon;
         myCmd.CommandType = CommandType.Text;
         myCmd.CommandText = @"insert into myTable (FullName, TotalCost) 
                               values(@FullName, @TotalCost)";
         myCmd.Parameters.AddWithValue("@FullName", FullName.ToString());

         myCmd.Parameters.AddWithValue("@TotalCost", TotalCost)

         myCon.Open();
         myCmd.ExecuteNonQuery();
         myCon.Close();
     }
}

If TotalCost is numeric, then it needs to be converted to decimal\double and then passed as myCmd.Parameters.AddWithValue('@TotalCosts') — Sergey Litvinov
– Sergey Litvinov, Commented Oct 26, 2015 at 23:12
AddWithValue has no way to know that you are passing a double if the second parameter is a string. Avoid it — Steve
– Steve, Commented Oct 26, 2015 at 23:13
NUMERIC(10,12) is impossible - that would mean a number with 10 digits all together, 12 of which to the right side of the decimal point.... — marc_s
– marc_s, Commented Oct 27, 2015 at 5:50

Steve · Accepted Answer · 2015-10-26 23:36:10Z

AddWithValue is a convenient shortcut to add a parameter, but has serious limitations as explained in these two blog posts

Can We Stop using AddWithValue already?
How Data Access Code Affects Database Performance

In your case, you are passing a string as second argument in the AddWithValue for the parameter @TotalCost and AddWithValue, diligently, pass a string to your database engine resulting in the mentioned error.

You should convert your string to a decimal value (It seems more appropriate to use a decimal for money values) and then add the parameter using a more explict declaration of your datatype

 string TotalCost = row.Cells[6].ToString(); 
 decimal cost;
 if(!decimal.TryParse(TotalCost, out cost))
     // Put here an error message for your user
     // "The value cannot be converted to a decimal value"
 else
 {
    using (SqlConnection myCon = new SqlConnection(myConnStr))
    using (SqlCommand myCmd = new SqlCommand())
    {
        myCmd.Connection = myCon;
        myCmd.CommandType = CommandType.Text;
        myCmd.CommandText = @"insert into myTable (FullName, TotalCost ) 
                              values(@FullName, @TotalCost)";
        myCmd.Parameters.Add("@FullName", SqlDbType.NVarChar).Value = FullName;
        myCmd.Parameters.Add("@TotalCost", SqlDbType.Decimal).Value = cost;
        myCon.Open();
        myCmd.ExecuteNonQuery();
   }
}

Of course you should adapt this code to the actual datatype of your TotalCost column on the datatable

Collectives™ on Stack Overflow

Arithmetic overflow error converting varchar to data type numeric in asp.net

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related