ListGetAt Error in Cold Fusion - Need Help

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
LVL 1
CraigDeringtonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gdemariaCommented:

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
CraigDeringtonAuthor Commented:
Thank you for the quick reply.  I will review the other EE Expert Answer you quoted.  

Thank You!


Craig
0
gdemariaCommented:

 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

CraigDeringtonAuthor Commented:
Since we are no longer using the CF ListGetAt function, is it still necessary to peforms the replace on the empty elements?
0
gdemariaCommented:

 nope, you can remove that.
0
CraigDeringtonAuthor Commented:
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
CraigDeringtonAuthor Commented:
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
gdemariaCommented:


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

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

FORM.paymentAmount & i

Should be..

FORM['paymentAmount' & i]
0
CraigDeringtonAuthor Commented:
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
CraigDeringtonAuthor Commented:
Ahh, I will try that right now.  Thanks.
0
gdemariaCommented:

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
CraigDeringtonAuthor Commented:
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
gdemariaCommented:

is your payNote a string?

 if so, add single quotes...

  payNote = '#FORM["payNote" & i]#'
0
CraigDeringtonAuthor Commented:
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
gdemariaCommented:

 excellant, glad I could lend a hand
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.