Synchronize the identity value in the SQL server table

[dusty]

Hi, I'll try to simplify the problem:

I created a table "TestTable" in a database on the SQL server. The first column, 'id', is the primary key with a auto-increment identity. I want to work connection-less, so I made my dataset where I populated a table with the data from the TestTable. When I insert a new row with the .NewRow() method, the identity column value is the next value available. For instance: if the last row had the value of 105, the inserted rows identity value will be 106, and so on.
But if I deleted a few last rows in the table in the database (in example rows with identity values 106 and 107), after executing a SQL insert statement the new inserted rows identity value will 108, not 106 ! I hope I as clear. For the sake of simplicity I ommited why it is important to me to have the 'real' value of the identity before updating to the database.

How can I keep these values synchronized, without having to update the datasource after every inserted row in the dataset table ?

Thanks.

[Nick Malik]

Sure.

Don't use autonumber.

Use UniqueIdentifier data type. Create the GUID in your VB or C# code.
Assign it to both the header row and the detail rows (Foreign Key).

No need to coordinate with the database at all.

--- Nick

"dusty" <du***@discussions.microsoft.com> wrote in message
news:C8**********************************@microsof t.com...

Hi, I'll try to simplify the problem:

I created a table "TestTable" in a database on the SQL server. The first column, 'id', is the primary key with a auto-increment identity. I want to
work connection-less, so I made my dataset where I populated a table with
the data from the TestTable. When I insert a new row with the .NewRow()
method, the identity column value is the next value available. For instance:
if the last row had the value of 105, the inserted rows identity value will
be 106, and so on. But if I deleted a few last rows in the table in the database (in example rows with identity values 106 and 107), after executing a SQL insert
statement the new inserted rows identity value will 108, not 106 ! I hope I
as clear. For the sake of simplicity I ommited why it is important to me to
have the 'real' value of the identity before updating to the database.
How can I keep these values synchronized, without having to update the datasource after every inserted row in the dataset table ?
Thanks.