Xml file to SQLCe

[Graeme Richardson]

Hi, I have the coded the following in an attempt to get xml data into an
SQLCe database. The code executes without error, but the table,
tlkpBaitSpecies, remains empty.

Try
Dim da As New SqlCeDataAdapter
Dim cmdInsert As SqlCeCommand

' msqlConnection (As SqlCeConnection) is open.
da.SelectCommand = New SqlCeCommand("SELECT uidBaitSpecies, CommonName
FROM tlkpBaitSpecies", msqlConnection)

cmdInsert = New SqlCeCommand("INSERT INTO tlkpBaitSpecies
(uidBaitSpecies, CommonName) " & _
"VALUES (@uidBaitSpecies, @CommonName) ",
msqlConnection)
cmdInsert.Parameters.Add("@uidBaitSpecies", SqlDbType.NVarChar, 2,
"uidBaitSpecies")
cmdInsert.Parameters.Add("@CommonName", SqlDbType.NVarChar, 50,
"CommonName")
da.InsertCommand = cmdInsert

Dim ds As New DataSet
da.Fill(ds) ' Probably nonsense
ds.ReadXml(vstrXMLFile) ' xml and xsd file exist
ds.AcceptChanges()
da.Update(ds)

Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try

I'm sure the problem is in configuring the DataSet, but can't find an
example on how to do this.
Please suggest a way forward.

I tried appending row by row from xml file but found that data anomalies
(e.g. quotes in string, Null values, etc) will always be a problem.

Thanks, Graeme

 

[Ilya Tumanov [MS]]

You call AcceptChanges() before doing Update(). That means there are no
changes in the DataSet after that.
Since there are no changes, there's nothing to update/insert. That means
nothing's inserted/updated.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------

 

[Graeme Richardson]

Hi Ilya, your suggestion didn't fix the problem - I still think that I have
the DataSet and AataAdapter calls in the correct order from the original
code. Please prove me wrong; I'm trying to learn about these objects and how
they interact.

I rewrote the code as:

Dim da As New SqlCeDataAdapter
Dim cmdInsert As SqlCeCommand

cmdInsert = New SqlCeCommand("INSERT INTO tlkpBaitSpecies (uidBaitSpecies,
CommonName) " & _
"VALUES (?, ?) ", msqlConnection) ' change in
SQL, no tokens
cmdInsert.Parameters.Add("@uidBaitSpecies", SqlDbType.NVarChar, 2,
"uidBaitSpecies")
cmdInsert.Parameters.Add("@CommonName", SqlDbType.NVarChar, 50,
"CommonName")
da.InsertCommand = cmdInsert
Dim ds As New DataSet
ds.ReadXml(vstrXMLFile)
Dim dt As DataTable
dt = ds.Tables("tlkpBaitSpecies")
da.Update(dt) ' now adding changes from the datatable, not the data set.

and this works. It came as an epiphany after seeing your suggstion, so you
have the credit.

Cheers, Graeme.

 

[Ilya Tumanov [MS]]

Hmm... My suggestion was to _remove_ this call to AcceptChanges() after
loading data from XML.
From looking at your new code, that seems to be exactly what you did to
make it work.
If you interpreted my suggestion in any other way, I'm sorry I was not
clear enough.

Anyway, I'm glad it worked out just fine.

Oh, be careful with the schema. If XML will get out of sync with SQL CE
database, it might suddenly stop working.
Make sure you have XSD in your XML (or load it separately), do not use
inference.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------