ADO Connection Object Events

[Previous] [Next]

The connection object raises the events listed in the following table.

Connection Object Events
Event Name Description
BeginTransComplete Fires when the BeginTrans method completes
CommitTransComplete Fires when the CommitTrans method completes
ConnectComplete Fires when the attempt to connect to the database completes
Disconnect Fires when the Close method completes
ExecuteComplete Fires when the Execute method completes
InfoMessage Returns informational error messages
RollbackTransComplete Fires when the RollbackTrans method completes
WillConnect Fires when the Open method on the Connection object is called, prior to the attempt to connect to the database
WillExecute Fires prior to submitting a query to the Connection object with the Execute method or when the Open method is called on a Recordset associated with the Connection object

In order to create an object variable in Microsoft Visual Basic that exposes events, you'll need to use code such as the following:

 Dim WithEvents cnDatabase As ADODB.Connection 

You cannot use the New keyword in the Dim statement in Visual Basic and access the events for the object you've created.

BeginTransComplete Event

The BeginTransComplete event fires when you call the BeginTrans method on your Connection object. The event handler has four parameters:

 BeginTransComplete TransactionLevel, pError, adStatus, pConnection 

The first parameter, TransactionLevel, specifies the transaction level of the initiated transaction. This is the nesting level value that is returned by the BeginTrans method. The second parameter, pError, is a pointer to an Error object that contains error information. To determine whether an error has occurred, you can test to see whether this parameter is set to a valid Error object or you can check the value of the third parameter, adStatus. The adStatus parameter will return a value from the EventStatusEnum type. If adStatus is set to adStatusErrorsOccurred, errors have occurred and pError should be pointing to an object. The last parameter, pConnection, is a pointer to the Connection object that raised the event. Using this pointer can simplify the code in your event handlers.

If you want to prevent the event from firing again, set the adStatus parameter to adStatusUnwantedEvent.

CommitTransComplete Event

In the same way that the BeginTransComplete event fires when you call the BeginTrans method, the CommitTransComplete event fires when you call the CommitTrans method. The CommitTransComplete event handler has the same parameters, for the same information, as the BeginTransComplete event handler, with the exception of TransactionLevel:

 CommitTransComplete pError, adStatus, pConnection 

Setting the adStatus parameter to adStatusUnwantedEvent will prevent this event from firing again.

ConnectComplete Event

The ConnectComplete event fires when ADO has completed its attempt to connect to your database. This event handler has three parameters, which are the same as the last three parameters in the event handlers for BeginTransComplete and CommitTransComplete:

 ConnectComplete pError, adStatus, pConnection 

This event will fire immediately after you call the Open method of the Connection object, unless you attempt to connect to your database asynchronously. Setting the adStatus parameter to adStatusUnwantedEvent won't prevent this event from firing in the future.

Disconnect Event

When you call the Close method on the Connection object, the Disconnect event will fire. This event handler uses only two parameters:

 Disconnect adStatus, pConnection 

The first parameter, adStatus, returns a value contained in the EventStatusEnum type and indicates whether the Close method succeeded or failed. I've yet to receive any value other than adStatusOK in this event. If the server has shut down while the Connection object is open, the Close method still succeeds. If you have a transaction pending on the Connection object when you call the Close method, you'll generate an error that will prevent the Connection object from closing and the Disconnect event from firing. Setting the adStatus parameter to adStatusUnwantedEvent won't prevent this event from firing in the future.

If you disconnect from your database by letting the Connection object go out of scope or by setting the object pointer to Nothing, the Disconnect event will not fire.

ExecuteComplete Event

The ExecuteComplete event will fire when the query you executed on your connection completes. This event fires only with queries submitted by the Connection.Execute, Command.Execute, or Recordset.Open methods. If you call the OpenSchema method on the Connection object, the ExecuteComplete event will not fire.

After you submit your query, and before the query actually executes, the WillExecute event fires. The ExecuteComplete event fires after the query has executed. Unless you have executed your query asynchronously, the ExecuteComplete event will fire before your code continues.

The ExecuteComplete event takes six parameters:

 ExecuteComplete RecordsAffected, pError, adStatus, pCommand, _ pRecordset, pConnection 

The first parameter, RecordsAffected, returns the number of rows in the database affected by your query. This value is used if you submit an action query to modify rows in your database. If your query returns rows, this parameter is set to -1. The next two parameters, pError and adStatus, are used the same as in the events discussed previously.

