ADO Recordset Object Events | Programming MicrosoftВ® ADO.NET 2.0 Core Reference

[Previous] [Next]

ADO introduced events in version 2.0. They can greatly help you organize your code and respond to actions on your Recordset objects. Documenting all the different ways the events could fire, however, would be an overwhelming and nearly impossible task. Instead, we'll cover the basics of each event, as described in the following table. If you're looking for a list of events and parameter values for a specific scenario—"What if a user modifies a field in the grid and then clicks on another record?"—your best bet is to put code into each of the event handlers, create the desired scenario, verify the events that fire, and check the value of the parameters in those events.

Recordset Object Events
Event Name	Description
EndOfRecordset	Fires when you navigate beyond the last record of data in your Recordset
FetchComplete	Fires when the ADO Cursor Engine retrieves the last of the data in your Recordset that uses asynchronous fetching
FetchProgress	Fires after the ADO Cursor Engine retrieves a batch of records for your Recordset that uses asynchronous fetching
FieldChangeComplete	Fires after you've modified the value for a field
MoveComplete	Fires after the current position of the Recordset changes
RecordChangeComplete	Fires after you modify a record
RecordsetChangeComplete	Fires after the Recordset object has changed
WillChangeField	Fires before the contents of a field change
WillChangeRecord	Fires before the contents of a record change
WillChangeRecordset	Fires before the Recordset object changes
WillMove	Fires before the current position in the Recordset changes

EndOfRecordset Event

This event fires when you move beyond the last record in your Recordset. There's a property you can set on most Visual Basic navigation controls that enables the control to automatically add a record to the end of your Recordset when you reach EOF. With this event, you can provide similar functionality in your application. The EndOfRecordset event supports three parameters:

fMoreData This parameter accepts Boolean operators. Set it to True if you appended a new record to the end of your Recordset in the event handler.

adStatus This parameter returns a value from EventStatusEnum, as shown in the table below.

pRecordset This parameter is a pointer to the Recordset object that fired the event.

You can also use the EndOfRecordset event simply to determine when you've reached the end of your Recordset.

EventStatusEnum Values
Constant	Value	Description
adStatusOK	1	The operation that caused the event was successful.
adStatusErrorsOccurred	2	The operation that caused the event failed because of an error or errors.
adStatusCantDeny	3	The event cannot request cancellation of the pending operation.
adStatusCancel	4	Requests cancellation of the operation that caused the event to occur.
adStatusUnwantedEvent	5	Prevents subsequent notifications of the event.

If you do not want this event to continue to fire, you can set the adStatus parameter to adStatusUnwantedEvent.

FetchComplete Event

This event fires only if you're using asynchronous fetching with a client-side Recordset. After the final FetchProgress event fires, FetchComplete will fire. The FetchComplete event has three parameters:

pError If adStatus is set to adStatusErrorsOccurred, this parameter is a pointer to an Error object.

adStatus This parameter takes its value from EventStatusEnum, as shown in the table above.

pRecordset This is a pointer to the Recordset object that fired the event.

Setting adStatus to adStatusUnwantedEvent will not prevent the event from firing in the future. We'll talk more about ADO's asynchronous fetching feature and events at the end of this chapter.

FetchProgress Event

Like the FetchComplete event, the FetchProgress event fires when you use asynchronous fetching with the ADO Cursor Engine. It has four parameters:

Progress This parameter accepts a long value.

MaxProgress This parameter accepts a long value.

adStatus This parameter takes its value from EventStatusEnum, as shown in the table above.

pRecordset This parameter is a pointer to the Recordset object that fired the event.

The Progress and MaxProgress parameters are designed to help you determine what percentage of the fetching has completed thus far. To date, I've retrieved only a value of 1 from the MaxProgress parameter and 0 for Progress until the final time the event fires.

