Getting ID of record just inserted

zkenet · #1 (**permalink**) 01-15-04, 12:25

Hi,
I searched this forum for this topic and could nt find it. Found something under MS SQL but it was MS-SQL specific solution.

SO,
After I insert a new record into a table, how do I get its record ID, making sure that it is the record in question? In theory for a very busy site two users can submit at the same time, and one of them get the other persons record ID.

Option 1:

Code:

  ...insert...
  sqlString="SELECT MAX(recordID) AS maxID FROM tbl_records"
  set maxRS = con.Execute(sqlString) 
  recordID = maxRS("maxID")

CON: possibility that two users submit at the same time, and returns wrong ID. (this is what I use now, and I take a risk)

Option 2:

Code:

  ...insert...
  sqlString="SELECT recordID FROM tbl_records WHERE userID=" & userID & " ORDER BY recordID DESC LIMIT 0,1"
  set maxRS = con.Execute(sqlString) 
  recordID = maxRS("recordID ")

CON: Is this efficient? Also will not work for guests/non-registered users, unless I store session ID and look that up... (yuck)

Is there an option 3?

Thx,
Ziv.