HOWTO: Trap for ADO Connection Errors Using WithEvents (190991)

SUMMARY

Microsoft ActiveX Data Objects (ADO) allows developers to define ADO object variables using the WithEvents keyword. With this functionality developers can trap for errors that occur while trying to establish an ADO connection to a data source.

To properly trap for errors such as an incorrect user ID, incorrect password, or a connection timeout error, you must declare your ADO connection variable using the WithEvents keyword, and you must attempt to establish your ADO connection asynchronously. Under these conditions, ADO connection errors can be processed in the ConnectionComplete event of the ADO connection object.

MORE INFORMATION

The following sample demonstrates how to trap for errors that can occur while trying to establish an ADO connection to an SQL Server data source. To create this sample, use the following steps:

Open Visual Basic 6.0 and create a new Standard.exe project.
From the Project menu, choose References and add the Microsoft ActiveX Data Objects Library reference to your project.
Add a Command button to Form1, the project's default form.

Cut and paste the following code into Form1:

      Option Explicit

      Dim WithEvents Con As ADODB.Connection

      Private Sub Form_Load()

         Set Con = New ADODB.Connection

      End Sub

      Private Sub Form_QueryUnload(Cancel As Integer, _
         UnloadMode As Integer)

         If (Con Is Nothing) Then
            Exit Sub
         ElseIf Con.State = adStateOpen Then
            Con.Close
         ElseIf Con.State = adStateConnecting Then
            Con.Cancel
         ElseIf Con.State = adStateExecuting Then
            Con.Cancel
            Con.Close
         End If

         Set Con = Nothing

      End Sub

      Private Sub Command1_Click()

         Dim sServer As String
         Dim sUserID As String
         Dim sPassword As String
         Dim sConnectStr As String

         sServer = "<your server name>"
         sUserID = "<your user ID>"
         sPassword = "<your password>"

         sConnectStr = "Driver={SQL Server};Server=" & sServer & ";"
         sConnectStr = sConnectStr & "UID=" & sUserID & ";"
         sConnectStr = sConnectStr & "PWD=" & sPassword & ";DSN=''"

         Con.Open sConnectStr, , , adAsyncConnect

      End Sub

      Private Sub Con_ConnectComplete(ByVal pError As ADODB.Error, _
         adStatus As ADODB.EventStatusEnum, _
         ByVal pConnection As ADODB.Connection)

         If adStatus = adStatusErrorsOccurred Then GoTo EH

         MsgBox "Connection Established.", vbInformation, "Success!"
         Con.Close
         Exit Sub

     EH:
        MsgBox "Check your connection parameters.", vbCritical, _
             "Connection Failed!"
         set con = nothing
         Err.Clear

     End Sub

Change the values of the sServer, sUserID, and sPassword variables in the Command1_Click event to valid values for your SQL Server environment.
Run the program and click Command1. If your connection string is valid, you see a message display indicating that a connection was successfully established.
Change the values of the sServer, sUserID, and sPassword variables in the Command1_Click event to bogus values for your SQL Server environment.
Run the program again and click Command1. You should see a message box display indicating that the connection attempt failed.

HOWTO: Trap for ADO Connection Errors Using WithEvents (190991)

SUMMARY

MORE INFORMATION

REFERENCES