We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now



JohnLucania asked
Medium Priority
Last Modified: 2013-12-24
Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 2: Incorrect syntax near '000.00'.  

73 :                         UPDATE Donations
74 :                               Set Amount = #dollarformat(rereplace(form.amount,"(\$)|(,)","","all"))#
75 :                         WHERE pledgeID = #getPledge.pledgeID# AND donationID = #getDonations.DOnationID#
76 :                   </cfquery>
77 :                   

SQL    UPDATE Donations Set Amount = $3,000.00 WHERE pledgeID = 219 AND donationID = 11676  

How do you correct this?
Watch Question

dollarFormat takes a number string and formats is putting the $ , and . in the number.

try using number format instead.

UPDATE Donations
    Set Amount = #numberFormat(rereplace(form.amount,"(\$)|(,)","","all"), "9999999.00")#
    WHERE pledgeID = #getPledge.pledgeID# AND donationID = #getDonations.DOnationID#

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
I've had better luck with:

Set Amount = <cfqueryparam value="#numberFormat(REReplace(form.amount,"[^0-9.]","","all"), "9999999.00")#" CFSQLType="CF_SQL_DECIMAL" />
WHERE pledgeID = <cfqueryparam value="#getPledge.pledgeID#" CFSQLType="CF_SQL_INTEGER" />
AND donationID = <cfqueryparam value="#getDonations.DOnationID#" CFSQLType="CF_SQL_INTEGER" />

But rob's answer is essentially what you'll want to do.


That is great!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.