Retrieve latest ID and store in Session var ASP/VBScript

I have the following code that goes inside an insert statement. It retrieves the last ID inserted and saves it into a session variable. It works on ASP/JavaScript page but mine is ASP/VBScript page.
What would the code be for ASP/VB ?

-- code --

                                          <!--set up Auto Number retrieval for SQL Server -->
     var rsNewAutoIncrement = MM_editCmd.ActiveConnection.Execute("select @@identity")
     Session("QnrId") = rsNewAutoIncrement(0).Value
     <!--rsNewAutoIncrement.Close -->
     <!--var rsNewAutoIncrement = Nothing -->
     <!--end retrieval -->

---

Full code of the insert so to make sense of what it does:

if (String(Request("MM_insert")) == "form1") {
  if (!MM_abortEdit) {
    // execute the insert
      
    var MM_editCmd = Server.CreateObject ("ADODB.Command");
    MM_editCmd.ActiveConnection = MM_bluedotjs_STRING;
    MM_editCmd.CommandText = "INSERT INTO dbo.QnrsMailed (SenderEmail, NumRec, Expireson, SentBy, IsAccepted, FirmId, Originatedby, Processtypeid, SentTo, bcc, QnreData,cc) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)";
    MM_editCmd.Prepared = true;
    MM_editCmd.Parameters.Append(MM_editCmd.CreateParameter("param1", 201, 1, 250, Request.Form("from"))); // adLongVarChar
    MM_editCmd.Parameters.Append(MM_editCmd.CreateParameter("param2", 5, 1, -1, (String(Request.Form("recipients")) != "undefined" && String(Request.Form("recipients")) != "") ? Request.Form("recipients") : null)); // adDouble
    MM_editCmd.Parameters.Append(MM_editCmd.CreateParameter("param3", 135, 1, -1, (String(Request.Form("expdate")) != "undefined" && String(Request.Form("expdate")) != "") ? Request.Form("expdate") : null)); // adDBTimeStamp
    MM_editCmd.Parameters.Append(MM_editCmd.CreateParameter("param4", 5, 1, -1, (String(Request.Form("UserId")) != "undefined" && String(Request.Form("UserId")) != "") ? Request.Form("UserId") : null)); // adDouble
    MM_editCmd.Parameters.Append(MM_editCmd.CreateParameter("param5", 5, 1, -1, (String(Request.Form("Status")) != "undefined" && String(Request.Form("Status")) != "") ? Request.Form("Status") : null)); // adDouble
    MM_editCmd.Parameters.Append(MM_editCmd.CreateParameter("param6", 5, 1, -1, (String(Request.Form("FirmId")) != "undefined" && String(Request.Form("FirmId")) != "") ? Request.Form("FirmId") : null)); // adDouble
    MM_editCmd.Parameters.Append(MM_editCmd.CreateParameter("param7", 5, 1, -1, (String(Request.Form("originatedby")) != "undefined" && String(Request.Form("originatedby")) != "") ? Request.Form("originatedby") : null)); // adDouble
    MM_editCmd.Parameters.Append(MM_editCmd.CreateParameter("param8", 5, 1, -1, (String(Request.Form("process")) != "undefined" && String(Request.Form("process")) != "") ? Request.Form("process") : null)); // adDouble
    MM_editCmd.Parameters.Append(MM_editCmd.CreateParameter("param9", 202, 1, 255, Request.Form("to"))); // adVarWChar
    MM_editCmd.Parameters.Append(MM_editCmd.CreateParameter("param10", 202, 1, 255, Request.Form("bcc"))); // adVarWChar
    MM_editCmd.Parameters.Append(MM_editCmd.CreateParameter("param11", 201, 1, -1, Request.Form("comments"))); // adLongVarChar
      MM_editCmd.Parameters.Append(MM_editCmd.CreateParameter("param12", 202, 1, 255, Request.Form("cc"))); // adVarWChar
    MM_editCmd.Execute();
                                          <!--set up Auto Number retrieval for SQL Server -->
     var rsNewAutoIncrement = MM_editCmd.ActiveConnection.Execute("select @@identity")
     Session("QnrId") = rsNewAutoIncrement(0).Value
     <!--rsNewAutoIncrement.Close -->
     <!--var rsNewAutoIncrement = Nothing -->
     <!--end retrieval -->
      
      
    MM_editCmd.ActiveConnection.Close();

    // append the query string to the redirect URL
    var MM_editRedirectUrl = "EmailQnrreadytosend.asp";
    if (MM_editRedirectUrl && Request.QueryString && Request.QueryString.Count > 0) {
      MM_editRedirectUrl += ((MM_editRedirectUrl.indexOf('?') == -1) ? "?" : "&") + Request.QueryString;
    }
    Response.Redirect(MM_editRedirectUrl)
  }
}
LVL 1
AleksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hieloCommented:
Try:
If  CStr(Request("MM_insert")) = "form1"  Then
  If Not MM_abortEdit  Then
    ' execute the insert
      
    Set MM_editCmd = Server.CreateObject ("ADODB.Command")

    MM_editCmd.ActiveConnection = MM_bluedotjs_STRING

    MM_editCmd.CommandText = "INSERT INTO dbo.QnrsMailed (SenderEmail, NumRec, Expireson, SentBy, IsAccepted, FirmId, Originatedby, Processtypeid, SentTo, bcc, QnreData,cc) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)"

    MM_editCmd.Prepared = true

    'adLongVarChar=201
    Set cmdParam = MM_editCmd.CreateParameter("param1", 201, 1, 250, Request.Form("from"))
    MM_editCmd.Parameters.Append cmdParam


    'adDouble=5
    If Len(CStr(Request.Form("recipients"))) > 0 Then
       Set cmdParam = MM_editCmd.CreateParameter("param2", 5, 1, -1,Request.Form("recipients"))
    Else
       Set cmdParam = MM_editCmd.CreateParameter("param2", 5, 1, -1,Null)
    End If
   MM_editCmd.Parameters.Append cmdParam

    'adDBTimeStamp = 135
    If Len(CStr(Request.Form("expdate"))) > 0 Then
       Set cmdParam = MM_editCmd.CreateParameter("param3", 135, 1, -1,Request.Form("expdate") )
    Else
       Set cmdParam = MM_editCmd.CreateParameter("param3", 135, 1, -1,Null)
    End If
    MM_editCmd.Parameters.Append cmdParam

     'adDouble=5
    If Len(Request.Form("UserId"))) > 0 Then
       Set cmdParam = MM_editCmd.CreateParameter("param4", 5, 1, -1, Request.Form("UserId"))
    Else
       Set cmdParam = MM_editCmd.CreateParameter("param4", 5, 1, -1, Null)
    End If
    MM_editCmd.Parameters.Append cmdParam

    ' adDouble=5
    If Len(CStr(Request.Form("Status"))) > 0 Then
       Set cmdParam = MM_editCmd.CreateParameter("param5", 5, 1, -1,Request.Form("Status"))
    Else
       Set cmdParam = MM_editCmd.CreateParameter("param5", 5, 1, -1,Null)
    End If
    MM_editCmd.Parameters.Append cmdParam

    'adDouble=5
    If Len(CStr(Request.Form("FirmId"))) > 0 Then
       Set cmdParam = MM_editCmd.CreateParameter("param6", 5, 1, -1,Request.Form("FirmId") )
    Else
       Set cmdParam = MM_editCmd.CreateParameter("param6", 5, 1, -1,Null)
    End If
    MM_editCmd.Parameters.Append cmdParam

    ' adDouble=5
    If Len(CStr(Request.Form("originatedby"))) > 0 Then
        Set cmdParam = MM_editCmd.CreateParameter("param7", 5, 1, -1,Request.Form("originatedby"))
    Else
       Set cmdParam = MM_editCmd.CreateParameter("param7", 5, 1, -1,Null)
    End If
    MM_editCmd.Parameters.Append cmdParam

    ' adDouble=5
    If Len(CStr(Request.Form(""))) > 0 Then
       Set cmdParam = MM_editCmd.CreateParameter("param8", 5, 1, -1, Request.Form("process") )
    Else
       Set cmdParam = MM_editCmd.CreateParameter("param8", 5, 1, -1, Null)
    End If
    MM_editCmd.Parameters.Append cmdParam

    ' adVarWChar=202
    Set cmdParam = MM_editCmd.CreateParameter("param9", 202, 1, 255, Request.Form("to"))
    MM_editCmd.Parameters.Append cmdParam

    ' adVarWChar=202
    Set cmdParam = MM_editCmd.CreateParameter("param10", 202, 1, 255, Request.Form("bcc"))
    MM_editCmd.Parameters.Append cmdParam

    'adLongVarChar=201
    Set cmdParam = MM_editCmd.CreateParameter("param11", 201, 1, -1, Request.Form("comments"))
    MM_editCmd.Parameters.Append cmdParam

    ' adVarWChar=202
    Set cmdParam = MM_editCmd.CreateParameter("param12", 202, 1, 255, Request.Form("cc"))
    MM_editCmd.Parameters.Append cmdParam

    MM_editCmd.Execute()

    ' set up Auto Number retrieval for SQL Server
    ' if you are using mssql, then you most likely should be using SCOPE_IDENTITY() instead of @@identity
    ' https://msdn.microsoft.com/en-us/library/ms187342.aspx
    Set rsNewAutoIncrement = MM_editCmd.ActiveConnection.Execute("select SCOPE_IDENTITY() as ID")

    ' but if you are using MSAccess, try this
    'Set rsNewAutoIncrement = MM_editCmd.ActiveConnection.Execute("select @@identity as ID")

    Session("QnrId") = rsNewAutoIncrement(0).Value

    rsNewAutoIncrement.Close
    Set rsNewAutoIncrement = Nothing

    MM_editCmd.ActiveConnection.Close

    ' append the query string to the redirect URL
    MM_editRedirectUrl = "EmailQnrreadytosend.asp"

    If MM_editRedirectUrl AND Request.QueryString AND Request.QueryString.Count > 0  Then
      MM_editRedirectUrl = MM_editRedirectUrl &  "?" & Request.QueryString
    End If

    Response.Redirect(MM_editRedirectUrl)
  End If
End If

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AleksAuthor Commented:
That returned no value, but I checked the link in your code and tried some variation, this works:

      ' set up Auto Number retrieval for SQL Server
    Set rsNewAutoIncrement = MM_editCmd.ActiveConnection.Execute("select @@identity as ID")
      Session("QnrId") = rsNewAutoIncrement(0).Value
      rsNewAutoIncrement.Close
    Set rsNewAutoIncrement = Nothing

Thank you Hielo !

A
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.