ColdFusion Update multipe fields in multiple records at once

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

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

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?

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.

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

Select allOpen 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.

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>

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>

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.

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?

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>

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

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.

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>

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

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!

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

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>

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>

Select allOpen in new window

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.

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

Here's the link about increasing the max POST parameters

https://www.experts-exchange.com/questions/27810818/HELP-Wierd-CF-9-02-issue-when-to-many-form-fields-are-posted-in-post-method.html?anchorAnswerId=38240256#a38240256

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>

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>

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>

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>

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?

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.

Thanks _agx_, you are the code King!