asp.net InsertCommand to return latest insert ID
- by Stijn Van Loo
Dear all,
I'm unable to retrieve the latest inserted id from my SQL Server 2000 db using a typed dataset in asp.NET
I have created a tableadapter and I ticked the "Refresh datatable" and "Generate Insert, Update and Delete statements". This auto-generates the Fill and GetData methods, and the Insert, Update, Select and Delete statements.
I have tried every possible solution in this thread
http://forums.asp.net/t/990365.aspx
but I'm still unsuccesfull, it always returns 1(=number of affected rows).
I do not want to create a seperate insert method as the auto-generated insertCommand perfectly suits my needs.
As suggested in the thread above, I have tried to update the InsertCommand SQL syntax to add SELECT SCOPY_IDENTITY() or something similar, I have tried to add a parameter of type ReturnValue, but all I get is the number of affected rows.
Does anyone has a different take on this?
Thanks in advance!
Stijn