?
Solved

ColdFusion Update multipe fields in multiple records at once

Posted on 2012-08-26
29
Medium Priority
?
1,380 Views
Last Modified: 2012-08-28
I can update multiple records in one field, but need help with updating multiple fields in multiple records.

How can I add two more fields?

My code for updating multiple records for one field:

<cfif structKeyExists(form, "save")>
    <cfloop item="field" collection="#form#">
       <cfif findNoCase("art_", field)>
           <cfset keyid = listLast(field, "_")>
                           
            <cfquery datasource="support">
            update    table
           
            SET      QTY = <cfqueryparam value="#form[field]#" cfsqltype="cf_sql_varchar">,
                  
               
            where    keyid = <cfqueryparam cfsqltype="cf_sql_integer" value="#keyid#">
           
                       
           </cfquery>
         </cfif>
    </cfloop>
</cfif>


<td><cfinput type="Text" name="art_#keyid#" value="#DATEFORMAT(REC_DATE, "m/d/yyyy")#" size="10"message="Please input Date Received!" validateat="onSubmit" validate="noblanks" required="yes"></td>
 
Need to add these inputs for the same record:

  <td> <cfinput type="Text" name="QTY" value="#QTY#" size="10" message="Please input Quantity Received!" validateat="onSubmit" validate="noblanks" required="yes"></td>
    <td> <cfinput type="text" name="INVOICE" Value="#INVOICE#" size="10" message="Pleas input Invoice Number!" validateat="onsubmit" validate="noblanks" required="yes"></td>
</tr>
0
Comment
Question by:DJPr0
  • 13
  • 10
  • 3
  • +2
29 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38334452
I believe you would just need to add more fields in between your first field (QTY) and the WHERE keyword, all separated by commas.  Something like this:
SET      QTY = <cfqueryparam value="#form[field]#" cfsqltype="cf_sql_varchar">,
             AMT=<......>,
             TOT=<......>,    
               
            where    keyid = <cfqueryparam cfsqltype="cf_sql_integer" value="#keyid#">
0
 

Author Comment

by:DJPr0
ID: 38334494
I tried that and it didn't work:
SET      QTY = <cfqueryparam value="#form[field]#" cfsqltype="cf_sql_varchar">,
      DATE = <cfqueryparam value="#form[field]#" cfsqltype="cf_sql_varchar">,
        INVOICE = <cfqueryparam value="#form[field]#" cfsqltype="cf_sql_varchar">
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38334503
Hmm, in the code you originally posted, you have a comma right before the WHERE clause.  If that one was right, what if you try adding one after the INVOICE line?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:TechHelpr08210
ID: 38334521
I'm a bit confused as tyo what your trying to do. I see that your inserting the value of QTY but, you list is state that you need to insert it. Here is my questions:

Your existing setup would allow you update additional values in each row selected by adding in the additional values to the SQL statement.


Are you just trying to add the new values into the existing updatestatement or are you trying to add the new values into other tables instead?

Im happy to help but, just not very clear at the moment.
0
 
LVL 4

Expert Comment

by:TechHelpr08210
ID: 38334545
Oh, now I see, you just want the proper SQL syntax. What Database software are you using?

I would suggest trying this method if your also using MSSQL 2005.

UPDATE TABLE

SET      
       QTY = <cfqueryparam value="#form[field]#" cfsqltype="cf_sql_varchar">,
       DATE = <cfqueryparam value="#form[field]#" cfsqltype="cf_sql_varchar">,
       INVOICE = <cfqueryparam value="#form[field]#" cfsqltype="cf_sql_varchar">

WHERE
       keyid = <cfqueryparam cfsqltype="cf_sql_integer" value="#keyid#">      

Open in new window


If the above update doesn't work, there are a few other options. Here are More Examples for you covering multiple SQL types: Updating Multiple values examples in SQL.
0
 

Author Comment

