Solved

decimal datatype not behaving correctly

Posted on 2007-11-14
11
2,105 Views
Last Modified: 2013-11-26
I have a field in a table of type decimal(10,3)
so I want it sohuld be able to save the number upto 3 decimal places.

This is in SQL Server 2005

Now while saving this data the stored procedure has declared this field as decimal.


What is passes from the application-->
shippingquantity = CType(sShippingQty, Decimal)

sShippingQty is takrn from a text box

If I enter 20.345 the value in shippingquantity  is 20.345

but when it saves this to the database it saves it as 20.000

Why so? Is decimal datatype not the correct datatype for this requirement?
What shall i use..what precision?
0
Comment
Question by:sonalj
  • 6
  • 4
11 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
Comment Utility
>>Now while saving this data the stored procedure has declared this field as decimal.<<
You need to define the parameter in your stored procedure also as decimal(10,3)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Also, make sure you are declaring the precision and scale in VB.NET
0
 

Author Comment

by:sonalj
Comment Utility
I defined the datatype in stored procedure as decimal(10,3)
and it is working correctly..but please let me know how to declare  the precision and scale in VB.NET?
Thanks.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Post your VB.NET code as it stands now.
0
 

Author Comment

by:sonalj
Comment Utility
Dim sShippingQty As String = CType(oRowItem.FindControl("txtQty"), TextBox).Text
                            If Not String.IsNullOrEmpty(sQty) Then
                                Try
                                    Item.Fields("dQty") = CType(sShippingQty, Decimal)
                                Catch
                                End Try
                            End If


Actually its textbox in a gridview. Item is a class . Fields is a property of type hashtable in item class.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
I was talking about the code you use to call the stored procedure, that is where you are supposed to set the Precision and Scale.
0
 

Author Comment

by:sonalj
Comment Utility
Actually it is being saved in a different component and   Item.Fields("dQty")  actually is set in db.setparameter. Not sure how can we set the precesion here.  Otherwise modifying the stored proc works fine

Dim db As Database = DatabaseFactory.CreateDatabase(connection)
            Dim dbCommand As DbCommand = db.GetStoredProcCommand("sprocSaveItems")
            Try
                db.DiscoverParameters(dbCommand)            
                db.SetParameterValue(dbCommand, "_gId", Id)
 For Each sKey As String In Fields.Keys                  
                        db.SetParameterValue(dbCommand, sKey, Fields(sKey))
                    End If
                Next
0
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
                           If Not String.IsNullOrEmpty(sQty) Then
                                Try
                                    Item.Fields("dQty") = CType(sShippingQty, Decimal)

try Double instead of Decimal on the last line above. also, do you mean
String.IsNullOrEmpty(sShippingQty)  instead of just sQty?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
You are not making this easy, so assuming you are using the SQLParameter object than you should set the Precision and Scale properties.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>try Double instead of Decimal on the last line above.<<
Why in world would you want to lose the precision of a decimal data type by using a double data type instead?  In T-SQL terms, that is like saying replace decimal with the far less precise float or real.
0
 

Author Comment

by:sonalj
Comment Utility
try Double instead of Decimal on the last line above. also, do you mean
String.IsNullOrEmpty(sShippingQty)  instead of just sQty?

Imitchei--> Oh yes i mean String.IsNullOrEmpty(sShippingQty)

acperkins-->
 Dim db As Database = DatabaseFactory.CreateDatabase(connection)
            Dim dbCommand As DbCommand = db.GetStoredProcCommand("sprocSaveItems")
            Try
                db.DiscoverParameters(dbCommand)            
                db.SetParameterValue(dbCommand, "_gId", Id)
 For Each sKey As String In Fields.Keys                  
                        db.SetParameterValue(dbCommand, sKey, Fields(sKey))
                    End If
                Next
               


I am taking the parameter and its value in a loop to set the precision and scale for 1 of the parameter..I'll have to check if parameter namel ike qty ( or whatever) then set the scale & precision.


But I think setting the scale and precision in the stored procedure should be enough..right?

There is no declaration as sql parameter in the above code..so i m not very sure where to give the scale and precision for 1 of the fields in the loop
 For Each sKey As String In Fields.Keys                  
                        db.SetParameterValue(dbCommand, sKey, Fields(sKey))
                    End If
                Next
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now