Getting the ID number created by Insert

[telmessos]

Hi all,

I use ASP + MySQL. I would like to learn how I can get the autoincrement ID number of a record just created by Insert into command.

Thanks

Ceyhun

[gil davis]

There may be a better way than this, but this is how I do it:
' insert a new record for this user
SQLQuery = "INSERT INTO ..."
Conn.Execute(SQLQuery)
' discover the record number
SQLQuery = "SELECT * FROM [Master Log] WHERE ... ORDER BY [Master Log].[ID] DESC"
Set RS = Conn.Execute(SQLQuery)
The first record is the one just made.

[russell]

gil, don't mean to be harsh but...that code is a classic example of what we call a race condition. a race condition is when one process depends upon another, with no guarantee of correct results from the initial process. in this case, suppose a second record was inserted between the execution of your two queries. the results could be disastrous.

the way to accomplish this in a thread safe manner is to return the identity value of the record inserted in the same transaction as the insert. use of stored procedures is definitely recommended in this case as well.

telmessos, have a look at The MySQL Documentation (http://dev.mysql.com/doc/refman/5.0/en/set-option.html) on identity values.

In MSSQL it is really easy to accomplish. In MS ACCESS it is a little harder but possible. I've never done it in MySQL but it sure looks a lot like the way it's done in MSSQL. Anyway, the link above demonstrates how to accomplish in MySQL.

[gil davis]

gil, don't mean to be harsh but...that code is a classic example of what we call a race condition.Part of the SELECT statement I did not post included the "userId" that triggered the record creation. The chances of the user making duplicate requests at exactly the same time is negligible.

Here is a page that looked helpful if you can use stored procedures:

http://databases.aspfaq.com/general/how-do-i-get-the-identity/autonumber-value-for-the-row-i-inserted.html