by:DJPr0
ID: 38334702
It could be something in my form - please see below - when I click the save button is copies the updated data to the other field - copies the ship field data to the project field.

I'm using Access for the database.

Test Form data:

<form action="test.cfm" method="post">
<table>
<cfloop query="getArt">
    <tr>
        <cfoutput><td><input type="text" name="art_#keyid#" value="#ship#" /></td></cfoutput>
        <cfoutput><td><input type="text" name="art_#keyid#" value="#project#" /></td></cfoutput>
    </tr>
</cfloop>
</table>
<input type="submit" name="save" value="Save" />
</form>

Update statement:

<cfif structKeyExists(form, "save")>
    <cfloop item="field" collection="#form#">
       <cfif findNoCase("art_", field)>
       
           <cfset keyid = listLast(field, "_")>
            <cfquery datasource="support">
            update    table
            set        ship = <cfqueryparam value="#form[field]#" cfsqltype="cf_sql_varchar">,
                      project = <cfqueryparam value="#form[field]#" cfsqltype="cf_sql_varchar">
       
                       
                       
            where    keyid = <cfqueryparam cfsqltype="cf_sql_integer" value="#keyid#">
           
 
           
         
           </cfquery>
         </cfif>
         
    </cfloop>
</cfif>
0
 

Author Comment

by:DJPr0
ID: 38334756
I got it to work with two loops, but not sure if this is the correct procedure.

Please take a look.

Code that works for updating two fields on two or more records:

<cfif structKeyExists(form, "save")>
    <cfloop item="field" collection="#form#">
       <cfif findNoCase("art_", field)>
       
           <cfset keyid = listLast(field, "_")>
            <cfquery datasource="support">
            update    table
            set       ship = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form[field]#">
                         
                   
            where    keyid = <cfqueryparam cfsqltype="cf_sql_integer" value="#keyid#">
                 
           
           
           </cfquery>
         </cfif>
         
    </cfloop>
    <cfloop item="field" collection="#form#">
       <cfif findNoCase("art1_", field)>
       
           <cfset keyid = listLast(field, "_")>
            <cfquery datasource="support">
            update    table
            set      
             project = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form[field]#">      
                   
            where    keyid = <cfqueryparam cfsqltype="cf_sql_integer" value="#keyid#">
                 
           
           
           </cfquery>
         </cfif>
         
    </cfloop>
</cfif>

<cfquery name="getArt" datasource="support" maxrows="10">
select *
from    table
</cfquery>

<form action="test.cfm" method="post">
<table>
<cfloop query="getArt">
    <tr>
        <cfoutput><td><input type="text" name="art_#keyid#" value="#ship#" /></td></cfoutput>
        <cfoutput><td><input type="text" name="art1_#keyid#" value="#project#" /></td></cfoutput>
         
    </tr>
</cfloop>
</table>
<input type="submit" name="save" value="Save" />
</form>
<body>
</body>
</html>
0
 
LVL 4

Expert Comment

by:TechHelpr08210
ID: 38334793
What datbase are you suing?

Oracle?
MSSQL 2008?
MySQL?

There is a cleaner way to write the update but, I need to know which DB type your trying to update.
0
 

Author Comment

by:DJPr0
ID: 38335041
Access database.

The update button doesn't work on my production form - just works for the record near the button - how can I update all records with one button?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38335209
Here's an example of how I do it.  Not sure of the real form field names as they changed a lot. The example uses: "keyid", "ship", "invoice" and "qty".  Change them as needed.  

Also, check the cfsqltypes. "Qty" sounds like a numeric field, not a varchar.

<!---- select only the columns you need --->
<cfquery name="getArt" datasource="support" maxrows="10">
     SELECT *
     FROM    table
</cfquery>


