[cfaussie] RE: NEW batch style update request

Wed, 16 Jun 2004 15:50:25 -0700

Hi i have updated the query
i have some sort of data mismatch that i just can find.
 
price is a data type =number
special hidden and featured are all data type =yes/ no
 
Values:
productIDs= 1,10,11,12,13,31,33,34,35
 
FEATURED_10=1
FEATURED_12=1
FEATURED_33=1
FEATURED_35=1
SPECIAL_1=1
SPECIAL_11=1
SPECIAL_13=1
SPECIAL_35=1

HIDDENPROD_1=1
 
PRICE_1=2
PRICE_10=333
PRICE_11=7
PRICE_12=65
PRICE_13=213
PRICE_31=888
PRICE_33=7
PRICE_34=7
PRICE_35=6699
the code:
////////////////// update script //////////////////////////////
<cfif isdefined("form.updateprice")>
<cfloop list="#Form.ProductIDs#" index="p">
<cfoutput>'#Form["price_#p#"]#'#prefs.dsn#</cfoutput>
 
<CFQUERY NAME="updateprice" DATASOURCE="#prefs.dsn#">
UPDATE     products
set unitprice = #Form["price_#p#"]#
,Featured=<cfif isDefined("Form.Featured_#p#")>1<cfelse>0</cfif>
,special=<cfif isDefined("Form.special_#p#")>1<cfelse>0</cfif>
,hiddenprod=<cfif isDefined("Form.hiddenprod_#p#")>1<cfelse>0</cfif>
WHERE productid = #p#
</CFQUERY>
</cfloop>
</cfif>
 
eror:

Error Diagnostic Information

ODBC Error Code = 22005 (Error in assignment)

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria _expression_.

SQL = "UPDATE products set unitprice = 2 ,Featured=0 ,special=1 ,hiddenprod=1 WHERE productid = 1"

Data Source = "QD_CART15"

 

 
 
Kind Regards
Greg Stone
PH: 0411 787 565

 
----- Original Message -----
Sent: Wednesday, June 16, 2004 2:39 PM
Subject: [cfaussie] RE: NEW batch style update request

Greg,

Will this update SQL allow you to record the un-checking featured, special
and hiddenprod boxes?

I think you would need to set featured etc to 1 if the form field was
defined and 0 otherwise.

i.e.

<cfif isDefined("Form.Featured_#p#")>, Featured=1</cfif>

to

, Featured=<cfif isDefined("Form.Featured_#p#")>1<cfelse>0</cfif>


cheers

David

 
 -----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
[EMAIL PROTECTED]
Sent: Wednesday, 16 June 2004 9:29 AM
To: CFAussie Mailing List
Subject: [cfaussie] RE: NEW batch style update request

Hi steve.
found your snipet and now i understand the method... very clever..
 i did think of that idea just couldnt work out how to match up on the
process page.
now i know the trick... thank you

now to get it working , i am getting a really basic error on the update
query and cant seem to track where the problem might be.



heres the update query
<cfif isdefined("form.updateprice")>
<cfloop list="#Form.ProductIDs#" index="p">

<CFQUERY NAME="updateprice" DATASOURCE="#prefs.dsn#">
UPDATE     products
set unitprice = #Form["price_#p#"]#
<cfif isDefined("Form.Featured_#p#")>, Featured=1</cfif>
<cfif isDefined("Form.special_#p#")>,special=1</cfif>
<cfif isDefined("Form.hiddenprod_#p#")>,hiddenprod=1</cfif>
WHERE productid = #p#
</CFQUERY>
</cfloop>
</cfif>


The error
ODBC Error Code = 22005 (Error in assignment)
[Mirosoft][ODBC Microsoft Access Driver] Data type mismatch in criteria
_expression_.
SQL = "UPDATE products set unitprice = 2.0 ,special=1 ,hiddenprod=1 WHERE
productid = 1"

Kind Regards
Greg Stone
PH: 0411 787 565

 
----- Original Message -----
From: Steve Onnis
To: CFAussie Mailing List
Sent: Tuesday, June 15, 2004 5:49 PM
Subject: [cfaussie] RE: NEW batch style update request


Greg

the code i sent would update it all at once, in the same update statement

check back through it

Steve
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of
[EMAIL PROTECTED]
Sent: Tuesday, June 15, 2004 5:23 PM
To: CFAussie Mailing List
Subject: [cfaussie] RE: NEW batch style update request


