?
Solved

ListGetAt Error in Cold Fusion - Need Help

Posted on 2007-03-22
16
Medium Priority
?
393 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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
 
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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

762 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