<cfform method="post" ...>

   <cfoutput query="getArt">
      <!--- generate sequential field names ie keyid_1, keyid_2, etc.. --->
      <cfinput type="Text" name="keyid_#currentRow#" value="#keyid#" ....>
      <cfinput type="Text" name="Ship_#currentRow#" value="#ship#" ...>
      <cfinput type="Text" name="Qty_#currentRow#" value="#QTY#" ...>
      <cfinput type="text" name="Invoice_#currentRow#" Value="#INVOICE#" .... >
   </cfoutput>

   <input type="submit" value="Save">

   <!--- save number of dynamic fields --->
   <cfoutput><input type="hidden" value="#getArt.recordCount#"></cfoutput>
</cfform>

<cfparam name="form.numOfFields" default="0">
<cfif structKeyExists(form, "save")>

   <cfloop from="1" to="#form.numOfFields#" index="counter">
       <!--- extract values of form fields --->
       <cfset variables.keyID   = FORM["keyid_"& counter]>
       <cfset variables.ship    = FORM["ship_"& counter]>
       <cfset variables.qty     = FORM["qty_"& counter]>
       <cfset variables.Invoice = FORM["invoice_"& counter]>

       <cfquery datasource="support">
          UPDATE Table
          SET    ship      = <cfqueryparam value="#variables.ship#" cfsqltype="cf_sql_varchar" >
               , invoice = <cfqueryparam value="#variables.invoice#" cfsqltype="cf_sql_varchar">
               , qty     = <cfqueryparam value="#variables.qty#" cfsqltype="cf_sql_varchar">
               ,            
          WHERE  keyid = <cfqueryparam cfsqltype="cf_sql_integer" value="#variables.keyID#">
       </cfquery>

   </cfloop>      
</cfif>
0
 
LVL 16

Expert Comment

by:Gurpreet Singh Randhawa
ID: 38335946
well why not also wrap cftranaction tag to as ou are running a loop and inside the loop, the cfquery is run each for the record

But as i do not know much abt access, why cfloop and inside cfloop, cfquery is used, it will hit it everytime to database

Suggestion: why not use the procedure here

Thanks
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38336027
Access doesn't really support stored procedures.   It doesn't hurt to use cftransaction here, though truthfully I'm not sure Access fully supports that either.
0
 

Author Comment

by:DJPr0
ID: 38338478
Thanks _agx_ for your reply.
I incorporated the code into my search and nothing gets updated after I submit.
No errors.
Please help.

My table columns I want updated: Date, Qty and invoice.

Code:

 <cfinput name="keyid_#currentRow#" type="hidden" value="#KEYID#" size="15">

    <td><cfinput type="Text" name="DATE_#currentRow#" value="#DATEFORMAT(DATE, "m/d/yyyy")#" size="10"message="Please input Date Received!" validateat="onSubmit" validate="noblanks" required="yes"></td>

    <td> <cfinput type="Text" name="Qty_#currentRow#" value="#QTY#" size="10" message="Please input Quantity Received!" validateat="onSubmit" validate="noblanks" required="yes"></td>

    <td> <cfinput type="text" name="Invoice_#currentRow#" Value="#INVOICE#" size="10" message="Pleas input Invoice Number!" validateat="onsubmit" validate="noblanks" required="yes"></td>
</tr>
</table>

<!-- END -->
</td>

<td width="100%">


<cfinput type = "submit" name="save" class="button" value = "save"  
                                              onClick="return confirm('Are You Sure You Want To Update This Record?');"></td>


</td>


</tr>
</table>
<input type="submit" name="save" value="Save" />

 <br>
 <br>  
<!--- save number of dynamic fields --->
   <cfoutput><input type="hidden" value="#results.recordCount#"></cfoutput>
</cfform>

</cfoutput>      


</cfif>

</cfif>





