XML Dataset

30 Nov 2004 2:53 PM

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

30 Nov 2004 3:23 PM

Cowboy (Gregory A. Beamer) - MVP

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!
***************************

Show quoteHide quote

"SÃ©an Connolly" wrote:

> 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
>
>
>

1 Dec 2004 10:34 AM

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

1 Dec 2004 2:29 PM

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

3 Dec 2004 11:13 PM

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?

Show quoteHide quote

"Séan Connolly" <m**@sean-connolly.co.uk> wrote in message
news:41ac8950$0$565$ed2619ec@ptn-nntp-reader03.plus.net...
> 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
>
>

6 Dec 2004 10:12 AM

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!