tableadapter update - existing primary key

[jbaldi]

I have two seperate .net programs that are working with the same table
in a SQL database. Both programs are using TableAdapters to read/
write to the table. Both programs can create a record in their
TableAdapters using the same primary key. If this happens and one
program updates the database before the other, the later will generate
the following SqlException: "Violation of PRIMARY KEY constraint
'PK_TableName'. Cannot insert duplicate key in object 'TableName'.
How do I force the latter program's TableAdapter to do an UPDATE
rather than and INSERT?

 

[Sheng Jiang[MVP]]

1 use a Guid field and generate a new Guid each time. In this way you have
total control of the new value, and Guids does is garenteed unique in the
forseeable future.
2 if you are use an autoincrement int as the primary key and manuplating
DataTable to add new rows, edit the primary key field in your dataset
designer, set the Autoincrement seed as -1 and the Autoincrement value
to -1.
This is the default setting in Visual Studio 2008.

 

[jbaldi]

The primary key is a serial number so I can't use a guid or an auto
id. If user A enters the same serial number as user B at the same
time then the problem occurs.

 

[jbaldi]

I found a solution:

http://msdn2.microsoft.com/EN-US/library/ms971481.aspx

"Refreshing Data in a DataSet

If you want to refresh the values in your DataSet with updated values
from the server, use DataAdapter.Fill. If you have primary keys
defined on your DataTable, DataAdapter.Fill matches new rows based on
the primary keys, and applies the server values as it changes to the
existing rows. The RowState of the refreshed rows are set to Unchanged
even if they were modified before the refresh. Note that, if no
primary key is defined for the DataTable, DataAdapter.Fill adds new
rows with potentially duplicate primary key values."

Specifically this:

"If you want to refresh a table with the current values from the serve
while retaining any changes made to the rows in the table, you must
first populate it with DataAdapter.Fill, fill a new DataTable, and
then Merge that DataTable into the DataSet with a preserveChanges
value of true."

Using this technique I can merge user B's new record into user A's
dataset (preserving user A's changes) and write back to the database.