HOWTO: Trap for ADO Connection Errors Using WithEvents

• ActiveX Data Objects (ADO), versions 2.0, 2.1 SP2

SUMMARY

Starting with version 2.0, 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:

1. Open Visual Basic 6.0 and create a new Standard.exe project.



2. From the Project menu, choose References and add the Microsoft ActiveX Data Objects Library reference to your project.



3. Add a Command button to Form1, the project's default form.



4. 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 




5. Change the values of the sServer, sUserID, and sPassword variables in the Command1_Click event to valid values for your SQL Server environment.



6. Run the program and click Command1. If your connection string is valid, you see a message display indicating that a connection was successfully established.



7. Change the values of the sServer, sUserID, and sPassword variables in the Command1_Click event to bogus values for your SQL Server environment.



8. Run the program again and click Command1. You should see a message box display indicating that the connection attempt failed.

REFERENCES

Microsoft Developer Network Library for Visual Studio 6.0; search on: "ADO Event Model and Asynchronous Operations"; "ConnectComplete and Disconnect (ConnectionEvent) Methods (ADO)"

Additional query words:

Keywords : kbADO kbADO200 kbDatabase kbVBp600 kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2
Version : WINDOWS:2.0,2.1 SP2
Platform : WINDOWS
Issue type : kbhowto