DataAdapter Problem

[Phill]

I have an PPC app that calls a web service which returns
a dataset. I am trying to load this dataset into a SQL
CE table called "Route_Sheets". It goes through the
motions, but no data is inserted. I have added code to
create an XML file to verify that data is returned from
the web service is good, it is. Here is my code. Please
help.

DataSet dsRemote = new DataSet();
dsRemote = ws.GetRouteSheet(2);

SqlCeConnection conn = new SqlCeConnection("Data
Source=" + LocalStorage.FileName);
SqlCeDataAdapter da = new SqlCeDataAdapter();
da.SelectCommand = new SqlCeCommand("SELECT * FROM
ROUTE_SHEETS", conn);
SqlCeCommandBuilder cb = new SqlCeCommandBuilder(da);

conn.Open();

da.Fill(dsRemote,"Route_Sheets");

int rowsaffected = da.Update(dsRemote,"Route_Sheets");

conn.Close();

 

[B Mermuys]

Hi,
[inline]

I have an PPC app that calls a web service which returns
a dataset. I am trying to load this dataset into a SQL
CE table called "Route_Sheets". It goes through the
motions, but no data is inserted. I have added code to
create an XML file to verify that data is returned from
the web service is good, it is. Here is my code. Please
help.

DataSet dsRemote = new DataSet();
dsRemote = ws.GetRouteSheet(2);

SqlCeConnection conn = new SqlCeConnection("Data
Source=" + LocalStorage.FileName);
SqlCeDataAdapter da = new SqlCeDataAdapter();
da.SelectCommand = new SqlCeCommand("SELECT * FROM
ROUTE_SHEETS", conn);
SqlCeCommandBuilder cb = new SqlCeCommandBuilder(da);

conn.Open();

da.Fill(dsRemote,"Route_Sheets");

int rowsaffected = da.Update(dsRemote,"Route_Sheets");

conn.Close();

The problem is that the dataset you get from the webservice probely
contains a table Route_Sheets where all DataRow's states are set to
'Unchanged'. In this case, your update on da, doesn't work, the rows aren't
marked as 'Added'...

The solution isn't easy, because you cannot change the row state yourself.

Some (not so fun) solutions:

* The webservice that returns the dataset can be modified, so that the rows
stay marked as 'Added', this can be done, by setting AcceptChangesDuringFill
property to false before calling Fill on a DataAdapter

* Add a copy of each row and delete the original

DataTable dtRoutes = dsRemote["Route_Sheets"];
for (int i=0; i< dtRoutes.Rows.Count; ++i)
{
dtRoutes.Rows.Add ( dtRoutes.Rows[0].ItemArray ); // 0!
dtRoutes.Rows.RemoveAt(0); // 0!
}
da.Update ( dtRoutes );

* Manually updating row by row via the InsertCommand property of the
CommandBuilder


HTH
greetings