Passing ID

[RikForgo]

I have two related tables that I want to update via forms on two CF pages. The first page asks the user to select a 'week number' from a drop list. Once the week number is chosen, the user clicks 'Submit' and CF runs an action page that updates the week table and and MySQL autoincrements an ID for that entry.

A CFLocation tag at the end of the action page directs the user to the next form page, which asks for the tasks created during the selected week. On this page the user simply chooses a category from a drop list field and in a text field types the work completed. A 'Submit' button enters that data as well. But I need to relate the 'week table' entry to the 'task' table entry.

My question is this: I need to take the ID assigned from the 'week number' table (period_id) and set it up so that it inserts into the corresponding field in the 'Task' table (also period_id). How do I go about doing that?

 

[NorthStarDA]

you can pass the drop list value in the cflocation if you want, then it will be available for the next page.

<cflocation url="mypage.cfm?week=#FORM.week#">

then on insert, use #URL.week# (if week is what you named the drop list) for the period_id value.

does this help you?

 

[TruthInSatire]

that won't give him the ID to relate the second query too.

after you do the insert in your first action page get the max id.

<cfquery name = "newRecord">
select max(id) as NewID from table
</cfquery>

then pass the ID in the url.
<cflocation url="mypage.cfm?id=#newRecord.NewID#">

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1959-2001)

 

[RikForgo]

That did it. Thanks guys!

 

[r937]

no, using max(id) will get you into major difficulty in a multi-user environment

of course, if you only have 1 user, max, ever, then i guess you'll be okay...

rudy

http://r937.com/

SQL Consulting

 

[webmigit]

Rudy, what do you think about

<cflock name="insertrecord" timeout="30">
  <cfquery... name="insrec">
    insert into table (...)
    values (...)
    select @@Identity as newID
  </cfquery>
</cflock>

That single-threads the engine until all is processes so as long as all inserts into that tables in any pages are locked under the same name, that would allow one insert at a time so the @@identity would retrieve the desired ID.. as #insertrecord.newID#

Depending on the database of course...

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[webmigit]

er rather, #insrec.newID#... yeah... its a monday, thursday style..

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[r937]

webmigit, i dunno if that will work, is @@identity supported in mysql? you could use last_insert_id(), i suppose, or mysql_insert_id(), whichever is more appropriate...

my own personal favourite method is simply to query the inserted row back using candidate key values

you know, the "real" primary key, since the autoincrement is just a surrogate

no locking required!!!

rudy

http://r937.com/

SQL Consulting