Redirect and then view the last (highest numbered) record

[dpav29]

I have found a lot of discussion surrounding this, but not quite what I'm trying to do.

Users can create a new record in my dbase. Everything works fine wout problems. What I"m haveing trouble with is after the new record is created, I was to redirect to another page and display that brand new record. Since I don't know the ID of the new record, I can't figure it out. I've tried a couple variations of max, but to no avail. Here's the code (part of it).

con.Execute "insert into Groups (GroupName, TaxID, HospitalBased, Hospital1, Hospital2, Hospital3, Hospital4, Loc1Addr1, Loc1Addr2, Loc1City, Loc1St, Loc1Zip, Loc1CountyCode, Loc1Region, Loc1Phone, Loc1Fax, Loc1Contact, Loc1Email, Loc2Addr1, Loc2Addr2, Loc2City, Loc2St, Loc2Zip, Loc2CountyCode, Loc2Region, Loc2Phone, Loc2Fax, Loc2Contact, Loc2Email, createdby, editedby,maindbid,TypeGroup) values " &_
"('" & txtGroupName & "','" & txtTaxID & "','" & txtHospitalBased & "'," & txtHospital1 & "," & txtHospital2 & "," & txtHospital3 & "," & txtHospital4 & ",'" & txtLoc1Addr1 & "','" & txtLoc1Addr2 & "','" & txtLoc1City & "','" & txtLoc1St & "','" & txtLoc1Zip & "','" & txtLoc1CountyCode & "','" & txtLoc1Region & "','" & txtLoc1Phone & "','" & txtLoc1Fax & "','" & txtLoc1Contact & "','" & txtLoc1Email & "','" & txtLoc2Addr1 & "','" & txtLoc2Addr2 & "','" & txtLoc2City & "','" & txtLoc2St & "','" & txtLoc2Zip & "','" & txtLoc2CountyCode & "','" & txtLoc2Region & "','" & txtLoc2Phone & "','" & txtLoc2Fax & "','" & txtLoc2Contact & "','" & txtLoc2Email & "','" & user & "','" & user & "','" & txtmaindbid & "','" & TypeGroup & "')"
con.Close
set con=nothing
Response.Redirect("/cfdev/app/Groupdetail.asp?GroupID=Max(PID)")
end if
set rs1=server.CreateObject("ADODB.Recordset")
rs1.ActiveConnection=con
rs1.Open "Select HospID, HospName from hospitals where deleted=0 order by HospName"
do until rs1.EOF or i=500
i=i+1
hospitalId(i)=rs1(0)
hospitalName(i)=rs1(1)
rs1.MoveNext
loop
rs1.Close
set rs1=nothing
con.Close
set con=nothing

Any help is appreciated as always!

 

[dpav29]

I found a simple (albiet crazy) way to do it:

I redirected to a new page that included one simply query. . descending list of ID's from the table returning only one record (i.e. my new record). It's a confirmation page that then links to my intended page and takes with it the newly created ID number.

It's an extra step, but I'm tired!

Anyone with a better idea?

 

[Tarwn]

If it works...

Actually tat seems like a fairly solid method except when someone add's another record in that few ms between you submittingthe new record then goign to the next page and displaying the newest one. Generally in the web-world I don't bother with that, since I rarely expect two people to be adding records to the database at even the same minute, let alone the exact same second.

However, if your using a recordset object on the previous page and the .AddNew method, you cn .Update aftr entering new data then grab the new id from the recordset before closing it. Or you could simply call a second query that SELECT's on certain key info that you just inserted (I don't like recordset objects).


My one problem with your method is that your pulling back ALL of the recorss to look for the last one. It would b easier on communications and memory to simply ask for the last one, something like:
"SELECT TOP 1 YourIdField FROM Groups ORDER BY YourIdField DESC"

(I assumed you were using a seeded, auto-incrementing integer as your key)

In any case, hope this helps

-T

 

[newmediaguy]

Hi

What I tend to do when I cant use @@IDENTITY is to essentially copy the insert statment and make it in to a Select statement that I run on subsequent pages to return the ID like this...[Avoids the possiblity on heavily used sites of returning someone elses ID]

'Insert on insert page
INSERT "Val1", "Val2", "Val3"
INTO "Col1","Col2","Col3"
etc....

'Select that new Record
'You need to pass these values 
'to the next page if you are not 
'going to use just one page to do it

SELECT ID FROM NewUsers
WHERE Col1Val1 = Val1 AND Col2Val2 = Val2
etc...

Have I explaind that well enough?



Glen
Conception | Execution