Not inline. Your only option of keeping it in one query is CURRVAL.
-Adam
----- Original Message -----
From: daniel kessler <[EMAIL PROTECTED]>
Date: Thu, 16 Sep 2004 10:53:30 -0400
Subject: Re: Using a value from one insert for another insert
To: CF-Talk <[EMAIL PROTECTED]>
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]
