Solved

How do I stop a decimal from rounding off in asp.net?

Posted on 2004-09-10
18
701 Views
Last Modified: 2007-12-19
I have a stored procedure returning a value of 34.7. when I display this in a label  it is rounded off to 35.00. I tested the value in SQL Server Query Analyzer so I know I am stating off with 34.7.  I assume I have a formating proplem.

here is my label code:
 lblshipweight.Text = Format(myOrderDetails.ShipWeightTotal,"#00.00")

here is the pertinent code from the stored procedure:

@shipweighttotal decimal(10,2) OUTPUT

 SELECT  

        @shipweighttotal = Cast(SUM(CMRC_OrderDetails.Quantity * CMRC_OrderDetails.Weight) as decimal(10,2))

pertinent code from busines object (.vb file)

Dim myOrderDetails As New OrderDetails()
myOrderDetails.shipweighttotal = CDec(parametershipweighttotal.Value)
0
Comment
Question by:dplsr
  • 6
  • 4
  • 3
  • +3
18 Comments
 
LVL 18

Expert Comment

by:DotNetLover_Baan
Comment Utility
Hi there,
use..  
   myOrderDetails.shipweighttotal = CType(parametershipweighttotal.Value,Double)
   lblshipweight.Text = myOrderDetails.ShipWeightTotal.ToString("#,##0.00;(#,##0.00);")

-@Baan
0
 
LVL 24

Expert Comment

by:Justin_W
Comment Utility
You could also use any of these:
   lblshipweight.Text = myOrderDetails.ShipWeightTotal.ToString("R")
   lblshipweight.Text = myOrderDetails.ShipWeightTotal.ToString("G2")
   lblshipweight.Text = myOrderDetails.ShipWeightTotal.ToString("0.##")
etc.

See here for details:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconformattingoverview.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconnumericformatstrings.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp
0
 

Author Comment

by:dplsr
Comment Utility
Sorry none of these work.

 lblshipweight.Text = myOrderDetails.ShipWeightTotal.ToString("R")  format specifier was invalid
   lblshipweight.Text = myOrderDetails.ShipWeightTotal.ToString("G2")treturns 35
   lblshipweight.Text = myOrderDetails.ShipWeightTotal.ToString("0.##")  returns 35

myOrderDetails.shipweighttotal = CType(parametershipweighttotal.Value,Double)
   lblshipweight.Text = myOrderDetails.ShipWeightTotal.ToString("#,##0.00;(#,##0.00);")  returns 35.00  should be 34.7

I tested with the Qery Analyzer again here are the results:
Output Parameter(s):
      @shipweighttotal = 34.70
0
 
LVL 10

Expert Comment

by:jnhorst
Comment Utility
What type is the OrderDetails class?  If it is a data table, check the type of the ShipWeightTotal column.  Also try changing:

CDec(parametershipweighttotal.Value)

To:

CType(parametershipweighttotal.Value, Decimal)

I have no idea whether that CDec() vs CType() matters, but it's worth a try.

John
0
 

Author Comment

by:dplsr
Comment Utility
CType(parametershipweighttotal.Value, Decimal)

this also returns 35.00 instead of 34.7

ShipWeightTotal column is a decimal 9(18,2)
0
 
LVL 10

Expert Comment

by:jnhorst
Comment Utility
No, not the data type from the SQL Server table.  Do you have a predefined dataset in your project that contains the OrderDetails data table?  In your code you have:

Dim myOrderDetails As New OrderDetails()

What is the OrderDetails class?  Can you view it in the object browser?  I am thinking that it inherits from System.Data.DataTable.  A DataTable has a Columns collection of DataColumn objects.  It is the type of the DataColumn named ShipWeightTotal that I am asking about.

John
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
Comment Utility
How about converting into Double (as I showed) not Decimal ??
0
 
LVL 17

Expert Comment

by:AerosSaga
Comment Utility
You need to leave it as a decimal, CDec it with the formatting then CStr it to your label

decShippingUSPS = CDec(decShippingUSPS.ToString("$ ##.00"))
                Me.lblShippingTotal.Text = CStr(decShippingUSPS)

Regards,

Aeros
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 18

Expert Comment

by:DotNetLover_Baan
Comment Utility
Try to output the value directly to the Label without any formatting. Lets see what are you getting directly from ur class. I am guessing it is getting rounded there only.
-@Baan
0
 

Author Comment

by:dplsr
Comment Utility
Goodmorning All!

I have tried the label with no formating. then I get 35.

In response to jnhorst, here is the complete function from my business object:

