asked on

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:

SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

phillystyle123

ASKER

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>

SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brijesh Chauhan

>> 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

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brijesh Chauhan

>> 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

Brijesh Chauhan

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

_agx_

Personally I prefer the single line syntax over the cfif / cfelse. But either works

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

phillystyle123

ASKER

perfection -thanks for the help!