Asp.net InsertCommand to return the last insert identifier

Question

I cannot get the last inserted identifier from my SQL Server 2000 db using a typed dataset in asp.NET

I created a tableadapter and I marked "Refresh data" and "Create insert, update and delete statements". This automatically generates the Fill and GetData methods, as well as the Insert, Update, Select, and Delete statements.

I tried all possible solutions in this thread

http://forums.asp.net/t/990365.aspx

but I still have not succeeded, it always returns 1 (= the number of rows affected). I do not want to create a separate insertion method, since the automatically created insertCommand is great for my needs.

As suggested in the thread above, I tried updating the InsertCommand SQL syntax to add SELECT SCOPY_IDENTITY () or something similar, I tried to add a parameter of type ReturnValue, but all I get is the number of rows affected.

Does anyone have another thing? Thank you in advance! Stein

Answer 1

I have successfully found a way to get incremental id after insertion using my table adapter.

My approach is a little different, I use the Store procedure to insert, so my insert command has all the values, but the identifier, I made sp return an identifier that just calls: SET @ID = SCOPE_IDENTITY () and then COMMIT TRAN and the last line will be RETURN @ID

InsertCommand @RETURNVALUE , id.

Answer 2

, . Insert, MAX (id)

- , .

Answer 3

, /. .

• TableAdapter → Default Select Query → Advnaced options. . . , table-adapter, ​​[] / . , coloumn auto-increment, .

• TableAdapterObj.Update(ds.dataTable);

• Read the latest values ​​from the DataTable columns (ds.dataTable) and assign the appropriate values ​​to the child table before updating / pasting . This will work just the way you want.

alt text http://ruchitsurati.net/files/tds1.png