Public Function GetInvoiceDetails(orderID As Integer) As OrderDetails

            ' Create Instance of Connection and Command Object
            Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim myCommand As New SqlDataAdapter("CMRC_InvoiceDetails", myConnection)

            ' Mark the Command as a SPROC
            myCommand.SelectCommand.CommandType = CommandType.StoredProcedure

            ' Add Parameters to SPROC
            Dim parameterOrderID As New SqlParameter("@OrderID", SqlDbType.Int, 4)
            parameterOrderID.Value = orderID
            myCommand.SelectCommand.Parameters.Add(parameterOrderID)

            Dim parameterOrderDate As New SqlParameter("@OrderDate", SqlDbType.DateTime, 8)
            parameterOrderDate.Direction = ParameterDirection.Output
            myCommand.SelectCommand.Parameters.Add(parameterOrderDate)

            Dim parameterShipDate As New SqlParameter("@ShipDate", SqlDbType.DateTime, 8)
            parameterShipDate.Direction = ParameterDirection.Output
            myCommand.SelectCommand.Parameters.Add(parameterShipDate)

            Dim parameterOrderTotal As New SqlParameter("@OrderTotal", SqlDbType.Money, 8)
            parameterOrderTotal.Direction = ParameterDirection.Output
            myCommand.SelectCommand.Parameters.Add(parameterOrderTotal)
                
                  Dim parameterShipTotal As New SqlParameter("@ShipTotal", SqlDbType.Money, 8)
            parameterShipTotal.Direction = ParameterDirection.Output
            myCommand.SelectCommand.Parameters.Add(parameterShipTotal)
                  
                  Dim parametershipweighttotal As New SqlParameter("@shipweighttotal", SqlDbType.Decimal, 9,2)
            parametershipweighttotal.Direction = ParameterDirection.Output
            myCommand.SelectCommand.Parameters.Add(parametershipweighttotal)

            ' Create and Fill the DataSet
            Dim myDataSet As New DataSet()
            myCommand.Fill(myDataSet, "OrderItems")

             'ship date is null if order doesn't exist, or belongs to a different user
           If Not parameterShipDate.Value Is DBNull.Value Then

                ' Create and Populate OrderDetails Struct using
                 'Output Params from the SPROC, as well as the
                 'populated dataset from the SqlDataAdapter
                Dim myOrderDetails As New OrderDetails()

                myOrderDetails.OrderDate = CType(parameterOrderDate.Value, DateTime)
                myOrderDetails.ShipDate = CType(parameterShipDate.Value, DateTime)
                myOrderDetails.OrderTotal = CDec(parameterOrderTotal.Value)
       myOrderDetails.ShipTotal = CDec(parameterShipTotal.Value)
        'myOrderDetails.shipweighttotal = CDec(parametershipweighttotal.Value)
       myOrderDetails.shipweighttotal = CType(parametershipweighttotal.Value,d
                                          myOrderDetails.OrderItems = myDataSet

            'Return the DataSet
                Return myOrderDetails
          Else
            Return Nothing
            End If
        End Function



here is my stored procedure:



CREATE Procedure CMRC_InvoiceDetails
(
    @OrderID    int,
    @OrderDate  datetime OUTPUT,
    @ShipDate   datetime OUTPUT,
    @OrderTotal money OUTPUT,
    @ShipTotal money OUTPUT,
    /*@shipweighttotal decimal(10,2) OUTPUT*/
@shipweighttotal money OUTPUT

)
AS

/* Return the order dates from the Orders
    Also verifies the order exists for this customer. */
SELECT
    @OrderDate = OrderDate,
    @ShipDate = ShipDate,
    @ShipTotal = shiptotal

   
FROM    
    CMRC_Orders
   
WHERE  
    OrderID = @OrderID


IF @@Rowcount = 1
BEGIN

/* First, return the OrderTotal out param */
SELECT  
    @OrderTotal = Cast(SUM(CMRC_OrderDetails.Quantity * CMRC_OrderDetails.UnitCost) as money),
       /* @shipweighttotal = Cast(SUM(CMRC_OrderDetails.Quantity * CMRC_OrderDetails.Weight) as decimal(10,2))*/

 @shipweighttotal= sum(CMRC_OrderDetails.Quantity * CMRC_OrderDetails.Weight)

FROM    
    CMRC_OrderDetails
   
WHERE  
    OrderID= @OrderID

/* Then, return the recordset of info */
SELECT  
    CMRC_Products.ProductID,
    CMRC_Products.ModelName,
    CMRC_Products.ModelNumber,
    CMRC_OrderDetails.UnitCost,
    CMRC_OrderDetails.Quantity,
CMRC_OrderDetails.Weight,

    (CMRC_OrderDetails.Quantity * CMRC_OrderDetails.UnitCost) as ExtendedAmount

FROM
    CMRC_OrderDetails
  INNER JOIN CMRC_Products ON CMRC_OrderDetails.ProductID = CMRC_Products.ProductID
 
WHERE  
    OrderID = @OrderID

select
 customerID
FROM    
    CMRC_Orders
   
WHERE  
    OrderID = @OrderID

END
GO


and my label:
lblshipweight.Text = myOrderDetails.ShipWeightTotal.ToString("#,##0.00;(#,##0.00);")
0
 
LVL 10

Expert Comment

by:jnhorst
Comment Utility
So OrderDetails is a struct with a ShipWeightTotal member.  How is that member declared (what type)?

John
0
 

Author Comment

by:dplsr
Comment Utility
Public Class OrderDetails

        Public OrderDate As DateTime
        Public ShipDate As DateTime
        Public OrderTotal As Decimal
        Public OrderItems As DataSet
        Public CustomerID As Integer
            Public intorderID As Integer
             Public ShipTotal As Decimal
             Public shipweighttotal As Decimal
    End Class
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
Comment Utility
How about declaring them as double ?
-Baan
0
 

Author Comment

by:dplsr
Comment Utility
Goodmorning! All,

I finally fixed this problem. This may not be the proper way but, I changed the SQLDbType to Money,8 and did the same in the Stored Procedure. I am no getting the correct values.
0
 

Author Comment

by:dplsr
Comment Utility
Ok Thanks,
You may split the points between Experts, if that is done.
Thanks
0
 

Accepted Solution

by:
ee_ai_construct earned 0 total points
Comment Utility
Please read entire thread.  Asker solved or content important.
Closed, 500 points refunded.
ee_ai_construct (replacement part #xm34)
Community Support Admin
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

772 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

13 Experts available now in Live!

Get 1:1 Help Now