CODEWITHSUNDEEP
c#asp.netsql-server
moe
moe

Reputation: 5249

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

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();
     }
}

Upvotes: 0

Views: 2367

Answers (1)

Steve
Steve

Reputation: 216302

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

Upvotes: 2

Related Questions