Solved

SQLHelper Problem with Decimal DataType

Posted on 2009-05-18
6
704 Views
Last Modified: 2013-11-26
Hi.
I have a strange problem with SQLHelper from the Enterprise Library.
I am using a statement like the following:

SqlHelper.ExecuteScalar(SqlHelper.ConnStr, "InsertTotalAmount", TotalAmount);

The InsertTotalAmount SP will insert the TotalAmount to a field with Decimal(38,23) Type.
This statement works fine with values less than 99999, but gives me an error with greater than 99999, the error is: "Conversion overflows".
The Decimal(38,23) Type should be enough for storing values like: 1,000,000 !!
what is worng with SQLHelper?
0
Comment
Question by:TaibaDXB
  • 3
  • 3
6 Comments
 
LVL 9

Expert Comment

by:cdaly33
ID: 24416813
What happens if you run the stored proc on its own using the same values?  Are you doing any multiplication or division in the stored proc which might affect the accuracy of the decimal?
0
 

Author Comment

by:TaibaDXB
ID: 24419082
Hi.
if I run the stored proc directly with the same values, it rus correctly without any problems!!
The stored proc just inserts the value into the table without doing any arithmatic operation.
Thanks for you rhelp.
0
 
LVL 9

Expert Comment

by:cdaly33
ID: 24421795
Can you post the declaration and assignment of TotalAmount?
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:TaibaDXB
ID: 24423249
Hi.
Total amount is declared in .NET as Decimal.
Its value is entered by the user like the following:
Decimal totalAmount = Convert.ToDecimal(txtTotalAmount.Text);
In SQL Server 2008 it is declared in the table as : decimal(38,23)
0
 

Accepted Solution

by:
TaibaDXB earned 0 total points
ID: 24468067
I have found the cause for this problem.
It is a bug in .NET as described in these links:
http://tjoe.wordpress.com/2007/04/
http://support.microsoft.com/kb/932288
It is all about the difference between The Decimal type in SQL Server and the Decimal type in .NET
In SQL the maximum precision is 38, but in .NET it is 28. which makes the overflow.
0
 
LVL 9

Expert Comment

by:cdaly33
ID: 24468107
Wow, great find.  That's great to know.
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

IP addresses can be stored in a database in any of several ways.  These ways may vary based on the volume of the data.  I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.   …
Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question