Skip to main content
I
istock1
Participant
January 14, 2008
Question

Index Looping in a Query ...

  • January 14, 2008
  • 2 replies
  • 487 views
All,

I am trying use the index of a cfloop to structure a query, but can not get it to work. Here is the code:

<cftransaction>
<cfquery datasource="#ds#" name="newServices">
INSERT INTO ii_services
VALUES
(#Form.TID#,#Form.pocID#
<cfloop index="i" from="1" to="30">
,
'#Form.service##i#',
'#Form.recDate##i#',
'#Form.included##i#',
'#Form.incDate##i#',
'#Form.status##i#',
'#Form.statDate##i#',
'#Form.negImpact##i#',
'#Form.comment##i#'
</cfloop>)
</cfquery>
</cftransaction>

I have 30 values for service, recdate, etc. that I would like to load, but receive a "Element SERVICE is undefined in FORM" error when it is executed. Can someone point me in the right direction?

Thanks!
This topic has been closed for replies.

2 replies

T
The_ScareCrow
Inspiring
January 14, 2008
You need to loop over the entire query

See attached code...
P
paross1
Participating Frequently
January 14, 2008
You can't insert multiple rows within a single INSERT like that, since each row inserted is a distinct transaction. Place your CFLOOP outside of your CFQUERY.

Phil
D
Dan_Bracuk
Inspiring
January 15, 2008
quote:
Originally posted by: paross1
You can't insert multiple rows within a single INSERT like that, since each row inserted is a distinct transaction. Place your CFLOOP outside of your CFQUERY.

Phil

Actually you can. Something like this will work. You have to test it though, sometimes it's faster than queries inside loops and sometimes it's slower.

insert into thetable
(f1, f2, etc)
<cfloop>
select distinct #value1#, #value2#
from somesmalltable
<cfif loop not done>
union
closing tags
P
paross1
Participating Frequently
January 15, 2008
You are right.... what was I thinking? Using a select in that manner is a viable option, I just hadn't thought about combining it with a cfloop within the insert statement.

Phil
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices