id from insertion 1

[xue]

I have the id field which is a primary key, identifer, and auto generated. but after insert statement, I can not get the id right away:
Set rs2 = conn.Execute("INSERT INTO Cart VALUES ('2/01/01','','" & strCONTROL & "' )&quot
Tempid=rs2("id&quot
would give me something like "operation is not allwed since the object is closed"
so, I have to use SELECT statment to get the last generated id:
Set rs3 = conn.Execute("SELECT * FROM Cart WHERE control= '" & strCONTROL & "' order by id desc&quot
rs3.MoveFirst
Response.Cookies("cart&quot("ID&quot = rs3("id&quot
is there a way that I can get the id right away after I did the INSERT statment or when I am doing a INSERT statment, so I do not have to do a SELECT statment again later on to get that id?

if there is no way that I can catch the id field right after INSERT, can some one give me some detail on how to use @@IDENTITY ?
in SQL, is make a new field called identityid, make it a uniqueidentifier,put (newid()) in default name enough for SQL part? it is not going to be a primary key though, the id is. then in ASP part, how do I get it right after INSERT statment?
Set rs2 = conn.Execute("INSERT INTO Cart VALUES ('2/01/01','','" & strCONTROL & "' )&quot
Tempstr = rs3("identityid&quot

am I able to get the unique identifier right away after insert? or I can't because the object is closed?

 

[LucyP]

I had this problem.
If you can use @@identity (you can't if you're using access)
then you have to use it in a SELECT statement after your INSERT statement.

Here's the SQL:
sql = "INSERT INTO TableName (field1,field2) VALUES (" & var1 & "," & var2 & " SELECT @@IDENTITY AS NewIdentity"

You would then set a recordset using this sql:

set RS = cn.execute (sql)

And now you can use the field NewIdentity like any other field in a recordset:

varID = RS("NewIdentity&quot

It's fantastic. If you're using Access, maybe now is the time to upgrade.

 

[xue]

thank you very much. I think that is what I need for getting the id after insert.

 

[jhowe]

I was getting the same "object is closed" issue and Microsoft's site put NOCOUNT settings around the SQL like this:

SET NOCOUNT ON INSERT INTO TABLE ( VAL1, VAL2, VAL3 )
VALUES ( 1, null,0); SELECT @@IDENTITY AS NEW_ID SET NOCOUNT OFF

and then you can get the identity number as field NEW_ID!