Trying to retrieve autonumber after insert

Posted on 2004-08-14
Last Modified: 2007-12-19
I've literally spent the entire afternoon trying to create an insert page that gives me the unique id of the newly created record in Access 2000 (so I can display the record that was just inserted in the response page).

I found an extention that is supposed to do this (  but I'm getting an error:

Microsoft JET Database Engine error '80040e14'
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
/test_insert.asp, line 90

Below is the text of my code. I don't know what to do. Can someone please hold my hand and tell me how to do this? I'm an ASP novice. I tried reading tutorials doing this without DW style code and I couldn't figure those out either.

here's my code as it is right now:

<!--#include file="Connections/connSane1.asp" -->
' *** Edit Operations: declare variables

MM_editAction = CStr(Request("URL"))
If (Request.QueryString <> "") Then
  MM_editAction = MM_editAction & "?" & Request.QueryString
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = ""
' *** Insert Record: set variables (With IDENTITY Funtionality. Author George Petrov)

If (CStr(Request("MM_insert")) <> "") Then

  MM_editConnection = MM_connSane1_STRING
  MM_editTable = "tblTerm"
  MM_editRedirectUrl = "test_insert_success.asp"
  MM_fieldsStr  = "term|value"
  MM_columnsStr = "term|',none,''"

  ' create the MM_fields and MM_columns arrays
  MM_fields = Split(MM_fieldsStr, "|")
  MM_columns = Split(MM_columnsStr, "|")
  ' set the form values
  For i = LBound(MM_fields) To UBound(MM_fields) Step 2
    MM_fields(i+1) = CStr(Request.Form(MM_fields(i)))

  ' append the query string to the redirect URL
  If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
    If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
      MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
      MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
    End If
  End If

End If
' *** Insert Record: construct a sql insert statement and execute it  (With IDENTITY Funtionality. Author George Petrov)

If (CStr(Request("MM_insert")) <> "") Then

  ' create the sql insert statement
  MM_tableValues = ""
  MM_dbValues = ""
  For i = LBound(MM_fields) To UBound(MM_fields) Step 2
    FormVal = MM_fields(i+1)
    MM_typeArray = Split(MM_columns(i+1),",")
    Delim = MM_typeArray(0)
    If (Delim = "none") Then Delim = ""
    AltVal = MM_typeArray(1)
    If (AltVal = "none") Then AltVal = ""
    EmptyVal = MM_typeArray(2)
    If (EmptyVal = "none") Then EmptyVal = ""
    If (FormVal = "") Then
      FormVal = EmptyVal
      If (AltVal <> "") Then
        FormVal = AltVal
      ElseIf (Delim = "'") Then  ' escape quotes
        FormVal = "'" & Replace(FormVal,"'","''") & "'"
        FormVal = Delim + FormVal + Delim
      End If
    End If
    If (i <> LBound(MM_fields)) Then
      MM_tableValues = MM_tableValues & ","
      MM_dbValues = MM_dbValues & ","
    End if
    MM_tableValues = MM_tableValues & MM_columns(i)
    MM_dbValues = MM_dbValues & FormVal
  MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ")"

  If (Not MM_abortEdit) Then
    ' execute the insert
    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_editConnection
    MM_editCmd.CommandText = "SET NOCOUNT ON;" & MM_editQuery & ";SELECT @" & "@IDENTITY AS Ident"
    Set rsLastIdent = MM_editCmd.Execute
    if NOT rsLastIdent.EOF then
        LastIdent = rsLastIdent.Fields.Item("Ident").Value
    end if

    If (MM_editRedirectUrl <> "") Then
    End If
  End If

End If
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "">
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

<form name="form1" method="POST" action="<%=MM_editAction%>">
<input name="term" type="text" id="term">
<input type="submit" name="Submit" value="Submit">
<input type="hidden" name="MM_insert" value="true">
Question by:polaatx
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2

Expert Comment

ID: 11803202
To get the id of the last insert use this query;

FROM myTable

Where myID is your unique identifier field.  LastID will be your return value.

This will get the last, or maximum value for the id field.  Which will be the last insert you made.

What I see in your code above is @@IDENTITY, which is used in MSSQL, but I'm not sure you can use it in Access.

Author Comment

ID: 11803759
Hi Alexhogan,

Where do you see the @@IDENTITY ?  I don't see it anywhere.

Can you please give me a complete insert statement + your query? I am trying to get the last ID and put it in a session variable.

Accepted Solution

alexhogan earned 500 total points
ID: 11804448
Where do you see the @@IDENTITY ?  I don't see it anywhere

MM_editCmd.CommandText = "SET NOCOUNT ON;" & MM_editQuery & ";SELECT @" & "@IDENTITY AS Ident"

The insert query would look like this;

INSERT INTO myTable (fieldone, fieldtwo, fieldthree)
VALUES ('valueone', 'valuetwo', 'valuethree')

To get the last inserted record;

FROM myTable

Or if you only want to retrieve the recordset id;

FROM myTable

Now you just take the result from the query and assign it to the session object;
session('myvar') = varResults
On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.


Expert Comment

ID: 12224491
Hi yea,
this extensions from The Tom Muck works really great and I have used it for years. not knocking georges extensions though but ok TOms one works it is free and I have never had a problem with support from him.



Author Comment

ID: 12230228
Well, I don't mind if you knock george's extentions. I wasted a lot of time trying to make it work. DMX zone is is mess, I think, with a lot of outdated things and I certainly would never buy anything there. Try getting a question answered in their forums and you'll have to wait for days or weeks---if it is ever answered.

Thanks for the Tom Muck suggestion. I will try it.

Expert Comment

ID: 12230471
hi yea,
I was online so thought I would mention, my fav extensions since I am here lol.

I find that have and will have the best server behaviours over the near future, if anything I can see macromedia taking them over as they are brilliant, (still a few bugs but really quckly they are getting sorted), i believe there extension makes Dreamweaver MX 7  into Dreamweaver MX version 10 over night.

I find to create a million and one different type of navigation one can use Marja's layer geni

I personally find layer geni is better then the magic menu's as I found magic menu's had a bigger learning curb and one was limited as to what they could do compared to Marja's  layer geni.

also since I am going to ahve a moan I will say webassists ecart is great but they still dont have a pdf tutorial, this has been the case with there other extensions before but fingers crossed they may have one soon.

dont forget tom muck also has a few extensions here too ,




Author Comment

ID: 12235297
Hi thatelvis,

I've looked at webassists ecart but couldn't figure out how much is included. Is it a full-fledged e-commerce program? I mean is it a complete store or just the shopping cart? Why do you like it?

Expert Comment

ID: 12235620
Webassist ecart is just a dreamweaver ecart extension, and it also helps connecting to Pay Pay's IPN, i.e. when a customer purchases you may want to have the details in your own data base, (I dont mean the customers credit card details), alson with that it is possibly the logic they use. on top of that one can use it on asp, jsp, and php
i know there is othr carts ouyt there, but as far as I am aware this is the m,ore advanced one, it is just the fact that they are dicking around with a printable tutorial pi**es me off. they have a kinda robo domo as a tutorial when you purchase the extension but for me there is nothing like a good pdf tutorial i can print and read and reference.

did you look at the layer geni at all.



Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I still run into .cgi files every now and then. In some instances, I actually prefer the simplicity of a .cgi script to other options. Since I use DreamWeaver extensively, what I needed was a way to open .cgi scripts in Dreamweaver. And I wanted to …
This article is very specific and is only intended to help if you are installing Dreamweaver 8 in a Windows 7 environment with Office 2007 installed.   I'm not sure why Microsoft tends to release OS' that should not be released but they do.  Windows…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question