ASP.NET tableadapter insert not returning the correct identity value

In my Visual Studio 2008 ASP.NET web page codebehind, I am calling an add routine in an SQL tableadapter, as follows.

NewLabID = LabItem.AddLaborItem(ThisWONum, TechID, DateTime.Now, DateTime.Now, "Working", "");

The record is being added correctly, but ‘NewLabID’ returns 1 every time, not the actual new LabID (LabID is defined as int/identity). The autogenerated code insert command is

INSERT INTO [LaborDetail] ([WONum], [TechID], [StartDate], [StopDate], [OtherType], [OtherDesc])
VALUES (@WONum, @TechID, @StartDate, @StopDate, @OtherType, @OtherDesc);
SELECT LabID FROM LaborDetail WHERE (LabID = SCOPE_IDENTITY())

and the autogenerated code which calls this is:

returnValue = command.ExecuteNonQuery();

Stepping through this with the debugger, returnValue is always 1. Shouldn’t the above command return the new identity field value?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

ExecuteNonQuery returns the number of rows affected. You will want to use ExecuteScalar() instead.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0