Automatically set a session variable with the new record ID

Make this slight modification to an already great extension to place the just inserted record ID in to a session variable called NewID. Then, you can reference the NewID in a new recordset. (There may be a better way to do this but it worked great for me using UD4 and SQL Server 7.0!)

In order to set a Session Variable to the just inserted record ID or "Identity" you can either modify the code or edit the extension itself. Here is what it should look like:

If (Not MM_abortEdit) Then
    ' 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
    strLastIdent = rsLastIdent.Fields.Item("Ident").Value
         ' place the new record ID in a session variable called "NewID"
    Session("NewID") = strLastIdent
    end if
    MM_editCmd.ActiveConnection.Close

Once you have modified the code like the red section above then Click on the + on the Data Bindings panel and select Session. Name it "NewID". It will contain the just created record ID for you to use in new recordsets or whatever! If you want to test it make the changes I mentioned, then create a blank page and drag the new Session variable you created called NewID on to the page. Refer to that page when the form you created submits and the record ID of the just submitted form will show on that page.

Enjoy!

Chris