Decimal problem

Hi guys,

I've got a table with one of the columns 'ConversionRate' declared as decimal. The value for this column is defined as 1.45. In my vb file, I tried to retrieve the value using a SqlParameter as shown below:

myConversionRate = Me.SqlCommand.Parameters.Add(Sql.StoredProcParameter.ConversionRate, SqlDbType.Decimal)
myConversionRate.Direction = ParameterDirection.Output

which will access the following query in my stored proc:

ALTER PROCEDURE dbo.Charge @.PConversionRateDECIMAL = 0OUTPUT ASSELECT @.PConversionRate = ConversionRate,FROM TblCharge

For some reasons, the value returned is always 1 instead of 1.45. Anyone experienced this problem before and knows how to resolve it? Thanks in advance.

The solution is provided by Microsoft support in the link below. Hope this helps.

http://support.microsoft.com/?kbid=892406

|||I see. Will try that out. Thanks again|||Hmmmm...I've installed the SP4 for SqlServer as suggested in the website but it is still not working. The decimal value is still being rounded into Integer type. Any idea what else might have caused the problem?|||Alter PROCEDURE dbo.Charge
@.PConversionRate decimal(18, 2) = 0 OUTPUT
AS
SELECT @.PConversionRate = ConversionRate
FROM TblCharge|||

Silvertype:

Hmmmm...I've installed the SP4 for SqlServer as suggested in the website but it is still not working. The decimal value is still being rounded into Integer type. Any idea what else might have caused the problem?

I did not tell you service pack will fix your problem, you need to go into your SQL Server table and make sure the data type is Decimal and set the precision and scale in your table, in your store proc like the Microsoft code below, in your ADO.NET code and maybe add strings and formatting in your UI and it will not be rounded. Hope this helps.

CREATE PROCEDURE ParameterPrecisionTest(
@.pIn DECIMAL(19,4),
@.pOut DECIMAL(19,4) OUTPUT)
AS
SET @.pOut = @.pIn

ALTER PROCEDURE dbo.Charge
@.PConversionRate DECIMAL = 0 OUTPUT
AS
SELECT @.PConversionRate = ConversionRate,
FROM TblCharge

http://blogs.msdn.com/kathykam/archive/2006/03/29/564426.aspx

|||I see. Thanks.|||

Silvertype:

I see. Thanks.

I am glad I could help.