Solved

Updating multiple records using cfloop

Posted on 2011-02-24
9
529 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
[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
  • 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
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
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 …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

737 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