ADODB.Recordset : Copy filtered row + updates row + Save as XML => The xml file contains the update statement...


I'm working with VB6 (sp3).
And I found a weird problem when trying to save a recordset as XML file.

1. Create a recordset, load data in it and filter on a subset of rows.
    dim rs as ADODB.Recordset
    dim rsCopy as ADODB.Recordset

2. Copy the filtered rows (not clone)
  dim strm as new
  Set strm = New
  rs.Save strm
  rsCopy.Open strm
3. Updates a row
    For i = 0 To rsCopy.Fields.Count - 1
        'replace null value by an empty string
        rsCopy.Fields.item(i).Value = "" & rsCopy.Fields.item(i).Value
    Next i

4. Save as XML file
    rsCopy.Save "C:\Test.xml", adPersistXML

5. Open the XML file
    you see the update....  and this format is not supported by crystal report...  
       <z:row OID='17900' c1='' c2='Mar 29 2004  3:17PM' c3='' c4='' c5='' c6='' c7='' c8='' c9='' c10='' c11=''
                         c12='' c13='' c14='29-17-04' c15='' c16='' Usuario='' c18=''/>
    <z:row OID='17900' c1='' c2='Mar 29 2004  3:17PM' c3='' c4='' c5='' c6='' c7='' c8='' c9='' c10='' c11='' c12=''
                   c13='' c14='29-17-04' c15='' c16='' Usuario='' c18=''/>
<z:row OID='17910' c2='Mar 29 2004  3:17PM' c14='29-17-04' Usuario='' c18=''/>

6. Why I replace null value by an empty string ?
 This is because I use the XML file as source for my Crystal reports. And the columns containing null values are not
 exported in the XML file...

7. Any solutions ???
- How to avoid to keep trace of the update ?
- How to export the column with only null value in the XML file ?
Who is Participating?
LunchyConnect With a Mentor Commented:
Closed, 250 points refunded.
Friendly Neighbourhood Community Support Moderator
Hi msl22,
Just a shot in the dark, but who knows?
Have you tried setting "Convert Null Field Value to Default" in CR (File -> Report Options)?

Anthony PerkinsCommented:
Please maintain these old open questions.
1 08/13/2003 500 Crystal report doesn't show the report  Open Crystal Reports
2 02/19/2004 125 LINUX/APACHE/JSP : Calling a batch file ...  Open JSP
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

What is your backend database? Can you use the CASE WHEN THEN syntax to return a recordset which already does not have any NULL values?

msl22Author Commented:
Hi Dabas and leonstryker,

I finally found that the problem occured only when the recordset have been build from a stream...
I used to use a stream to copy only the filtered records of a recordset.

Public Sub Recordset_CopyFilteredRecords(p_rsSource As ADODB.Recordset, ByRef p_rsCopy As ADODB.Recordset)
    Dim stream As
    Set stream = New
    'save the filtered result in a stream
    p_rsSource.Save stream, adPersistXML
    'open the recordset with the stream
    p_rsCopy.Open stream
    'free memory
    Set stream = Nothing
End Sub

And working with the p_rsCopy, causes to keep all modification made when saving it as XML...

But I found that, when saving my data as adPersistXML it is only saving the filtered data... so I don't need anymore to use this procedure.

Thanks for your help !

No problem.  Please close this question.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.