<cfparam name="form.numOfFields" default="0">
<cfif structKeyExists(form, "save")>

   <cfloop from="1" to="#form.numOfFields#" index="counter">
       <!--- extract values of form fields --->
       <cfset variables.keyID   = FORM["keyid_"& counter]>
       <cfset variables.QTY = FORM["qty_"& counter]>
       <cfset variables.Invoice = FORM["invoice_"& counter]>
       <cfset variables.DATE = FORM["REC_DATE_"& counter]>
       <cfquery datasource="bsupport">
          UPDATE table
          SET    DATE  = <cfqueryparam value="#variables.date#" cfsqltype="cf_sql_varchar">,
                 Invoice = <cfqueryparam value="#variables.invoice#" cfsqltype="cf_sql_varchar">,
                 QTY     = <cfqueryparam value="#variables.QTY#" cfsqltype="cf_sql_varchar">,
                           
          WHERE  keyid = <cfqueryparam cfsqltype="cf_sql_integer" value="#variables.keyID#">
       </cfquery>

   </cfloop>      
</cfif>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38338507
Oops.  Change this line:
 <cfoutput><input type="hidden" value="#results.recordCount#"></cfoutput>

To this:
 <cfoutput><input type="hidden" name="numOfFields" value="#results.recordCount#"></cfoutput>


Also, "DATE" is often a reserved word in db's. So it's not a good choice for a column name.  If that's the real name of the column you may have to escape it with square brackets    
                 <!--- for MS SQL and Access ---->
               ie UPDATE Table SET [Date]  = .. etc....
0
 

Author Comment

by:DJPr0
ID: 38338726
Thanks, works on 1 record, but when I try to update 2 records I receive the following error:

 Element keyid_1 is undefined in a Java object of type class coldfusion.filter.FormScope.
 

596 :    <cfloop from="1" to="#form.numOfFields#" index="counter">
597 :        <!--- extract values of form fields --->
598 :        <cfset variables.keyID   = FORM["keyid_"& counter]>
599 :        <cfset variables.QTY = FORM["qty_"& counter]>
600 :        <cfset variables.Invoice = FORM["invoice_"& counter]>

Also when all records are being shown - the user can choose which ones to update - I.E. not all records with an input box will have a value to update. When updating one record which multiple records being shown, I receive this error:
 The value 9,9,9,9,9,9,9,9,9 cannot be converted to a number.
 
The error occurred in line 596

594 : <cfif structKeyExists(form, "save")>
595 :
596 :    <cfloop from="1" to="#form.numOfFields#" index="counter">
597 :        <!--- extract values of form fields --->
598 :        <cfset variables.keyID   = FORM["keyid_"& counter]>


Thanks!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38338784
>   The value 9,9,9,9,9,9,9,9,9 cannot be converted to a number.

That shouldn't be possible if you're really naming the fields like this:

         <cfinput name="keyid_#currentRow#"  ....>

Something about your actual code is different than my example. Can you post your real FORM code? I need to see the whole thing.

     >  The user can choose which ones to update - I.E. not all records with
     > an input box will have a value to update.

Not sure what that means...  But when post your new form code, maybe it'll become clear.
0
 

Author Comment

by:DJPr0
ID: 38338876
Here is the code, not including the search section - let me know if you need that part too:
Check out the site here: http://24.63.53.42/Search.cfm
The save button is also a problem - would like to see one button floating on the right.  



  <cfoutput query="Results">  
 
   
  <cfform action="/search.cfm" method="Post">  
 

       
   
 <!-- Main Table(You can define padding accordingly) -->
<table width="100%" border="0" cellspacing="0" cellpadding="4" align="center">

<tr>
  <td width="100%">

<!-- Table on left side -->

<table width="80%" border="0" cellspacing="0"  align="right" cellpadding="1" table style="border:4px solid black;">
<tr bgcolor="##336699">
 <td width="20%"><font size="2" face="Arial" color="White"><strong>Ship</strong></font></td>
    <td width="10%"><font size="2" face="Arial" color="White"><strong>Project</strong></font></td>
    <td width="10%"><font size="2" face="Arial" color="White"><strong>REQ</strong></font></td>
    <td width="20%"><font size="2" face="Arial" color="White"><strong>Vendor</strong></font></td>
