Link to home
Start Free TrialLog in
Avatar of msl22
msl22

asked on

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

Hi,

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 ADODB.stream
   
  Set strm = New ADODB.stream
  rs.Save strm
  rsCopy.Open strm
 
3. Updates a row
    ------------------------------------------------------------------------          
    rsCopy.MoveFirst
    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...  
...
<rs:update>
    <rs:original>
       <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=''/>
    </rs:original>
    <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=''/>
</rs:update>
<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 ?
or
- How to export the column with only null value in the XML file ?
Avatar of Dabas
Dabas
Flag of Australia image

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)?

Dabas
Avatar of Anthony Perkins
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
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?

Leon
Avatar of msl22
msl22

ASKER

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 ADODB.stream
    Set stream = New ADODB.stream
   
    'save the filtered result in a stream
    p_rsSource.Save stream, adPersistXML
   
    'open the recordset with the stream
    p_rsCopy.Open stream
   
    'free memory
    stream.Close
    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 !

Mary
No problem.  Please close this question.

Leon
ASKER CERTIFIED SOLUTION
Avatar of Lunchy
Lunchy
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial