I am writing a simple shopping cart app and the price field is being rounded when inserted into the database.
There is a textbox for the user to enter the price. A stored procedure is used to insert the line item info to the data base table. The field in the table is formatted as datatype decimal. Below is the stored procedure and the code that adds the value to the parameter. Any suggestions on where I am going wrong?
<Code>
CREATE PROCEDURE [sp_insert_CartDetail]
(
@.ReferenceNum [int],
@.Item [varchar](26),
@.Desc1 [varchar](27),
@.Desc2 [varchar](20),
@.Desc3 [varchar](30),
@.Desc4 [varchar](30),
@.Note [text],
@.Quantity [int],
@.DateOrdered [datetime],
@.SalesPrice [decimal],
@.DistCost [decimal],
@.SalesTaxable [smallint],
@.RequiredDate [datetime],
@.User1 [varchar](12),
@.User2 [varchar](12),
@.User3 [varchar](12),
@.User4 [varchar](12),
@.User5 [varchar](12),
@.User6 [varchar](12))
AS INSERT INTO [ToolCrib].[dbo].[CartDetail]
(
[ReferenceNum],
[Item],
[Desc1],
[Desc2],
[Desc3],
[Desc4],
[Note],
[Quantity],
[DateOrdered],
[SalesPrice],
[DistCost],
[SalesTaxable],
[RequiredDate],
[User1],
[User2],
[User3],
[User4],
[User5],
[User6])
VALUES
(
@.ReferenceNum,
@.Item,
@.Desc1,
@.Desc2,
@.Desc3,
@.Desc4,
@.Note,
@.Quantity,
@.DateOrdered,
@.SalesPrice,
@.DistCost,
@.SalesTaxable,
@.RequiredDate,
@.User1,
@.User2,
@.User3,
@.User4,
@.User5,
@.User6)
SqlCmdInsetCartDetail.Parameters("@.SalesPrice").Value =CType(txtCost.Text,Decimal)
</code>
Does the table field have precision and scale set for the decimal type? You'd want to set them also for decimal type parameters.|||db table has precision of 18 and scale of 0. What should it be set to if I want decimal to 4 places. Is decimal the correct data type for this situation?Thanks,
Danny|||Try this link for code to set the precision and scale in .NET. Hope this helps.
http://support.microsoft.com/?kbid=892406|||I have set the scale to 4 on both the data table and the parameters... No change
Caddre, I don't think that pertains to my situation. 1 - I am running this on MSSQL7. 2-I am not returning a value from the sproc.
|||In SQL Server 7.0 change the data type to Numeric it is more stable in SQL Server 7.0 than Decimal because I remember when Money was rounding to less than one dollar in 1999 the only fix was with Numeric. And set the precision and scale, why I think it comes from TDS(tabular data stream) 7.0. Hope this helps.