The final three parameters are pointers to the Command, Recordset, and Connection objects being used, respectively. Even if you do not explicitly use a Command object, one is created for you. If you use the adExecuteNoRecords option on the Execute method of the Connection or Command object, the pRecordset parameter will be set to Nothing.

If you want to prevent this event from firing again, set the adStatus parameter to adStatusUnwantedEvent.

InfoMessage Event

Some database applications, such as SQL Server, raise informational messages that do not generate run-time errors but that might be helpful to trap in your application. When you connect to SQL Server through the SQL Server ODBC driver, you'll receive messages that state which database you're using (if you're not using the default Master database) and the programming language you're using. I'm not sure why, but these messages are not returned when connecting through the SQL Server OLE DB provider.

The InfoMessage event handler uses three parameters that might appear familiar to you by now if you've been reading this chapter straight through: pError, adStatus, and pConnection. You're probably better off walking through the Errors collection of the Connection object referenced by pConnection rather than relying on the Error object referenced by pError. If the database returns multiple informational messages based on the operation you perform, the InfoMessage event will fire only once. Only the first informational message will be available in the pError object, but all the messages can be accessed by means of the Errors collection on the pConnection object.

In my opinion, ADO doesn't do a great job of returning informational messages from stored procedures that return at least one recordset. Messages returned by your stored procedure after the first recordset might not be available. Apparently, this is because of limitations in the ODBC and OLE DB APIs. Hopefully this behavior will improve in a future release of ADO.

Despite what the documentation might say, setting the adStatus parameter to adStatusUnwantedEvent does not prevent the event from firing in the future.

RollbackTransComplete Event

The RollbackTransComplete event fires when you call the RollbackTrans method. The RollbackTransComplete event handler has three parameters:

 RollbackTransComplete pError, adStatus, pConnection 

See the earlier sections "BeginTransComplete Event" and "CommitTransComplete Event" for explanations of these parameters.

WillConnect Event

The WillConnect event fires once you've called the Open method on the Connection object. This event handler takes six parameters:

 WillConnect ConnectionString, UserID, Password, Options, adStatus, _  pConnection 

The first four parameters map to the four parameters on the Open method of the Connection object. If you specify the user ID and/or password information in your connection string rather than through the UserID and Password parameters on the Connection object's Open method, these parameters will be blank in the WillConnect event. You can modify the ConnectionString, UserID, Password, and Options parameters within this event to change how you're connecting to your database.

If you set the adStatus parameter to adStatusCancel, the Open method on the Connection object will return with an error stating "Operation has been cancelled by the user."

Despite what the documentation might say, setting the adStatus parameter to adStatusUnwantedEvent does not prevent the event from firing in the future.

WillExecute Event

Similar to how the WillConnect event fires as you attempt to connect to your database, the WillExecute event fires as you submit a query to the database. As with the ExecuteComplete event, calling the Execute method of the Connection and Command objects as well as the Open method of the Recordset object generates this event. The OpenSchema method on the Connection object will not cause this event to fire.

The WillExecute event uses eight parameters:

 WillExecute Source, CursorType, LockType, Options, adStatus, _ pCommand, pRecordset, pConnection 

The Source parameter refers to the query string. The CursorType and LockType parameters refer to the properties with the same name on the Recordset object.

Keep in mind that these parameters simply store the requested cursor type and lock type. What is actually returned might be different. If you set the CursorType and LockType properties on your Recordset object to invalid values (such as setting a client-side Recordset to a CursorType of adOpenDynamic and a LockType of adLockPessimistic), the invalid values will appear in the WillExecute event. Don't worry, we'll discuss these properties in more detail when we cover the Recordset object in Chapter 4 and cursors in Chapter 7.

The Options parameter holds the same value as the Options parameter used on the method call that submitted the query, such as Connection.Execute.

You can use the adStatus parameter to keep from submitting the query by setting it to adStatusCancel. If you want to prevent this event from firing again, set the adStatus parameter to adStatusUnwantedEvent.

The final three parameters on this event refer to the Command, Recordset, and Connection objects, respectively, used in the query. As described earlier in the ExecuteComplete event, if the adExecuteNoRecords value is used on the Execute method of the Connection or Command object, the pRecordset parameter in the WillExecute event will be set to Nothing.



Programming ADO
Programming MicrosoftВ® ADO.NET 2.0 Core Reference
ISBN: B002ECEFQM
EAN: N/A
Year: 2000
Pages: 131
Authors: David Sceppa

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net