SCOPE_IDENTITY and parameters during insert

[Israel]

I found that if I use parameters on my insert statement and then
execute "select SCOPE_IDENTITY()" it always returns DbNull. However
if I DO NOT use parameters in the insert statement then I get the id
back correctly. Also if I use parameters and use "select @@identity"
it works fine? Note that I am using two different SqlCommand objects
that are using the same connection and transaction.

So this seems like some crazy bug but for some reason I can't find any
real answers about it. It appears that this guy had the same issue a
year ago but no one really answered the question:

http://groups.google.com/group/micr...=gst&q=SCOPE_IDENTITY+DbNull#272deab74ae2ff28

Here are two methods that demonstrate the problem:
Private Function InsertData1(ByVal SQLcon As SqlConnection, ByVal
transaction As SqlTransaction) As Decimal

Dim insCmd As SqlCommand
insCmd = New SqlCommand("insert into table_1 (name, value) values
(@name, @value);", SQLcon, transaction)
insCmd.Parameters.Add("@name", SqlDbType.NVarChar)
insCmd.Parameters.Add("@value", SqlDbType.Int)
insCmd.Parameters("@name").Value = "Israel"
insCmd.Parameters("@value").Value = 666
insCmd.ExecuteNonQuery()

Dim siCmd As SqlCommand
siCmd = New SqlCommand("select SCOPE_IDENTITY()", SQLcon,
transaction)
Dim objId = siCmd.ExecuteScalar()
Dim Id As Decimal = 0
If IsDBNull(objId) Then
Throw New Exception("Bad Monkey!") ' ALWAYS GET HERE
Else
Id = DirectCast(objId, Decimal)
End If

Return Id
End Function

Private Function InsertData2(ByVal SQLcon As SqlConnection, ByVal
transaction As SqlTransaction) As Decimal

Dim insCmd As SqlCommand
insCmd = New SqlCommand("insert into table_1 (name, value) values
('Israel', 666);", SQLcon, transaction)
insCmd.ExecuteNonQuery()

Dim siCmd As SqlCommand
siCmd = New SqlCommand("select SCOPE_IDENTITY()", SQLcon,
transaction)
Dim objId = siCmd.ExecuteScalar()
Dim Id As Decimal = 0
If IsDBNull(objId) Then
Throw New Exception("Bad Monkey!")
Else
Id = DirectCast(objId, Decimal) ' ALWAYS GET HERE
End If

End Function

 

[Stephany Young]

If you are having the same problem as 'this guy had a year ago' then the
code you have posted is not what you are actually executing. His problem was
caused by reusing the same SqlCommand object.

Are you certain the code you have posted was copy-and-paste'ed verbatim
(with no changes whatsover?

I would expect that the code you have posted would fail at the same place
and for the same reason in both functions.

Now ... Turn Option Strict On (and leave it on)!!!!!!!!!!!!

What you are actually doing is executing 2 separate batches of T-SQL.

The first batch is the insert into ... and the second batch is the select
....

In your context the scope for the SCOPE_IDENTITY() function is a batch and,
because there is no insert involving an identity column in the 2nd batch.

You can solve your 'problem' by constructing the batch correctly:

insert into ... ;select scope_ideitity()

and using ExecuteScalar() instead of ExecuteNonQuery()

 

[Armin Zingler]

Now ... Turn Option Strict On (and leave it on)!!!!!!!!!!!!

*sing* Yes yes, turn it on, turn it on, yes... *sing*




Armin

 

[Stephany Young]

Touche

*sing* Yes yes, turn it on, turn it on, yes... *sing*




Armin

 

[Israel]

Are you certain the code you have posted was copy-and-paste'ed verbatim
(with no changes whatsover?

Positive. I just verified that I was actually calling those two
methods and getting to break points in them. One works, one doesn't.
If I use "select @@identity" with the one that has parameters then it
also works.

Putting the insert and SCOPE_IDENTITY() into a single SqlCommand
worked for my test example but for some stupid reason in the "real"
code I'm always getting back Integer.MinValue.

 

[Israel]

Putting the insert and SCOPE_IDENTITY() into a single SqlCommand
worked for my test example but for some stupid reason in the "real"
code I'm always getting back Integer.MinValue.

Forget that last comment about Integer.MinValue. I realized that I
was erroneously setting IDENTITY_INSERT ON for that table in the one
case I just tried. In that case the code actually inserted
Integer.MinValue. So unfortunately like a lot of software "bugs" it
did exactly what I told it to do.

 

[Israel]

*sing* Yes yes, turn it on, turn it on, yes... *sing*



Armin

Sorry, I'm not a VB programmer - just doing a project in VB. I'm far
more efficient in C# and don't have to worry about accidentally not
declaring type of a variable. I feel like I'm speaking pig latin
using VB. And it writes too much code for me; I'm far too used to
writing my own code that I end up doing it anyway before I even notice
that the editor has written something for me.