</tr>
<tr>
<td>#SHIP#</td>
    <td>#PROJECT#</td>
    <td>#REQUIC#</td>
    <td>#VENDOR#</td>
</tr>
<tr>
<tr bgcolor="##336699">
    <td><font size="2" face="Arial" color="White"><strong>Contract</strong></font></td>
    <td><font size="2" face="Arial" color="White"><strong>DISP</strong></font></td>
    <td><font size="2" face="Arial" color="White"><strong>Find Number</strong></font></td>
    <td><font size="2" face="Arial" color="White"><strong>Part Description</strong></font></td>
</tr>
<tr>
   <td>#CONTRACT#</td>
    <td>#DISP#</td>
    <td>#FINDNO#</td>
    <td>#DESCRIP#</td>
</tr>
<tr>
   <tr bgcolor="##336699">
    <td><font size="2" face="Arial" color="White"><strong>NSN</strong></font></td>
    <td><font size="2" face="Arial" color="White"><strong>Quantity</strong></font></td>
    <td><font size="2" face="Arial" color="White"><strong>UI</strong></font></td>
    <td><font size="2" face="Arial" color="White"><strong>Cost</strong></font></td>
</tr>
<tr>
  <td>#NSN#</td>
    <td>#QTY#</td>
    <td>#UI#</td>
    <td>#NumberFormat(COST, "$__.__")#</td>
 
             
</tr>
</table>
<!-- END -->
</td>
<td width="100%">

<!-- Table on right side -->
<table width="100" border="0" cellspacing="0" cellpadding="1" align="left" table style="border:4px solid black;">
<tr bgcolor="##336699">

<td><font size="2" face="Arial" color="White"><strong>Date Received</strong></font></td>
    <td><font size="2" face="Arial" color="White"><strong>Qty Received</strong></font></td>
    <td><font size="2" face="Arial" color="White"><strong>Invoice No.</strong></font></td>
</tr>
<tr>
 
  <cfinput name="keyid_#currentRow#" type="hidden" value="#KEYID#" size="15">
    <td><cfinput type="Text" name="REC_DATE_#currentRow#" value="#DATEFORMAT(REC_DATE, "m/d/yyyy")#" size="10"message="Please input Date Received!" validateat="onSubmit" validate="noblanks" required="yes"></td>
    <td> <cfinput type="Text" name="Qty_#currentRow#" value="#REC_QTY#" size="10" message="Please input Quantity Received!" validateat="onSubmit" validate="noblanks" required="yes"></td>
    <td> <cfinput type="text" name="Invoice_#currentRow#" Value="#INVOICE#" size="10" message="Pleas input Invoice Number!" validateat="onsubmit" validate="noblanks" required="yes"></td>
</tr>
</table>

<!-- END -->
</td>

<td width="100%">
<!---<cfinput type = "submit" name="update" class="button" value = "Update"  
                                              onClick="return confirm('Are You Sure You Want To Update This Record?');"></td>--->

<cfinput type = "submit" name="save" class="button" value = "save"  
                                              onClick="return confirm('Are You Sure You Want To Update This Record?');"></td>


</td>


</tr>
</table>
<input type="submit" name="save" value="Save" />

 <br>
 <br>  
<!--- save number of dynamic fields --->
  <cfoutput><input type="hidden" name="numOfFields" value="#results.recordCount#"></cfoutput>
</cfform>

</cfoutput>      


</cfif>

</cfif>





<cfparam name="form.numOfFields" default="0">
<cfif structKeyExists(form, "save")>

   <cfloop from="1" to="#form.numOfFields#" index="counter">
       <!--- extract values of form fields --->
       <cfset variables.keyID   = FORM["keyid_"& counter]>
       <cfset variables.REC_QTY = FORM["qty_"& counter]>
       <cfset variables.Invoice = FORM["invoice_"& counter]>
       <cfset variables.REC_DATE = FORM["REC_DATE_"& counter]>
       <cfquery datasource="support">
          UPDATE table
          SET    REC_DATE  = <cfqueryparam value="#variables.rec_date#" cfsqltype="cf_sql_varchar">,
                 Invoice = <cfqueryparam value="#variables.invoice#" cfsqltype="cf_sql_varchar">,
                 REC_QTY     = <cfqueryparam value="#variables.REC_QTY#" cfsqltype="cf_sql_varchar">
                           
          WHERE  keyid = <cfqueryparam cfsqltype="cf_sql_integer" value="#variables.keyID#">
       </cfquery>

   </cfloop>      
