Asp.net tableadapter insert not returning correct identity value

Question

In my VS2008 asp.net web page codebehind I am calling an add routine in a 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);\r\nSELECT 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?

Answer 1

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