Solved

Trying to retrieve autonumber after insert

Posted on 2004-08-14
8
2,686 Views
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 (http://www.dmxzone.com/showDetail.asp?TypeId=3&NewsId=195&offset=10)  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:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#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)))
  Next

  ' 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
    Else
      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
    Else
      If (AltVal <> "") Then
        FormVal = AltVal
      ElseIf (Delim = "'") Then  ' escape quotes
        FormVal = "'" & Replace(FormVal,"'","''") & "'"
      Else
        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
  Next
  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
    MM_editCmd.ActiveConnection.Close

    If (MM_editRedirectUrl <> "") Then
      Response.Redirect(MM_editRedirectUrl)
    End If
  End If

End If
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<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">
</form>
</body>
</html>
0
Comment
Question by:polaatx
  • 3
  • 3
  • 2
8 Comments
 
LVL 8

Expert Comment

by:alexhogan
Comment Utility
To get the id of the last insert use this query;

SELECT MAX(myID) as LastID
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.
0
 

Author Comment

by:polaatx
Comment Utility
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.
0
 
LVL 8

Accepted Solution

by:
alexhogan earned 500 total points
Comment Utility
[snip]
Where do you see the @@IDENTITY ?  I don't see it anywhere
[/snip]

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;

SELECT MAX(*)
FROM myTable

Or if you only want to retrieve the recordset id;

SELECT MAX(myID) as LastID
FROM myTable

Now you just take the result from the query and assign it to the session object;
session('myvar') = varResults
0
 
LVL 1

Expert Comment

by:thatelvis
Comment Utility
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.

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

regards

Kenny
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:polaatx
Comment Utility
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.
0
 
LVL 1

Expert Comment

by:thatelvis
Comment Utility
hi yea,
I was online so thought I would mention, my fav extensions since I am here lol.

I find that http://www.interaktonline.com 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
http://www.flevooware.nl/dreamweaver/default.asp

I personally find layer geni is better then the http://www.projectseven.com 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 http://www.dwteam.com/ ,

regards

Kenny


0
 

Author Comment

by:polaatx
Comment Utility
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?
0
 
LVL 1

Expert Comment

by:thatelvis
Comment Utility
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.

regards

Kenny
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now