HOWTO: Trap for ADO Connection Errors Using WithEvents

MORE INFORMATION

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.