Microsoft KB Archive/190991

= HOWTO: Trap for ADO Connection Errors Using WithEvents =

Article ID: 190991

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q190991



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 = " " sUserID = "" sPassword = " "

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.</li> Change the values of the sServer, sUserID, and sPassword variables in the Command1_Click event to bogus values for your SQL Server environment.</li> Run the program again and click Command1. You should see a message box display indicating that the connection attempt failed.</li></ol>

<div class="references_section">