</cfif>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38338969
Right now you're generating one <form> per record.  So clicking "save" only ever updates 1 record.  If you want to update *all* records on the page when they click "save", the <cfform> must be outside the query.

ie
<cfform  method="Post">  
	<cfoutput query="Results">  
       ....
	<table>
	<tr>
		<td><cfinput name="keyid_#currentRow#" type="hidden" value="#KEYID#" size="15"></td>
    	<td><cfinput type="Text" name="REC_DATE_#currentRow#" value="#DATEFORMAT(REC_DATE, "m/d/yyyy")#" size="10"message="Please input Date Received!" validateat="onSubmit" validate="noblanks" required="yes"></td>
    	<td> <cfinput type="Text" name="Qty_#currentRow#" value="#REC_QTY#" size="10" message="Please input Quantity Received!" validateat="onSubmit" validate="noblanks" required="yes"></td>
    	<td> <cfinput type="text" name="Invoice_#currentRow#" Value="#INVOICE#" size="10" message="Pleas input Invoice Number!" validateat="onsubmit" validate="noblanks" required="yes"></td>
	</tr>
	</table>
	<cfinput type = "submit" name="save" class="button" value = "save"  
                                              onClick="return confirm('Are You Sure You Want To Update This Record?');"></td>
	</cfoutput>      
        ....

         <!--- this MUST be OUTSIDE the query loop --->
         <cfoutput><input type="hidden" name="numOfFields" value="#results.recordCount#"></cfoutput>

</cfform>

Open in new window

0
 

Author Comment

by:DJPr0
ID: 38339092
Works! Thanks a million!
One little problem - I want to skip the post/update if no data is inputted.
A user will look at many records but only update a few.

If all records are updated, I receive an error:
POST parameters exceeds the maximum limit.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38339140
Add some validation before the query.  Then only run the update if all values are ok. ie

<cfparam name="form.numOfFields" default="0">
<cfif structKeyExists(form, "save")>
           ....
         <!--- validate all fields are populated w/correct type --->
         <cfset isValidEntry = false>
         <cfif isNumeric(variables.REC_QTY)  
                           and len(variables.INVOICE)
                           and isDate(variables.REC_DATE)>
               <cfset isValidEntry = true>
         </cfif>
                     
         <cfif isValidEntry>
               <cfquery ....> run the UPDATE query </cfquery>
           </cfif>
             .....
</cfif>

If all records are updated, I receive an error:
POST parameters exceeds the maximum limit.


That's a new security setting to prevent hash attacks.  The max fields you can POST is something like 100.  If you want to allow more than that, you have to change the limit in the Admin settings.  I'll see if I can find the link about...
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38339149
0
 

Author Comment

by:DJPr0
ID: 38340786
I think I'm inserting this wrong: (still updates when nothing is inputted)

<cfparam name="form.numOfFields" default="0">
<cfif structKeyExists(form, "save")>

