Retrieving record ID after insert 2

[GunJack]

I need to insert a record and then retrieve the value of the autonumber field associated with the record. I can easily do this with an insert followed by a select max(id) from table1 (where id is an autonumber field). What I'm looking for is how to ensure that two inserts at the same time don't return the same value for "ID".

In Cold Fusion, all I have to do is put <cflock> tags around the insert and select queries to ensure single threaded execution. What I'm looking for is the way to do this in ASP to ensure that even on high traffic sites, there is no chance for a mixup.

Thanks
GJ

 

[cmansert]

The best way to solve this problem is to move your sql insert into a stored procedure. After the insert runs have the stored procedure return the @@Identidy value.

Ex

INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @@IDENTITY AS 'Identity'

 

[GunJack]

Unfortunately stored procs are not an option. I was hoping for a way to serialize the code execution and thought there would be a similar type of locking mechanism available in ASP as in CF.

Thanks
GJ

 

[Tarwn]

If you use the AddNew method to insert your new row, you can set the fields and then call the Update method which will update the db with that row, before you close your object you can then read the value of the incremented field into a variable.
Sample code copied from something:
basically I have a strConect variable that is my connection string. qset_id, order_id, faculty_id are fields in my Class_Session table, and session_id is the incremental id field (autonumber or seeded integer type) in the table. When I update the recordset it inserts the row into the db leaving my recordset pointer still pointing to the row I entered, so i can pull the value of that incrememnted field right back out.

Set rs_create_session = Server.CreateObject("ADODB.Recordset")
		rs_create_session.Open "Class_Session", strConnect, adOpenKeySet, adLockOptimistic, adCmdTable
		rs_create_session.AddNew
		rs_create_session("qset_id") = question_set_id
		rs_create_session("order_id") = 0
		rs_create_session("faculty_id") = fac_id
		rs_create_session.Update
		session_id = rs_create_session("session_id")
		rs_create_session.Close

Oh, forgot to mention, the , adOpenKeySet, adLockOptimistic, adCmdTable are optional keys, i can't remember off the top of my head which one you need to use to do this, they are included from the adovbs.inc file. you should be able to find the numeric values relatively easy with a web search if you don't have that file.
-Tarwn
I know what I'm doing, I meant to hit that tree

 

[forecasting]

First you run sql = &quot;INSERT INTO jobs(job_desc, min_lvl, max_lvl) VALUES ('Accountant', 12, 125)&quot;

Then you run the sql = &quot;SELECT @@IDENTITY AS 'Identity'&quot; on the same connection, with no intervening sql commands on that connection.

It will return the unique id of the record you just inserted. You don't need to use a stored procedure.

 

[GunJack]

Thanks for the info, that's what I needed. I didn't realize you could work with the recordset object like that but that'll do what I needed. It's now working so thanks a bunch!

GJ