Help getting started with @@Identity

OK, so, from what people tell me I should be using @.@.Idnetity. What Im trying to do is insert data into a table, than revrieve the id from the new row in that table, than use that id in another sql insert statment later down the road. Currnetly the way im doing it is with a sql insert, than executte the scalar, than execute a reader, which is causeing me much grief.

This is my sql statment here:

Dim

sqlInsertAsNew SqlCommand("INSERT INTO Author (Lastname, FirstName, FullName) VALUES (@.LName, @.FName, @.FullName)", sqlConn)

From what I understand, using @.@.Identity, I can retrive data from my insert statment without using a sperate select statment. Can you guys point me in the right direction, ive looked alot in the forms and such, most I found is either assuming you know what todo, or is security related.

Well,

I just posted a very similar question to yours in the same forum. Wish I would have noticed your post first :->

Thanks

Josh

|||After reading a colleges code I figured out what to do, however I dont think I could explain it. If anyone wants to see my code, feel free to ask.|||

I do not thing you can retrive data from database without a select statement. to use @.@.identtiy, you have to have one column in the table are identity column, and you can not insert any data into this column. usually this column is used as a primary key of the table, so to get the primary key of the row which you just inserted you can do this

INSERT INTO Author (Lastname, FirstName, FullName) VALUES (@.LName, @.FName, @.FullName)
select @.@.iidentity

this statement will return the idtentiy number of the row you just inserted. but you have to make sure on table author there is no trigger to insert data into another table with a identity column, otherwise you will get the identity number of the other table

to avoid this, you probably should alway useselect scope_identity()as much as you can

Hope this help

|||

DavidDu:

I do not thing you can retrive data from database without a select statement. to use @.@.identtiy, you have to have one column in the table are identity column, and you can not insert any data into this column. usually this column is used as a primary key of the table, so to get the primary key of the row which you just inserted you can do this

INSERT INTO Author (Lastname, FirstName, FullName) VALUES (@.LName, @.FName, @.FullName)
select @.@.iidentity

this statement will return the idtentiy number of the row you just inserted. but you have to make sure on table author there is no trigger to insert data into another table with a identity column, otherwise you will get the identity number of the other table

to avoid this, you probably should alway useselect scope_identity()as much as you can

Hope this help

IDENTITY is not a column it is a property of a column.