Skip to main content
A
alecken
Known Participant
September 10, 2012
Answered

Money datatype

  • September 10, 2012
  • 2 replies
  • 1399 views

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

    This topic has been closed for replies.
    Correct answer JMF3

    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.

    2 replies

    Carl Von Stetten
    Carl Von Stetten
    Legend
    September 10, 2012

    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.

    A
    aleckenAuthor
    Known Participant
    September 10, 2012

    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!!!

    J
    JMF3Correct answer
    Participating Frequently
    September 10, 2012

    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.

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