CDec() rounding?

[djc]

I have a sql field (MSDE 2000) of type Decimal. When inserting a value into
the field from an asp.net web form I am using the CDec() function. If I
enter a 2.1 through a 2.4 a 2.00 is entered into the database. If I enter a
2.5 through a 2.9 a 3.00 is entered into the database???? Here is the stored
procedure I'm using and a vb.net code snippet:

-------------------------------------------------------
CREATE PROCEDURE dbo.UpdateHoursEstimate
(@MainID INT, @NewHoursEstimate DECIMAL)

AS

UPDATE tblMain
SET EstimatedHours = @NewHoursEstimate
WHERE MainID = @MainID
GO
---------------------------------------------------

----------------------------------------------------
<snippet>
Dim Param_NewHoursEstimate As New SqlParameter("@NewHoursEstimate",
SqlDbType.Decimal)
Param_NewHoursEstimate.Value = CDec(txtEstimatedHours.Text)
.Parameters.Add(Param_NewHoursEstimate)
</snippet?
----------------------------------------------------

I have entered data directly into the table without issue. So I know the sql
table accepts and stores the numbers I expect. Where is this rounding taking
place?

any info is appreciated. Thanks.

 

[djc]

I have verified the issue is not with CDec() by checking that value right
before it is assigned to the value of the SqlParameter. So the issue must be
within the stored procedure itself or the parameter object itself. I did
notice one strange thing in the declaration of this SqlParameter object. I
am using ASP Web Matrix which color codes the code for legibility. Just the
word 'decimal' in 'SqlDbType.Decimal' is in blue text? Other items that are
in blue text are elements of the vb.net language itself like IF Then, End
If, Dim, etc... This is probably a big clue to the problem and what I need
to do to fix it... so if Decimal is a keyword or something then how do I
declare this SqlParameter correctly?

anyone?

 

[Stephen Muecke]

When declaring a decimal, supply the precision and scale parameters (see SQL
Server Books Online for detailed explanation)
The default scale for scale is 0 (ie no digits to right of decimal point)
ie
CREATE PROCEDURE dbo.UpdateHoursEstimate
(
@MainID INT,
@NewHoursEstimate DECIMAL(precision,scale)
)


Stephen

 

[djc]

ah... Thank you! I just posted to the ADO group after realizing my issue was
not with VB thinking it was a more appropriate place for this. But hey,
cool, thanks a lot!