Solved

Updating multiple records using cfloop

Posted on 2011-02-24
9
518 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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
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 …
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now