Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 532
  • Last Modified:

Updating multiple records using cfloop

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
phillystyle123
Asked:
phillystyle123
  • 4
  • 3
  • 2
3 Solutions
 
_agx_Commented:
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
 
phillystyle123Author Commented:
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
 
_agx_Commented:
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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
Brijesh ChauhanStaff IT EngineerCommented:
>> 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
 
_agx_Commented:
<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
 
Brijesh ChauhanStaff IT EngineerCommented:
>> 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
 
Brijesh ChauhanStaff IT EngineerCommented:
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
 
_agx_Commented:
Personally I prefer the single line syntax over the cfif / cfelse. But either works

       <cfqueryparam ... null="#not IsNumeric(variables.OrderID)#">
0
 
phillystyle123Author Commented:
perfection -thanks for the help!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now