Reading a dataset into XML

[Chet Cromer]

I have a table in my SQL Server that I am loading into a datatable. I am
then saving the dataset that this table is a member of (it's the only
table in the dataset) to an XML file so that I can use it offline in
another part of my program. The code I use for this is:



ds.WriteXML("c:\clients.xml") 'DS is my dataset object



This generates an XML file like the following (the entire XML file is
included in this post at the end of the message)



<?xml version="1.0" standalone="yes"?>

<ClientData>

<Clients>

<c_recordid>1</c_recordid>

<c_name>NOL</c_name>

</Clients>

<Clients>

<c_recordid>2</c_recordid>

<c_name>TCG</c_name>

</Clients>

..more clients.

</ClientData>



In another part of my program, which is designed to use when you're
offline and can't connect to the SQL server, I want to reload this
information back into a datatable. I do so using the following code:



ds = New DataSet("ClientData")

ds.ReadXml("c:\clients.xml")

dt = ds.Tables("Clients")



Next, I try to refer to a specific row in the Clients data table in the
following manner:



Dim rows as datarow() = dt.select("c_recordid=" + clientid.tostring)
'ClientID is an integer being passed to the subroutine

Try

Dim row as datarow = rows(0)

Catch ex as Exception

Msgbox ex.tostring

End Try



Because c_recordid is the unique key for the table, I know that only one
row will be returned - hence I can logically refer to row(0). However,
when I call the subroutine setting the ClientID variable to 2,3.10, or
11, I get the following exception when referring trying to access
rows(0):



System.IndexOutOfRangeException: Index was outside the bounds of the
array.



This does not happen when I set ClientID = 1,4,5,6,7,8, or 9. I know all
of these are valid because I've looked at the XML file in it's entirety.
I've checked for strange characters that might be messing it up and
haven't found anything - all in all, I thought it would be an easy
process, but the only thing that is consistent is the ClientID's I can't
access.


Can anyone direct me to what I'm doing wrong, or what might be causing
this thing to blow up? I'd appreciate any help. One last thing. I HAVE
written a little subroutine to loop through each row in the datatable.



Dim r as datarow

For each r in dt.rows

Msgbox r(0) 'Msgbox's the clientid column (1-11)

Next r



This works fine. I see all 11 clients when I loop through them, but
cannot use the .select method to consistently get a particular one.



Thanks,



Chet





Here is the XML file in it's entirety:



<?xml version="1.0" standalone="yes"?>

<ClientData>

<Clients>

<c_recordid>1</c_recordid>

<c_name>NOL</c_name>

</Clients>

<Clients>

<c_recordid>2</c_recordid>

<c_name>TCG</c_name>

</Clients>

<Clients>

<c_recordid>3</c_recordid>

<c_name>CHN</c_name>

</Clients>

<Clients>

<c_recordid>4</c_recordid>

<c_name>CNT</c_name>

</Clients>

<Clients>

<c_recordid>5</c_recordid>

<c_name>CityNet</c_name>

</Clients>

<Clients>

<c_recordid>6</c_recordid>

<c_name>IFN</c_name>

</Clients>

<Clients>

<c_recordid>7</c_recordid>

<c_name>IU</c_name>

</Clients>

<Clients>

<c_recordid>8</c_recordid>

<c_name>Lilly</c_name>

</Clients>

<Clients>

<c_recordid>9</c_recordid>

<c_name>Plainfield</c_name>

</Clients>

<Clients>

<c_recordid>10</c_recordid>

<c_name>St. Vincent</c_name>

</Clients>

<Clients>

<c_recordid>11</c_recordid>

<c_name>IFW</c_name>

</Clients>

</ClientData>

 

[Cor Ligthert]

Chet,

Did you try it already with

ds.WriteXML("c:\clients.xml",XmlWriteMode.WriteSchema )

or diffgram when you want it with all made changes not accepted.

I hope this helps?

Cor

 

[Chet Cromer]

I found a posting that said I should try

Ds=ds.copy()

Before doing anything with my data. I tried that and didn't find any
difference. The code to read XML is now:

Ds=new dataset("ClientData") 'Also tried just "ds=new dataset"
Ds=readxml("C:\clients.xml")
Ds=ds.copy()
Dt=ds.tables("Clients")

 

[Chet Cromer]

Well that was TOO easy! THANK YOU, I should have wrote this post 5 hours
ago. I will have to go see what that did different to my XML file.

Again, THANK YOU!

Chet