Exit
  • Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
  • 한국 커뮤니티
0

Why is cfsqltype="CF_SQL_DECIMAL" rounding values

Enthusiast ,
Oct 07, 2023 Oct 07, 2023

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?

 

TOPICS
Advanced techniques , Database access
664
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Enthusiast , Oct 07, 2023 Oct 07, 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">

Translate
Enthusiast ,
Oct 07, 2023 Oct 07, 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">

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 08, 2023 Oct 08, 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-ser...


/Charlie (troubleshooter, carehart. org)
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Oct 13, 2023 Oct 13, 2023
LATEST

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!

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources