Retrieve id of last added record and add to session

Using:  MS SQL 2008 and classic ASP

I have a SP that inserts a new record in table  'users'. I need to right after the record is inserted to retrieve the number of the id (userid) from this table to use on the next Stored Procedure.
I have code to do this when I do the insert directly in the ASP without a SP, but this time I am using a SP. Below is my code:

<!--#Start of SP-->

<%

Dim sp_addpartytousers__FirstNm
sp_addpartytousers__FirstNm = "0"
if(Request("FirstNm") <> "") then sp_addpartytousers__FirstNm = Request("FirstNm")

Dim sp_addpartytousers__LastNm
sp_addpartytousers__LastNm = "0"
if(Request("LastNm") <> "") then sp_addpartytousers__LastNm = Request("LastNm")

Dim sp_addpartytousers__Firmid
sp_addpartytousers__Firmid = "0"
if(Session("Firmid") <> "") then sp_addpartytousers__Firmid = Session("Firmid")

Dim sp_addpartytousers__Updatedby
sp_addpartytousers__Updatedby = "0"
if(Session("userid") <> "") then sp_addpartytousers__Updatedby = Session("userid")

Dim sp_addpartytousers__FirmAddressIdcon
sp_addpartytousers__FirmAddressIdcon = "0"
if(rs_usersoffice("FirmAddressIdcon") <> "") then sp_addpartytousers__FirmAddressIdcon = rs_usersoffice("FirmAddressIdcon")

Dim sp_addpartytousers__Sex
sp_addpartytousers__Sex = "male"
if(Request("Sex") <> "") then sp_addpartytousers__Sex = Request("Sex")

%>
<%

set sp_addpartytousers = Server.CreateObject("ADODB.Command")
sp_addpartytousers.ActiveConnection = MM_bluedot_STRING
sp_addpartytousers.CommandText = "dbo.addpartytousers"
sp_addpartytousers.CommandType = 4
sp_addpartytousers.CommandTimeout = 0
sp_addpartytousers.Prepared = true
sp_addpartytousers.Parameters.Append sp_addpartytousers.CreateParameter("@RETURN_VALUE", 3, 4)
sp_addpartytousers.Parameters.Append sp_addpartytousers.CreateParameter("@FirstNm", 200, 1,30,sp_addpartytousers__FirstNm)
sp_addpartytousers.Parameters.Append sp_addpartytousers.CreateParameter("@LastNm", 200, 1,30,sp_addpartytousers__LastNm)
sp_addpartytousers.Parameters.Append sp_addpartytousers.CreateParameter("@Firmid", 3, 1,4,sp_addpartytousers__Firmid)
sp_addpartytousers.Parameters.Append sp_addpartytousers.CreateParameter("@Updatedby", 3, 1,4,sp_addpartytousers__Updatedby)
sp_addpartytousers.Parameters.Append sp_addpartytousers.CreateParameter("@FirmAddressIdcon", 3, 1,4,sp_addpartytousers__FirmAddressIdcon)
sp_addpartytousers.Parameters.Append sp_addpartytousers.CreateParameter("@Sex", 200, 1,20,sp_addpartytousers__Sex)
sp_addpartytousers.Execute()

%>

<!--#End of of SP-->

Open in new window


And this is the code that I usually use when inserting with ASP code:

	' 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

Open in new window


I was wondering if I can use this code and if so where to place it in the code above ?

This is the code for my SP in the database. Is the code to retrieve the latest ID correct ?   and if so .. how can I retrieve it in the asp to use it like:  <%=Request("IDENT_CURRENT")%>   ?  

