Deleting Record Encounter Error When No Record Exist
Hello EveryBody,
The code below encounter error when no record is existing on the table. Do I need To make another query to determine if the record exist before issuing the SQL delete command ? Thank U.
VB Code:
Public Function DeleteCreditCard(ByVal xBranchCode As String, ByVal xTrans_No As String) As String
Dim objKeyCommand As SqlCommand
Dim objConn As SqlConnection
Dim objDA As SqlDataAdapter
Dim strSQL As String
objConn = New SqlConnection("SERVER=SirlitzServer;Database=dbSirlitz;UID=sa")
objKeyCommand = New SqlCommand
strSQL = "DELETE FROM arCreditCard WHERE BranchCode = '" & CInt(xBranchCode) & "' AND Trans_No = '" & CInt(xTrans_No) & "'"
objConn.Open()
objKeyCommand.CommandText = strSQL
objKeyCommand.CommandType = CommandType.Text
objKeyCommand.Connection = objConn
objKeyCommand.ExecuteNonQuery()
objKeyCommand = Nothing
objConn.Close()
Re: Deleting Record Encounter Error When No Record Exist
Several things I notice, but none of them would cause an error. Change your code to this:
VB Code:
Public Function DeleteCreditCard(ByVal xBranchCode As String, ByVal xTrans_No As String) As String
Dim objKeyCommand As SqlCommand
Dim objConn As SqlConnection
Dim objDA As SqlDataAdapter 'Delete this if it isn't being used
Dim strSQL As String
objConn = New SqlConnection("SERVER=SirlitzServer;Database=dbSirlitz;UID=sa")
objKeyCommand = New SqlCommand
strSQL = "DELETE FROM arCreditCard WHERE BranchCode = '" & xBranchCode & "' AND Trans_No = '" & xTrans_No & "'"
objKeyCommand.CommandText = strSQL
objKeyCommand.CommandType = CommandType.Text
objKeyCommand.Connection = objConn
Try
objConn.Open()
objKeyCommand.ExecuteNonQuery()
End Try
Finally
objConn.Close()
End Finally
You do not need to convert your string arguments to integers before concatenating them to the SQL string unless you are trying to remove extra non-digit characters from the string. A better method would be to have the DeleteCreditCard function take Integers as arguments, forcing the caller to perform a check on the input values to ensure they are numbers.
Tell us exactly what exception was thrown. We need to know the type of exception and it's message. Anytime there is an error in your code you have to tell us what it is, we cannot run a snippet of your code and reproduce the error. Also, there is no harm in executing a DELETE statement with no records in the table.
Re: Deleting Record Encounter Error When No Record Exist
Do the BranchCode and Trans_No columns contain numbers? If so then you shouldn't be putting single quotes around the values. That will check for records that have text values in those columns, of which there will be none, of course. That's just another example of why parameters should be used when inserting values into SQL statements.
As DNA says, executing a DELETE statement will not produce any errors because there are no matching rows, so your error is caused by another issue. If you had told us what the error message was we could probably work out what it is. Please don't say your code produces an error without quoting the error message. Thats' like telling a doctor your sick without describing your symptoms and expecting a diagnosis.