Redisplaying a new record after inserting, @@IDENTITY? 1

CyrusAyers · May 23, 2004

Hi, Im using Access with an ASP interface. The primary key for my records is an autonumber field (im calling it ID). I'm having problems re-displaying the contents of a form when its purpose is to insert a new record -- it dosent know what ID to use in the SELECT statement. Heres the code im using currently. Any ideas would be very appriciated!

post_date = now()
Title = Trim(fixQuotes(request("Title")))
Category = Trim(fixQuotes(request("CatSelect")))
Content = Trim(fixQuotes(request("Content")))

sqlString = "insert into foodguru (post_date, title, category, content) VALUES ('" & post_date & "', '" & Title & "', '" & Category & "', '" & Content & "')"

conn.Execute (sqlString)

PS I know that SQL uses @@IDENTITY for this purpose...
Thanks,
Cyrus

onpnt · May 23, 2004

I would suggest looking at other methods over grabing the last inserted record. Perhaps Server.Transfer or such to display the recently inserted values. In doing things of that nature you optimize your code to take a extra database query out of the mix.

But...

I also suggest if you do go the route you are going to use MAX() instead of @@IDENTITY.

e.g.
SELECT MAX(Primary Key) FROM tbl

we've also discussed it to great extent in other threads if the @@IDENTITY is the only way to go for you. remember, M$ 2K only supports @@IDENTITY

threads to look at
get id from new inserted record (MS-Access) thread333-796061
Retrieve the last record from the dB thread333-787980
Unique ID thread333-735831
Insert record and then pull out that records ID thread333-712309
Retrieve id from record just inserted thread333-677869
Error using @@Identity with Access thread333-646607
Retrieve @@Identity within a transaction? thread333-592873
How can I retrieve AutoNumber after INSERT? thread333-553233
Problem with returning @@Identity thread333-514722
Optimize your code thread333-132705
get new record id thread333-473267

___________________________________________________________________
[sub]

http://www.onpnt.com

The answer to your ??'s may be closer then you think. faq333-3811
Join the Northern Illinois/Southern Wisconsin members in Forum1064[/sub]

CyrusAyers · May 23, 2004

OK, ill try that. As a side note to the Server.Transfer...
is there then a way to set Form variables i.e. request("Name"), from within the <%%> delimiters? Dropping a bunch of Session objects would seem equally inefficient.

onpnt · May 23, 2004

I'm a bit confused on "Set Form variables"

The form collection is basically passed along to the next page (script) when you use Server.Transfer

So in all you can do this

Code:

page name : First.asp
<html>
<body>
<form name="frm" action="next.asp" method="post">
<input type="text" name="txt" value="test">
<input type="submit" value="First">
</form>
</body>
</html>

Then in the next page you do your insert into the database minding empty form fields and testing etc. (more then likely done in the first page though). After you've tested the form fields for accurate INSERT values, perform the INSERT and then after that successful process is done do

Code:

page name : next.asp
<%
'test c form collection
'INSERT INTO database table as needed

'if all is fine and no errors have occured
Server.Transfer "Final.asp"
%>

and then you can do this in the third page

Code:

page name : Final.asp
<%
Response.Write Request.Form("txt")
'that will write out "Test" from the first page
%>

I may have misunderstood what you asked though.

___________________________________________________________________
[sub]

http://www.onpnt.com

The answer to your ??'s may be closer then you think. faq333-3811
Join the Northern Illinois/Southern Wisconsin members in Forum1064[/sub]

CyrusAyers · May 23, 2004

Well this may be silly, but I feel like im not using all the tools at my disposal...

You are right on, but what im asking would be:

Server.Transfer "Final.asp?var=1" <--ERROR we know that

So if on the "next.asp" page in your example i wanted to create an 'on the fly' Form variable in code and send it to Final.asp what if anything could I use...?

(I got the original problem solved BTW -thanx!)

CyrusAyers · May 23, 2004

PS what was happening was I got everything to insert and display again, but if the end-user somehow clicked REFRESH it would RE-insert a whole new record causing a confusing replica.

Thx Again!

onpnt · May 23, 2004

The refresh situation is something you just need to program and catch. using meta tags
e.g.
<META HTTP-EQUIV="Pragma" CONTENT="no-cache">
<META HTTP-EQUIV="Expires" CONTENT="-1">
and on the server side of things the setting
<% Response.CacheControl = "no-cache" %>>
<% Response.AddHeader "Pragma", "no-cache" %>
<% Response.Expires = -1 %>

The no cache question is very related to the previous setting a value to send along with the transfer.

In that case you're best bet (I think) is to use a session variable. If limited usage is made, session variables can be a very useful method of processing and catching things the user can do to basically screw your work up.

In the next.asp page up there simply after the INSERT is made you can set a session variable to some value letting you know that the insert was made. Then in the first portions of the processing script you condition for that session var being set already. If it was then you error the script out and send the message to the user. In some cases seperating your scripts into multiple pages as the example above is makes this process much easier and much more stable of a catch sitaution for you as the programmer.

___________________________________________________________________
[sub]

http://www.onpnt.com

The answer to your ??'s may be closer then you think. faq333-3811
Join the Northern Illinois/Southern Wisconsin members in Forum1064[/sub]

CyrusAyers · May 23, 2004

Yes, ill try those out!
I ended up using a session variable for the Refresh prob, and used:

Code:

sql = "insert into foodguru (post_date, title, category, content) VALUES ('" & post_date & "', '" & Title & "', '" & Category & "', '" & Content & "');"	

conn.Execute(sql) 
Set RS = conn.Execute("SELECT @@IDENTITY")
session("Recipe") = RS(0)  '<--this solved it

--Cyrus
TGF-Tek Tips!

shaddow · May 24, 2004

You could also use another way to find out your last ID inserted.
I remember that @@IDENTITY works only on MSSQL databases and not for Access.

Code:

sql="select top 1 * from myTable"
rs.Open sql,conObj,3,3

if Session("canInsert")==true then
 rs.AddNew
  rs("name")=Request("name")
  '...
  rs.Update
  lastID=rs("mytableid")
end if
Session("canInsert")=false

________
George, M
Searches(faq333-4906),Carts(faq333-4911)

onpnt · May 24, 2004

CyrusAyers, Glad you got a resolution for the script.

Shaddow, @@IDENTITY doesn't work with any Acess version under 2K. I still have been turned away from using it from the vast number of very talented SQL programmers I've talked to and discussed the usage with.

Cheers all!

___________________________________________________________________
[sub]

http://www.onpnt.com

The answer to your ??'s may be closer then you think. faq333-3811
Join the Northern Illinois/Southern Wisconsin members in Forum1064[/sub]

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Redisplaying a new record after inserting, @@IDENTITY? 1

CyrusAyers

Programmer

onpnt

Programmer

CyrusAyers

Programmer

onpnt

Programmer

CyrusAyers

Programmer

CyrusAyers

Programmer

onpnt

Programmer

CyrusAyers

Programmer

shaddow

Programmer

onpnt

Programmer

Similar threads

Part and Inventory Search

Sponsor