USE [BlueDot]
GO
/****** Object:  StoredProcedure [dbo].[addpartytousers]    Script Date: 10/08/2015 08:09:49 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[addpartytousers]
@FirstNm VARCHAR (30), @LastNm VARCHAR (30), @Firmid INT, @Updatedby INT, @FirmAddressIdcon INT, @Sex VARCHAR(20)
		
AS   
INSERT 
   INTO   Users (Archivedcont,ContactStatus, FirstNm, LastNm,usertype, FirmId,Updatedby, FirmAddressIdcon, Sex  )
   VALUES         ('0', '1', @FirstNm, @LastNm,'contact', @Firmid, @Updatedby , @FirmAddressIdcon, @Sex )


SELECT IDENT_CURRENT(users)

Open in new window

LVL 1
AleksAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Jim HornMicrosoft SQL Server Data DudeCommented:
The T-SQL is --> SELECT SCOPE_IDENTITY()

To demonstrate in SSMS..
CREATE TABLE #test (id int identity(1,1), str varchar(10)) 

INSERT INTO #test (str) VALUES ('banana') 
SELECT SCOPE_IDENTITY()

INSERT INTO #test (str) VALUES ('grape') 
SELECT SCOPE_IDENTITY()

INSERT INTO #test (str) VALUES ('watermelon') 
SELECT SCOPE_IDENTITY()

SELECT * FROM #test ORDER BY id

Open in new window


Use that line in place of the SELECT IDENT_CURRENT(users) you have so that the Stored Procedure returns the identity value, which you can call in your ASP code after executing the SP.
AleksAuthor Commented:
I understand I can get the last ID that way. But in the ASP how can I retrieve it so I can use it to insert a record in the SP just below this one ?

Here is my entire code. On line 128 is where I need to retrieve the value of the last ID inserted in the previous Stored Procedure.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!DOCTYPE html>

<!--#BlueDot Include files-->
<!--#include file="../../Connections/bluedot.asp" -->
<!--#include file="../../includes/bdot/attysessiontimeoutvb2.asp"-->
<!--#BeginBlock-->

<!--#User recordset-->
<%
Dim rs_contact__MMColParam
rs_contact__MMColParam = "0"
If (Request("newuserid") <> "") Then 
  rs_contact__MMColParam = Request("newuserid")
End If
%>
<%
Dim rs_contact__MMColParam1
rs_contact__MMColParam1 = "0"
If (Session("FirmId") <> "") Then 
  rs_contact__MMColParam1 = Session("FirmId")
End If
%>
<%
Dim rs_contact
Dim rs_contact_cmd
Dim rs_contact_numRows

Set rs_contact_cmd = Server.CreateObject ("ADODB.Command")
rs_contact_cmd.ActiveConnection = MM_bluedot_STRING
rs_contact_cmd.CommandText = "SELECT UserId FROM dbo.Users WHERE UserId = ? and users.firmid = ?" 
rs_contact_cmd.Prepared = true
rs_contact_cmd.Parameters.Append rs_contact_cmd.CreateParameter("param1", 5, 1, -1, rs_contact__MMColParam) ' adDouble
rs_contact_cmd.Parameters.Append rs_contact_cmd.CreateParameter("param2", 5, 1, -1, rs_contact__MMColParam1) ' adDouble

Set rs_contact = rs_contact_cmd.Execute
rs_contact_numRows = 0
%>

<!--#office recordset-->
<%
Dim rs_usersoffice__MMColParam
rs_usersoffice__MMColParam = "0"
If (Session("userid") <> "") Then 
  rs_usersoffice__MMColParam = Session("userid")
End If
%>
<%
Dim rs_usersoffice
Dim rs_usersoffice_cmd
Dim rs_usersoffice_numRows

Set rs_usersoffice_cmd = Server.CreateObject ("ADODB.Command")
rs_usersoffice_cmd.ActiveConnection = MM_bluedot_STRING
rs_usersoffice_cmd.CommandText = "SELECT UserId, FirmAddressIdcon FROM dbo.Users WHERE UserId = ?" 
rs_usersoffice_cmd.Prepared = true
rs_usersoffice_cmd.Parameters.Append rs_usersoffice_cmd.CreateParameter("param1", 5, 1, -1, rs_usersoffice__MMColParam) ' adDouble

Set rs_usersoffice = rs_usersoffice_cmd.Execute
rs_usersoffice_numRows = 0
%>


<!--#Start of SP retrieve last ID inserted -->

<%

Dim sp_addpartytousers__FirstNm
sp_addpartytousers__FirstNm = "0"
if(Request("FirstNm") <> "") then sp_addpartytousers__FirstNm = Request("FirstNm")

Dim sp_addpartytousers__LastNm
sp_addpartytousers__LastNm = "0"
if(Request("LastNm") <> "") then sp_addpartytousers__LastNm = Request("LastNm")

Dim sp_addpartytousers__Firmid
sp_addpartytousers__Firmid = "0"
if(Session("Firmid") <> "") then sp_addpartytousers__Firmid = Session("Firmid")

Dim sp_addpartytousers__Updatedby
sp_addpartytousers__Updatedby = "0"
if(Session("userid") <> "") then sp_addpartytousers__Updatedby = Session("userid")

Dim sp_addpartytousers__FirmAddressIdcon
sp_addpartytousers__FirmAddressIdcon = "0"
if(rs_usersoffice("FirmAddressIdcon") <> "") then sp_addpartytousers__FirmAddressIdcon = rs_usersoffice("FirmAddressIdcon")

Dim sp_addpartytousers__Sex
sp_addpartytousers__Sex = "male"
if(Request("Sex") <> "") then sp_addpartytousers__Sex = Request("Sex")

%>

<%

set sp_addpartytousers = Server.CreateObject("ADODB.Command")
sp_addpartytousers.ActiveConnection = MM_bluedot_STRING
sp_addpartytousers.CommandText = "dbo.addpartytousers"
sp_addpartytousers.CommandType = 4
sp_addpartytousers.CommandTimeout = 0
sp_addpartytousers.Prepared = true
sp_addpartytousers.Parameters.Append sp_addpartytousers.CreateParameter("@RETURN_VALUE", 3, 4)
sp_addpartytousers.Parameters.Append sp_addpartytousers.CreateParameter("@FirstNm", 200, 1,30,sp_addpartytousers__FirstNm)
sp_addpartytousers.Parameters.Append sp_addpartytousers.CreateParameter("@LastNm", 200, 1,30,sp_addpartytousers__LastNm)
sp_addpartytousers.Parameters.Append sp_addpartytousers.CreateParameter("@Firmid", 3, 1,4,sp_addpartytousers__Firmid)
sp_addpartytousers.Parameters.Append sp_addpartytousers.CreateParameter("@Updatedby", 3, 1,4,sp_addpartytousers__Updatedby)
sp_addpartytousers.Parameters.Append sp_addpartytousers.CreateParameter("@FirmAddressIdcon", 3, 1,4,sp_addpartytousers__FirmAddressIdcon)
sp_addpartytousers.Parameters.Append sp_addpartytousers.CreateParameter("@Sex", 200, 1,20,sp_addpartytousers__Sex)
sp_addpartytousers.Execute()

%>

<!--#End of of SP-->

<!--#Start of SP user last inserted ID-->
<%

Dim sp_addpartcont__Partcontid
sp_addpartcont__Partcontid = "0"
if(rs_contact("userid")   <> "") then sp_addpartcont__Partcontid = rs_contact("userid")  

Dim sp_addpartcont__Firmid
sp_addpartcont__Firmid = "0"
if(Session("Firmid")  <> "") then sp_addpartcont__Firmid = Session("Firmid") 

Dim sp_addpartcont__Userid
sp_addpartcont__Userid = "0"
if(Request("LASTINSERTEDID")  <> "") then sp_addpartcont__Userid = Request("LASTINSERTEDID")

Dim sp_addpartcont__Relation
sp_addpartcont__Relation = "0"
if(Request("relation")  <> "") then sp_addpartcont__Relation = Request("relation") 

%>
<%

set sp_addpartcont = Server.CreateObject("ADODB.Command")
sp_addpartcont.ActiveConnection = MM_bluedot_STRING
sp_addpartcont.CommandText = "dbo.addpartytpartiescont"
sp_addpartcont.Parameters.Append sp_addpartcont.CreateParameter("@RETURN_VALUE", 3, 4)
sp_addpartcont.Parameters.Append sp_addpartcont.CreateParameter("@Partcontid", 3, 1,4,sp_addpartcont__Partcontid)
sp_addpartcont.Parameters.Append sp_addpartcont.CreateParameter("@Firmid", 3, 1,4,sp_addpartcont__Firmid)
sp_addpartcont.Parameters.Append sp_addpartcont.CreateParameter("@Userid", 3, 1,4,sp_addpartcont__Userid)
sp_addpartcont.Parameters.Append sp_addpartcont.CreateParameter("@Relation", 200, 1,50,sp_addpartcont__Relation)
sp_addpartcont.CommandType = 4
sp_addpartcont.CommandTimeout = 0
sp_addpartcont.Prepared = true
sp_addpartcont.Execute()

%>

<!--#End of of SP-->

<html>
<head>
<title></title>
</head>

<body>
<p>
</html>
<%
rs_contact.Close()
Set rs_contact = Nothing
%>
<%
rs_usersoffice.Close()
Set rs_usersoffice = Nothing
%>

Open in new window

Carl TawnSystems and Integration DeveloperCommented:
Based on your stored proc, you would need to return a recordset and grab the value from that:
Set rs = sp_addpartytousers.Execute()
Response.Write "The ID is: " & rs(0)

Open in new window

IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Big MontyWeb Ninja at largeCommented:
only try the code below if Carl's answer doesn't get you what you want

I don't remember if there is a difference between sp.Execute and sp.Open when it comes to parameterized queries, so if it DOESN'T work, try using

sp_addpartytousers.Open
AleksAuthor Commented:
I tried using the code from Carl. And it does display the value on the page. Now I am trying to use it to insert it into the table but it still inserts '0'

Here is my code after the first SP.

<%
Set LASTINSERTEDID = sp_addpartytousers.Execute()
%>

<!--#Start of SP user last inserted ID-->
<%

Dim sp_addpartcont__Partcontid
sp_addpartcont__Partcontid = "0"
if(rs_contact("userid")   <> "") then sp_addpartcont__Partcontid = rs_contact("userid")  

Dim sp_addpartcont__Firmid
sp_addpartcont__Firmid = "0"
if(Session("Firmid")  <> "") then sp_addpartcont__Firmid = Session("Firmid") 

Dim sp_addpartcont__Userid
sp_addpartcont__Userid = "0"
if(Request("LASTINSERTEDID")  <> "") then sp_addpartcont__Userid = Request("LASTINSERTEDID")

Dim sp_addpartcont__Relation
sp_addpartcont__Relation = "0"
if(Request("relation")  <> "") then sp_addpartcont__Relation = Request("relation") 

%>
<%

set sp_addpartcont = Server.CreateObject("ADODB.Command")
sp_addpartcont.ActiveConnection = MM_bluedot_STRING
sp_addpartcont.CommandText = "dbo.addpartytpartiescont"
sp_addpartcont.Parameters.Append sp_addpartcont.CreateParameter("@RETURN_VALUE", 3, 4)
sp_addpartcont.Parameters.Append sp_addpartcont.CreateParameter("@Partcontid", 3, 1,4,sp_addpartcont__Partcontid)
sp_addpartcont.Parameters.Append sp_addpartcont.CreateParameter("@Firmid", 3, 1,4,sp_addpartcont__Firmid)
sp_addpartcont.Parameters.Append sp_addpartcont.CreateParameter("@Userid", 3, 1,4,sp_addpartcont__Userid)
sp_addpartcont.Parameters.Append sp_addpartcont.CreateParameter("@Relation", 200, 1,50,sp_addpartcont__Relation)
sp_addpartcont.CommandType = 4
sp_addpartcont.CommandTimeout = 0
sp_addpartcont.Prepared = true
sp_addpartcont.Execute()

%>

Open in new window

Carl TawnSystems and Integration DeveloperCommented:
There look to be a couple of things wrong in there.

1) You need to read the ID from the recordset returned by the Execute() method.

So, this:
Set LASTINSERTEDID = sp_addpartytousers.Execute()

Open in new window

Should be:
Set rs = sp_addpartytousers.Execute()
LASTINSERTEDID = rs(0)

Open in new window


2) This part:
Dim sp_addpartcont__Userid
sp_addpartcont__Userid = "0"
if(Request("LASTINSERTEDID")  <> "") then sp_addpartcont__Userid = Request("LASTINSERTEDID")

Open in new window

Doesn't make sense.  LASTINSERTEDID is a variable in the page, not a Request parameter (unless you are moving between pages, but it doesn't look like you are). So you should be able to replace that section with:
sp_addpartcont__Userid = LASTINSERTEDID

Open in new window

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:
Perfect !  I apologize I am still learning some new things :)  
That worked great.
Carl TawnSystems and Integration DeveloperCommented:
No problem. Glad you're sorted.
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
Web Development

From novice to tech pro — start learning today.