Is there a way to insert a record into SQL Server DB and retrieve unique ID?

[smclane]

Hi all - I have an asp page that inserts records into the sql server database but I need to retrieve the unique (auto-incremented) ID for that record and display on the results page for the user. Is there a way to do this? Any help much appreciated!

[Daemonspyre]

Easiest way is to do the INSERT then run a SELECT. However, it requires that you use a TIMESTAMP field to track your records.

If you are using MySQL, then you can use LAST_INSERT_ID(). MSSQL and Access to not have this feature.


strSQL = "INSERT INTO your_table (col1,col2,col3) VALUES ('test1','test2','test3');"

strSQL2 = "SELECT RecordID FROM your_table WHERE col1 = 'test1' AND col2 = 'test2' ORDER BY LastEdit DESC LIMIT 1;"

[Roelf]

Easiest way is to do the INSERT then run a SELECT. However, it requires that you use a TIMESTAMP field to track your records.

If you are using MySQL, then you can use LAST_INSERT_ID(). MSSQL and Access to not have this feature.



MSSQL does have this feature. In fact, there are different ways to get the identity, depending of your needs.

SELECT @@IDENTITY will return the last inserted value in an identity column of the database. Databasewide. So if your insert fires a trigger, which does an insert also, you will get the identity of the insert by the trigger.

SELECT SCOPE_IDENTITY() AS [IDENTITY] will give you the last inserted identity value in the current scope, but also table independant

SELECT IDENT_CURRENT('tablename') AS [IDENTITY] will give the last inserted value in the identity column of the mentioned table.

So choose well and be happy with MSSQL also.

[Daemonspyre]

Then I happily stand corrected.

Thanks!

[smclane]

Thank you both for your help! Now I am trying to figure out how to display the SELECT IDENT_CURRENT('tablename') AS [IDENTITY] results in the asp page. Sorry, everything I try does not seem to work :( Here is the code:

SQL="INSERT INTO tablename(column1, column2) VALUES ('" & value1 & "', '" & value2 & "'); SELECT IDENT_CURRENT('tablename') AS ['IDENTITY']"

'execute the SQL
Conn.Execute(SQL)

[Roelf]

the second part of the query returns a recordset (of one record). So you cannot execute with conn.execute the way you do, but this call returns a recordset object. Catch it in a variable and use it.

Set rs = conn.Execute(SQL)

then you can access the id by doing


id = rs("IDENTITY")