Money datatype

[alecken]

I'm having a very hard time doing a simple insert and update for columns with NUMBER(38,2) and NUMBER(10,0) datatypes on Oracle.

These columns represent Cash/Donation and they used to be set as MONEY datatype and TINYINT datatype respectively (When it was on non-Oracle DB)

I used ColdFusion cf_sql_numeric, cf_sql_decimal and cf_sql_money but all gave me error such as:

The following information is meant for the website developer for debugging purposes.

Error Occurred While Processing Request

Invalid data 7,261.00 for CFSQLTYPE CF_SQL_NUMERIC.

OR

The following information is meant for the website developer for debugging purposes.

Error Occurred While Processing Request

Invalid data 7,261.00 for CFSQLTYPE CF_SQL_Double.

When I run the following:

<CFSET Cash = "#Replace(Form.Cash, "$", "", "ALL")#"> 

<CFSET CFM = "#Replace(Form.CFM, "$", "", "ALL")#">

UPDATE Tbl_Gift

SET FiscalYear =

<cfqueryparam cfsqltype="cf_sql_varchar" value="#Form.FYR#">, 

CashAmount =

<cfqueryparam cfsqltype="cf_sql_numeric" value="#Cash#"> ,

DonationAmount =  

<cfqueryparam cfsqltype="cf_sql_numeric" value="#CFM#">

etc.

I ran out of datatype to use. What else can I use for Oracle datatype NUMBER(38,2) and NUMBER(10,0) ? Please help

[JMF3 · Accepted solution]

It's the comma in the number that's causing the problem within the

CFQUERYPARAM. Rem the commas before you save to the database and you

should be fine.

[Carl Von Stetten]

alecken,

Try using the "scale" paramenter in your <cfqueryparam> tags and the "decimal" cfsqltype:

<cfqueryparam cfsqltype="decimal" scale="2" value="#Cash#">

Set scale to the number of decimal places you need to use.

According to the Adobe docs (http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7f6f.html), "decimal" corresponds to the Oracle "Number" data type.

HTH,

-Carl V.

[alecken]

I tried using cf_sql_decimal with scale attribute still causing similar error.

Then I get rid of the comma in order to make it work.

Thank you very much for both of you!

New knowledge for me to learn! Thanks!!!