Mike,

Do you plan on using Datasets to contain multiple related tables? The reason I am asking is that the answer to your question really depends on the method you plan on using to select & update the data from the datasource.

FYI, SQL Server does not support more than one autoincrement column in a table. If you are concerned about adding rows to the parent and child tables at the same time, you can either:

1. Enclose multiple SQL commands within a transaction, or

2. Add new rows to the tables in your Dataset and then "post" the new records to the datasource in one step.

As far as retrieving the new identity value, the best way that I have found is to return the newly created row from the same stored procedure that inserts it. Here is a stored procedure that I use to Insert:

CREATE PROCEDURE AccountManagerInsert
(
     @Name varchar(30)
)
     AS
SET NOCOUNT OFF;
     INSERT INTO [AccountManager] ([Name])
     VALUES (@Name);
SELECT * FROM [AccountManager] WHERE ([AccountManagerID] = @@IDENTITY)

This stored procedure works well with Datasets or multiple SQL commands inside of a transaction.

>Hi Guys,
>
>We were planning to migrate our applications into C#. We are very much concern on how to get the newly autoincrement value from the datasource. Our main concern lies in Datatables wherein there is a parent and a child relation. Is it possible to use the autoincrement as the Primary and Foreign key for the Parent and child table respectively? If there are more than one concurrent users accessing the Datasource, how would the system know whether it got the right autoincrement number. We are looking at SCOPE_IDENTITY() to get the newly generated autoincrement. How does it work?
>
>Thanks...