Cancel Method Example (VB)

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

'BeginCancelVB
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=MyServer;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
    Dim i As Integer
      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
'EndCancelVB

See Also

Cancel Method | Connection Object