CODEWITHSUNDEEP
sql-servercoldfusion
Evik James
Evik James

Reputation: 10473

Why is ColdFusion changing my values when I use <cfqueryparam>?

I am using ColdFusion 9.0.1 and SQL Server 2005.

The column in my database is name Points. It is set up as numeric(6,1).

When I write a query in SQL Server Management Studio to drop in values like 1.2 or 5.5, the numbers store correctly. When I drop in 1.23 it rounds to 1.2. When I drop in 5.55, it rounds to 5.6. This is exactly what I expect.

UPDATE TableName
SET Points = 1.2

When I pass my values to the CFC and DO NOT use CFQUERYPARAM, I get the exact results as described above.

When I pass my values to the CFC uses CFQUERYPARAM, I do not get what I expect. When I drop in a number like 1.2 or 5.5, the value gets rounded to 1.0 or 6.0.

UPDATE TableName
SET Points = <cfqueryparam type="CF_SQL_DECIMAL" value="#Points#">

I have changed the CFSQLTYPE to CF_SQL_NUMERIC and CF_SQL_DECIMAL. Still, I cannot get the numbers to save correctly.

When I run the query below in my CFC, the number gets stored as 1.0.

UPDATE TableName
SET Points = <cfqueryparam type="CF_SQL_DECIMAL" value="1.23">

Can you help me figure out why this isn't behaving as I expect it to?

Upvotes: 1

Views: 1081

Answers (2)

Dan Short
Dan Short

Reputation: 9616

You need to add the scale attribute to your cfqueryparam to let it know how many decimal places to send. Add scale="2" and you should be good to go.

Upvotes: 9

Evik James
Evik James

Reputation: 10473

I figured it out. It's all about SCALE. I need to add the scale attribute to my CFQUERYPARAM when I use CF_SQL_NUMERIC and CF_SQL_DECIMAL.

Upvotes: 0

Related Questions