[C#] Getting the value of an identity column after an insert

[Curtis Rutland]

OK, here's what I'm doing. I have a SQL Server 2005 table with an auto-number Identity column that I will be inserting to. I used the DataSet designer to add a table adapter to my DataSet to which I want to add an insert command.

Now, I want to know if there is some way to retrieve the number that was inserted into the Identity field. I know that if I were writing the update command manually I could set a parameter to SCOPE_IDENTITY(), but is there anyway to do this in the TableAdapter's insert query that I will be adding?

Thanks.

[mldisibio]

I personally have avoided this situation precisely because it is so unintuitive, but the situation is obviously legitimate..

Just to help get you started...this is outdated (2003), but the keywords should lead you in the right direction: Bill Vaughn - IdentityValues

Here is the MSDN blurb. Again, not intutive. Seems the assumption is your insert will be done via a stored procedure. Is that an option for you? That is, can your Adapter's Insert query call a stored procedure?

Retrieving Identity Or AutoNumber Values

[Curtis Rutland]

Thanks for the repy.

To answer your question, yes, an SP is an option. Actually, it was the plan.

I'll take a look at both of these tomorrow and give them a shot.

I'll be sure to post the results.

Thanks!

[Plater]

Wouldn't the number of rows change? Could you just look at the last row in the index?

[Curtis Rutland]

Thanks a lot, mldisibio.

Turns out that the output params were a real hassle when you are using the dataset to do the hard work for you. I figured out that I can make an SP with the insert statement followed by a select statement, and get exactly the result I want.

CREATE PROCEDURE name
--paramlist here
AS
BEGIN
--insert statement here
SELECT SCOPE_IDENTITY() AS IdOfNewRow
END

And then you add a new query to your table adapter, specifying to use an existing SP. VS will poll the DB and find your SP, find it's input parameters, and it's output values, and let you specify it as a single return value or a rowset return value. I chose single value.

It works perfectly.

Again, thanks for your help.

[Plater]

Hehe, when you said you couldn't use scope_indentity() above, I just wrote that off.
I use just:
return SCOPE_IDENTITY()

But I guess if you use SELECT it would come out like a "table"

[Curtis Rutland]

Hehe, when you said you couldn't use scope_indentity() above, I just wrote that off.
I use just:
return SCOPE_IDENTITY()
But I guess if you use SELECT it would come out like a "table"

Well, if I would have thought of RETURN instead of SELECT I would have used that. Oh well. all's well that ends well.