<cfset isValidEntry = false>
         <cfif isNumeric(variables.REC_QTY)  
                           and len(variables.INVOICE)
                           and isDate(variables.REC_DATE)>
               <cfset isValidEntry = true>
         </cfif>
                     
         <cfif isValidEntry>
 

   <cfloop from="1" to="#form.numOfFields#" index="counter">
       <!--- extract values of form fields --->
       <cfset variables.keyID   = FORM["keyid_"& counter]>
       <cfset variables.REC_QTY = FORM["qty_"& counter]>
       <cfset variables.Invoice = FORM["invoice_"& counter]>
       <cfset variables.REC_DATE = FORM["REC_DATE_"& counter]>
       
       
                   
       
       
       
             <cfquery datasource="support">
              UPDATE duein
              SET    REC_DATE  = <cfqueryparam value="#variables.rec_date#" cfsqltype="cf_sql_varchar">,
                     Invoice = <cfqueryparam value="#variables.invoice#" cfsqltype="cf_sql_varchar">,
                     REC_QTY     = <cfqueryparam value="#variables.REC_QTY#" cfsqltype="cf_sql_varchar">
                           
              WHERE  keyid = <cfqueryparam cfsqltype="cf_sql_integer" value="#variables.keyID#">
             </cfquery>
         
    </cfloop>      
</cfif>
</cfif>
0
 

Author Comment

by:DJPr0
ID: 38340806
I think I'm inserting it wrong: (still updates if no data is inputted)


cfparam name="form.numOfFields" default="0">
<cfif structKeyExists(form, "save")>

<cfset isValidEntry = false>
         <cfif isNumeric(variables.REC_QTY)  
                           and len(variables.INVOICE)
                           and isDate(variables.REC_DATE)>
               <cfset isValidEntry = true>
         </cfif>
                     
         <cfif isValidEntry>
 

   <cfloop from="1" to="#form.numOfFields#" index="counter">
       <!--- extract values of form fields --->
       <cfset variables.keyID   = FORM["keyid_"& counter]>
       <cfset variables.REC_QTY = FORM["qty_"& counter]>
       <cfset variables.Invoice = FORM["invoice_"& counter]>
       <cfset variables.REC_DATE = FORM["REC_DATE_"& counter]>
       
       
                   
       
       
       
             <cfquery datasource="support">
              UPDATE table
              SET    REC_DATE  = <cfqueryparam value="#variables.rec_date#" cfsqltype="cf_sql_varchar">,
                     Invoice = <cfqueryparam value="#variables.invoice#" cfsqltype="cf_sql_varchar">,
                     REC_QTY     = <cfqueryparam value="#variables.REC_QTY#" cfsqltype="cf_sql_varchar">
                           
              WHERE  keyid = <cfqueryparam cfsqltype="cf_sql_integer" value="#variables.keyID#">
             </cfquery>
         
    </cfloop>      
</cfif>
</cfif>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38341889
You need to validate each set of fields, so the validation code needs to be inside the loop, not before it (ie you can't validate variables that don't exist yet.).

<cfparam name="form.numOfFields" default="0">
<cfif structKeyExists(form, "save")>
      <cfloop from="1" to="#form.numOfFields#" index="counter">
             
             <!--- first you need get the values you want to validate ... --->
            <cfset variables.keyID   = FORM["keyid_"& counter]>
            <cfset variables.REC_QTY = FORM["qty_"& counter]>
            <cfset variables.Invoice = FORM["invoice_"& counter]>
            <cfset variables.REC_DATE = FORM["REC_DATE_"& counter]>

             <!--- now that you've got the values .. you can validate them --->
             <cfset isEverythingValidated = false>
              <cfif isNumeric(variables.REC_QTY)  
                           and len(variables.INVOICE)
                           and isDate(variables.REC_DATE)>
                      <cfset isEverythingValidated = true>
             </cfif>      
                   
       
             <!--- if the values are all good, go ahead an do the UPDATE --->
             <cfif isEverythingValidated>
                      ... everything's good so run your here
                    <cfquery datasource="support">
                              UPDATE table  .... etc.....
                    </cfquery>
             </cfif>
         
    </cfloop>      
</cfif>
0
 

Author Comment

by:DJPr0
ID: 38342185
Still doesn't work - when I click submit with no data it updates all records shown and I receive the "parameters reached the maximum limit".

Code:



