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
  • 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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


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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mobile friendly link not working 4 268
Lock Form Controls 3 309
Wrapping 2 Divs in a Container 8 570
Dreamweaver CC no database behaviors or connections possible ? 10 4,301
For those who don't know, Adobe Dreamweaver is a popular commercial web editor that enables you to design, build and manage complex websites. The editor is a WYSIWYG (What You See Is What You Get) web editor, which means that you can create your web…
Adobe Dreamweaver CS5 is a WYSIWYG web page editor that has advanced HTML, CSS, and Javascript rendering functionality and is probably the most well-known HTML editor available. Much of Dreamweaver's appeal centers around the Design View interfac…
In an interesting question ( here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

685 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