cfloop help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Olivia Crazy Horse - 02 Jul 2008 23:55 GMT

I have an edit form where I display records for line item 1, records for line
item 2, and records for line item 3. Each output/display is based on a flag, so
I use <cfif flag is .....> to determine what to display for each line item.
This works good, so far.

The problem that I am running into is when I attempt to update the table with
the changes. I use an index loop, from 1 to number of records, in this case 3.
I want to update each record at a time. However, the first column that it
attempts to update, quantity, blows up. The form shows item 1 with quantity
100, item 2 with quantity 2, and item 3 with quantity 5. The sql error shows
that I am attempting to update quantity = '100,2,5'.

I though the loop would take care of it but I guess not.

Here is my code for the loop, what am I doing wrong ?

<cfquery name="qryUpdate_ref_Line_Items" datasource="db">
<cfloop index="i" from="1" to="#form.max_line_Item#">
update TableName
set quantity = '#form.quantity#',
<cfif form.ref_action is "Receive">
po_number = '#form.po_number#',
item = '#form.item#',
part_number = '#form.part_number#'
<cfelseif form.ref_action is "NRC">
tracking_number = '#form.tracking_number#'
<cfelseif form.ref_action is "Other">
instructions = '#form.instructions#'
</cfif>
where ref_number = '#form.ref_number#'
and ref_line_item = '#i#'
</cfloop>
</cfquery>

Reply to this Message

Dan Bracuk - 03 Jul 2008 03:14 GMT

Put the cfquery tag inside the loop.

Reply to this Message

Olivia Crazy Horse - 03 Jul 2008 03:49 GMT

I tried putting the cfquery inside the cfloop and it is still doing the same
thing. Instead of one value per column, it is putting in all the values, for
example, quantity='100,2,5' and the errro message is regarding and integer
problem.

My loop is incorrect since it does not do one at a time.

Reply to this Message

Azadi - 03 Jul 2008 08:10 GMT

