Retrieve AutoID from insert

[Newsgroup_User]

Yikes this is driving me crazy

ASP, VBScript, IIS 6, Access

I have a page that inserts into two tables. One table is ClientsW and the
other is Eligibility, they are related by the Casenum field that is the
primary key in both tables and they are linked.

I have one recordset that inserts into the ClientsW table called rsClients.
I have another recordset that inserts into the Eligibility table called
rsEligibility. After the insert I requery the ClientsW table and set the
Session variable to the autonumber that is in the CASENUM field like so:

rsEligibility.Update
rsClients.Update
rsClients.Requery
Session("UserID")=rsClients("CASENUM")
response.redirect "done1.asp"

Then I redirect to another page. On that page I set up two recordsets to
pull from the two tables that information was just inserted into. The
Session ID seems to work for the rsClients recordset and pulls from the
ClientsW table, but the second recordset, rsEligibility that pulls out of
the Eligibility table doesn't pull anything! It is in there, but I can't
get it out. Again, this works in IIS 5, but not IIS 6. What do I have to
do to get the information to pull from the Eligibility table according to
the Casenum field which is the same in the ClientsW table? Is there any
help out there?


<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/connNewdatabase.asp" -->

<%
Dim rsClients__svuserid
rsClients__svuserid = "0"
if (Session("UserID") <> "") then rsClients__svuserid = Session("UserID")
%>
<%
set rsClients = Server.CreateObject("ADODB.Recordset")
rsClients.ActiveConnection = MM_connNewdatabase_STRING
rsClients.Source = "SELECT * FROM CLIENTSW WHERE CASENUM=" +
Replace(rsClients__svuserid, "'", "''") + " ORDER BY CASENUM"
rsClients.CursorType = 0
rsClients.CursorLocation = 2
rsClients.LockType = 3
rsClients.Open()
rsClients_numRows = 0
%>
<%
Dim rsEligibility__svuserid
rsEligibility_svuserid="0"
if(Session("UserID")<>"") then rsEligibility__svuserid = Session("UserID")
%>
<%
set rsEligibility = Server.CreateObject("ADODB.Recordset")
rsEligibility.ActiveConnection = MM_connNewdatabase_STRING
rsEligibility.Source = "SELECT * FROM ELIGIBILITY WHERE CASENUM=" +
Replace(rsEligibility__svuserid, "'", "''") + " ORDER BY Casenum"
rsEligibility.CursorType = 0
rsEligibility.CursorLocation = 2
rsEligibility.LockType = 3
rsEligibility.Open()
rsEligibility_numRows = 0
%>

[Newsgroup_User]

OK, I believe I have fixed it, but I am wondering if this is always going to
work as I want it, although it appears to be working now.

For example, if two people submit at the same time, would my rsEligibility
and rsClients query go into the database and get the correct record if I
coded as such?: (Session("UserID") is passed from the previous page, it is
supposed to equal the autonumber which is the number I need)

<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/connNewdatabase.asp" -->

<%
Dim svuserid
svuserid = "0"
if (Session("UserID") <> "") then svuserid = Session("UserID")
%>
<%
set rsClients = Server.CreateObject("ADODB.Recordset")
rsClients.ActiveConnection = MM_connNewdatabase_STRING
rsClients.Source = "SELECT * FROM CLIENTSW WHERE CASENUM=" +
Replace(svuserid, "'", "''") + " ORDER BY CASENUM"
rsClients.CursorType = 0
rsClients.CursorLocation = 2
rsClients.LockType = 3
rsClients.Open()
rsClients_numRows = 0
%>

<%
set rsEligibility = Server.CreateObject("ADODB.Recordset")
rsEligibility.ActiveConnection = MM_connNewdatabase_STRING
rsEligibility.Source = "SELECT * FROM ELIGIBILITY WHERE CASENUM=" +
Replace(svuserid, "'", "''") + " ORDER BY CASENUM"
rsEligibility.CursorType = 0
rsEligibility.CursorLocation = 2
rsEligibility.LockType = 3
rsEligibility.Open()
rsEligibility_numRows = 0
%>

[CarlGrint]

You can grab the last record ID, using Identity, and a quick list recordset after your Insert code

<%
If (CStr(Request("MM_insert")) <> "") Then <!-- if normal insert -->
If (CStr(UploadFormRequest("MM_insert")) <> "") Then <!-- if using asp uploader on page -->
set recordsetname = Server.CreateObject("ADODB.Recordset")
recordsetname.ActiveConnection = MM_editCmd.ActiveConnection
recordsetname.Source = "SELECT @@IDENTITY as MaxID FROM table" <!--change table to the name of the table -->
recordsetname.CursorType = 0
recordsetname.CursorLocation = 2
recordsetname.LockType = 3
recordsetname.Open()
Session("LatestID")=recordsetname("MaxID")
Response.Redirect(MM_editRedirectUrl)
end if
%>

This code has the first two lines of optional IF, which are only needed if you are using the inbuilt Insert behaviour, if not you can irgnore them

If you are using the inbuilt Insert behaviour, you also need to alter the code a little

Comment out the following 2 lines of the INSERT behaviour

If (Not MM_abortEdit) Then
' 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.ActiveConnection.Close <!--comment this line out -->

If (MM_editRedirectUrl <> "") Then
' Response.Redirect(MM_editRedirectUrl) <!--comment this line out -->
End If
End If

End If
%>

ID is the name of the column you use for the records ID, change as required, also change "recordsetname" to the name of the whatever you want to call this recordset, obviously making sure it does not duplicate any recordset used on the page.

[Newsgroup_User]

Thank you sir, I will try it.

Gale