Using DataAdapter .Update to insert/upd rows in dataset (not based on DBs PK) problem

Question

I have a batch process that reads data from multiple tables into a dataset based on a common key. I then build a second dataset of the destination data querying on the same key.

At this point I have two Datasets that are structurally identical (from a table/column layout perspective). I then have a process that adds any row that exists in source to the destination dataset. In addition, the process will attempt to update certain columns based on the common key as well.

The problem seems to come in when the DataAdapter.UPDATE command is called with existing rows that it needs to update. Error: System.InvalidOperationException was unhandled Message="The table specified in the SELECT statement does not contain a unique key or identifier column, or the SELECT statement does not include all of the key columns."

Since I have no way of controlling what the PK is on the destination DB, is there a way to tell the Adapter what the key is for this particular update? I have "custom" set the primary keys for each DataTable in the Dataset.

This is a non user interfacing batch process and its perf requirements are quite low. (to explain the use of datasets, etc)

Any Thoughts?

Answer 1

you have to set the PK for each table in the dataset like you did. i don't think there's a way for adapter to figure out what the PK's are by itself. if there is and someone knows it i'm all ears.

Answer 2

how did you try to update ? through sql statement ?

http://vb.net-informations.com/dataadapter/dataadapter-updatecommand-sqlserver.htm

thanks.

Answer 3

profile the db and see what queries are being fired. copy the queries from Profiler and try to exe them manually. if they fail, then the problem is with the sql and the error is coming from the db and simply being passed up to your application.

this should at least tell u if the problem is caused by the lack of real PK's in the db.