asked on

DataAdapter Update help

Hi Everyone,

Here is my scenario, I am retrieving a dataset from a webservice and I would like to update my database with the information from the dataset. I'm attempting to perform this with a data adapter and I keep stumbling across this error:

"Update unable to find TableMapping"

Here is a sample of what I am working with:

// Variables
SqlDataAdapter adptAppSession;
DataSet dsAppSession;

// Creating the DataAdpater to be used for update
adptAppSession = new SqlDataAdapter("select * from AppSessions", dbConnection);

// Calling the web services
dsAppSession = webService.getAppSessionTable("01/01/2005","01/30/2005");


// Updating the data adapter
adptAppSession.Update(dsAppSession);

Can someone please help me with this, it seems like a common thing to do to retrieve a dataset from a webservice and update a database. What am i missing?? Does the structure of the dataset matter? I thought it would infer a schema and match it to the dataAdapter, do I have to do this implicitly and if so, how do I do it? Specific code examples would be greatly appreciated. Thanks

Zozig

jonvaughan

I am not sure that your code makes sense ....

at which point do you get the error.

You need to create the dataset from the dataAdapter ... through using

adptAppSession.fill(dsAppSession);

that will create the dataset which is matched to the dataAdapter and contains the entries recovered by "select * from AppSessions".

then you get a dataSet from the Web Service. Which is a separate DataSet.

you then need to use the new dataset to update the existing one ... that will create the new records which will be marked for Insert / Update / Delete
then when you use the Update() method it will update the DB.
Jon

b1xml2

The thing with ADO.NET 1.x is that Updates use the RowState property of the DataRows. So you'd need Rows that are marked as Modified (the UpdateCommand will be fired), Added (the InsertCommand will be fired) and/or Deleted (the DeleteCommand will be fired)

also, unless there's table mappings occurring, you'd be better off using the DataTable as well as the GetChanges() method

// Calling the web services
dsAppSession = webService.getAppSessionTable("01/01/2005","01/30/2005");

if (dsAppSession.HasChanges)
{
adptAppSession.Update(dsAppSession.Tables[0].GetChanges());
}

zozig

ASKER

jonvaugan,

I've added some code that utilizes two datasets as you've mentioned, I now get no error but nothing gets updated to the database, here is the code I am using:

// A new dataset to merge with the one from the web service
dsSessionData = new DataSet();
adptAppSession.Fill(dsSessionData);

// this is the data set from the webservice
dsAppSession = webService.getAppSessionTable("01/01/2005","01/30/2005");

// Merge the dataset with the webservice dataset
dsSessionData.Merge(dsAppSession,true,MissingSchemaAction.Add);

// Update the dataset
adptAppSession.Update(dsSessionData);

Is that what you are recommending? If so, why does nothing get updated, I've created xml files from the webservice dataset and confirmed that I am definately getting data back. Is there another method to use besides merge? Thanks.

Zozig

ASKER CERTIFIED SOLUTION

jonvaughan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

zozig

ASKER

Jonvaughan,

I've inspected the Datset after the merge and I've noticed that the Row state is 'unchanged' for all the rows. One item that is confusing me is the tablename. The tablename for the DataSet from the webservice states that the name is "Item" and the table I wish to update is AppSessions. I've placed a tablemapping on the dataAdapter to see if that would resolve any descrepancies but still no luck. Any ideas? Here is the code on the dataAdapter:

dsSessionData.Merge(dsAppSession,false,MissingSchemaAction.Add);
adptAppSession.TableMappings.Add("Item", "AppSessions");
adptAppSession.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adptAppSession.Update(dsSessionData,"AppSessions");

Thanks.