Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2698
  • Last Modified:

Trying to retrieve autonumber after insert

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
polaatx
Asked:
polaatx
  • 3
  • 3
  • 2
1 Solution
 
alexhoganCommented:
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
 
polaatxAuthor Commented:
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
 
alexhoganCommented:
[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
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

 
thatelvisCommented:
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
 
polaatxAuthor Commented:
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
 
thatelvisCommented:
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
 
polaatxAuthor Commented:
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
 
thatelvisCommented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now