Oppps.
what did i miss.
i got so confused on which one to work with and thought your  snipet was the
sweetest for the update price function.
for the checkbox i came up with what i thought was a elegant solution.


This was the most functional for price update ..YES or NO?


<cfloop index="x" from="1" to="#listlen(form.productpartno)#" step="1">
 
<CFQUERY NAME="updateprice" DATASOURCE="#prefs.dsn#">
UPDATE     products
SET        unitprice= #listgetat(form.price,x)#
WHERE   productpartno = '#listgetat(form.productpartno,x)#'
</CFQUERY>

</cfloop>

as for the check boxes i cant think of a way to check if they are ticked or
not.

HMMMM
it works...... but .... back to the drawing board or ....???



Kind Regards
Greg Stone
PH: 0411 787 565

 
----- Original Message -----
From: Steve Onnis
To: CFAussie Mailing List
Sent: Tuesday, June 15, 2004 5:18 PM
Subject: [cfaussie] RE: NEW batch style update request


Sorry greg

but thats sooooooooooooooooooooo yuk
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of
[EMAIL PROTECTED]
Sent: Tuesday, June 15, 2004 4:39 PM
To: CFAussie Mailing List
Subject: [cfaussie] RE: NEW batch style update request


Hi
for all those who contributed i settled on these updates.


<!--- loop over selected fields and update table --->
<cfif isdefined("form.featured")>
<cfloop index='item' list='#form.featured#' delimiters=','>
<CFQUERY NAME="updatefeatured" datasource="#prefs.dsn#" >
UPDATE     products
SET             featured=1
WHERE       (productid = '#item#')
</CFQUERY>
</cfloop>
</cfif>

<cfif isdefined("form.special")>

<cfloop index='item' list='#form.special#' delimiters=','>
<CFQUERY NAME="updatespecial" datasource="#prefs.dsn#" >
UPDATE     products
SET             special=1
WHERE       (productid = '#item#')
</CFQUERY>
</cfloop>
</cfif>

<cfif isdefined("form.hiddenprod")>
<cfloop index='item' list='#form.hiddenprod#' delimiters=','>
<CFQUERY NAME="updatehiddenprod" DATASOURCE="#prefs.dsn#">
UPDATE     products
SET        hiddenprod=1
WHERE       (productid = '#item#')
</CFQUERY>
</cfloop>
</cfif>

<cfloop index="x" from="1" to="#listlen(form.productpartno)#" step="1">
 <CFQUERY NAME="updateprice" DATASOURCE="#prefs.dsn#">
UPDATE     products
SET        unitprice= #listgetat(form.price,x)#
WHERE   productpartno = '#listgetat(form.productpartno,x)#'
</CFQUERY>
</cfloop>


regards greg


 
----- Original Message -----
From: Steve Onnis
To: CFAussie Mailing List
Sent: Tuesday, June 15, 2004 4:29 PM
Subject: [cfaussie] RE: NEW batch style update request


I would recommend against this method

Not very efficient and your going to run into problems especially as your
dealing with checkboxes aswell which unless they are checked wont get passed
with your form and thus wont be available in your list, not to mention is i
check one of the boxes, trying to work out which one actually got checked
would be a nightmare

Steve

---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to
[EMAIL PROTECTED] Aussie Macromedia Developers:
http://lists.daemon.com.au/ ---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to
[EMAIL PROTECTED] Aussie Macromedia Developers:
http://lists.daemon.com.au/
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to
[EMAIL PROTECTED] Aussie Macromedia Developers:
http://lists.daemon.com.au/ ---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to
[EMAIL PROTECTED] Aussie Macromedia Developers:
http://lists.daemon.com.au/
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to
[EMAIL PROTECTED] Aussie Macromedia Developers:
http://lists.daemon.com.au/ ---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to
[EMAIL PROTECTED] Aussie Macromedia Developers:
http://lists.daemon.com.au/
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to
[EMAIL PROTECTED] Aussie Macromedia Developers:
http://lists.daemon.com.au/

---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
Aussie Macromedia Developers: http://lists.daemon.com.au/ ---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED] Aussie Macromedia Developers: http://lists.daemon.com.au/ ---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED] Aussie Macromedia Developers: http://lists.daemon.com.au/ ---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED] Aussie Macromedia Developers: http://lists.daemon.com.au/

Reply via email to