Solved

ListGetAt Error in Cold Fusion - Need Help

Posted on 2007-03-22
16
375 Views
Last Modified: 2013-12-24
Hello EE.

I inherited a CF site recently and have been able to work through most of the issues I have run into.  However, there is this one problem which I can not solve.  I have a page with a form that contains 4 elements.  Date, PaymentAmount, DisbursementID, and I have now added PayNote.

The query looks like this:

<CFELSEIF IsDefined("FORM.updateAllDisbursements")>
      <CFQUERY DATASOURCE="#APPLICATION.datasource#">
            <CFLOOP FROM="1" TO="#ListLen(FORM.disbursementID)#" STEP="1" INDEX="i">
            <CFIF i IS NOT "">
                  UPDATE disbursements
                     SET paymentDueDate = #CreateODBCDate(CreateDate(ListGetAt(FORM.year,i), ListGetAt(FORM.month,i), ListGetAt(FORM.day,i)))#, paymentAmount = #ListGetAt(FORM.paymentAmount,i)#, payNote = '#ListGetAt(FORM.payNote,i)#'
                   WHERE disbursementID = #ListGetAt(FORM.disbursementID,i)#;
            </CFIF>
            </CFLOOP>
      </CFQUERY>

The form can contain multiple payments and a ListGetAt function is being used to put all of the elements into a list to loop through and update the database for each Disbursement ID.  After reading a lot of questions and answers on EE yesterday and this morning about this particular CF function, I am still getting the index error.  I know that is a element is empty, CF ignores it.

Let's say there are 15 payments that need to be updated.  If the end user loads the form displaying the 15 payments that need to be rescheduled but only needs to add a note for 3 of the 15 payments, when they click Update All, I get this error.

In function ListGetAt(list, index [, delimiters]), the value of index, 2, is not a valid as the first argument (this list has 1 elements). Valid indexes are in the range 1 through the number of elements in the list.

Any ideas?  As always, thank you in advance for your help.

Craig
0
Comment
Question by:CraigDerington
  • 8
  • 8
16 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 18771973

Right, just finishing up another question on this, you may find the answers you need here...

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/Cold_Fusion_Markup_Language/Q_22463420.html

In short, you need to convert the empty cells of your list to real values so the list functions won't skip over them...

 <cfset variables.myList = replace(variables.myList, ",,",  ",NULL,", "all")>
 
then whenever you see the word NULL, you take it to mean EMPTY.

It's a pain for sure.   The better way is not to use lists but to use an "array" style input tags.   Follow the link above for details.   Please see posts with my username.

0
 
LVL 1

Author Comment

by:CraigDerington
ID: 18772023
Thank you for the quick reply.  I will review the other EE Expert Answer you quoted.  

Thank You!


Craig
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 18772168

 The selected answer on that other question addresses the issue, however, if you read further you will find the preferred solution, copied here ...


<cfloop query="getproducts">
   <cfset variables.ct = getProducts.currentRow>
 <tr>
   <td width="94" class="txt_gray_11">
   <input type="text"   name="quantity#variables.ct#" value="0" class="txt_gray_11" size="2">
   <input type="hidden" name="invproduct_id#variables.ct#" value="#invproduct_id#">
   <input type="hidden" name="itemnumber#variables.ct#" value="#itemnumber#">
   <input type="hidden" name="description#variables.ct#" value="#description#">
   <input type="hidden" name="price#variables.ct#" value="#price#">
   </td>
</tr>
</cfloop>
<input type="hidden" name="totalRecords" value="#variables.ct#">


HERES MY PROCESSING PAGE
-----------------------------------------------------
<cfif isdefined('form.add_button.y')>

<cfloop index="ii" from="1" to="#form.totalRecords#">

  <cfset invID   = form['invproduct_id' & i]>
  <cfset iNumber = form['itemnumber' & i]>
  <cfset Descr   = form['description' & i]>
  <cfset pr      = form['price' & i]>
  <cfset quan    = form['quantity' & i]>


  ... now work with the variables  invID, iNumber, etc..

0
 
LVL 1

Author Comment

by:CraigDerington
ID: 18772187
Since we are no longer using the CF ListGetAt function, is it still necessary to peforms the replace on the empty elements?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18772299

 nope, you can remove that.
0
 
LVL 1

Author Comment

