To retreive the ID of a new record, use the Insert Record With Identity extension (www.dmxzone.com/showDetail.asp?TypeId=3&NewsId=195) then locate the following lines of code:

' execute the insert
Set MM_editCmd = Server.CreateObject("ADODB.Command"
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = "SET NOCOUNT ON;" & MM_editQuery & ";SELECT @" & "@IDENTITY AS Ident"
Set rsLastIdent = MM_editCmd.Execute
if NOT rsLastIdent.EOF then
LastIdent = rsLastIdent.Fields.Item("Ident".Value
end if
MM_editCmd.ActiveConnection.Close

and replace them with:

' execute the insert
Set MM_editCmd = Server.CreateObject("ADODB.Command"
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.CommandText = "SELECT @" & "@IDENTITY AS Ident"
Set rsLastIdent = MM_editCmd.Execute
if NOT rsLastIdent.EOF then
Session("LastIdent" = rsLastIdent.Fields.Item("Ident".Value
end if
MM_editCmd.ActiveConnection.Close


You can then create a recordset which uses the session variable 'LastIdent' to find the new record ID on the page after the insert page - something like 'select * from table where id = session('lastident'). This code is NOT dependant on driver (as the original code works on MS SQL Server/Access 2000 with "Jet OLEDB Provider 4.0" only)

There appears to be errors in the original Insert Record With Identity extension - read the posts after the download page for more info.

Been looking for something like this for ages and thought someone else might be too.

Cheers.

<img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>