SQL UPDATE Command causes Arithmetic Overflow error converting numeric to datatype numeric

Posted on 2007-10-10
Last Modified: 2013-12-25
While using an SQL statement to update a database, a NULL value will not update and causes an  Arithmetic Overflow error converting numeric to datatype numeric. How do I stop this from occurring? Below is an example update string used to update the DB. The P_Reading value can occasionally be a Zero or NULL. This is when the error occurs. The Sql Column is Numeric with precision of 5 and a scale of 5.

Dim UpdateAll As String = "UPDATE P_Data " & _
                                        "SET d_ID = " & d_IDVal & ", " & _
                                        "pc_No = " & pc_noVal & ", " & _
                                        "P_Chamber = '" & P_ChamberVal & "', " & _
                                        "P_Reading = '" & p_ReadingVal & "', " & _
                                        "Comment = '" & Commenttext & "', " & _
                                        "Complete = " & ComplVal & "  " & _
                                        "WHERE P_ID = " & P_val & " "
                    Dim Update As New SqlCommand(UpdateAll, SqlConnection1) ' Add a record
                    UpdateSuccess = Update.ExecuteNonQuery
                    If UpdateSuccess <= 0 Then
                        MessageBox.Show("The attempt to Update Failed!")
                    End If
                Catch SQLExp As SqlException
                    MsgBox(SQLExp.Message, MsgBoxStyle.Critical, "SQL Error")
                Catch GenExp As Exception
                    MsgBox(GenExp.Message, MsgBoxStyle.Critical, "General Error")
                End Try
Question by:WesGoad
    LVL 19

    Expert Comment

    if P_Reading is numeric you dont need the ''. Null wont be a problem then

     "P_Reading = " & p_ReadingVal & ",

    Author Comment

    I took the " out and it still gives me the error. I guess the question would be how do I assign the NULL value to the variable p_ReadingVal?

    Accepted Solution

    I solved the problem.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now