Two dynamic properties on client-side Recordsets can help determine how often this event fires: Initial Fetch Size and Background Fetch Size. They default to 50 and 15, respectively. These properties are available as soon as you set the Recordset's CursorLocation property to adUseClient. Once ADO has retrieved the number of records specified in the Initial Fetch Size property, the ExecuteComplete event on the Connection object will fire. From that point on, after ADO retrieves the number of records specified in the Background Fetch Size property, it will fire the FetchProgress event. The FetchProgress event will also be fired if ADO reaches the end of the Recordset without retrieving the number of records specified by Background Fetch Size. After FetchProgress fires for the final time, FetchComplete will fire.

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

WillChangeField and FieldChangeComplete Events

Whenever you change the Value property of Field objects in your Recordset, the WillChangeField and FieldChangeComplete events will fire. These events use the following syntax:

 WillChangeField cFields, Fields, adStatus, pRecordset FieldChangeComplete cFields, Fields, pError, adStatus, pRecordset

The following is a list of the combined parameters for both WillChangeField and FieldChangeComplete:

cFields This is a long data type parameter that specifies the number of fields modified.

Fields This parameter accepts a Variant array of Field objects.

pError This parameter is a pointer to the Error object if an error occurred.

adStatus This parameter takes its value from EventStatusEnum, as shown in the table in the section, "EndOfRecordset Event."

pRecordset This parameter is a pointer to the Recordset object that fired the event.

If you want to determine which fields have been or will be modified, you can use code as follows in the event handler for either event:

 Dim lngCounter As Long For lngCounter = 0 To cFields - 1 Debug.Print Fields(lngCounter).Name Next lngCounter

If an error occurs, you can use the pError parameter in the FieldChangeComplete event handler to determine the cause.

You can set the adStatus parameter to adStatusUnwantedEvent to prevent either event from occurring in the future. You can set this parameter to adStatusCancel if you want to prevent the Field from being updated. In the FieldChangeComplete event, this parameter will be set to adStatusErrorsOccurred if an error occurs.

As far as I can tell, there is no way to use the WillChangeField event to examine the change that will be made to the Field.

WillChangeRecord and RecordChangeComplete Events

You can use the WillChangeRecord and RecordChangeComplete events to keep track of changes to the records in your Recordset. These events will fire as a result of calls to the Update, Delete, CancelUpdate, AddNew, UpdateBatch, CancelBatch, or Resync methods on the Recordset. The events will also fire the first time you modify the value of a field. When the event fires, the only records visible through the filter are those affected by the action that caused these events to fire. You cannot change the Filter property in either of these events. The WillChangeRecord and RecordChangeComplete events use the following syntax:

 WillChangeRecord adReason, cRecords, adStatus, pRecordset RecordChangeComplete adReason, cRecords, pError, adStatus, pRecordset

The following is a list of the combined parameters for both these events:

adReason This parameter accepts certain values from EventReasonEnum.

cRecords A long data type parameter, it specifies the number of records modified.

pError This parameter is a pointer to the Error object if an error occurred.

adStatusThis parameter accepts a value from EventStatusEnum, as shown in the table in the section "EndOfRecordset Event."

pRecordsetThis parameter is a pointer to the Recordset object that fired the event.

EventReasonEnum Values Available to WillChangeRecord and RecordChangeComplete
Constant	Value	Description
adRsnFirstChange	11	The record was modified for the first time. Subsequent modifications to the record prior to submitting those changes to the database will not cause the events to fire.
adRsnAddNew	1	The record was added by using AddNew. Subsequent modifications to the record prior to submitting those changes to the database will not cause the events to fire.
adRsnDelete	2	The record was deleted.
adRsnUpdate	3	The pending changes in the record were submitted to the database by Update (for LockTypes of adLockOptimistic or adLockPessimistic), or by UpdateBatch (for a LockType of adLockBatchOptimistic).
adRsnUndoUpdate	4	A pending modification was canceled by CancelUpdate or CancelBatch.
adRsnUndoAddNew	5	A pending insertion was canceled by CancelUpdate or CancelBatch.
adRsnUndoDelete	6	A pending deletion was canceled by CancelBatch.
adRsnResynch	8	The record was changed by calling the Resync method.

