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.

Problem inserting data into SQL Server 2000

Tryinna insert a new row

This is my code:
Dim myConn As SqlConnection
Dim mycmd As SqlCommand
myConn = New SqlConnection("Initial Catalog=science;" & _
"Data Source=localhost;Integrated Security=SSPI;")
mycmd = New SqlCommand("INSERT into STEP1(firstname) VALUES('Amin')", myConn)
myConn.Open()
mycmd.ExecuteNonQuery()
myConn.Close()

This is the error message I get:
The name 'firstname' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.Are you certain STEP1 is a table name? Can you run the same SQL in Query Analyzer and see what happens?|||Thank you Douglas... you made me use SQL Query Analyzer which I never used before, it's a very nice progie..

OK, I tried the following code and run it:
USE science
INSERT into STEP1(firstname) VALUES('Amin')

And the message I got:
(1 row(s) affected)

So I was amazed with the result and checked it for becoming sure. I opened Enterprise Manager, opened my table named STEP1 and... guess what? a new row with the firstname of 'Amin' and NULL for the remaining fields...

Oh my God!! plz help me... this asp.net thang is getting me old! When am I gonna learn it??|||Maybe you could try enclosing the table and column names in square brackets:

mycmd = New SqlCommand("INSERT into [STEP1]([firstname]) VALUES('Amin')", myConn)

Terri|||Thank you very much for the tip Terri

I found why this error occurs but still don't know how to solve it

I've used a function located in a self-made namespace:

Function newUser(ByVal firstname As String, ByVal surname As String, ByVal title As String,
ByVal gender As String, ByVal birthday As Integer, ByVal country As String, ByVal city As String, ByVal state As String, ByVal username11 As String, ByVal pass As String, ByVal email As String)

Dim myConn As SqlConnection
Dim mycmd As SqlCommand

myConn = New SqlConnection(ConfigurationSettings.AppSettings("MyConnection1"))

mycmd = New SqlCommand("INSERT into [users_contact]([username],[pass],[email],[firstname],[surname],[title],[gender],[birthday],[country],[city],[state]) VALUES(username,pass,email,firstname,surname,title,gender,birthday,country,city,state)", myConn)

myConn.Open()
mycmd.ExecuteNonQuery()
myConn.Close()

End Function

Maybe I have to do some kind of conversion like ToString or something
Cz when I use 'string' instead of those variables, the insert command works properly.|||You initially said your statement looked like this:

mycmd = New SqlCommand("INSERT into STEP1(firstname) VALUES('Amin')", myConn)

And now you are saying that your statement looks like this:

mycmd = New SqlCommand("INSERT into [users_contact]([username],[pass],[email],[firstname],[surname],[title],[gender],[birthday],[country],[city],[state]) VALUES(username,pass,email,firstname,surname,title,gender,birthday,country,city,state)", myConn)

This is completely different. Please be sure to supply *exact* code in the future so that you can receive accurate help.

The solution to your problem is to use SQL parameters, like this:

mycmd = New SqlCommand("INSERT into [users_contact]([username],[pass],[email],[firstname],[surname],[title],[gender],[birthday],[country],[city],[state]) VALUES(@.username,@.pass,@.email,@.firstname,@.surname,@.title,@.gender,@.birthday,@.country,@.city,@.state)", myConn)
mycmd.Parameters.Add(New SqlParameter("@.username", SqlDbType.VarChar, 99))
mycmd.Parameters("@.username").Value = username
mycmd.Parameters.Add(New SqlParameter("@.pass", SqlDbType.VarChar, 99))
mycmd.Parameters("@.pass").Value = pass
mycmd.Parameters.Add(New SqlParameter("@.email", SqlDbType.VarChar, 99))
mycmd.Parameters("@.email").Value = email
<add the rest of the parameters here
myConn.Open()
mycmd.ExecuteNonQuery()
myConn.Close()

You will need to change the SqlDbType and length as appropriate for each of the parameters.

Terri|||WwWwWoOoOoOoOoOoWwWwWwWw.......

Thank you dear Terri...

How amazing you are......

It wroked!! It was been a long time I had this prob and couldn't find any useful article.. Go on and create a website with articles. hehee

Well, I don't know how to thank you...... anyway, thanks a lot!!

visit my website: www.aminsadeghi.com|||I am glad you got it working!

There are actually a lot of articles out there on this, but I know it can be difficult to find something when you are not really sure what you are looking for.

I suggest looking through theASP.NET Quickstart Tutorials, especiallyInserting Data into a SQL Database.

Terri|||I think the ConnectionString is wrong.
try any on bellow:

myConn = New SqlConnection("Persist Security Info=False;Integrated Security=SSPI;Server=(local);Database=science;")

or

myConn = New SqlConnection("Persist Security Info=False;Integrated Security=SSPI;Server=127.0.0.1;Database=science;")

or

myConn = New SqlConnection("Server=(local);Database=science;UID=sa;PWD=;")

or

myConn = New SqlConnection("Server=127.0.0.1;Database=science;UID=sa;PWD=;")|||Dear Yijun_lee,

The connection string is totally correct, it's calling the connection string from appSettings inside the web.config file.
If you wanna learn this new method, find and see the movie on http://msdn.microsoft.com/msdntv - Application Settings - Rob Howard

and Dear Terri,

You think I didn't search for it? as a matter of fact the problem is with Microsoft that doesn't offer free learning stuff for it's new programming language.
To get the answer of this question i had, i read the link you dropped for me, i remember i read that b4, but it only describes how to add 'data' data, and it didn't show how to insert untyped variables that u mentioned to make it look like this: @.variable and after that typing some SQL Parameters for it...
To learn this I even downloaded ASP.NET Kit... and nothing useful at all.

I believe now that Microsoft has built this very nice ASP.NET language, it has to do some work to make it comprehensive and popular.

Anyway special thanks to you dear Terri, Rob Howard (as an intellectual maker of ASP.NET) and ofcourse Bill Gates ;)