PRB: Recordset.Save Method Does Not Persist Filtered Data When You Use adFilterPendingRecords (261126)


The information in this article applies to:

  • ActiveX Data Objects (ADO) 2.5
  • ActiveX Data Objects (ADO) 2.6
  • ActiveX Data Objects (ADO) 2.7
This article was previously published under Q261126

SYMPTOMS

If the Filter property of an ADODB.Recordset object is set to adFilterPendingRecords and the data of the recordset is persisted to XML by using the Save method, the entire contents of the recordset are saved. However, you are expecting to only see the modified records persisted.

CAUSE

When the ActiveX Data Objects (ADO) Recordset Filter property is set to a FilterGroupEnum value (adFilterPendingRecords, adFilterAffectedRecords, adFilterFetchedRecords, or adFilterConflictingRecords), or to an array of bookmarks, ADO processes the filter. However, the ADO Recordset Save method uses OLE DB. OLE DB cannot see the applied filter.

When you set the ADO Recordset Filter to a criteria string, OLE DB processes the filter so that the Save method persists the filtered records as you expect.

RESOLUTION

To work around this problem, you can transform the persisted data in a standard way, which produces the data you originally expected. To do this, use the MSXML.DOMDocument object to remove that nodes that represent records that have not been modified. The following XML was generated by using the code shown in the "Steps to Reproduce Behavior" heading of the "More Information" section. Note that the XML output includes nodes for rows that were not modified.

XML Code

<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
    xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
    xmlns:rs='urn:schemas-microsoft-com:rowset'
    xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
    <s:ElementType name='row' content='eltOnly' rs:CommandTimeout='30'
     rs:updatable='true'>
        <s:AttributeType name='au_lname' rs:number='1' rs:writeunknown='true'
             rs:basecatalog='pubs' rs:basetable='Authors' rs:basecolumn='au_lname'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='40'
             rs:maybenull='false'/>
        </s:AttributeType>
        <s:AttributeType name='au_fname' rs:number='2' rs:writeunknown='true'
             rs:basecatalog='pubs' rs:basetable='Authors' rs:basecolumn='au_fname'>
            <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='20'
             rs:maybenull='false'/>
        </s:AttributeType>
        <s:extends type='rs:rowbase'/>
    </s:ElementType>
</s:Schema>
<rs:data>
    <rs:update>
        <rs:original>
            <z:row au_lname='Bennet' au_fname='Abraham'/>
        </rs:original>
        <z:row au_lname='Changed Last Name' au_fname='Abraham'/>
    </rs:update>
    <z:row au_lname='Blotchet-Halls' au_fname='Reginald'/>
    <z:row au_lname='Carson' au_fname='Cheryl'/>
    <rs:delete>
        <z:row au_lname='DeFrance' au_fname='Michel'/>
    </rs:delete>
    <rs:insert>
        <z:row au_lname='New Last Name' au_fname='New First Name'/>
    </rs:insert>
</rs:data>
</xml>
You can use the following Microsoft Visual Basic function to modify the output XML so that the output XML represents the expected output of the Save method when the Filter property of the recordset is set to adFilterPendingRecords. Before you use this function, the project to which it is added must have a reference created to the Microsoft XML 2.0 library.

To add a reference to the Microsoft XML 2.0 library, from the Project menu, click References. In the References dialog box, select Microsoft XML 2.0.

' The following function takes a string representing a recordset
' persisted to XML format as its input parameter, and returns
' a modified string representing only the modified portion of the
' recordset. The XML string used for input can be obtained from the
' ReadText method of the ADODB.Stream object. This object can be used
' with the Save method of the ADODB.Recordset object to persist its data
' to XML.

Function FilterRecordsetXMLForChangedRecordsOnly(xml As String) As String
    Dim DOMDoc As New MSXML.DOMDocument
    Dim DataNode As MSXML.IXMLDOMNode
    Dim RemNode As MSXML.IXMLDOMNode
    Dim i As Integer, offset As Integer
    DOMDoc.async = False
    DOMDoc.loadXML xml
    Set DataNode = DOMDoc.selectSingleNode("xml/rs:data")
    offset = 0
    For i = 0 To DataNode.childNodes.length - 1
        If DataNode.childNodes(i - offset).nodeName = "z:row" Then
            Set RemNode = DataNode.removeChild(DataNode.childNodes(i - offset))
            offset = offset + 1
        End If
    Next
    FilterRecordsetXMLForChangedRecordsOnly = DOMDoc.xml
End Function

MORE INFORMATION

Steps to Reproduce Behavior

  1. Create a new Visual Basic 6.0 project.
  2. Create a reference to the Microsoft ActiveX Data Object library. To add a reference to the Microsoft ActiveX Data Object library, from the Project menu, click References. In the References dialog box, select Microsoft ActiveX Data Object.
  3. Remove Form1, and then add a new code module.
  4. Paste the following code into the new code module:

    Note You must change UID=<username> and PWD=<strong password> to the correct values before you run this code. Make sure that the UID has the appropriate permissions to perform this operation on the database.
    Option Explicit

Sub Main()
    Dim rs As ADODB.Recordset
    Dim strm As New ADODB.Stream
    Set rs = New ADODB.Recordset
    rs.Open "SELECT TOP 4 au_lname, au_fname FROM Authors", "DSN=Pubs;UID=<username>;PWD=<strong password>;", adOpenKeyset, adLockBatchOptimistic
    rs("au_lname") = "Changed Last Name"
    rs.MoveLast
    rs.Delete
    rs.AddNew
    rs("au_lname") = "New Last Name"
    rs("au_fname") = "New First Name"
    rs.Save strm, adPersistXML
    Debug.Print strm.ReadText
    Debug.Assert False
End Sub
    The output in the Immediate Window is the XML for the entire recordset, not for the filtered records only, as you expect.

REFERENCES

For more information on the ADO Filter property, please see the following Microsoft Developer Network (MSDN) page:

http://msdn.microsoft.com/library/psdk/dasdk/mdap0i7m.htm

Modification Type:MajorLast Reviewed:11/7/2003
Keywords:kbBug kbMSXMLnosweep kbpending kbprb KB261126
©2003 Microsoft Corporation. All rights reserved.