[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

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
                                    
0
zozig
Asked:
zozig
  • 2
  • 2
1 Solution
 
jonvaughanCommented:
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
0
 
b1xml2Commented:
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());
}
0
 
zozigAuthor Commented:
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
0
 
jonvaughanCommented:
Merge should be the correct method to use - if you debug the dataset after the merge can you see that some have a row state of
Modified / Added / Deleted ? as b1xml2 mentioned the DA.Update method uses these to fire the correct Update / Insert / Delete statements to the DB.

If you do have these Row States then you may need to add the correct Update / Delete / Insert statements into your data adapter to ensure that you get the correct changes in your DB.
0
 
zozigAuthor Commented:
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.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now