Converting from data type varchar to data type money

[schaudry]

Hi all,

Tearing my hair out trying to figure this out. If anyone can provide any help
i would greatly appreciate it. When I try to do an insert into my MS SQL
Database, keep getting following error....

[Macromedia][SQLServer JDBC Driver][SQLServer]Disallowed implicit conversion
from data type varchar to data type money, table
'05NeoCon.dbo.RegistrationT', column 'AmtPaid'. Use the
CONVERT function to run this query.

The error occurred in D:\mmpi\showreg\neoconseminar\scout\insertdata.cfm : line
21
Called from D:\mmpi\showreg\neoconseminar\scout\insertdata.cfm : line 9
Called from D:\mmpi\showreg\neoconseminar\scout\insertdata.cfm : line 7
Called from D:\mmpi\showreg\neoconseminar\scout\insertdata.cfm : line 1
Called from D:\mmpi\showreg\neoconseminar\scout\insertdata.cfm : line 21
Called from D:\mmpi\showreg\neoconseminar\scout\insertdata.cfm : line 9
Called from D:\mmpi\showreg\neoconseminar\scout\insertdata.cfm : line 7
Called from D:\mmpi\showreg\neoconseminar\scout\insertdata.cfm : line 1

19 : '<cfloop from="1" to="#arrayLen(session.semcart)#"
index="i">#DollarFormat('#session.semcart.ActCost# ')##chr(13)##chr(10)#</cfloop>
',
20 : '<cfloop from="1" to="#arrayLen(session.semcart)#"
index="i">#rtrim('#session.semcart.ActTime#')##chr (13)##chr(10)#</cfloop>',
21 :
#DollarFormat('#session.totalprice#')#,'#session.S pamPhone#','#session.SpamFax#'
,'#session.SpamEmail#','#session.SpamMail#','#sess ion.Spam3rdParty#');
22 : </cfquery>
23 :


First off, can someone please explain why i'm getting this error? And also,
can you please tell me how I would convert #session.totalprice# from data type
varchar to data type money in order to make SQL happy? Thanks in advance for
your help.

Salman

[Mountain Lover]

When you use dollarformat it adds the $ sign, which makes the variable
no longer a number (or money type). Don't use dollarformat.

HTH

--
Tim Carley
[url]www.recfusion.com[/url]
[email]info@NOSPAMINGrecfusion.com[/email]

[mkane1]

Before the query is run, I recommend validating the value of
session.totalprice, i.e. use IsNumeric(session.totalprice) to ensure the value
is acceptable. In SQL Server, you can convert a number to datatype=money using
the CAST function as follows:


CAST(#session.totalprice# AS money)

[AnXiao]

We use Dollarformat On Output only but in a case where output becomes input we
use
Replace(Replace(var, ',',''), '$', '') This solution works to format data
prior to sending it to a database some database engines will have better
techniques for converting the data

[schaudry]

How would I write the query in coldfusion to convert it? I tried the following
but that doesn't work...

<cfquery name="convertTotalPrice" datasource="05NeoCon">
CAST(#session.totalprice# AS money)
</cfquery>

[mkane1]

Originally posted by: schaudry
How would I write the query in coldfusion to convert it? I tried the following
but that doesn't work...


Soryy. Your original post did not have the full query, at least not that I
could see. Here's how to do it in an insert query, assuming a table named
Table1 where column "Zmoney" is of datatype money, and that session.totalprice
has been validated to be a number:



INSERT QUERY:
<cfquery name="DoInsert" datasource="whatever">
insert into Table1(zmoney, notmoney)
select cast(#session.totalprice# AS money), 'zippy'
</cfquery>


You can also use convert, the syntax is similar but rearranged:
<cfquery name="DoInsert" datasource="whatever">
insert into Table1(zmoney, notmoney)
select convert(money, #session.totalprice#), 'zippy'
</cfquery>

[schaudry]

This is my query, what you wrote doesn't seem to work. I tried Replace, now its
giving me the error:

[Macromedia][SQLServer JDBC Driver][SQLServer]Line 15: Incorrect syntax near
&apos;re&apos;.



<cfquery name="insertRegistrationT" datasource="05NeoCon">
INSERT INTO
RegistrationT(CustomerNumber,DateLastModified,Firs tName,LastName,NickName,Title,
Organization,Address1,Address2,City,State,PostalCo de,Country,BusPhone,Fax,Email,
Associations,ProfType,Source,DateAttended,AmtPaid, TixListing,Tix01,Tix02,Tix03,T
ix04,Tix06,Tix12,SemFees,SpamPhone,SpamFax,SpamEma il,SpamMail,Spam3rdParty)
VALUES
('OS','#DateLastModified#','#session.FirstName#',' #session.LastName#','#session.
FirstName#','#session.Title#','#session.Organizati on#','#session.Address1#','#se
ssion.Address2#','#session.City#','#session.State# ','#session.PostalCode#','#ses
sion.Country#','#session.Phone#','#session.Fax#',' #session.Email#','#session.use
r1#','#session.user2#','05NEOSEM',#DateAttended#,# Replace(Replace('#session.tota
lprice#', ',',''), '$', '')#,
'<cfloop from="1" to="#arrayLen(session.semcart)#"
index="i">#session.semcart.ActID# </cfloop>',
'<cfloop from="1" to="#arrayLen(session.semcart)#"
index="i">#rtrim('#session.semcart.ActID#')##chr(1 3)##chr(10)#</cfloop>',
'<cfloop from="1" to="#arrayLen(session.semcart)#" index="i"><cfif
Len('#session.semcart.ActDescr#') LT
24>#session.semcart.ActDescr##chr(46)##chr(46)##ch r(46)##chr(13)##chr(10)#<cfels
e>#RemoveChars('#session.semcart.ActDescr#',24,30) ##chr(46)##chr(46)##chr(46)##c
hr(13)##chr(10)#</cfif></cfloop>',
'<cfloop from="1" to="#arrayLen(session.semcart)#"
index="i">#dateformat('#session.semcart.ActDate#', 'm/dd/yyyy')##chr(13)##chr(10)
#</cfloop>',
'<cfloop from="1" to="#arrayLen(session.semcart)#"
index="i">#rtrim('#session.semcart.ActRoom#')##chr (13)##chr(10)#</cfloop>',
'<cfloop from="1" to="#arrayLen(session.semcart)#"
index="i">#DollarFormat('#session.semcart.ActCost# ')##chr(13)##chr(10)#</cfloop>
',
'<cfloop from="1" to="#arrayLen(session.semcart)#"
index="i">#rtrim('#session.semcart.ActTime#')##chr (13)##chr(10)#</cfloop>',
#Replace(Replace('#session.totalprice#', ',',''), '$',
'')#,'#session.SpamPhone#','#session.SpamFax#','#s ession.SpamEmail#','#session.S
pamMail#','#session.Spam3rdParty#');
</cfquery>