problem declaring SqlParameter of type Decimal

  • Thread starter Thread starter djc
  • Start date Start date
D

djc

Problem:
When peforming an update on an SQL (MSDE2000) data field of type DECIMAL my
values are being rounded. For example if I enter 1.1 through 1.4 a 1.0 is
entered into the table. If I enter 1.5 through 1.9 a 2.0 is entered into the
table.

I am using ASP WebMatrix which color codes your code as most development
tools do. I noticed the the word 'Decimal' in "SqlDbType.Decimal' is colored
blue while none of my other SqlParameters that I have used thus far (INT,
varchar, datetime) are colored in any way. So I am currently assuming the
the word 'decimal' is special to VB.Net and I need to use some other syntax
to setup this SqlParameter.

Below is a vb.net code snippet followed by the stored procedure I'm using.
Maybe the problem is in my stored procedure?

<snippet>
Dim Param_NewHoursEstimate As New SqlParameter("@NewHoursEstimate",
SqlDbType.Decimal)
Param_NewHoursEstimate.Value = CDec(txtEstimatedHours.Text)
..Parameters.Add(Param_NewHoursEstimate)
</snippet>
----------------------------------------------------------
CREATE PROCEDURE dbo.UpdateHoursEstimate
(@MainID INT, @NewHoursEstimate DECIMAL)

AS

UPDATE tblMain
SET EstimatedHours = @NewHoursEstimate
WHERE MainID = @MainID
GO
 
nevermind.... found answer: problem is in stored procedure. I didn't realize
I needed to supply the precision and scale to the stored procedure parameter
declaration like so: @DecVarName DECIMAL(precision, scale)

live and learn.
 
Back
Top