none
arithmetic overflow error converting int to data type numeric

    Question

  • HI, I am developing an application with VB.Net as front end and SQL Server 2008 as backend. I have a text box in a windows form. The text box is defined as NumericOnly and maxlength of 6 in properties. Iam trying to save the data in the textbox to a table field in sql server which has been defined as [decimal](5, 2). If I click on the save button, iam getting the following error

    "arithmetic overflow error converting int to data type numeric". Can anybody help? The vb.net code is as below

    objSqlStatement = New StringBuilder
    objSqlStatement.Append("UPDATE [Table_Name] SET ")               
    objSqlStatement.Append("[Field_Name] = " & CDbl(textbox1.Text.Trim) & " ")
    objSqlStatement.Append("WHERE [Group_Id] = " & Group_Id)
    objActivateDB.ExecuteNonQuery(objSqlStatement.ToString)

     I have tried Convert.ToDecimal instead of CDbl but no use
    • Moved by Bob BeaucheminMVP Tuesday, May 13, 2014 11:47 AM Moved to the forum for client-side ADO.NET for best results
    Tuesday, May 13, 2014 5:34 AM

Answers

  • You are constructing the SQL statement in the wrong way:

    cmd.CommandText = "UPDATE Table_name " & _
                 "SET Field_name = @fieldvalue" & _
                 "WHERE Group_id = @group_id"
    cmd.Parameters.Add("@fieldvalue", SqlDbType.Decimal, 5, 2).Value &_
        CDbl(textbox1.Text.Trim)
    cmd.Parameters.Add("@group_id", SqlDbType.Int) = Group_id
    cmd.ExecuteNonQuery()

    The types you specify should match the database columns. I had to make a guess the Group_id.

    It appears that you call some local object that accepts a query string as a parameter. That is just wrong. Maybe that objActivateDB also accepts parameters; I don't know about that. But the important thing is that you should never interpolate values into the query string, but always use parameters. It makes your code simpler to write, and you are protected against SQL injection.

    And also, don't add bracket around your column names - it only makes the code more difficult to read.

    As for the error, it appears that you have entered a value > 999.99 in your textbox. Don't do that. It will not fit your database column. But maybe you should change the field to not permit entering of such a value to start with.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, May 13, 2014 7:43 AM

All replies

  • Change your datatype to decimal(10,2) and see.

    declare @s decimal(5, 2)=1234567 --Error
    Select @s
    
    declare @s decimal(10, 2)=1234567
    Select @s

    Tuesday, May 13, 2014 5:39 AM
  • Which is the number you have inserted in the textbox?


    SQLhint.com

    Tuesday, May 13, 2014 7:07 AM
  • You are constructing the SQL statement in the wrong way:

    cmd.CommandText = "UPDATE Table_name " & _
                 "SET Field_name = @fieldvalue" & _
                 "WHERE Group_id = @group_id"
    cmd.Parameters.Add("@fieldvalue", SqlDbType.Decimal, 5, 2).Value &_
        CDbl(textbox1.Text.Trim)
    cmd.Parameters.Add("@group_id", SqlDbType.Int) = Group_id
    cmd.ExecuteNonQuery()

    The types you specify should match the database columns. I had to make a guess the Group_id.

    It appears that you call some local object that accepts a query string as a parameter. That is just wrong. Maybe that objActivateDB also accepts parameters; I don't know about that. But the important thing is that you should never interpolate values into the query string, but always use parameters. It makes your code simpler to write, and you are protected against SQL injection.

    And also, don't add bracket around your column names - it only makes the code more difficult to read.

    As for the error, it appears that you have entered a value > 999.99 in your textbox. Don't do that. It will not fit your database column. But maybe you should change the field to not permit entering of such a value to start with.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, May 13, 2014 7:43 AM