CODEWITHSUNDEEP
asp.netweb-servicesdecimal
Lakeshore
Lakeshore

Reputation: 323

Web Service: Decimal Not Being Passed to Database Correctly

I have a web service and I can see the data member of 0.03 being passed to the web service but that database is only showing 0.000. The column in the database is defined as DECIMAL(18,4). The data member of the business class is defined as decimal. Using Visual Studio 2010, SQL Server 2008 and .NET 4.0. Why isn't 0.03 appearing in the database column rather than 0.000?

int createApp;

ShortSaleInvestorReviewServiceClient _service = new ShortSaleInvestorReviewServiceClient();

decimal currentInterestRate = txtCurrentInterestRate.Text != string.Empty ? Decimal.Parse(txtCurrentInterestRate.Text) : new decimal();

createApp = _service.UpdateShortSaleApplication(currentInterestRate);

The web service through LINQ calls a stored procedure to update the table.

ALTER PROCEDURE [dbo].[UpdateShortSaleApplication]                 
@iShortSaleApplicationID int,   
@iCurrentInterestRate decimal,     

AS

UPDATE [ShortSaleApplications] SET [CurrentInterestRate] = @iCurrentInterestRate   

WHERE ShortSaleApplicationID = @iShortSaleApplicationID

When I am stepping through the above code, I can see "0.03" being passed to the web-service into the LINQ to the stored procedure. But the underlying column in the database shows "0.0000". The column in the database is defined as decimal(18,4). How else can I debug or fix?

Upvotes: 0

Views: 198

Answers (1)

Kai
Kai

Reputation: 352

You have not set the precision and scale for the decimal variable in your stored procedure and it is creating the problem.

ALTER PROCEDURE [dbo].[UpdateShortSaleApplication]
@iShortSaleApplicationID int,
@iCurrentInterestRate decimal,

By default sql server takes the precision 18 and scale 0.Link

p (precision) The maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18. s (scale) The number of decimal digits that will be stored to the right of the decimal point. This number is substracted from p to determine the maximum number of digits to the left of the decimal point. The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

Change it to

The column in the database is defined as DECIMAL(18,4)

Upvotes: 1

Related Questions