how to ID when i have insert the new record immediately

hi all,
i have a question that how to get id afer insert a new record.
i just read from the following article, but still can't get the meaning:
http://forums.asp.net/1008337/ShowPost.aspx

and my code
Dim sql_3 As String
sql_3 = "insert into paper_type (name, enable,creation_date,lastupdate,user_id) values "
sql_3 += "('" & name & "','" & enable & "', getdate() ,getdate ," & user_id & ")"
Call OpenRs(sql_3)
' I want to get the id immediately, but how?? (i am using sql server)

can somebody help me to solve the problem??

thanks a lot

Hi,

In SQL Server you have the @.@.IDENTITY which returns the last entered Identity Column value.

You can use that to capture the last entered record's ID.

Check this articlehttp://www.sqlteam.com/item.asp?ItemID=422 for more information.

Thanks.

|||You are better off using SCOPE_IDENTITY() than @.@.IDENTITY for that purpose.|||thx ranganh,
but i don't know how to write code?? can u show me some codes?|||thx Motley,
but what is the meaning of SCOPE_IDENTITY()?|||@.@.IDENTITY
SCOPE_IDENTITY()|||

Hi,

SCOPE_IDENTITY returns the IDENTITY of the current user's session in case there are multiple concurrent users.

The @.@.IDENTITY just returns the last inserted identity column value. However, if concurrent users will access your application, then its better to use the SCOPE_IDENTITY.

Regarding source code, checkhttp://msdn2.microsoft.com/en-us/library/59x02y99.aspx

Write back if you need further help.

Thanks.

|||

Ranganh is close, but the problem isn't the concurrent users. The difference is scope of the identity.

Assume for example you have a table, we'll call this t1. Now we create a stored procedure to insert a record and return the identity:

CREATE p1 AS
BEGIN
INSERT INTO t1(c2) VALUES ('')
SELECT @.@.IDENTITY
END

For this example, assume c1 is an autoincrementing identity field. When we call the stored procedure, it returns the last identity to be created in the session, which appears to be the value of c1 we just inserted. Problem is, that assumption isn't always correct.

Now assume a month later a problem is found and that we've been tasked to go ahead and track all changes to the t1 table in a new table called auditT1. It has columns auditid,auditdate,audittype,c1,c2.

We'll create an insert,update,delete trigger on t1, that automatically records the changes in auditt1, great.

CREATE TRIGGER t1_insert FOR UPDATE AS
INSERT INTO auditT1(auditdate,audittype,c1,c2)
SELECT getutcdate(),'I',inserted.c1,inserted.c2
FROM inserted

CREATE TRIGGER t1_update FOR UPDATE AS
INSERT INTO auditT1(auditdate,audittype,c1,c2)
SELECT getutcdate(),'U',inserted.c1,inserted.c2
FROM inserted

CREATE TRIGGER t1_delete FOR DELETE AS
INSERT INTO auditT1(auditdate,audittype,c1,c2)
SELECT getutcdate(),'D',deleted.c1,NULL
FROM deleted

Great, now we have our audit trail, and everything is working. Until your application you wrote a few months ago starts blowing up. The stored procedure you wrote to insert and return identity of the newly inserted record is now returning the inserted auditid instead of c1. WHOOOPS!

The insert statement in the stored procedure causes t1_insert to fire AFTER the insert on t1 happens. We then (inside t1_insert) insert into auditT1 to record the change. Then all changes are commited to the database, and we execute the next statement in our stored procedure. At this point @.@.IDENTITY refers to the last identity inserted (Which was auditT1.auditid), however, SCOPE_IDENTITY() will return the last identity inserted within our scope, which was t1.c1.

Our original procedure should have been:

CREATE PROCEDURE p1 AS
BEGIN
INSERT INTO t1(c2) VALUES ('')
SELECT SCOPE_IDENTITY()
END

Which will correctly return the identity that was created because of our insert 100% of the time -- it ignores any identity values that may have been created because of a trigger, function, or stored procedure/function called from a trigger, etc.

Make sense?

|||

THX ALL!!! ^^

i solved the problem and i am using scrope_identity to get the "id"

|||

Hi Tisa,

You are welcome.

You would like to checkhttp://www.superexpert.com/default.aspx?id=1 which has got various resources on working with ASP.NET and SQL Server extensively.

Also,http://aspnet.4guysfromrolla.com/1.x/ is an excellent resource.

Thanks.