sonalj
asked on
decimal datatype not behaving correctly
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Also, make sure you are declaring the precision and scale in VB.NET
ASKER
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.
and it is working correctly..but please let me know how to declare the precision and scale in VB.NET?
Thanks.
Post your VB.NET code as it stands now.
ASKER
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.
If Not String.IsNullOrEmpty(sQty)
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.
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.
ASKER
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.CreateData base(conne ction)
Dim dbCommand As DbCommand = db.GetStoredProcCommand("s procSaveIt ems")
Try
db.DiscoverParameters(dbCo mmand)
db.SetParameterValue(dbCom mand, "_gId", Id)
For Each sKey As String In Fields.Keys
db.SetParameterValue(dbCom mand, sKey, Fields(sKey))
End If
Next
Dim db As Database = DatabaseFactory.CreateData
Dim dbCommand As DbCommand = db.GetStoredProcCommand("s
Try
db.DiscoverParameters(dbCo
db.SetParameterValue(dbCom
For Each sKey As String In Fields.Keys
db.SetParameterValue(dbCom
End If
Next
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(sShip pingQty) instead of just sQty?
Try
Item.Fields("dQty") = CType(sShippingQty, Decimal)
try Double instead of Decimal on the last line above. also, do you mean
String.IsNullOrEmpty(sShip
You are not making this easy, so assuming you are using the SQLParameter object than you should set the Precision and Scale properties.
>>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.
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.
ASKER
try Double instead of Decimal on the last line above. also, do you mean
String.IsNullOrEmpty(sShip pingQty) instead of just sQty?
Imitchei--> Oh yes i mean String.IsNullOrEmpty(sShip pingQty)
acperkins-->
Dim db As Database = DatabaseFactory.CreateData base(conne ction)
Dim dbCommand As DbCommand = db.GetStoredProcCommand("s procSaveIt ems")
Try
db.DiscoverParameters(dbCo mmand)
db.SetParameterValue(dbCom mand, "_gId", Id)
For Each sKey As String In Fields.Keys
db.SetParameterValue(dbCom mand, 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(dbCom mand, sKey, Fields(sKey))
End If
Next
String.IsNullOrEmpty(sShip
Imitchei--> Oh yes i mean String.IsNullOrEmpty(sShip
acperkins-->
Dim db As Database = DatabaseFactory.CreateData
Dim dbCommand As DbCommand = db.GetStoredProcCommand("s
Try
db.DiscoverParameters(dbCo
db.SetParameterValue(dbCom
For Each sKey As String In Fields.Keys
db.SetParameterValue(dbCom
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(dbCom
End If
Next