Returning SCOPE_IDENTITY from a Stored Procedure

[Jason James]

Hi all,

I have written stored procdures to manager inserts,
updates and deletes of my data. These procedures
are supplied to the data adapter that is being tasked
with updating data from my dataset.

However, to address some of the table relationship issues
I want to be able to retrieve the ID of the last inserted
record using SCOPE_IDENTITY.

Can I modify the SP to return SCOPE_IDENTITY
as a parameter of the command and then use the
parameter value to update the corresponding value
in the datarow that has just been inserted using
something like this:

Private Sub sqlDa_RowUpdated(ByVal sender As Object, ByVal e As
SqlRowUpdatedEventArgs)
If e.StatementType = StatementType.Insert Then
Dim x As Int32
x = sqlInsertCmd.Parameters("iID").Value
e.Row("iID") = x
e.AcceptChanges()
End If
End Sub

Where Paramters("iID") contains the SCOPE_IDENTITY
value from the SP.

My SP looks like this a the moment:

ALTER PROC imInsertItems
@ilID INT,
@iPartNumber NVARCHAR(50),
@iDescription NVARCHAR(50),
@iUnits INT,
@iQty INT,
@iGRNNum INT,
@iGRNType NVARCHAR(50),
@iTime datetime,
@iGroup NVARCHAR(50),
@iUserName NVARCHAR(50),
@iID INT OUTPUT

AS

INSERT INTO tblStockTake_Items
(ilID, iPartNumber, iDescription, iUnits, iQty, iGRNNum, iGRNType,
iTime, iGroup, iUserName)
VALUES
(@ilID, @iPartNumber, @iDescription, @iUnits, @iQty, @iGRNNum,
@iGRNType, @iTime, @iGroup, @iUserName)

SET @iID = SCOPE_IDENTITY

Is this a trival problem or so I have to query the database
using a datareader and obtain SCOPE_IDENTITY using
execute scaler?

Many thanks,

Jason.

 

[Guest]

You sproc looks fine, so that is not your issue.

I don't see where you are attaching your parameters to your command object,
so that might be the issue. Make sure the parameter is set as type output, or
you will have a short ride.

Debug to ensure your event handler is firing. I do not see the handles
statement, the normal VB.NET wiring (I spend much more time in C# these
days), so it is possible you are never hitting this code.

Also check to make sure the parameter is in a scope where it will have a
value at this point. This is unlikely to be the issue, but possible.

ExecuteScalar is a possibility, but if the event is not firing, you are not
going to get anything from it either.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[Jason James]

Greg,

I have used an addhandler to deal with the event. The parameter
is set as anoutput and added to the parameters collection.

Some time later....

It was a problem with the parameter name. I forgot the @. All
works great now.

Thanks,
Jason.