by:CraigDerington
ID: 18772381
The guy who originally designed this uses a CF include - dateSelect.cfm to generate the drop downs for the data menu (if the end user needs to reschedule the disbursement for another date, along with the other fields I have already mentioned.  I saved the dateSelect.cfm as dateSelect2.cfm and appended the field names with the #variables.ct#.  In my form, I have also appended the other form elements with the #variables.ct#.

It seems that when I view the source after loading the page, the date fields month, day, year all have the counter appended to the name like year.15, but my other form elements names are paymentAmount#variables.ct# and not paymentAmount15.  Is this because the included dateSelect2.cfm is getting the variables.ct count and it can not also append the other form elements with the correct count?

I'm stuck.

Thanks for your help.

Craig
0
 
LVL 1

Author Comment

by:CraigDerington
ID: 18772500
Here is the form.

<form action="receiptsDisbursements.cfm" method="post">
<CFLOOP query="disbursementList">
<CFSET variables.ct = disbursementList.currentRow>
<tr class="color-row"><td width="16%" height="20">Disbursement Date:</td>
<td height="20"><CFSET date = DateFormat(paymentDueDate, "MM/DD/YYYY")>      <CFINCLUDE TEMPLATE="includes/dateSelect2.cfm">
</td>
</tr>
<tr class="color-row"><td width="16%" height="20">Disbursement Amount:</td>      
<td height="20"><input type="text" name="paymentAmount#variables.ct#" value="#NumberFormat(paymentAmount, '.99')#" size="5" />
&nbsp;&nbsp;Payment Note:
<input name="payNote#variables.ct#" type="text" class="color-row" id="payNote" value="#payNote#" size="30" maxlength="200" /><input type="hidden" name="totalRecords" value="#variables.ct#">
</td>
</tr>
<tr align="left" class="color-row">
<td colspan="3" height="20"><input type="hidden" name="disbursementID#variables.ct#" value="#disbursementID#" /></td>
</tr>
</CFLOOP>
<tr align="left" class="color-row">
<td colspan="3" height="20">
<input type="submit" name="updateAllDisbursements" value="Update All Disbursements" /></td></tr>
</form>


The form processor code:

<CFELSEIF IsDefined("FORM.updateAllDisbursements")>
<CFQUERY DATASOURCE="#APPLICATION.datasource#">
<CFLOOP INDEX="i" FROM="1" TO="#FORM.totalRecords#">
UPDATE disbursements
SET paymentDueDate = #CreateODBCDate(CreateDate((FORM.year & i), (FORM.month & i), (FORM.day & i)))#,
paymentAmount = #(FORM.paymentAmount & i)#,
payNote = #('FORM.payNote' & i)#
WHERE disbursementID = #(FORM.disbursementID & i)#;
</CFLOOP>
</CFQUERY>

From looking at the other answer, this looks accurate...


0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18772578


>  It seems that when I view the source after loading the page, the date fields month, day, year all have the counter appended to the name like year.15, but my other form elements names are paymentAmount#variables.ct# and not paymentAmount15.

When this happens its because the coldfusion variable is not being processed, you need to surround this in CFOUTPUT.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 39

Expert Comment

by:gdemaria
ID: 18772593

this format you are using is not quite right...

FORM.paymentAmount & i

Should be..

FORM['paymentAmount' & i]
0
 
LVL 1

Author Comment

by:CraigDerington
ID: 18772594
OK.  I have worked through all of that and have the form and the values displaying properly.  When I click Update All I get this CF error:

The value "1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19" cannot be converted to a number

Something is amiss with my processing code I believe.
0
 
LVL 1

Author Comment

by:CraigDerington
ID: 18772601
Ahh, I will try that right now.  Thanks.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18772620

Your totalRecords input tag is INSIDE your CFLOOP, that means it will be a comma delimited list of 1,2,3,4,5..   I think you want it to be outside your CFLOOP so you get just one value, the total count.

  <tr align="left" class="color-row">
     <td colspan="3" height="20"><input type="hidden" name="disbursementID#variables.ct#" value="#disbursementID#" /></td>
 </tr>
</CFLOOP>

<input type="hidden" name="totalRecords" value="#variables.ct#">

0
 
LVL 1

Author Comment

by:CraigDerington
ID: 18772699
Do you see a problem with this.  

UPDATE disbursements
SET paymentDueDate = #CreateODBCDate(CreateDate(FORM['year' & i], FORM['month' & i], FORM['day' & i]))#,
paymentAmount = #FORM['paymentAmount' & i]#,
payNote = #FORM['payNote' & i]#
WHERE disbursementID = #FORM['disbursementID' & i]#;  


Now I am getting this error

Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'WHERE'.  
 
I'm going to try and remove the commas and the semi-colon from the query.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18772741

is your payNote a string?

 if so, add single quotes...

  payNote = '#FORM["payNote" & i]#'
0
 
LVL 1

Author Comment

by:CraigDerington
ID: 18772794
Yes, it is a string.

Thank you very much.  That did it.  I was trying to do the single quote around the #FORM['payNote' & i] instead of the entire string '#FORM["payNote" & i]#' and I wasn't using double quotes for the payNote inside the brackets like you have above.

I will be awarding the points to you immediately.

Thank you again for your exceptional EXPERT support.

Craig
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18773494

 excellant, glad I could lend a hand
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now