How to find an inserted value in a table

Hello,

I have 2 tables, and use objectdatasource and stored procedures, with sql server.

Let say in the first table I have IDCustomer as a datakey, and other records, and in the second I have the same IDCustomer and CustomerName. I have an INSERT stored procedure that will create a new record in the first table (so generate a new IDCustomer value), and I would like to insert immediately this new value in the second table.

How can I know the value of this new IDCustomer ? What is the best way to handle that ? Once the insert in the first table is done should read it the table and extract (with an executescalar) the value and then insert it in the second table ? This solution should work but I am not sure this is the best one.

Thanks for your help.

In your stored proc use @.@.IDENTITY to get the ID for the newly inserted row.

SET NOCOUNT ONINSERT INTO...SELECT @.@.IDENTITY as IDCustomer

Now in your code execute the proc as if it were a SELECT one and you'll get a single-row result set back. Or if using the SqlCommand object use ExecuteScalar.

|||

Perfect!

Thanks for the help.

|||Please use SCOPE_IDENTITY() rather than @.@.IDENTITY to retrieve the ID of the last inserted row. Check out books on line to understand why.