Retrieving ID Field after insert into Access DB

[Saeed42]

Please help before I loose my sanity,


I have two forms that should insert a record into two different tables, the first form is for customer details, second form is for that customer's circuits, so what I would like to happen is when someone submits the first form with customer details that would be inserted into table one and then forwarded to the second form so they can insert that customers circuit details into the 2nd table, this is where I'm running into problems when first form is submitted and forwarded to the 2nd page, this page doesn't know anything about the customer since the 1st page didn't submit any values to the 2nd page, what I want to do is to grab the autonumber of the newly created customer details (CustID) and pass it to the 2nd page so the second form will insert the circuit details into table 2 with the CustID in a hidden field.


----------------------------------------------------------------------------------------
Found this solution but I'm not sure which bit of the code I need and where to put it in my page which is created with UD. With my limited understanding I think I only need the last few line but I'm not sure where to put them.

'Create the Connection object
set conn = server.createobject("adodb.connection&quot
conn.open strconn

'Create the recordset object
set rs = server.createobject("adodb.recordset&quot
'This statement opens the table so we can add a record notice the addnew
'The 2, 2 is how the table is opened there are many ways it can be opened
rs.open "tblAdoAdd", conn, 2, 2
'Use the addnew method of the recordset object to add a record
rs.addnew

'Set the table column = to my input text box from my form
rs("FirstName&quot = request("FirstName&quot
rs("LastName&quot = request("LastName&quot
rs("FavoriteColor&quot = request("FavoriteColor&quot
rs.update

'I do a movelast here to get the ID that is automatically generated
'I also set the value to a local variable so I can write out to the database

rs.movelast
strID = rs("ID&quot
rs.close
Set rs = nothing
conn.close
Set conn = nothing


Table Structure
----------------------------------------------------------------------------------------
Table 1
CustID|CompanyName

Table 2
CctID|CustID|CircuitNo|FibernetRef|ServiceType|POP|CircuitTailed|CctNotes|Bearer|Ports

Info
------------------------------------------------------------
Database is access 2000 with access driver
ASP


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Don't be content with being average. Average is as close to the bottom as it is to the top
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[schase]

Your pretty close, try this - Your fields are not really matching what your describing so I just picked custID - which I'm assuming is an autonumber field.

Later on you just refer to the custID as <%=Session(&quot;svCustID&quot%>
[colorblue]
set conn = server.createobject(&quot;adodb.connection&quot
conn.open strconn
set rs = server.createobject(&quot;adodb.recordset&quot
rs.open &quot;tblAdoAdd&quot;, conn, 2, 2
rs.addnew


rs(&quot;FirstName&quot = request(&quot;FirstName&quot
rs(&quot;LastName&quot = request(&quot;LastName&quot
rs(&quot;FavoriteColor&quot = request(&quot;FavoriteColor&quot
rs.update
Session(&quot;svCustID&quot=rs(&quot;CustID&quot

rs.movelast
rs.close
Set rs = nothing
conn.close
Set conn = nothing

[/color]
&quot;am I a star?&quot;

Stuart

 

[schase]

lol ignore the [colorblue] and [/color] kinda did it wrong &quot;am I a star?&quot;

Stuart

 

[Saeed42]

I think this is the bit I need
Session(&quot;svCustID&quot=Addnewcust(&quot;CustID&quot

Addnewcust.movelast
Addnewcust.close
Set Addnewcust = nothing
conn.close
Set conn = nothing

but I'm not sure were it should go within the code, I put it under

' execute the insert
Set MM_editCmd = Server.CreateObject(&quot;ADODB.Command&quot
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute

But nothing worked, it goes through the next page but without the CustID value


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Don't be content with being average. Average is as close to the bottom as it is to the top
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[schase]

goes right after rs.update &quot;am I a star?&quot;

Stuart

 

[Saeed42]

The problem I have is I'm new to the ASP stuff and to be honest I'm not sure how this code relates to my page or where it should go in my page, remember I made the page using ultradev and though I can roughly see which bit of the code is doing what I'm still a complete novice, so if you can beak it down for me it would be nice, I would post the code for my page here but I know how much people dislike that sort of thing.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Don't be content with being average. Average is as close to the bottom as it is to the top
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[schase]

ok no problem,

Clarify your recordset properties and your table fields.

Your first post was not matching.

On table one you said you have CustID & Company name

Table two you have: CctID|CustID|CircuitNo|FibernetRef|ServiceType|POP|CircuitTailed|CctNotes|Bearer|Ports

Now your recordset has:
rs(&quot;FirstName&quot = request(&quot;FirstName&quot
rs(&quot;LastName&quot = request(&quot;LastName&quot
rs(&quot;FavoriteColor&quot = request(&quot;FavoriteColor&quot


Which is cool - but you see where they do not match at all.


Break it out like this - basically give me laymens terms to how yours works. I.e. on the first page - the form has the fields......... Which inserts into table &quot;Name&quot; with the table fields of ........... and I want to grab the field &quot;CustID&quot;? and carry that onto the second page.

The second page's form has the fields....... and they insert into table &quot;name&quot; with the table fields ......


etc.

&quot;am I a star?&quot;

Stuart

 

[mlawson]

Hi Saeed42,

Don't be worried about posting your code, it provides a way for the rest of us luddites to see if we're going awry with our code. In the big picture a couple dozen lines of code can offer more info than 20 separate posts. Eh, schase?



M &quot;There are 3 kinds of people; those that can count and those that can't&quot;

 

[schase]

Now isnt that a goofy lookin face &quot;am I a star?&quot;

Stuart

 

[Saeed42]

Ok mate, I'm going to try my best to break it down

I have a form that has only one field &quot;CompanyName&quot; that should insert the company name into table one in the above example ( which I called tblcustdetails in my db), the next thing I want to do is grab the new CustID that was generated and pass it to another page that has a form so I can fill more details that relate to that customer and insert a record in table 2 in the above example ( which is called tblcircuit in my db).

Hope that made it clear,

Thanks in advance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Don't be content with being average. Average is as close to the bottom as it is to the top
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[schase]

Ok, sorry I mis-read, I thought that was ahh never mind.

Anyhow try this - cut and paste this (after changing whats in blue) into your database above all other recordsets on the 2nd page. make sure your 1st page's form has action=&quot;thenextpage.asp&quot; Also verify that tblcustdetails has the fields custID (that is primary key & autonumber) and field CompanyName. If it does not have the custID - and just has ID, than change the Session(&quot;custID&quot=rs5(&quot;CustID&quot to Session(&quot;custID&quot=rs5(&quot;ID&quot.

Then, anytime you want to use that session, put this as your code. <%=Session(&quot;svCustID&quot%>

<%
set rs5 = Server.CreateObject(&quot;ADODB.Recordset&quot
rs5.ActiveConnection = MM_your_STRING
rs5.Source = &quot;SELECT * FROM tblcustdetails order by ID DESC&quot;
rs5.CursorType = 1
rs5.CursorLocation = 2
rs5.LockType = 3
rs5.Open()
rs5_numRows = 0

rs5.addnew
rs5(&quot;CompanyName&quot=Request.Form(&quot;CompanyName&quot
rs5.update
Session(&quot;svCustID&quot=rs5(&quot;CustID&quot
%>
<%
rs5.Close()
%> &quot;am I a star?&quot;

Stuart

 

[Saeed42]

After resisting so much I decided to post the code.

Now it writes to the db, but I still cannot (don't know) how to retrieve the CustID, I tried what you said and used <%=Session(&quot;svCustID&quot%> in the body of the same file that is submitting the data into the db, but to no avail, I also tried to forward everything to another page, soit works like this the form forwards everything to the page that I printed its code here and this page submits everything into the db and then forwards everything to a 2nd page, this solution was fruitless as the other one, so I was wondering if you could shed some light where I'm going wrong .

Thanks in advance


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
<%@LANGUAGE=&quot;VBSCRIPT&quot;%>
<!--#include file=&quot;../Connections/Newcircuits.asp&quot; -->
<%
set Addnewcust = Server.CreateObject(&quot;ADODB.Recordset&quot
Addnewcust.ActiveConnection = MM_Newcircuits_STRING
Addnewcust.Source = &quot;SELECT * FROM tblcustdetails&quot;
Addnewcust.CursorType = 0
Addnewcust.CursorLocation = 2
Addnewcust.LockType = 3
Addnewcust.Open()
Addnewcust_numRows = 0
Addnewcust.addnew
Addnewcust(&quot;CompanyName&quot=Request.Form(&quot;CompanyName&quot
Addnewcust.update
Session(&quot;svCustID&quot=Addnewcust(&quot;CustID&quot
%>
<html>
<head>
<title>Autonumber</title>
<meta http-equiv=&quot;Content-Type&quot; content=&quot;text/html; charset=iso-8859-1&quot;>
</head>
<body bgcolor=&quot;#FFFFFF&quot; text=&quot;#000000&quot;>
<%= Session(&quot;svCustID&quot %>
</body>
</html>
<%
Addnewcust.Close()
%>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Don't be content with being average. Average is as close to the bottom as it is to the top
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[schase]

where you have that code (above the <head> tag - and make sure this is on page two not page one.

replace it with this

<%@LANGUAGE=&quot;VBSCRIPT&quot;%>
<!--#include file=&quot;../Connections/Newcircuits.asp&quot; -->
<%
set rs5 = Server.CreateObject(&quot;ADODB.Recordset&quot
rs5.ActiveConnection = MM_Newcircuits_STRING
rs5.Source = &quot;SELECT * FROM tblCustDetails order by custID DESC&quot;
rs5.CursorType = 1
rs5.CursorLocation = 2
rs5.LockType = 3
rs5.Open()
rs5_numRows = 0

rs5.addnew
rs5(&quot;CompanyName&quot=Request.Form(&quot;CompanyName&quot
rs5.update
Session(&quot;svCustID&quot=rs5(&quot;custID&quot
%>
<%
rs5.Close()
%>


And put the <%=session(&quot;svCustID&quot%> Somewhere in a table cell. now I'm having you put the session in a table cell to test to see if it is indeed pulling the value and putting it into a session variable. If it does then we're having a different issue. If it doesnt - the value is not making it to page 2.

If the value is not making it to page two - Right below the session code you put in the table cell I want you to put

<%=request.form(&quot;CompanyName&quot%>

This will also help to see if the values are getting to page two.

Divide, conquer, narrow the possiblities. &quot;am I a star?&quot;

Stuart