Setting the adStatus parameter to adStatusUnwantedEvent will prevent the event from firing in the future. If you want to cancel the operation that caused the event, you can set the adStatus parameter to adStatusCancel in the WillChangeRecord event handler so long as the parameter was not equal to adStatusCantDeny when the event fired. Setting the adStatus parameter to adStatusCancel will cause the function that fired the event to return an error.

WillChangeRecordset and RecordsetChangeComplete Events

These events can fire as a result of changing the Filter property or by calling Requery, Close, or the different navigation methods on the Recordset. They use the following syntax:

 WillChangeRecordset adReason, adStatus, pRecordset RecordsetChangeComplete adReason, pError, adStatus, pRecordset

The following is a list of the combined parameters for these events:

adReason This parameter takes certain values from EventReasonEnum.

pError This parameter is a pointer to the Error object if an error occurred.

adStatus This parameter accepts a value from EventStatusEnum, as shown in the table in "EndOfRecordset Event."

pRecordset This parameter is a pointer to the Recordset object that fired the event.

The adReason parameter can help you understand why the event fired. Changing the current record in different ways will cause the adReason parameter to contain different values. (See the following table.) For example, if your Recordset does not support bookmarks (server-side forward-only and dynamic cursors), these events will fire when ADO refreshes the cache. This can happen the first time you examine the data in the Recordset object; by navigating outside the currently cached records by calling the Move, MoveNext, or similar methods; or by setting the Filter property. If you call the Requery method on a Recordset that doesn't support bookmarks, these events will fire with an adReason of adRsnMove.

EventReasonEnum Values Available to WillChangeRecordset and RecordsetChangeComplete
Constant	Value	Description
adRsnRequery	7	An operation requeried the Recordset.
adRsnSynch	8	The query has been resynchronized with the database.
adRsnClose	9	Calling the Open or Requery method (whether your Recordset supports bookmarks or not) will cause the RecordsetChangeComplete event (but not the WillChangeRecordset event) to fire with this value for the adReason parameter.
adRsnMove	10	Requery has been called on a Recordset that doesn't support bookmarks.

Setting the adStatus parameter to adStatusUnwantedEvent will prevent the event from firing in the future. If you want to cancel the operation that caused the event, you can set the adStatus parameter to adStatusCancel in the WillChangeRecordset event so long as the parameter was not equal to adStatusCantDeny when the event fired. Setting adStatus to adStatusCancel will cause the function that fired the event to return an error.

WillMove and MoveComplete Events

The WillMove and MoveComplete events fire when you navigate through your Recordset. These events use the following syntax:

 WillMove adReason, adStatus, pRecordset MoveComplete adReason, pError, adStatus, pRecordset

They support the following parameters:

adReason This parameter takes certain values from EventReasonEnum.

pError This is a pointer to the Error object if an error occurred.

adStatusThis parameter accepts a value from EventStatusEnum, as shown in the table in "EndOfRecordset Event"

pRecordset This parameter is a pointer to the Recordset object that fired the event.

EventReasonEnum Values Available to WillMove and MoveComplete
Constant	Value	Description
adRsnRequery	7	The Requery method was called. Currently, calling Requery will cause the WillMove and MoveComplete events to fire twice, first with an adReason of adRsnRequery and then with an adReason of adRsnMove.
adRsnMove	10	Calling the Move or Requery method, or moving to another record by setting the AbsolutePage, AbsolutePosition, Bookmark, Filter, or Index property, will cause these events to fire with this value in the adReason parameter.
adRsnMoveFirst	12	The MoveFirst method was called.
adRsnMoveNext	13	The MoveNext method was called.
adRsnMovePrevious	14	The MovePrevious method was called.
adRsnMoveLast	15	The MoveLast method was called.

Setting the adStatus parameter to adStatusUnwantedEvent will prevent the event from firing in the future. If you want to cancel the operation that caused the event, you can set the adStatus parameter to adStatusCancel in the WillMove event as long as the parameter is not equal to adStatusCantDeny when the event is fired. Setting adStatus to adStatusCancel will cause the function that fired the event to return an error.