Hi,
in the past i used the following code to get the last recordset(identity column ) id of a field when it is inserted in the ACCESS db
but now when i use the same code for MS SQL it does not work?
please help

set Rs2=server.createobject("adodb.recordset")
Rs2.open "select * from Events ",conn,3,3
Rs2.addNew
Rs2("UserID")=intUserID
Rs2("Event_Name")=strEventName
Rs2.Update


Set RS2 = Conn.Execute( "SELECT @@IDENTITY" )
intNewRec = RS2(0)

Read this:
http://doc.ddart.net/mssql/sql70/globals_7.htm

The reason this is happening is because @@Identity is to be used in a single connection e.g. SELECT @@Identity needs to directly follow a SELECT INTO or INSERT statement.

Also consider using scope_identity() as @@Identitiy can cause some problems in being accurate.

================================================== =========
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.
================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========

but I do not user insert into command!!!
i am using recordset add/update
can you write the code

No I will not write you the code because I am not here to do your work for you, I will try to help you though. Since you are trying to do this inside of MS SQL I would suggest writing a stored procedures that would add your UserID and Event_Name to the database and then call @@Identity.

Here is why: if you call SELECT @@Identity, such as you are doing, it will return NULL for every record in the current table.

So your procedure would need to do this

INSERT INTO table(columns)values(values)
SELECT @@Identity

================================================== =========
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.
================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========

The id field of your recordset should already contain the id after you have called the Update method on the recordset.
Is this not happening?

Woody Z
http://www.learntoprogramnow.com

You have to use Insert Into Statement...

-Saurabhj

No woodyz it return null

You state that the record is getting created, correct?
If so, the recordset can contain the newly generated Id as long as several things are in place.
Is that your actual code you are showing us?
Is the Id field set as the Primary Key and Autonumber?
What is the provider you use for your connection?

I use this mechanism all the time for the very purpose you need.


Woody Z
http://www.learntoprogramnow.com