ASP insert then get newly created autonumber

I'm using ASP Javascript & Access 2003 & Dreamweaver 8

I want to insert a new record then grab the autonumber that was generated for
that record and pass it as a session variable to the next page. From what I
have read in this and many other forums I can use "Select @@Identity as
Identity" to do this. However, I don't know where to place it in my code.
I've tried placing it in several locations but it hasn't worked. I figure it
needs to go before "MM_editCmd.ActiveConnection.Close();" to grab the right
record. If anyone can help me I would soooo appreciate it. OR if you have an
even better solution I would still sooooo appreciate it. I know I can use
"Select Max(column)" but I want to make sure the user isn't grabbing a record
that was created miliseconds after they created theirs. Here is the code:

<%
// *** Edit Operations: declare variables

// set the form action variable
var MM_editAction = Request.ServerVariables("SCRIPT_NAME");
if (Request.QueryString) {
MM_editAction += "?" + Server.HTMLEncode(Request.QueryString);
}

// boolean to abort record edit
var MM_abortEdit = false;
%>
<%
if (String(Request("MM_insert")) == "form1") {
if (!MM_abortEdit) {
// execute the insert

var MM_editCmd = Server.CreateObject ("ADODB.Command");
MM_editCmd.ActiveConnection = MM_Christmas_STRING;
MM_editCmd.CommandText = "INSERT INTO tbl_Head (H_FName, H_LName, H_MAdd1,
H_MAdd2, H_MCity, H_MState, H_MZip, H_Phone, H_Address1, H_Address2, H_City,
H_State, H_Zip, H_Directions, H_SS, H_DOB, H_Sex, H_Race, H_Comments,
H_Employer, H_ShirtSize, H_PantSize, H_ShoeSize, H_Special) VALUES (?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
MM_editCmd.Prepared = true;
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param1", 202, 1,
50, trim(Request.Form("H_FName")))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param2", 202, 1,
50, Request.Form("H_LName"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param3", 202, 1,
50, Request.Form("H_MAdd1"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param4", 202, 1,
50, Request.Form("H_MAdd2"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param5", 202, 1,
50, Request.Form("H_MCity"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param6", 202, 1,
50, Request.Form("H_MState"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param7", 202, 1,
50, Request.Form("H_MZip"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param8", 202, 1,
50, Request.Form("H_Phone"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param9", 202, 1,
50, Request.Form("H_Address1"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param10", 202, 1,
50, Request.Form("H_Address2"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param11", 202, 1,
50, Request.Form("H_City"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param12", 202, 1,
50, Request.Form("H_State"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param13", 202, 1,
50, Request.Form("H_Zip"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param14", 203, 1,
1073741823, Request.Form("H_Directions"))); // adLongVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param15", 202, 1,
50, Request.Form("H_SS"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param16", 135, 1,
-1, (String(Request.Form("H_DOB")) != "undefined" &&
String(Request.Form("H_DOB")) != "") ? Request.Form("H_DOB") : null)); //
adDBTimeStamp
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param17", 202, 1,
50, Request.Form("H_Sex"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param18", 5, 1,
-1, (String(Request.Form("H_Race")) != "undefined" &&
String(Request.Form("H_Race")) != "") ? Request.Form("H_Race") : null)); //
adDouble
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param19", 203, 1,
1073741823, Request.Form("H_Comments"))); // adLongVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param20", 202, 1,
50, Request.Form("H_Employer"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param21", 202, 1,
50, Request.Form("H_ShirtSize"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param22", 202, 1,
50, Request.Form("H_PantSize"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param23", 202, 1,
50, Request.Form("H_ShoeSize"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param24", 203, 1,
1073741823, Request.Form("H_Special"))); // adLongVarWChar
MM_editCmd.Execute();
MM_editCmd.ActiveConnection.Close();

// append the query string to the redirect URL
var MM_editRedirectUrl = "redirect_insert_head.asp";
if (MM_editRedirectUrl && Request.Form && Request.Form.Count > 0) {
MM_editRedirectUrl += ((MM_editRedirectUrl.indexOf('?') == -1) ? "?" :
"&")+ Request.Form;
}
Response.Redirect(MM_editRedirectUrl)
}
}
%>

many thanks,
Winona

If you can get it to work, try this. This is the wrong way to do this,
but take a unique value from the submitted content (say email) and query
the db for the newest record with that value. In your case, SS would
work (if it is required).

Or you can try:

http://www.tom-muck.com/extensions/help/insertretrieve/

Or, write your own INSERT statement and plug Select @@Identity as
Identity in the appropriate spot.



Winona wrote:

I'm using ASP Javascript & Access 2003 & Dreamweaver 8

I want to insert a new record then grab the autonumber that was generated for
that record and pass it as a session variable to the next page. From what I
have read in this and many other forums I can use "Select @@Identity as
Identity" to do this. However, I don't know where to place it in my code.
I've tried placing it in several locations but it hasn't worked. I figure it
needs to go before "MM_editCmd.ActiveConnection.Close();" to grab the right
record. If anyone can help me I would soooo appreciate it. OR if you have an
even better solution I would still sooooo appreciate it. I know I can use
"Select Max(column)" but I want to make sure the user isn't grabbing a record
that was created miliseconds after they created theirs. Here is the code:

%
// *** Edit Operations: declare variables

// set the form action variable
var MM_editAction = Request.ServerVariables("SCRIPT_NAME");
if (Request.QueryString) {
MM_editAction += "?" + Server.HTMLEncode(Request.QueryString);
}

// boolean to abort record edit
var MM_abortEdit = false;
%
%
if (String(Request("MM_insert")) == "form1") {
if (!MM_abortEdit) {
// execute the insert

var MM_editCmd = Server.CreateObject ("ADODB.Command");
MM_editCmd.ActiveConnection = MM_Christmas_STRING;
MM_editCmd.CommandText = "INSERT INTO tbl_Head (H_FName, H_LName, H_MAdd1,
H_MAdd2, H_MCity, H_MState, H_MZip, H_Phone, H_Address1, H_Address2, H_City,
H_State, H_Zip, H_Directions, H_SS, H_DOB, H_Sex, H_Race, H_Comments,
H_Employer, H_ShirtSize, H_PantSize, H_ShoeSize, H_Special) VALUES (?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
MM_editCmd.Prepared = true;
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param1", 202, 1,
50, trim(Request.Form("H_FName")))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param2", 202, 1,
50, Request.Form("H_LName"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param3", 202, 1,
50, Request.Form("H_MAdd1"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param4", 202, 1,
50, Request.Form("H_MAdd2"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param5", 202, 1,
50, Request.Form("H_MCity"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param6", 202, 1,
50, Request.Form("H_MState"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param7", 202, 1,
50, Request.Form("H_MZip"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param8", 202, 1,
50, Request.Form("H_Phone"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param9", 202, 1,
50, Request.Form("H_Address1"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param10", 202, 1,
50, Request.Form("H_Address2"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param11", 202, 1,
50, Request.Form("H_City"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param12", 202, 1,
50, Request.Form("H_State"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param13", 202, 1,
50, Request.Form("H_Zip"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param14", 203, 1,
1073741823, Request.Form("H_Directions"))); // adLongVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param15", 202, 1,
50, Request.Form("H_SS"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param16", 135, 1,
-1, (String(Request.Form("H_DOB")) != "undefined" &&
String(Request.Form("H_DOB")) != "") ? Request.Form("H_DOB") : null)); //
adDBTimeStamp
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param17", 202, 1,
50, Request.Form("H_Sex"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param18", 5, 1,
-1, (String(Request.Form("H_Race")) != "undefined" &&
String(Request.Form("H_Race")) != "") ? Request.Form("H_Race") : null)); //
adDouble
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param19", 203, 1,
1073741823, Request.Form("H_Comments"))); // adLongVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param20", 202, 1,
50, Request.Form("H_Employer"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param21", 202, 1,
50, Request.Form("H_ShirtSize"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param22", 202, 1,
50, Request.Form("H_PantSize"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param23", 202, 1,
50, Request.Form("H_ShoeSize"))); // adVarWChar
MM_editCmd.Parameters.Append(MM_editCmd.CreatePara meter("param24", 203, 1,
1073741823, Request.Form("H_Special"))); // adLongVarWChar
MM_editCmd.Execute();
MM_editCmd.ActiveConnection.Close();

// append the query string to the redirect URL
var MM_editRedirectUrl = "redirect_insert_head.asp";
if (MM_editRedirectUrl && Request.Form && Request.Form.Count > 0) {
MM_editRedirectUrl += ((MM_editRedirectUrl.indexOf('?') == -1) ? "?" :
"&")+ Request.Form;
}
Response.Redirect(MM_editRedirectUrl)
}
}
%

many thanks,
Winona

Regarding the first suggestion of using a field to query on - there isn't going
to be any one required field that would be unique. We won't require SS. I was
going to try to use multiple fields but every way I tried it did not work. I
tried to query using Where field1 = var1 and field2 = var2 and field3 = var3
but if any of those fields were blank then it failed. I don't know how to
adjust for a null field. I just don't know enough about ASP to work through
it. I can do it in ColdFusion but this had to be written in ASP.

As for Tom Muck's extension it's for the VBscript model and I'm using
Javascript which I have found 1/2 through the process was a big mistake. There
is absolutely nothing out there to help.

As for writing the insert statement with the Select @@Identity as Identity - I
don't know ASP well enough to do so.

thanks,
Winona

Winona:

When I am faced with this issue, I do a SELECT * ORDER by record number
DESC, and take the first record. That's the last autonumber used.

I am assuming you don't have so much traffic that this would cause a problem
with delay between adding the record in question and adding another one on
top.

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.dreamweavermx-templates.com - Template Triage!
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
http://www.macromedia.com/support/search/ - Macromedia (MM) Technotes
==================


"Winona" <webforumsuser (AT) macromedia (DOT) com> wrote

Regarding the first suggestion of using a field to query on - there isn't
going
to be any one required field that would be unique. We won't require SS.
I was
going to try to use multiple fields but every way I tried it did not work.
I
tried to query using Where field1 = var1 and field2 = var2 and field3 =
var3
but if any of those fields were blank then it failed. I don't know how to
adjust for a null field. I just don't know enough about ASP to work
through
it. I can do it in ColdFusion but this had to be written in ASP.

As for Tom Muck's extension it's for the VBscript model and I'm using
Javascript which I have found 1/2 through the process was a big mistake.
There
is absolutely nothing out there to help.

As for writing the insert statement with the Select @@Identity as
Identity - I
don't know ASP well enough to do so.

thanks,
Winona

Using JScript with ASP is a mistake.

Combine your queries.
INSERT INTO someTable(columns) VALUES(values);SELECT SCOPE_IDENTITY()

Note that SCOPE_IDENTITY() is a safer replacement for @@IDENTITY in SQL
Server 2000 or later.

You retrieve the value by modifying this line:
MM_editCmd.Execute();

It should instead be:
nextID = MM_editCmd.Execute().NextRecordset();
RecordID = nextID.Fields.Item(0).Value;
nextID.Close();
nextID = null;

RecordID is now your new record ID. If you have your database set up to
suppress metadata ("x rows affected" type messages) by using SET NOCOUNT ON
or some other setting, then eliminate the .NextRecordset() method call.