Here's a very simple question. I have an SP that inserts a row into a table and at the end there's the statement RETURN @@IDENTITY. What I can't seem to find is a way to retrieve this value in C#. I'm using the Enterprise library and using the method:
db.ExecuteNonQuery(cmd);
I've tried cmd.Parameters[0].Value to get the value but that returns 0 all the time. Any ideas?
Dim c as new sqlcommand("...")
Dim d As New SqlParameter()
d.Direction = ParameterDirection.ReturnValue
c.parameters.add(d)
c.executeNonQuery
(@@IDENTITY) = d.value
It is more or less like this...either this or just return the value from a stored procedure as an output parameter.
BTW, in most circumstances, you should use SCOPE_IDENTITY() rather than @@IDENTITY. Ref.
One point to mention. You should really use SCOPE_IDENTITY to return the identity value after INSERT. There is potential for @@IDENTITY to return the wrong value. Especially if TRIGGERS are in use, writing value to other tables after INSERT.
See the Books Online page for more detail.
Sounds to me like you want to execute with a scalar value
SqlCommand.ExecuteScalar()
as others have mentioned I think using SCOPE_IDENTITY() is more secure than the @@IDENTITY variable
Someone says use
IDENT_CURRENT('TableName')
http://www.velocityreviews.com/forums/t303448-return-identity-after-sql-insert.html
-Zubair http://zubairdotnet.blogspot.com