I have a problem with an incorrect ID thats returned from the second
insert.

1) There is a table in SQL Server 2005 with an ID colum that is marked
primary key and identity to autoincrement.

2) I fill a dataset with a dataadapter from this table

3) I delete a record. dataset.update. Fine.

4) I insert a record. dataset.update. Fine.

5) I delete the record I just inserted. Dataset.update. Fine.

6) I insert the same record again. dataset.update. ERROR

Now the ID field in the dataset has the ID number from the insert in
step 4, but when I check the table in SQL Server there is a higher
number there.

My problem is that I have a second table which has a foreign key
constraint on the id of the first table. And therefore I cannot insert
into the second table. Either I take the ID from the dataset, then it
gets inconsistent in the database. Or I take the ID from the database
with a select after the update, but then the constraint of my dataset
complains.

Please help.

kind regards
Oliver Drobnik

Show quote Hide quote That's how autoincrement fields work. You cannot reuse an ID value. SQL
Server generates a new number each time you attempt to insert a new row in
the table. Note also that the values in auto increment primary key fields
are not guaranteed to be in sequence, precisely because of the behavior you
experienced.
If you want to guarantee sequence, or reuse key values as you describe,
you'll have to take responsibility for managing key values in your
application.

Hi Peter,

I understand what you are saying. BUT  don't want to reuse any IDs.

I need to clarify that in step 6) I create a totally new row and insert
it. in the DB it will have a new ID, BUT the dataset will have a reused
ID after the dataset.update call.

Usually after a dataset.update I would expect the id row of the dataset
to reflect the acutal ID from the DB.

Why is this not the case?

kind regards
Oliver

Show quote Hide quote Are you calling AcceptChanges to update the dataset to what is in the
database?

Doesn't update include AcceptChanges?

Drops,

Inline

Should not be important
You create a new number in the datatable in SQL server this will as well be
in your DataTable as you use the standard SQL commands as used for that. The
datarow get a new ID

Is possible with SQL server not with every database but you are using that

How you do that, because in fact are you inserting a new record you should
have to add it completely to the datatable. Confirm your rules for an
instert as if it was the first time. You cannot insert as a kind of Update.

Cor

You can insert records by adding them to a dataset and then using the
update method of a dataadapter to get them posted back to the SQL
Server.

My point is, that if you delete and insert several times in a row then
the local id in the dataset (from the last insert) will no longer match
the id in the database.

I think I might have found a workaround for this problem by customizing
the insert command of my dataadapter. After the INSERT INTO ... I add a
; SELECT FROM ... where [ID] =@@IDENTITY;.

I am still testing this but this seems to update the local dataset with
the correct ID.

Show quote Hide quote The Update and Insert commands on a data adapter will not know the values the remote database used due to triggers, identity columns etc
unless you query that data from the server. Thus the data adaptor is just inventing the next id based upon the highest number+1 which in
this case is not correct.

The property to look at is SqlCommand.UpdateRowSource which includes the value "FirstReturnedRecord".
This tells the data adapter that the first record returned by the update/insert script should be considered as values the database stored,
the data adapter will then copy the values from the data reader to the saved row according to their column names (or data mappings if you've
set some up).

The reason your command "INSERT INTO ... ; SELECT FROM ... where [ID] =@@IDENTITY;" works is it is just mapping the ID column, any missing
columns are just assumed to be correct and left as they are, but you could select them as well as the id column if you wanted to be sure
that all the values matched that of the server in case of triggers, etc. May not have them now but you never know when you might decide it's
useful to have one.

DBConcurrencyException on dataset update (because of datetime primary key ?)
escaping special operators, ', etc.
Naming data access components
database connection loss
HOWTO: List SQL Servers on Local Machine