Creating then tracking an ID 4

[CTekMedia]

I have a form with an insert record behavior attached to it. On 'submit' the user is sent to a second page that shows (reviews) the info that the user entered on page one. So far so good. However, when the insert record behavior is run, and the record is created, the database auto-increments a unique key field called ID.

How do I retrieve the ID field from the database and display it on page two as the insert record behavior is being carried out. I use ColdFusion.

Thanks in advance! Peace

http://www.bluetone.biz

BT

 

[BrendaD]

One your second page make sure that your recordset includes the autonumber "UniqueID" field.

In your Select statement use ORDER BY "UniqueID" DESC.

This will return the last "UniqueId" entered into the database which will be the what the user just entered.

HTH,
Brenda

 

[CTekMedia]

That would work great in a low use environment but in my current situation there is too much chance of an error (too many users). I need to set a cookie or a session variable but not sure how to go about it.

Tks for the input though! Peace

http://www.bluetone.biz

BT

 

[schase]

well Brenda is still correct, however you can combine your difficulty with Brenda's solution.

If on page two - the record inserts and shows what they just inserted.

You can do the following before you close the recordset.

<% Session(&quot;svJustInserted&quot = rsXXX(&quot;theIDname&quot %>

If page two has a click button that then inserts the record - you'd do the same. &quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart

 

[TonyU]

[tt]
Here's how I accomplish this using DreamWeaver, you can scann the code and take what you need:

I set my SQL statement to the maxID making IT a session variable
<%
If (CStr(Request(&quot;MM_insert&quot) <> &quot;&quot Then
set TonyRS = Server.CreateObject(&quot;ADODB.Recordset&quot
TonyRS.ActiveConnection = MM_editCmd.ActiveConnection
TonyRS.Source = &quot;SELECT max(ID) as MaxID FROM MyTable&quot;
TonyRS.CursorType = 0
TonyRS.CursorLocation = 2
TonyRS.LockType = 3
TonyRS.Open()
Session(&quot;NewID&quot=TonyRS(&quot;MaxID&quot
Response.Redirect(MM_editRedirectUrl)
end if
%>

then I simply use the session...

[tt]&quot;A Successful man is one who can build
a firm foundation with the bricks
that others throw at him&quot;
[/tt]

 

[neofactor]

Seeing how you said you use coldfusion...
Let me give you a coldfusion answer:

You have a couple ways to do this:

[red]

<cftransaction>
<cfquery datasource=&quot;#DSN#&quot;>
INSERT INTO Table(blah, blah2)
VALUES ('#form.blah#', '#form.blah2#')
</cfquery>

<!--- get the last record --->
<cfquery datasource=&quot;#DSN#&quot; name=&quot;getMax&quot;>
SELECT MAX(ID) AS TopDog
FROM Table
</cfquery>

<cfset LastRecordID = getMax.TopDog>

</cftransaction>

[/red]

ORACLE is the same way:
<cftransaction>
<cfinsert dataSource=&quot;MyDatasource&quot; tableName=&quot;MYSCHEMA.MYTABLE&quot;>
<cfquery name=&quot;QGetLastID&quot; dataSource=&quot;MyDatasource&quot;>
SELECT MAX(MYCOLUMNAME) AS LASTID
FROM MYTABLE
</cfquery>
</cftransaction>

This way would work for you since you auto incriment... cftransaction will allow a roleback.. you might even try doing a cflock as well around it all

 

[Genimuse]

You might try the ColdFusion forum. This question has been answered repeatedly (in just the two weeks I've been here) in the ASP forum (doing it with ASP), so I suspect the same will be true of ColdFusion.

 

[Genimuse]

Sorry, to clarify: I mean an answer that doesn't use the Max(ID) solution, as you're very right, in a high-traffic envrionment it's completely unreliable, and even in a medium-traffic environment it's quite likely to fail.

For those unfamiliar with why this solution is dangerous (and can even fail in a low-traffic environment), there's no reason to believe that another user didn't manage to get an INSERT statement in between the time you INSERTed and SELECTed, resulting in retrieving the ID of that user rather than this one (both users accessing the same page).

 

[TonyU]

[tt] retrieving the record's @@Identity it's probably going to be your best bet...

http://www.aspfaq.com/show.asp?id=2174

is good reading...

[sup]

Delete * from brain Where MaxLevel = &quot;Full&quot; and reaction = &quot;Slow&quot; order by StartOver
​

[/sup]