WesGoad
asked on
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
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
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"P_Reading = " & p_ReadingVal & ",