asked on
Rounded numbers are not rounded in dataset
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.
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
.NET Programming
Last Comment
pigparent
instead of double, use decimal data type
Are you sure that all the variables used in the operations are rounded?
For instance, we do not see where dESCCharges come from, but it is involved in the value that is stored in AllocatedCharges.
Also, I would be careful about things such as the following:
Why not
I would do all the operations with standard .NET types and wait till the operations are done before putting the information in the table. row.Item handles the data as Object. When you go in an out of a DataTable field, the system need to convert back and forth between an Object and a Double. That could bring little changes such as the ones you are getting.
Why not round when you make the last assignment to row.Item("AllocatedCharges
Also, why design your own rounding function (JasonsRound, isn't it?) when there are a few built in Visual Basic and the framework Math class?
For instance, we do not see where dESCCharges come from, but it is involved in the value that is stored in AllocatedCharges.
Also, I would be careful about things such as the following:
row.Item("AllocatedCharges") = dAmountToApply
dAmountApplied += CDbl(row.Item("AllocatedCharges"))
Why not
dAmountApplied += dAmountToApply
I would do all the operations with standard .NET types and wait till the operations are done before putting the information in the table. row.Item handles the data as Object. When you go in an out of a DataTable field, the system need to convert back and forth between an Object and a Double. That could bring little changes such as the ones you are getting.
Why not round when you make the last assignment to row.Item("AllocatedCharges
Also, why design your own rounding function (JasonsRound, isn't it?) when there are a few built in Visual Basic and the framework Math class?
ASKER
deighton, I am about to change to decimal to try it and will let you know.
JamesBurger, dESCCharges is an argument and was at four decimal places prior to your question. I have since rounded it when the subprocedure is called and get the same result.
Thanks, too, for your suggesting dAmountApplied += dAmountToApply. That's exactly the way I would have coded it but I didn't originally have dAmountToApply. I added that solely to monitor the calculation to make sure that amount was rounded, and it is. I will make that change you suggested. I'm still perplexed about why the rounded number is being stored as something else, so I'll try deighton's suggestion. I hate to do that because I always use double and don't see why it wouldn't work...but if the other works I may just switch to using decimal exclusively and ditch double.
JamesBurger, dESCCharges is an argument and was at four decimal places prior to your question. I have since rounded it when the subprocedure is called and get the same result.
Thanks, too, for your suggesting dAmountApplied += dAmountToApply. That's exactly the way I would have coded it but I didn't originally have dAmountToApply. I added that solely to monitor the calculation to make sure that amount was rounded, and it is. I will make that change you suggested. I'm still perplexed about why the rounded number is being stored as something else, so I'll try deighton's suggestion. I hate to do that because I always use double and don't see why it wouldn't work...but if the other works I may just switch to using decimal exclusively and ditch double.
Be careful about ditching Double for Decimal. This has a big impact.
Double are fixed to 15 decimals. Decimal has a variable number of decimals (their number depends on the integer part of the value) and can have up to 28 decimals. They do not solve rounding problems, they often make them worst.
A Decimal also takes up twice the amount of memory, 64 bits compared to the 32 bits of the Double.
Because of their extra size, Decimal variables are usually slower to use on a 32 bits computer.
Many programmers see Decimal as the type to use because many Math functions require a Decimal. It is simply because those functions should be able to handle any mathematical value, and Decimal is the type that can contains the biggest values as well as the most decimals.
Double are fixed to 15 decimals. Decimal has a variable number of decimals (their number depends on the integer part of the value) and can have up to 28 decimals. They do not solve rounding problems, they often make them worst.
A Decimal also takes up twice the amount of memory, 64 bits compared to the 32 bits of the Double.
Because of their extra size, Decimal variables are usually slower to use on a 32 bits computer.
Many programmers see Decimal as the type to use because many Math functions require a Decimal. It is simply because those functions should be able to handle any mathematical value, and Decimal is the type that can contains the biggest values as well as the most decimals.
ASKER
JamesBurger (and deighton), I just tried this method with no change in the result. I'm going to have to dig a little deeper on this one. I honestly can't imagine why I can tell it to round and it just "decides" to have extra numbers in there...I won't call it "precision" since it is actually less precise in my case.
I have noticed that sometimes tables have double fields carried out several decimal places when I know for a fact that the entry would only have gone out two decimal places. I have just shrugged and accepted it as some nuance of SQL Server but this time it's causing more annoyances. I'll just round the final result and "stick" the pennies somewhere...
I'll leave this open another day to see if someone comes along and sees what the issue is. I sincerely appreciate the assistance you both have provided!
I have noticed that sometimes tables have double fields carried out several decimal places when I know for a fact that the entry would only have gone out two decimal places. I have just shrugged and accepted it as some nuance of SQL Server but this time it's causing more annoyances. I'll just round the final result and "stick" the pennies somewhere...
I'll leave this open another day to see if someone comes along and sees what the issue is. I sincerely appreciate the assistance you both have provided!
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Guys, thanks. I guess I'll have to accept that, as much as it makes me scratch my head. Good thing I work for the government and can say it's close enough for government work!