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).Valu e = "" & rsCopy.Fields.item(i).Valu e
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 ?
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).Valu
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 ?
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
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
Leon
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_CopyFilteredReco rds(p_rsSo urce 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
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_CopyFilteredReco
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
Leon
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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