Skip to main content
A
ACS LLC
Inspiring
October 7, 2023
Answered

Why is cfsqltype="CF_SQL_DECIMAL" rounding values

  • October 7, 2023
  • 2 replies
  • 1006 views

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?

 

This topic has been closed for replies.
Correct answer ACS LLC

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

2 replies

Charlie Arehart
Community Expert
Charlie ArehartCommunity Expert
Community Expert
October 8, 2023

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-server-ver16&redirectedfrom=MSDN#converting-decimal-and-numeric-data

/Charlie (troubleshooter, carehart. org)
A
ACS LLCAuthor
Inspiring
October 13, 2023

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!

A
ACS LLCAuthorCorrect answer
Inspiring
October 7, 2023

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

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