troubleshooting Question

Rounded numbers are not rounded in dataset

Avatar of pigparent
pigparent asked on
.NET Programming
8 Comments2 Solutions424 ViewsLast Modified:
I have a situation in which I have a charge for each person and a database of one or more records per person.  I need to allocate the charge on a percent-to-total basis to the rows in the dataset.  So I'm looping through each row in the dataset and making the calculation.  I want to store an amount rounded to the nearest penny on each row, so I'm making the calculation and when I step through it in debug mode it's perfect.  When I view the dataset at the end, there are fractions of pennies!  What on earth is causing this and how do I get it to just store the rounded amount that I am assigning?

All figures are of data type double and the field in the typed dataset is of type double.  As stated above, I have confirmed in debug that the custom function to round the numbers the way I want them rounded is working as designed.
Dim cmd As New OracleCommand("SELECT sum(v.gross_pay) AS GrossPay FROM wcs.wcs_uers_vouchers v LEFT OUTER JOIN hr.pay_cost_allocation_keyflex k ON v.cost_allocation_keyflex_id = k.cost_allocation_keyflex_id  WHERE v.social_security_number = '" & sSSN & "' AND k.segment3 IS NOT NULL AND (k.segment1 = '03' or k.segment1 = '07') AND v.check_date BETWEEN TO_DATE('" & dtStart & "', 'mm/dd/yy') AND TO_DATE('" & dtEnd & "', 'mm/dd/yy') AND frozen_for_adjustments IS NULL GROUP BY v.social_security_number", conOracle)
        Dim daOracle As OracleDataAdapter
        Dim dTotalFederalGross As Double = 0
        Dim dAmountToApply As Double = 0
        Dim dAmountApplied As Double = 0

        'Prepare
        Cursor.Current = Cursors.WaitCursor

        Try
            If conOracle.State = ConnectionState.Closed Then
                conOracle.Open()
            End If
        Catch orex As OracleException
            Cursor.Current = Cursors.Arrow
            MessageBox.Show("Oracle Production is down or not responding.  Please try again later.", Application.ProductName)
            Exit Sub
        End Try

        'Get total federal gross for this employee
        dTotalFederalGross = CDbl(cmd.ExecuteOracleScalar.ToString)

        'Populate the dataset of gross by PRC
        daOracle = New OracleDataAdapter("SELECT v.social_security_number AS SSN, '' AS EmpName, k.segment3 AS PRC, sum(v.gross_pay) AS GrossPay FROM wcs.wcs_uers_vouchers v LEFT OUTER JOIN hr.pay_cost_allocation_keyflex k ON v.cost_allocation_keyflex_id = k.cost_allocation_keyflex_id  WHERE v.social_security_number = '" & sSSN & "' AND k.segment3 IS NOT NULL AND (k.segment1 = '03' or k.segment1 = '07') AND v.check_date BETWEEN TO_DATE('" & dtStart & "', 'mm/dd/yy') AND TO_DATE('" & dtEnd & "', 'mm/dd/yy') AND frozen_for_adjustments IS NULL GROUP BY v.social_security_number, k.segment3 ORDER BY sum(v.gross_pay) DESC", conOracle)
        m_dsOracleFederallyFundedGrossByPRC = New dsOracleFederallyFundedGrossByPRC
        daOracle.Fill(m_dsOracleFederallyFundedGrossByPRC, "OracleFederallyFundedGrossByPRC")
        conOracle.Close()

        'Loop through the dataset and calculate the ESC charges by PRC
        For Each row As DataRow In m_dsOracleFederallyFundedGrossByPRC.Tables(0).Rows
            If row.Item("SSN").ToString = sSSN Then
                dAmountToApply = JasonsRound((CDbl(row.Item("GrossPay")) / dTotalFederalGross) * dESCCharges, 2)
                row.Item("AllocatedCharges") = dAmountToApply
                dAmountApplied += CDbl(row.Item("AllocatedCharges"))
            End If
        Next

        'Adjust for rounding error, if necessary
        If dAmountApplied <> dESCCharges Then
            For Each row As DataRow In m_dsOracleFederallyFundedGrossByPRC.Tables(0).Rows
                If row.Item("SSN").ToString = sSSN Then
                    row.Item("AllocatedCharges") = CDbl(row.Item("AllocatedCharges")) + dESCCharges - dAmountApplied
                End If
            Next
        End If

        Cursor.Current = Cursors.Arrow
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros