unattractive and effective three-query way that I do it. I use various
items to create something that I know will be unique. For example, I
concatenate user ID and current time. I put that in a local variable. I
run the insert query, putting this unique value into some field that really
is for something else, say product_description. I run a select query
pulling the primary key for the row that has that unique value in
product_description. I then run an update query, putting the proper product
description in the row at the primary key found.
As I said, ugly, but effective.
Good luck,
Matthieu
-----Original Message-----
From: daniel kessler [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 16, 2004 10:54 AM
To: CF-Talk
Subject: Re: Using a value from one insert for another insert
I'm now receiving an invalid character error. I can do each of the inserts
independently, but when I put them together with the RETURNING line, I get
the error.
INSERT INTO fsnep_polls
(
p_id,p_date_added,p_question,
p_status,p_date_last_used
)
VALUES
(
unique_poll_Num_s.NEXTVAL,#Now()#,'#Form.p_question#',
#Form.p_status#,#p_date_last_used#
)
RETURNING p_id INTO new_p_id;
INSERT INTO fsnep_pollAnswers
(
pA_ID,pA_answer
)
VALUES
(
unique_pollAnswers_Num_s.NEXTVAL,
'#Form.p_answer1#'
)
>Well you should put it in a procedure rather than inline. Then you
>could do something like:
>
>INSERT INTO fsnep_polls
>(
>p_id,p_date_added,p_question,
>p_status,p_date_last_used
>)
>VALUES
>(
>unique_poll_Num_s.NEXTVAL,#Now()#,'#Form.p_question#',
>#Form.p_status#,#p_date_last_used#
>)
>RETURNING p_id INTO newly_created_id
>
>(note the RETURNING syntax used in PL/SQL)
>
>Outside of that, if you must stay inline, you could use
>unique_poll_Num_s.CURRVAL. That should return the current value of the
>new primary key. Although this is dangerous, make sure to keep your
>transactions tight.
>
>-Adam
>
>
>----- Original Message -----
>From: Daniel Kessler <[EMAIL PROTECTED]>
>Date: Thu, 16 Sep 2004 09:56:46 -0400
>Subject: Using a value from one insert for another insert
>To: CF-Talk <[EMAIL PROTECTED]>
>
>I'm in Oracle. I do an insert for a poll question, then I need to
>get the ID of that question and insert it into each one of the
>answers that I write to a different table (see code below). Without
>a separate query, is there any way to get the ID inside the same
>query to use again for the answers? Also, would doing a seperate
>query be a bad idea?
>
>thanks.
>
><CFQUERY NAME="addItem" DATASOURCE="eatsmart">
> INSERT INTO fsnep_polls
> (
> p_id,p_date_added,p_question,
> p_status,p_date_last_used
> )
> VALUES
> (
> unique_poll_Num_s.NEXTVAL,#Now()#,'#Form.p_question#',
> #Form.p_status#,#p_date_last_used#
> )
>
> <cfloop index="ii" from="1" to="5">
> <cfif Form.p_answer1 NEQ "">
> INSERT INTO fsnep_pollAnswers
> (
> pA_ID,pA_pollID,pA_answer
> )
> VALUES
> (
> unique_pollAnswers_Num_s.NEXTVAL,#Form.p_id#,
> '#Form.p_answer1#'
> )
> </cfif>
> break;
> </cfloop>
></CFQUERY>
>
>--
>Daniel Kessler
>
>Department of Public and Community Health
>University of Maryland
>Suite 2387 Valley Drive
>College Park, MD 20742-2611
>301-405-2545 Phone
>www.phi.umd.edu________________________________
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
