Article ID: 261126
Article Last Modified on 11/7/2003
APPLIES TO
- Microsoft ActiveX Data Objects 2.5
- Microsoft ActiveX Data Objects 2.6
- Microsoft ActiveX Data Objects 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
- Create a new Visual Basic 6.0 project.
- 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.
- Remove Form1, and then add a new code module.
Paste the following code into the new code module:
Note You must change UID=<username> and PWD= 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 SubThe 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:
Additional query words: adFilterPendingRecords, XML
Keywords: kbbug kbprb kbpending kbmsxmlnosweep KB261126
