Load XML data file into DataSet to post back to Database Table

[StellaIndigo]

Hi Everyone

I'm having problems reading a datafile full of XML data into a dataset then posting it back to a database table (access).

Here's my code,

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim cnTrackConnection As OleDb.OleDbConnection
        Dim daTrackDataAdaptor As OleDb.OleDbDataAdapter
        Dim dtTrackDataTable As New DataTable
        Dim dsTrackDataSet As DataSet
        Dim strConnectionString As String
        Dim xmlStream As New System.IO.StreamReader("c:\temp\MyXMLFile.xml")
        Dim reader As New System.Xml.XmlTextReader(xmlStream)
        Dim command As OleDb.OleDbCommand

        strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp\MyDataBase.mdb"

        cnTrackConnection = New OleDb.OleDbConnection(strConnectionString)
        dsTrackDataSet = New DataSet("tblTracking")
        cnTrackConnection.Open()
        daTrackDataAdaptor = New OleDb.OleDbDataAdapter("select * from tblTracking", strConnectionString)
        daTrackDataAdaptor.Fill(dsTrackDataSet)
        dsTrackDataSet.ReadXml(reader, XmlReadMode.IgnoreSchema)

        MsgBox("Tables in set " & dsTrackDataSet.Tables.Count)
        For Each dtTrackDataTable In dsTrackDataSet.Tables
            MsgBox("Rows in table 0 " & dtTrackDataTable.Rows.Count())
        Next

        dsTrackDataSet.AcceptChanges()
        command = New OleDb.OleDbCommand("insert into tblTracking (customer_number) values (?)", cnTrackConnection)
        command.Parameters.Add("customer_number", OleDb.OleDbType.Char, 6, "customer_number")
        daTrackDataAdaptor.InsertCommand = command

        command = New OleDb.OleDbCommand("UPDATE tblTracking SET customer_number = @CustomerID ", cnTrackConnection)
        command.Parameters.Add("@CustomerID", OleDb.OleDbType.Char, 5, "customer_number")
        daTrackDataAdaptor.UpdateCommand = command

        daTrackDataAdaptor.Update(dsTrackDataSet)

        cnTrackConnection.Close()
        daTrackDataAdaptor.Dispose()
        dtTrackDataTable.Dispose()

        cnTrackConnection = Nothing
        daTrackDataAdaptor = Nothing
        dtTrackDataTable = Nothing
    End Sub

None of the code generates any error during execution. The XML data does not seem to load into the dataset. Only the table I use for the inital select exists and the data is not loadd into that either.

the xml file looks like this

<dataroot>
<tblTracking>
<customer_number>770010</customer_number>
<order_number>Y8762E</order_number>
<tray_number></tray_number>
<reference></reference>
<ordered_date>2006-08-24T11:00:00Z</ordered_date>
<expected_despatch_date>2006-08-24</expected_despatch_date>
<revised_despatch_date></revised_despatch_date>
<actual_despatch_date></actual_despatch_date>
<location_code> </location_code>
<location_description>Production Processing</location_description>
<order_type>F</order_type>
</tblTracking>
<tblTracking>
<customer_number>028617</customer_number>
<order_number>L7240I</order_number>
<tray_number>0004</tray_number>
<reference>MISS H MCALPINE 007767</reference>
<ordered_date>2006-11-02T14:01:00Z</ordered_date>
<expected_despatch_date>2006-11-07</expected_despatch_date>
<revised_despatch_date></revised_despatch_date>
<actual_despatch_date>2006-11-06T12:30:00Z</actual_despatch_date>
<location_code>Z</location_code>
<location_description>Despatched</location_description>
<order_type>G</order_type>
</tblTracking>
<tblTracking>
<customer_number>101101</customer_number>
<order_number>M5233Q</order_number>
<tray_number>0004</tray_number>
<reference>FORD S A163395</reference>
<ordered_date>2006-11-07T11:44:00Z</ordered_date>
<expected_despatch_date>2006-11-08</expected_despatch_date>
<revised_despatch_date>2006-11-08</revised_despatch_date>
<actual_despatch_date>2006-11-08T10:58:00Z</actual_despatch_date>
<location_code>Z</location_code>
<location_description>Despatched</location_description>
<order_type>G</order_type>
</tblTracking>
</dataroot>