correct me if i am wrong, but it looks like you have a form field with
same name (quantity) several times in your form (for each of your ;line
items' perhaps?)...
what you get in this case is ONE form.quantity var with comma-delimited
list of values of all quantity fields... to avoid this give all your
fields unique names.

hth

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

Reply to this Message

Olivia Crazy Horse - 03 Jul 2008 16:12 GMT

Yes, that is exactly what was happeing. When I cfoutput, they all have the same
names. I get it now. So I rewrote and got this, using the ref_line_items to
make each name unique :

<cfoutput query="qryGet_REF_Line_Items">
<tr>
<td align="center">#REF_line_item#<input type="hidden"
name="REF_line_item_#REF_line_item#"
value="#qryGet_REF_Line_Items.REF_line_item#"></td>
<td align="center">#REF_action#<input type="hidden"
name="REF_action_#REF_line_item#"
value="#qryGet_REF_Line_Items.REF_action#"></td>
<td align="center"><cfinput type="text" name="quantity_#REF_line_item#"
size="4" maxlength="4" value="#qryGet_REF_Line_Items.quantity#"></td>
<cfif qryGet_REF_Line_Items.REF_action is "Receive">
<td align="center"><cfinput type="text" name="po_number_#REF_line_item#"
value="#qryGet_REF_Line_Items.po_number#"></td>
<td align="center"><cfinput type="text" name="item_#REF_line_item#" size="4"
maxlength="4" value="#qryGet_REF_Line_Items.item#"></td>
<td align="center"><cfinput type="text" name="part_number_#REF_line_item#"
value="#qryGet_REF_Line_Items.part_number#"></td>
<td align="center">#qryGet_REF_Line_Items.tracking_number#"</td>
<td align="center">#qryGet_REF_Line_Items.instructions#"</td>
<cfelseif qryGet_REF_Line_items.REF_action is "RTV">
<td align="center">#qryGet_REF_Line_Items.po_number#</td>
<td align="center">#qryGet_REF_Line_Items.item#</td>
<td align="center">#qryGet_REF_Line_Items.part_number#</td>
<td align="center"><cfinput type="text" name="tracking_number_#REF_line_item#"
value="#qryGet_REF_Line_Items.tracking_number#"></td>
<td align="center">#qryGet_REF_Line_Items.instructions#"</td>
<cfelseif qryGet_REF_Line_Items.REF_action is "Other">
<td align="center">#qryGet_REF_Line_Items.po_number#</td>
<td align="center">#qryGet_REF_Line_Items.item#</td>
<td align="center">#qryGet_REF_Line_Items.part_number#</td>
<td align="center">#qryGet_REF_Line_Items.tracking_number#</td>
<td align="center"><cfinput tgype="text" name="instructions_#REF_line_item#"
value="#qryGet_REF_Line_Items.instructions#"></td>
</cfif>
</tr>
</cfoutput>

The output display above is based on <cfelseif
qryGet_REF_Line_items.REF_action is "RTV">, then display certain columns as
input and others as just display, if ref_action is somehting else, then disply
other columns as input and dispaly, etc.

So I display all the columnns, but based on cffi, only some are availabe for
edit and others are just display.

When I go to the actioni page to update is where I run into problems.

Here is my partial code below. Since only certain fields are to be updated, I
have to use the cfif to determine what the ref_action value is, and then only
update those fields. The problem I am runnig into is how to format the
ref_action in a cfif statement, since it is defined as ref_action_1,
ref_action_2., etc.

I want to say <cfif ref_action_#ref_line_item# is "xxxx">, then do this update
with these columns. <cfif something else, then do another update, etc.

But for some reason, I cannot seem to format it correctly. This little part is
preventing me from completing this task.

<cfloop index="REF_line_item" list="#form.list_of_line_items#" delimiters=",">
<cfquery name="qryUpdate_REF_Line_Items" datasource="recDisc">
update test_UnReceivables_REF_Line_Items
set quantity = '#Evaluate("form.quantity_#REF_line_item#")#',
po_number = '#Evaluate("form.po_number_#REF_line_item#")#',
item = '#Evaluate("form.item_#REF_line_item#")#',
part_number = '#Evaluate("form.part_number_#REF_line_item#")#'
where REF_number = '#form.REF_number#'
and REF_line_item = '#REF_line_item#'
</cfquery>
</cfloop>

Reply to this Message

Dan Bracuk - 03 Jul 2008 14:29 GMT

[q][i]Originally posted by: [b][b]Olivia Crazy Horse[/b][/b][/i]
I tried putting the cfquery inside the cfloop and it is still doing the same
thing. Instead of one value per column, it is putting in all the values, for
example, quantity='100,2,5' and the errro message is regarding and integer
problem.

My loop is incorrect since it does not do one at a time. What else can I do
?[/q]
You are on the right track. You have to use the loop index value in your
query though. Something like.

<cfloop index = "idx">
<cfquery>
update sometable
set somefield = #idx#
where some_conditions_are_met

Reply to this Message

dongzky - 03 Jul 2008 03:17 GMT

what object is your form.quantity? is it a textbox? or a list?

Reply to this Message

dongzky - 03 Jul 2008 07:14 GMT

what object is your form.quantity? is it a textbox? a list?

Reply to this Message

dongzky - 04 Jul 2008 03:53 GMT

What was the thrown error?

Anyway, I have questons on your variables:

1. Is #REF_line_item# (when you do a <cfoutput query="qryGet_REF_Line_Items">)
a string or a number?

2. Are the values in #form.list_of_line_items# (when you do a loop in the
list) strings or numbers?

The reason I'm asking these is to make sure that 1 & 2 have the same data
types and that the values in number 1 are present in the values in number 2.
That is, if the REF_line_item = 5 in number 1, your form.list_of_line_items in
number 2 should contain the value 5 also.

Another thing, how about your form.REF_number? seems like in your code this is
the same for all items.

Reply to this Message

Webmaster Forum / ColdFusion / General CF Topics / July 2008

cfloop help