Hi, I am a newbie to ASP.  My only education is from what I have read
online.

My problem is the following.  First I have one asp page that the user uses
to insert the the Make of a car.  This table consists of the Primary Key
(PK#Make), which is an autonumber, and the actual name of the car make
(Make).  Once the make is inserted, the page forwards the user to a second
asp page called Model, where the person inputs all model information.

The Make table is related to a second table called Model.  Field PK#Make
from table Make is referenced to field FK#Make in table Model.

The problem I have is saving the the autonumber from the new record
inserted in table make and inputting it into the new record in table model.
I am not sure how to reference from one asp page to the other.  Please
excuse the comments in the script.  I have tried a lot of things but to
avail.

Here is my script.

From the first page, Make.asp:

<!-- #include virtual = "adovbs.inc" -->
<%
'Dimension variables
Dim adoCon              'Holds the Database Connection Object
Dim rsAddMake       'Holds the recordset for the new record to be added
Dim strSQL                 'Holds the SQL query to query the database
Dim PK_Make

'Create an ADO connection object

Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using DSN connection

adoCon.Open "DSN=unify"

Set rsAddMake = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT Make.[PK#Make], Make.Make FROM Make;"

rsAddMake.CursorType = 2

rsAddMake.LockType = 3

rsAddMake.Open strSQL, adoCon

rsAddMake.AddNew

'Add a new record to the recordset

rsAddMake.Fields("Make") = Request.Form("Make")

rsAddMake.Update

' Retrive the Autonumber

' strSQL = "SELECT @@Identity AS PK_Make"

' rsAddMake.Open strSQL, adoCon

PK_Make = rsAddMake("PK#Make")

' PK_Make = rsAddMake.Fields.Item("PK#Make").Value

' Reset server objects

rsAddMake.Close

Set rsAddMake = Nothing

Set adoCon = Nothing

Response.Redirect "add_new_model.htm"

%>

The second form page, Model.asp:


<!-- #include virtual = "adovbs.inc" -->
<%
' Dimension variables
Dim adoCon
Dim rsAddModel
Dim strSQL
Dim objRS
Dim PK_Make
Dim rsAddMake

' Create an ADO connection object
Set adoCon = Server.CreateObject ("ADODB.Connection")

' Set an active connection to the connection object using DSN connection
adoCon.Open "DSN=unify"

Set rsAddModel = Server.CreateObject("ADODB.Recordset")

' Create the query to extract records

PK_Make = rsAddMake("PK#Make")

strSQL = "SELECT Model.[PK#Model], Model.[FK#Make], Model.[Model Name],
Model.[Make Name], Model.Mnenomic, Model.Plant, Model.Location FROM Model;"

rsAddModel.CursorType = 2

rsAddModel.LockType = 3

rsAddModel.Open strSQL , adoCon

rsAddModel.AddNew

rsAddModel.Fields("FK#Make") = PK_Make  <---  this is where i get the
problem.

rsAddModel.Fields("Model Name") = Request.Form("ModelName")

rsAddModel.Fields("Make Name") = Request.Form("MakeName")

rsAddModel.Fields("Mnenomic") = Request.Form("Mnenomic")

rsAddModel.Fields("Plant") = Request.Form("Plant")

rsAddModel.Fields("Location") = Request.Form("Location")

rsAddModel.Update

rsAddModel.Close

Set rsAddModel = Nothing

Set adoCon = Nothing

%>



Michael

Once you have created the first record, use techniques here:
www.adopenstatic.com/experiments/fastestautonumber.asp
to extract the new autonumber value.

Then you pass this to the next page in the querystring, cookie or session
variable, eg:

model_addNew.asp?MakeID=<%=intMakeID%>

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <MLumunsad@h...>
Subject: [access_asp] Autonumber and Passing variables from asp pages


: Hi, I am a newbie to ASP.  My only education is from what I have read
: online.
:
: My problem is the following.  First I have one asp page that the user uses
: to insert the the Make of a car.  This table consists of the Primary Key
: (PK#Make), which is an autonumber, and the actual name of the car make
: (Make).  Once the make is inserted, the page forwards the user to a second
: asp page called Model, where the person inputs all model information.
:
: The Make table is related to a second table called Model.  Field PK#Make
: from table Make is referenced to field FK#Make in table Model.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Ok, I did that but the query string will not pass the record to the  
subsequent htm form.  I'm not sure if it is the database itself or my 
scripting. 

<!-- #include virtual = "adovbs.inc" --> 
<%
'Dimension variables
Dim adoCon              'Holds the Database Connection Object
Dim rsAddMake   	'Holds the recordset for the new record to be added
Dim strSQL              'Holds the SQL query to query the database 
Dim PK_Make

'Create an ADO connection object

Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using DSN connection

adoCon.Open "DSN=unify"

Set rsAddMake = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT Make.[PK#Make], Make.Make FROM Make;"

rsAddMake.CursorType = 2

rsAddMake.LockType = 3

rsAddMake.Open strSQL, adoCon

rsAddMake.AddNew

'Add a new record to the recordset

rsAddMake.Fields("Make") = Request.Form("Make")

rsAddMake.Update

' Retrive the Autonumber

PK_Make = rsAddMake.Fields.Item("PK#Make").Value

' Reset server objects

rsAddMake.Close

Set rsAddMake = Nothing

Set adoCon = Nothing 

Response.Redirect "add_new_model.htm?MakeID=" + PK_Make

%>


> Once you have created the first record, use techniques here:
www.adopenstatic.com/experiments/fastestautonumber.asp
to extract the new autonumber value.

Then you pass this to the next page in the querystring, cookie or session
variable, eg:

model_addNew.asp?MakeID=<%=intMakeID%>

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <MLumunsad@h...>
Subject: [access_asp] Autonumber and Passing variables from asp pages


: Hi, I am a newbie to ASP.  My only education is from what I have read
: online.
:
: My problem is the following.  First I have one asp page that the user 
uses
: to insert the the Make of a car.  This table consists of the Primary Key
: (PK#Make), which is an autonumber, and the actual name of the car make
: (Make).  Once the make is inserted, the page forwards the user to a 
second
: asp page called Model, where the person inputs all model information.
:
: The Make table is related to a second table called Model.  Field PK#Make
: from table Make is referenced to field FK#Make in table Model.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Michael,

What cursortype is "2"? (do you know? I don't!)
What locktype is "3"?

This is why you should steer away from "magic numbers" - you're already
including a huge file containing ADO constant declarations (adovbs.inc), so
let's use it:

<%
...
objRS.Open strSQL, objConn, adOpenStatic, adLockOptimistic, adCmdText
objRS.AddNew
...
...
objRS.Update
intNewID = objRS.Fields.Item("PK#Make").Value
...
objRS.Close
Set objRS = Nothing

objConn.Close
Set objConn = Nothing

Response.Write(intNewID)
Response.End
%>

Couple of tips:
a) Try to name your fields a little better - using # in a field name will
only come back to bite you in the backside later on. If the table is
VehicleMake, then make the PK field VehicleMakeID. You should avoid naming
your Fields the same as your tables. If the field is named VehicleMake, then
the table could be called VehicleMakes, eg:

TABLE VehicleMakes
FIELD: VehicleMakeID (autonumber)
FIELD: VehicleMake (text)

or the field could be called VehicleMakeName

b) Try to avoid using recordsets to insert data - use SQL statements.
Recordsets are for retrieving data. This is really important in the Access
world, since Access scales really badly...

Cheers
Ken


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Michael Lumunsad" <mlumunsad@h...>
Subject: [access_asp] Re: Autonumber and Passing variables from asp pages


: Ok, I did that but the query string will not pass the record to the
: subsequent htm form.  I'm not sure if it is the database itself or my
: scripting.
:
: <!-- #include virtual = "adovbs.inc" -->
: <%
: 'Dimension variables
: Dim adoCon              'Holds the Database Connection Object
: Dim rsAddMake   'Holds the recordset for the new record to be added
: Dim strSQL              'Holds the SQL query to query the database
: Dim PK_Make
:
: 'Create an ADO connection object
:
: Set adoCon = Server.CreateObject("ADODB.Connection")
:
: 'Set an active connection to the Connection object using DSN connection
:
: adoCon.Open "DSN=unify"
:
: Set rsAddMake = Server.CreateObject("ADODB.Recordset")
:
: strSQL = "SELECT Make.[PK#Make], Make.Make FROM Make;"
:
: rsAddMake.CursorType = 2
:
: rsAddMake.LockType = 3
:
: rsAddMake.Open strSQL, adoCon
:
: rsAddMake.AddNew
:
: 'Add a new record to the recordset
:
: rsAddMake.Fields("Make") = Request.Form("Make")
:
: rsAddMake.Update
:
: ' Retrive the Autonumber
:
: PK_Make = rsAddMake.Fields.Item("PK#Make").Value
:
: ' Reset server objects
:
: rsAddMake.Close
:
: Set rsAddMake = Nothing
:
: Set adoCon = Nothing
:
: Response.Redirect "add_new_model.htm?MakeID=" + PK_Make
:
: %>

Thanks.  It works!


> Michael,

What cursortype is "2"? (do you know? I don't!)
What locktype is "3"?

This is why you should steer away from "magic numbers" - you're already
including a huge file containing ADO constant declarations (adovbs.inc), so
let's use it:

<%
...
objRS.Open strSQL, objConn, adOpenStatic, adLockOptimistic, adCmdText
objRS.AddNew
...
...
objRS.Update
intNewID = objRS.Fields.Item("PK#Make").Value
...
objRS.Close
Set objRS = Nothing

objConn.Close
Set objConn = Nothing

Response.Write(intNewID)
Response.End
%>

Couple of tips:
a) Try to name your fields a little better - using # in a field name will
only come back to bite you in the backside later on. If the table is
VehicleMake, then make the PK field VehicleMakeID. You should avoid naming
your Fields the same as your tables. If the field is named VehicleMake, 
then
the table could be called VehicleMakes, eg:

TABLE VehicleMakes
FIELD: VehicleMakeID (autonumber)
FIELD: VehicleMake (text)

or the field could be called VehicleMakeName

b) Try to avoid using recordsets to insert data - use SQL statements.
Recordsets are for retrieving data. This is really important in the Access
world, since Access scales really badly...

Cheers
Ken


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Michael Lumunsad" <mlumunsad@h...>
Subject: [access_asp] Re: Autonumber and Passing variables from asp pages


: Ok, I did that but the query string will not pass the record to the
: subsequent htm form.  I'm not sure if it is the database itself or my
: scripting.
:
: <!-- #include virtual = "adovbs.inc" -->
: <%
: 'Dimension variables
: Dim adoCon              'Holds the Database Connection Object
: Dim rsAddMake   'Holds the recordset for the new record to be added
: Dim strSQL              'Holds the SQL query to query the database
: Dim PK_Make
:
: 'Create an ADO connection object
:
: Set adoCon = Server.CreateObject("ADODB.Connection")
:
: 'Set an active connection to the Connection object using DSN connection
:
: adoCon.Open "DSN=unify"
:
: Set rsAddMake = Server.CreateObject("ADODB.Recordset")
:
: strSQL = "SELECT Make.[PK#Make], Make.Make FROM Make;"
:
: rsAddMake.CursorType = 2
:
: rsAddMake.LockType = 3
:
: rsAddMake.Open strSQL, adoCon
:
: rsAddMake.AddNew
:
: 'Add a new record to the recordset
:
: rsAddMake.Fields("Make") = Request.Form("Make")
:
: rsAddMake.Update
:
: ' Retrive the Autonumber
:
: PK_Make = rsAddMake.Fields.Item("PK#Make").Value
:
: ' Reset server objects
:
: rsAddMake.Close
:
: Set rsAddMake = Nothing
:
: Set adoCon = Nothing
:
: Response.Redirect "add_new_model.htm?MakeID=" + PK_Make
:
: %>