Problem Retrieving @@Identity when inside a transaction.

I'm using transactions with my SqlConnection ( sqlConnection.BeginTransaction() ).
I do an Insert into my User table, and then subsequently use thegenerated identity ( via @.@.identity ) to make an insert into anothertable. The foreign key relationship is dependant on the generatedidentity. For some reason with transactions, I can't retrieve theinsert identity (it always returns as 1). However, I need theinserts to be under a transaction so that one cannot succeed while theother fails. Does anyone know a way around this problem?
So heres a simplefied explanation of what I'm trying to do:
Begin Transaction
Insert into User Table
Retrieve Inserted Identity (userID) via @.@.Identity
Insert into UserContact table, using userID as the foreign key.
Commit Transaction.
Because of the transaction, userID is 1, therefore an insert cannot bemade into the UserContact table because of the foreign key constraint.
I need this to be a transaction in case one or the other fails. Any ideas??


Try using Scope_Identity() instead of @.@.IDENTITY and see if that solvesyour problem. Scope_Identity will give you the last identityvalue generated in your current scope.
|||I tried using Scope_Identity() and I'm getting the exact same problem.
|||I can tell you that I just tried out your scenario and it worked withno problem for me. I tried it both wrapped in a transaction andnot. So there is something particular about your code that iscausing the problem. Could you post it here?
|||Fixed it. Had nothing to do with transactions. Stupid error on my part.
It seems as though I forgot to encapsulate my sql insert procedure in aBegin / End block, so that when I performed an insert it returned 1instead of the @.@.Identity which was on the next line.
Thanks for trying to help! Much appreciated =D
|||I'm glad you got it sorted. Be sure to use Scope_Identity()instead of @.@.IDENTITY. If at some point in the future, e.g., atrigger is added to your table which inserts a record into some table,@.@.IDENTITY will contain the Identity value of that inserted record, andthat's not what you want.