Article ID: 252350
Article Last Modified on 5/8/2003
APPLIES TO
- Microsoft Visual Basic 6.0 Learning Edition
- Microsoft Visual Basic 6.0 Professional Edition
- Microsoft Visual Basic 6.0 Enterprise Edition
- Microsoft ActiveX Data Objects 2.0
- Microsoft ActiveX Data Objects 2.1
- Microsoft ActiveX Data Objects 2.1 Service Pack 1
- 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 Q252350
SYMPTOMS
In an optimistic batch update operation, if you try to access the Value property for a field that is in a record marked for deletion, when you set the disconnected recordset's Filter property to adFilterPendingRecords the following run-time error message occurs:
CAUSE
When a record is deleted in a disconnected ADO Recordset by using the rs.Delete method, you cannot access the Value property of its constituent fields in code.
RESOLUTION
To access the values that were stored in the fields of the deleted record, use the ADO Field object OriginalValue or UnderlyingValue property.
STATUS
This behavior is by design.
MORE INFORMATION
In an optimistic batch update operation, disconnected recordsets are updated locally on the client computer. Then the disconnected recordsets are associated with an active connection to post the changes made to the database. The changes may include the following:
- The addition of new records.
- Modification's to the values stored in the fields of existing records.
- Deletion of records in the disconnected recordset.
It is a good practice to examine the changes made to a disconnected recordset locally before you connect and apply the changes to the database. The Filter property of the ADO Recordset can be set to the adFilterPendingRecords ADO constant value to identify the records in the disconnected recordset that have changed, which will be applied to the database on the next call to the UpdateBatch method.
Setting the recordset's Filter property to adFilterPendingRecords enforces a filter on the recordset object so that the view of its constituent records is restricted to the records changed in the disconnected recordset that have not been posted to the database. When you set the filter you can loop through the records and examine the Status property of each record to determine whether a record has been added, changed, or deleted. Deleted records have their status property set to adStatusDeleted.
Although it is possible to access the values in the fields of the newly added and modified records by using the ADO Field object's Value property, doing the same for the deleted records results in the run-time error message shown in the "Symptoms" section of this article. The values that were stored in the fields of the records marked for deletion can only be accessed through the OriginalValue or UnderlyingValue properties of the ADO Field object.
Steps to Reproduce Behavior
Use the following code example to reproduce this problem. This example uses the Microsoft Access Biblio.mdb database that is installed with Visual Basic 6.0.
- Start a new Standard EXE project in Visual Basic 6.0.
- Set a project reference to the Microsoft ActiveX Data Objects 2.1 (or later) Library.
- Place a Command button on Form1.
In the General Declarations section of Form1, insert the following code:
Dim cn as ADODB.Connection Dim rs as ADODB.Recordset
Place the following code in the Command button's Click event procedure:
Private Sub Command1_Click() Set cn = New ADODB.Connection cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Biblio.mdb" cn.Open Set rs = New ADODB.Recordset rs.CursorLocation = adUseClient rs.Open "Select * from [Title Author]", cn, adOpenStatic, adLockBatchOptimistic 'THE RECORDSET IS DISCONNECTED Set rs.ActiveConnection = Nothing rs.MoveFirst rs.Delete rs.Filter = adFilterPendingRecords Do While Not rs.EOF If rs.Status = adRecDeleted Then MsgBox "ISBN : " & rs.Fields("ISBN") & " has been deleted !" 'Error occurs on this line End If rs.MoveNext Loop rs.Filter = adFilterNone Set rs.ActiveConnection = cn rs.CancelBatch rs.Close End SubPress F5 to run the code. Note that the error message occurs.
In the preceding code segment replace the following line:
MsgBox "ISBN : " & rs.Fields("ISBN") & " has been deleted !"with this line:
MsgBox "ISBN : " & rs.Fields("ISBN").OriginalValue & " has been deleted !"Note that you can see the value of the "ISBN" field in the record marked for deletion.
- In the preceding example, the deletion is not posted to the database. The call to rs.CancelBatch ensures that the deletion is not reflected in the database.
Additional query words: error -2147217885 (80040e23)
Keywords: kbprb KB252350