This loads into my table called tblTracking in the Access database if I use Application.ImportXML... within Access.

Main question, why doesn't the XML file load into the dataset?

Any ideas or help welcome.



Regards
Stella

There are 10 types of people in the world. Those that understand binary and those that don't.

 

[jebenson]

One problem is this line:

dsTrackDataSet.AcceptChanges()

You call AcceptChanges before atrtampting to save the data to the database. AcceptChanges does exactly what it says - it accepts any changes made to the dataset and marks everything as unchanged. What this means is when you do the Update to save the data, the dataset indicates that there are no changes (modified records, new records, deleted records), and so no update is performed.

To fix this just remove that line.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[StellaIndigo]

Hi

Thanks for the quick reply.

I removed the dsTrackDataSet.AcceptChanges() line and it's made no difference. The code just before that line

        MsgBox("Tables in set " & dsTrackDataSet.Tables.Count)
        For Each dtTrackDataTable In dsTrackDataSet.Tables
            MsgBox("Rows in table 0 " & dtTrackDataTable.Rows.Count())
        Next

first tells me how many tables are in my set and then the number of rows in each table. This always reports 1 table and the table has 0 records (I assume this is from the select * from tblTracking when I open the adaptor. (The tblTracking data has 0 records).

The XML file has more than 3,500 entries in it so I'd expect to see 1 or more tables in the set and at least 3,500 plus records in the set somewhere.

No errors or exceptions are thrown during code execution.

Any more suggestions?

Regards
Stella

There are 10 types of people in the world. Those that understand binary and those that don't.

 

[jebenson]

After some testing, I have found that if you change this line:

dsTrackDataSet.ReadXml(reader, XmlReadMode.IgnoreSchema)

to this:

dsTrackDataSet.ReadXml(reader)

it works.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[StellaIndigo]

Hi
I removed the IgnoreSchema parameter from the ReadXML command and it made no difference for me (

The code to view the dataset still shows 1 table and 0 rows.

Can you paste your code that works?

Many thanks for your help with this issue.



Regards
Stella

There are 10 types of people in the world. Those that understand binary and those that don't.

 

[jebenson]

Here's the code I use:

Dim dtTrackDataTable As New DataTable
Dim dsTrackDataSet As DataSet
Dim xmlStream As New System.IO.StreamReader("c:\temp\MyXMLFile.xml")
Dim reader As New System.Xml.XmlTextReader(xmlStream)

dsTrackDataSet = New DataSet("tblTracking")
dsTrackDataSet.ReadXml(reader)

MsgBox("Tables in set = " & dsTrackDataSet.Tables.Count)

For Each dtTrackDataTable In dsTrackDataSet.Tables
MsgBox("Rows in table " & dtTrackDataTable.TableName & " = " & dtTrackDataTable.Rows.Count())
Next

This shows 1 table and 3 rows (from the XML you posted).


The following also work for me:

dsTrackDataSet.ReadXml(reader, XmlReadMode.Auto)

dsTrackDataSet.ReadXml(reader, XmlReadMode.InferSchema)

dsTrackDataSet.ReadXml("c:\temp\MyXMLFile.xml")

dsTrackDataSet.ReadXml("c:\temp\MyXMLFile.xml", XmlReadMode.Auto)

dsTrackDataSet.ReadXml("c:\temp\MyXMLFile.xml", XmlReadMode.InferSchema)

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[StellaIndigo]

Hi

Thanks for the help. I got it working eventually. I found that the statement

        daTrackDataAdaptor = New OleDb.OleDbDataAdapter("select * from tblTracking", strConnectionString)
        daTrackDataAdaptor.Fill(dsTrackDataSet)

stopped the xml data load. if i comment out the daTrackDataAdaptor.Fill(dsTrackDataSet) statement it works ok.

If anyone has a quicker way to load an xml (or .csv/.txt) file with data into an access data table please let me know.

thanks


Regards
Stella

There are 10 types of people in the world. Those that understand binary and those that don't.