Solved

Updating multiple records using cfloop

Posted on 2011-02-24
9
530 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
Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

 
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

Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Question has a verified solution.

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

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 …
What You Need to Know when Searching for a Webhost Provider
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

717 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