Search code examples
htmlt-sqlcoldfusioncoldfusion-10

decimal getting truncated in tsql

Im new to coldfusion and tsql, so if this is a silly mistake, I am sorry for wasting your time. I have searched for "decimal truncation, tsql, cold fusion". And any of those in combination.

I have a form that submits a decimal, yet the DB is recording the data as an integer. Meaning, the form has the value of 2.2, the DB stores 2.

The column specs per my DB editor:

COLUMN_NAME      DATA_TYPE   DATA_PRECISION  DATA_SCALE  NULLABLE  DATA_DEFAULT 
promofactor      decimal          15              5           NO        ((0))

The HTML form:

<input type="number" name="promoFactor" class="form-control" id="promoFactor" placeholder="Must be a number" required step="0.0001">

The cold fusion query:

DECLARE @promofactor decimal(15,5)  = <cfqueryparam cfsqltype="cf_sql_decimal"  value="#form.promoFactor#"/>
Solution

  • for cf_sql_decimal cfsqltypes, you have to supply the scale attribute to cfqueryparam.

    If you want 5 decimal places: 

    <cfqueryparam type="cf_sql_decimal" value="#form.promofactor#" scale="5" />

    docs: https://cfdocs.org/cfqueryparam