Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-03-30
7
Medium Priority
?
1,563 Views
Last Modified: 2013-12-25
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 ?
0
Comment
Question by:msl22
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 27

Expert Comment

by:Dabas
ID: 10719545
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10726231
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
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 10765570
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:msl22
ID: 10767119
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
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 10767473
No problem.  Please close this question.

Leon
0
 
LVL 2

Accepted Solution

by:
Lunchy earned 0 total points
ID: 10798040
Closed, 250 points refunded.
Lunchy
Friendly Neighbourhood Community Support Moderator
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question