Skip to main content
S
slegendre
Inspiring
April 12, 2007
Answered

CONVERT varchar to money

  • April 12, 2007
  • 5 replies
  • 2537 views
Can someone help me with the syntax on how to convert data type varchar to data type money. I'm receiving the following error message:

[Macromedia][SQLServer JDBC Driver][SQLServer]Disallowed implicit conversion from data type varchar to data type money, table 'tableName', column 'columnName'. Use the CONVERT function to run this query.

Any help would be much appreciated! Thanks.
SLL
This topic has been closed for replies.
Correct answer cf_dev2
The error is caused by using single quotes around #form.price#. The single quotes tell sql server to treat #form.price# as a string value (varchar). While sql server can implicitly convert some values from one data type to another it doesn't automatically convert type varchar to type money.

Assuming you've already validated the #form.price# value, either get rid of the single quotes or better use cfqueryparam with the correct cfsqltype. (I think its cf_sql_decimal)

-- this works
update @tblName
set price = 10.00
where id = 1

-- this works
update @tblName
set price = cast('10.00' as money)
where id = 1

-- this doesn't
update @tblName
set price = '10.00'
where id = 1

-- using cfqueryparam
update @tblName
set price = <cfqueryparam value="10.00" cfsqltype="cf_sql_decimal">
where id = 1

5 replies

S
slegendreAuthor
Inspiring
April 12, 2007
Gotcha! Thanks a lot again. I'm sure i'll be posting much more, so look out for me! haha!
S
slegendreAuthor
Inspiring
April 12, 2007
Taking away the single quotes did it! Thank you so much! It feels sooo good!

Problem solved.
SLL
C
cf_dev2
Inspiring
April 12, 2007
As an aside.. don't forget about cfqueryparam. One of its benefits is helping prevent sql injection. Statements like this can result in bigger problems than data type conversions ;-)

update @tblName
set price = #form.price#
where id = 1
S
slegendreAuthor
Inspiring
April 12, 2007
Within my main page I call all the data from a particular table along with a link to edit that particular record. Clicking edit takes you to the "edit.cfm page".

The edit.cfm page displays the data from the particular record you clicked.

When you make changes (or dont make changes) you can update the record by clicking a button that takes you to a processor page. That runs the following code:



<!-- Edit or Update -->
<cfif IsDefined("form.ID")>
<!--Update-->
<cfquery datasource="db_name">
UPDATE dbo.tblName
SET Type='#form.Type#',
Price='#form.Price#',
Category='#form.Category#'
WHERE ID=#form.ID#
</cfquery>
</cfif>



The error that I receive is:
[Macromedia][SQLServer JDBC Driver][SQLServer]Disallowed implicit conversion from data type varchar to data type money, table 'tblName', column 'Price'. Use the CONVERT function to run this query.
C
cf_dev2Correct answer
Inspiring
April 12, 2007
The error is caused by using single quotes around #form.price#. The single quotes tell sql server to treat #form.price# as a string value (varchar). While sql server can implicitly convert some values from one data type to another it doesn't automatically convert type varchar to type money.

Assuming you've already validated the #form.price# value, either get rid of the single quotes or better use cfqueryparam with the correct cfsqltype. (I think its cf_sql_decimal)

-- this works
update @tblName
set price = 10.00
where id = 1

-- this works
update @tblName
set price = cast('10.00' as money)
where id = 1

-- this doesn't
update @tblName
set price = '10.00'
where id = 1

-- using cfqueryparam
update @tblName
set price = <cfqueryparam value="10.00" cfsqltype="cf_sql_decimal">
where id = 1
S
slegendreAuthor
Inspiring
April 12, 2007
Well the database table stores them as money. I've tried to change my code format in CF to "currency" but it still gives me the same error. So I figured i'd have to cast or convert it to make it work. Still learning i'm afraid. I'll take any advice you got to offer =).

I'll try the convert here in a bit and post if it works, Thanks.
C
cf_dev2
Inspiring
April 12, 2007
What is the code and values throwing this error?
C
cf_dev2
Inspiring
April 12, 2007
http://msdn2.microsoft.com/en-us/library/ms187928.aspx

convert(money, '10.00') ... or
cast('10.00' as money)

Just curious.. if you're treating the values as 'money' is there a reason why the column type is varchar?

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices