Deleting Record Encounter Error When No Record Exist

[aming]

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.

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()

[DNA7433]

Several things I notice, but none of them would cause an error. Change your code to this:

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.

[jmcilhinney]

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.