asked on

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"%>

<%
' *** 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>

alexhogan

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.

polaatx

ASKER

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.

ASKER CERTIFIED SOLUTION

alexhogan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

thatelvis

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

polaatx

ASKER

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.

thatelvis

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

polaatx

ASKER

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?

thatelvis

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