<cfparam name="form.numOfFields" default="0">
<cfif structKeyExists(form, "save")>


 

   <cfloop from="1" to="#form.numOfFields#" index="counter">
       <!--- extract values of form fields --->
       <cfset variables.keyID   = FORM["keyid_"& counter]>
       <cfset variables.REC_QTY = FORM["qty_"& counter]>
       <cfset variables.Invoice = FORM["invoice_"& counter]>
       <cfset variables.REC_DATE = FORM["REC_DATE_"& counter]>
       
       
       
       <cfset isEverythingValidated = false>
              <cfif isNumeric(variables.REC_QTY)  
                           and len(variables.INVOICE)
                           and isDate(variables.REC_DATE)>
                      <cfset isEverythingValidated = true>
               </cfif>      
                 
                 
                   
       
             <!--- if the values are all good, go ahead an do the UPDATE --->
             <cfif isEverythingValidated>
       
       
             <cfquery datasource="support">
              UPDATE table
              SET    REC_DATE  = <cfqueryparam value="#variables.rec_date#" cfsqltype="cf_sql_varchar">,
                     Invoice = <cfqueryparam value="#variables.invoice#" cfsqltype="cf_sql_varchar">,
                     REC_QTY     = <cfqueryparam value="#variables.REC_QTY#" cfsqltype="cf_sql_varchar">
                           
              WHERE  keyid = <cfqueryparam cfsqltype="cf_sql_integer" value="#variables.keyID#">
             </cfquery>
          </cfif>
    </cfloop>      
</cfif>
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 38342484
Still doesn't work - when I click submit with no data it updates all records

That's really not possible if you've implemented the code as described above.  Notice if you test the validation with empty values it always skips the update? So something must be different in your actual code. Maybe you're still using the old code (?)

Test it with empty values ...
<cfset FORM.keyid_1 = "">
<cfset FORM.qty_1 = "">
<cfset FORM.invoice_1 = "">
<cfset FORM.REC_DATE_1 = "">
<cfset FORM.numOfFields = 1>
   
<cfparam name="form.numOfFields" default="0">
<cfif structKeyExists(form, "save")>
	<cfloop from="1" to="#form.numOfFields#" index="counter">
       <!--- extract values of form fields --->
       <cfset variables.keyID   = FORM["keyid_"& counter]>
       <cfset variables.REC_QTY = FORM["qty_"& counter]>
       <cfset variables.Invoice = FORM["invoice_"& counter]>
       <cfset variables.REC_DATE = FORM["REC_DATE_"& counter]>
       
       
       
       <cfset isEverythingValidated = false>
	   <cfif isNumeric(variables.REC_QTY)  
        		and len(variables.INVOICE)
                and isDate(variables.REC_DATE)>
                <cfset isEverythingValidated = true>
		</cfif>      

		<cfif isEverythingValidated>
       		RUN UPDATE
		<cfelse>
			SKIP UPDATE
		</cfif>
    </cfloop>      
</cfif> 

Open in new window


       shown and I receive the "parameters reached the maximum limit"

Yeah, I explained the cause of that error and how to fix it earlier. You must have missed it.
0
 

Author Comment

by:DJPr0
ID: 38343647
OK, I think I got it.
I thought the exceed error was connected with the update.

Is it possible to not scoop the variables that don't have any data to avoid the exceed limit, rather than changing the admin settings?

Another option is to limit the records being shown is pagination the best way?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38343705
Is it possible to not scoop the variables that don't have any data
           to avoid the exceed limit, rather than changing the admin settings?


Unfortunately no because the error is thrown before your code even runs.  When a user submits your form,  the request is intercepted by the CF servlet which pre-processes the data.  If it finds too many FORM parameters, it stops right there and generates an error. So your cfm script is never even called.  

      Another option is to limit the records being shown is pagination the best way?

It's probably the only way - if you can't modify the admin settings.
0
 

Author Comment

by:DJPr0
ID: 38343716
Thanks _agx_, you are the code King!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.
Suggested Courses

862 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