Solved

Updating multiple records using cfloop

Posted on 2011-02-24
9
528 Views
Last Modified: 2013-12-24
I'm trying to update multiple records in my MySQL db using this form:

 <cfloop query="Selectedartworknew">
<input name="OrderID" type="text" size="5" maxlength="5" value="<cfoutput>#OrderID#</cfoutput>">
                        <input name="AutoArtID" type="hidden" id="AutoArtID" value="<cfoutput>#AutoArtID#</cfoutput>" size="5" maxlength="5">

</cfloop>

and this update sql:

<cfparam name="form.AutoArtID" default="1">
<cfparam name="form.ArtistID" default="1">
<cfparam name="form.OrderID" default="1">

<CFLOOP INDEX=Counter FROM=1 TO=#ListLen(AutoArtID)#>
  <CFQUERY NAME="Updateexbuildernew" DATASOURCE="Ikonltd">
      UPDATE artworknew
      SET <cfif ListLen(OrderID) Gte Counter>
      OrderID=#ListGetAt(OrderID,Counter)#
      </cfif>
      WHERE AutoArtID=#ListGetAt(AutoArtID,Counter)#
  </CFQUERY>
</CFLOOP>

if i fill out all of the OrderID form fields, my db records get updated. if i leave any blank, i get an error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE AutoArtID=2' at line 3
 
The error occurred in D:\gregikon\www\newsite\admin\artists\priority3.cfm: line 12

10 :       OrderID=#ListGetAt(OrderID,Counter)#
11 :       </cfif>
12 :       WHERE AutoArtID=#ListGetAt(AutoArtID,Counter)#
13 :   </CFQUERY>
14 : </CFLOOP>

VENDORERRORCODE         1064
SQLSTATE         42000
SQL          UPDATE artworknew SET WHERE AutoArtID=2
DATASOURCE         Ikonltd
Resources:
0
Comment
Question by:phillystyle123
  • 4
  • 3
  • 2
9 Comments
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 500 total points
ID: 34975994
Blank/missing elements are always a problem with list functions.  Name the fields uniquely (OrderID_1, OrderID_2, etc...) and you won't have that problem

FORM
....
<cfoutput query="Selectedartworknew">
   <input name="OrderID_#currentRow#" value="#OrderID#" ...>
   <input name="AutoArtID_#currentRow#" value="#AutoArtID#" ...>
</cfoutput>

<cfoutput>
<input type="hidden" name="totalFields" value="#Selectedartworknew.recordCount#">
</cfoutput>


ACTION PAGE
<cfparam name="form.totalFields" default="0">
<CFLOOP FROM="1" TO="#form.totalFields#" INDEX="Counter">
    <cfset variables.OrderID = FORM["OrderID_"& counter]>
    <cfset variables.AutoArtID= FORM["AutoArtID_"& counter]>

  <CFQUERY NAME="Updateexbuildernew" DATASOURCE="Ikonltd">
      UPDATE artworknew
      SET       OrderID = <cfqueryparam value="#variables.OrderID#" cfsqltype="cf_sql_integer">
      WHERE AutoArtID=<cfqueryparam value="#variables.AutoArtID#" cfsqltype="cf_sql_integer">
  </CFQUERY>
</CFLOOP>

Open in new window

0
 

Author Comment

by:phillystyle123
ID: 34976315
i'm getting this error on the action page:

nvalid data '' for CFSQLTYPE CF_SQL_INTEGER.
 
The error occurred in D:\gregikon\www\newsite\admin\artists\priority3.cfm: line 14

12 :       UPDATE artworknew
13 :       SET       OrderID = <cfqueryparam value="#variables.OrderID#" cfsqltype="cf_sql_integer">
14 :       WHERE AutoArtID=<cfqueryparam value="#variables.AutoArtID#" cfsqltype="cf_sql_integer">
15 :   </CFQUERY>
16 : </CFLOOP>
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 500 total points
ID: 34976344
What are the data types of your columns? I assumed they were integers. But you may need to change them.  For example, if the columns are varchar's use

   <cfqueryparam value="#variables.OrderID#" cfsqltype="cf_sql_varchar">

instead of

<cfqueryparam value="#variables.OrderID#" cfsqltype="cf_sql_integer">
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 34976678
>> i'm getting this error on the action page:

invalid data '' for CFSQLTYPE CF_SQL_INTEGER.


The issue here is that '' is passed, so you can check for it and if it is NOT NULL then do your query, copying _agx_ code, just adding cfif before query ...

