We help IT Professionals succeed at work.

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

809 Views
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 & " "
                Try
                    SqlConnection1.Open()
                    Dim Update As New SqlCommand(UpdateAll, SqlConnection1) ' Add a record
                    UpdateSuccess = Update.ExecuteNonQuery
                    SqlConnection1.Close()
                    If UpdateSuccess <= 0 Then
                        MessageBox.Show("The attempt to Update Failed!")
                    End If
                Catch SQLExp As SqlException
                    MsgBox(SQLExp.Message, MsgBoxStyle.Critical, "SQL Error")
                    SqlConnection1.Close()
                Catch GenExp As Exception
                    MsgBox(GenExp.Message, MsgBoxStyle.Critical, "General Error")
                    SqlConnection1.Close()
                End Try
Comment
Watch Question

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

 "P_Reading = " & p_ReadingVal & ",

Author

Commented:
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?
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.