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

Why is cfsqltype="CF_SQL_DECIMAL" rounding values

Enthusiast ,
Oct 07, 2023 Oct 07, 2023

Copy link to clipboard

Copied

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

Views

254

Translate

Translate

Report

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

Votes

Translate

Translate
Enthusiast ,
Oct 07, 2023 Oct 07, 2023

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

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

Copy link to clipboard

Copied

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)

Votes

Translate

Translate

Report

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

Copy link to clipboard

Copied

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!

Votes

Translate

Translate

Report

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
Documentation