SQL rounding numbers on insert 1

[tigerjade]

I'm not sure what's going on here, but whatever it is, I don't like it. I have an .aspx page that inserts data into a table. The field in question is a decimal field (I've also tried float and numeric, with the same results). When I look at the data that the page says it's sending, I see decimal values, but when the data is inserted into the table, it's rounding everything up. 5.5 is 6, 9.5 is 10, etc.

I'm sending the values as decimal parameters to the stored procedure, and doing a straight insert from there.

SQL:

ALTER PROCEDURE sp_holdConfigurationData
@Current1 decimal
,@Current2 decimal
AS

INSERT INTO ButtonTemp (Current1, Current2) VALUES (@Current1, @Current2)

Snippet of C#:

SqlParameter Current1SubParam = new SqlParameter("@Current1",SqlDbType.Decimal);
Current1SubParam.Value = Current1Sub;
AddToTempCmd.Parameters.Add(Current1SubParam);

Is there another way I can verify that the SP is actually receiving what the C# says it is? I've tried PRINT, but nothing comes out (I'm assuming because it's a decimal value).

Thanks!

tigerjade

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[gmmastros]

Ohhhh my pet peeve again!

Whenever you use decimal (or numeric) you need to specify the precision and scale, otherwise you may get unexpected results.

ALTER PROCEDURE sp_holdConfigurationData
@Current1 decimal[red](10,2)[/red]
,@Current2 decimal[red](10,2)[/red]

Of course, the 10,2 is an example. You should check the field's values and adjust accordingly.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[tigerjade]

It sounded like it should work perfectly, George, but I'm still getting the same results (I'm running the exec query straight from QA, so I can control what's going in); 6.5 still comes out as 7. My precision/scale values are 5,2; I've verified that in the table. I tried putting the (5,2) on the parameter declaration as well as casting and converting the value in the insert statement. I've got to be missing something else, but what?

Thanks!

tigerjade

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[gmmastros]

Hmmmm.... Something is a little wacky. I suggest that you temporarily modify the SP to print the input values.

ALTER PROCEDURE sp_holdConfigurationData
@Current1 decimal(5,2)
,@Current2 decimal(5,2)
AS

select @Current1 Current1, @Current2 Current2

INSERT INTO ButtonTemp (Current1, Current2) VALUES (@Current1, @Current2)

Run the sp (using QA) giving the 6.5 and 9.7 values. What values are returned?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[tigerjade]

Returns 6.50 and 9.70. I'm going to try rebooting the server; maybe it's having a bad morning. I'll let you know if that helps.

Thanks!

tigerjade

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[gmmastros]

Let's double check the table. Run this query and let me know what the results are.

Select Table_Name, 
       Column_Name, 
       Data_Type, 
       Numeric_Precision, 
       Numeric_Scale 
From   Information_Schema.Columns
Where  Table_Name = 'ButtonTemp'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[tigerjade]

ButtonTemp Current1 decimal 2 0
ButtonTemp Current2 decimal 2 0



Thanks!

tigerjade

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[gmmastros]

Well, there's your problem.

The numeric precision is 2 and the scale is 0.

Numeric precision configure the number of digits that the field will hold. The scale configures the number of digits after the decimal point.

So a Decimal(5,2) can store a number up to 999.99
A decimal(2,0) can store a number up to 99 (without any numbers after the decimal point).

You need to change the precision and scale for the fields in the table so that precision = 5 and scale = 2

You can run the following sql to alter the precision and scale. You may want to backup the DB first.

Alter Table ButtonTemp Alter Column Current1 Decimal(5,2)
Alter Table ButtonTemp Alter Column Current2 Decimal(5,2)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[tigerjade]

AHHHH! The blinders have been lifted. Thanks a million and three, George! Worked like it was s'posed to


Thanks!

tigerjade

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[gmmastros]

I'm glad you got it working. (and thanks for the star)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom