XML Dataset

[Séan Connolly]

I have a dataset that is based on an xml file, which contains the same
structure as a sql server table and is exported from a dataset.

When I do: sqlDataAdapter.Update(dataset, strTableName) I get the
error:

"Missing the DataColumn 'InspectionDate' in the DataTable
'tblEquipmentInspections' for the SourceColumn 'InspectionDate'

The reason the column InspectionDate is missing is beacuse its a null
value and when you export a dataset to xml in dot net it doesn't
create an entry for any null columns in the xml.

I'm guessing I need to do something with table mappings but I'm not
sure where to go.

Any ideas ?

Many thanks

Séan

 

[Guest]

Are you updating the XML file or Updating to a database (ie, XML input into
DataSet and updating to the database)? If updating a database, you can
control the update process by creating a stored procedure for the UPDATE (or
INSERT) command. If this is not possible, you will have to loop through the
records and manually insert rather than use Update().

---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[DrLargePants]

Are you updating the XML file or Updating to a database (ie, XML input into

DataSet and updating to the database)? If updating a database, you can
control the update process by creating a stored procedure for the UPDATE (or
INSERT) command. If this is not possible, you will have to loop through the
records and manually insert rather than use Update().

Sorry updating the database. As a bit of background the xml file is
exported from a sql server database to a workstation and then copied
to a pda and imported to a sqlserver ce database. The record that is
being imported contains a date field that is null until the pda app
has finished with it. Of course when you do dataset.writexml it
ignores any null fields when it writes the xml, so if the field is
allways null it never shows up in the xml !

I suppose I could loop though it, but I'm trying to make the classes
as abstract as possible

Thanks !

Séan

 

[Séan Connolly]

I suppose I could loop though it, but I'm trying to make the classes

as abstract as possible

This works to a point:

strSQL = "SELECT * from tablename"
Dim sqlDataAdapter As New SqlServerCe.SqlCeDataAdapter(strSQL, Cnn)
Dim sqlCommandBuilder As New SqlServerCe.SqlCeCommandBuilder(sqlDataAdapter)
Dim dsDest As New DataSet
sqlDataAdapter.Fill(dsDest)


For Each MySrcRow In dsSource.Tables(0).Rows
MyDestRow = dsDest.Tables(0).NewRow
For Each MySrcCol In dsSource.Tables(0).Columns
MyDestRow(MySrcCol.ColumnName) = MySrcRow.Item(MySrcCol.ColumnName)
Next
dsDest.Tables(0).Rows.Add(MyDestRow)
Next

This sticks all the data from the xml file into the dataset fine, but it
doesn't actually gert added to the database. What am I missing ?!

TIA

Séan

 

[Alex Homer]

If you export the XML schema from the original DataSet, you can reload it to
recreate the original table structure in the DataSet before importing the
XML. Does that help?

 

[Séan Connolly]

If you export the XML schema from the original DataSet, you can reload it to
recreate the original table structure in the DataSet before importing the
XML. Does that help?

Rather a lot actually, thanks!