Cancel Method Example (VB)

Cancel Method Example (VB)

This example uses the Cancel method to cancel a command executing on a Connection object if the connection is busy.

Public Sub CancelX()

    Dim cnn1 As ADODB.Connection
    Dim strCnn As String
    Dim strCmdChange As String
    Dim strCmdRestore As String
    Dim booChanged As Boolean
    
    ' Open a connection.
    Set cnn1 = New ADODB.Connection
        strCnn = "Provider=sqloledb;" & _
        "Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "
    cnn1.Open strCnn
    
    ' Define command strings.
    strCmdChange = "UPDATE titles SET type = 'self_help' " & _
        "WHERE type = 'psychology'"
    strCmdRestore = "UPDATE titles SET type = 'psychology' " & _
        "WHERE type = 'self_help'"
    
    ' Begin a transaction, then execute a command asynchronously.
    cnn1.BeginTrans
    cnn1.Execute strCmdChange, , adAsyncExecute
    
    ' do something else for a little while – this could be changed
        For i = 1 To 10
            i = i + i
            Debug.Print i
        Next i

    ' If the command has NOT completed, cancel the execute
    ' and roll back the transaction. Otherwise, commit the
    ' transaction.
    If CBool(cnn1.State And adStateExecuting) Then
        cnn1.Cancel
        cnn1.RollbackTrans
        booChanged = False
        MsgBox "Update canceled."
    Else
        cnn1.CommitTrans
        booChanged = True
        MsgBox "Update complete."
    End If
    
    ' If the change was made, restore the data
    ' because this is a demonstration.
    If booChanged Then
        cnn1.Execute strCmdRestore
        MsgBox "Data restored."
    End If
        
    cnn1.Close
    
End Sub