I am submitting values to MS SQL where I set the value in the CFQUERY using
databasefield = <cfqueryparam value="#TheValueHere#" cfsqltype="CF_SQL_DECIMAL">
The database is set to DECIMAL(8,2)
If I manually type in a value into the database such as 1.5 it works fine
If I use the cfqueryparam above, and post 1.5, it rounds to 2, so that's not right
I removed the cfqueryparam, and again submitted the valkue of 1.5 in the variable, and it worked fine, I end up with 1.5 in the database
databasefield = #TheValueHere#
Would anybody have any idea why using CFQUERYPARAM DECIMAL is causing it to round?
Copy link to clipboard
I can answer my own question, but may as well leave the answer here
I need to add scale = 2
<cfqueryparam value="#TheValueHere#" cfsqltype="CF_SQL_DECIMAL" scale="2">
Glad you found the solution. FWIW, it wasn't cf doing the rounding, but sql server. See the discussion of such rounding at https://learn.microsoft.com/en-us/sql/t-sql/data-types/decimal-and-numeric-transact-sql?view=sql-ser...
As long as I put something within range I wouldn't have expected a round. I have it set at DECIMAL(8,2), if I put in 12.34 (manually) I get 12.34 . If I use CFQUERY to add 12.34 it also works, however if I used CFQUERYPARAM with the same value and I missed off the scale=2 then it appears something bad happens within CF. It's all good now I have the scale, I just need to make sure I never forget!