<cfparam name="form.totalFields" default="0">
<CFLOOP FROM="1" TO="#form.totalFields#" INDEX="Counter">
    <cfset variables.OrderID = FORM["OrderID_"& counter]>
    <cfset variables.AutoArtID= FORM["AutoArtID_"& counter]>

   <cfif variables.OrderID NEQ ''>

  <CFQUERY NAME="Updateexbuildernew" DATASOURCE="Ikonltd">
      UPDATE artworknew
      SET       OrderID = <cfqueryparam value="#variables.OrderID#" cfsqltype="cf_sql_integer">
      WHERE AutoArtID=<cfqueryparam value="#variables.AutoArtID#" cfsqltype="cf_sql_integer">
  </CFQUERY>

  </cfif>
</CFLOOP> 

Open in new window



You can also use NULL is cfqueryparam and add VAL to the ID

<cfparam name="form.totalFields" default="0">
<CFLOOP FROM="1" TO="#form.totalFields#" INDEX="Counter">
    <cfset variables.OrderID = FORM["OrderID_"& counter]>
    <cfset variables.AutoArtID= FORM["AutoArtID_"& counter]>

  <CFQUERY NAME="Updateexbuildernew" DATASOURCE="Ikonltd">
      UPDATE artworknew
      SET       OrderID = <cfqueryparam value="#val(variables.OrderID)#" cfsqltype="cf_sql_integer" null="yes">
      WHERE AutoArtID=<cfqueryparam value="#val(variables.AutoArtID)#" cfsqltype="cf_sql_integer" null="yes">
  </CFQUERY>
</CFLOOP> 

Open in new window



0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 34976830
<cfqueryparam value="#val(variables.OrderID)#" cfsqltype="cf_sql_integer" null="yes">

Actually that would *always* use NULL which probably isn't what should happen.

The correct answer depends on the data type and you what you want to happen if the field is empty.  Say you want to insert a NULL instead, then use a conditional check with cfqueryparam's "null" attribute. This will insert the #OrderID# if it's a valid number and NULL if it's not.

....
<CFQUERY NAME="Updateexbuildernew" DATASOURCE="Ikonltd">
      UPDATE artworknew
      SET       OrderID = <cfqueryparam value="#variables.OrderID#" cfsqltype="cf_sql_integer" null="#not IsNumeric(variables.OrderID)#">
      WHERE AutoArtID=<cfqueryparam value="#variables.AutoArtID#" cfsqltype="cf_sql_integer">
  </CFQUERY>
....

Open in new window


0
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 34977007
>> Actually that would *always* use NULL which probably isn't what should happen.

yes, thanks for pointing, I think here the error is because of empty string is trying to be inserted into an int column,

Another way of doing it

<cfparam name="form.totalFields" default="0">
<CFLOOP FROM="1" TO="#form.totalFields#" INDEX="Counter">
    <cfset variables.OrderID = FORM["OrderID_"& counter]>
    <cfset variables.AutoArtID= FORM["AutoArtID_"& counter]>

  <CFQUERY NAME="Updateexbuildernew" DATASOURCE="Ikonltd">
      UPDATE artworknew
      SET       OrderID = <cfif IsValid("integer", variables.OrderID) AND OrderID.OrderID NEQ 0>
	   	    <cfqueryparam value="#variables.OrderID#" cfsqltype="cf_sql_integer">
		<cfelse>
		    <cfqueryparam null="true" cfsqltype="cf_sql_integer">
		</cfif>

      WHERE AutoArtID=<cfqueryparam value="#val(variables.AutoArtID)#" cfsqltype="cf_sql_integer" null="yes">
  </CFQUERY>
</CFLOOP>  

Open in new window

0
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 34977009
Sorry .. below is the correct one

<cfparam name="form.totalFields" default="0">
<CFLOOP FROM="1" TO="#form.totalFields#" INDEX="Counter">
    <cfset variables.OrderID = FORM["OrderID_"& counter]>
    <cfset variables.AutoArtID= FORM["AutoArtID_"& counter]>

  <CFQUERY NAME="Updateexbuildernew" DATASOURCE="Ikonltd">
      UPDATE artworknew
      SET       OrderID = <cfif IsValid("integer", variables.OrderID) AND OrderID.OrderID NEQ 0>
	   	    <cfqueryparam value="#variables.OrderID#" cfsqltype="cf_sql_integer">
		<cfelse>
		    <cfqueryparam null="true" cfsqltype="cf_sql_integer">
		</cfif>

      WHERE AutoArtID=<cfqueryparam value="#variables.AutoArtID#" cfsqltype="cf_sql_integer">
  </CFQUERY>
</CFLOOP>

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 34977117
Personally I prefer the single line syntax over the cfif / cfelse. But either works

       <cfqueryparam ... null="#not IsNumeric(variables.OrderID)#">
0
 

Author Closing Comment

by:phillystyle123
ID: 34981927
perfection -thanks for the help!
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

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…
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

756 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