• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 783
  • Last Modified:

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

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
0
WesGoad
Asked:
WesGoad
  • 2
1 Solution
 
LimbeckCommented:
if P_Reading is numeric you dont need the ''. Null wont be a problem then

 "P_Reading = " & p_ReadingVal & ",
0
 
WesGoadAuthor 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?
0
 
WesGoadAuthor Commented:
I solved the problem.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now