CODEWITHSUNDEEP
sqlsql-servervb.netdecimal
Charles Okwuagwu
Charles Okwuagwu

Reputation: 10866

Incorrect sum of decimal values in vb.net

I'm pulling data from a T-SQL procedure, one of my columns is cast as numeric(18,2)

When i run this procedure and copy the data to excel, the sum on this column is 0.01 less than the sum i get when i run this on vb.net.

In vb.net, i am aggregating the values for the column into a dictionary(of string, decimal)

I don't know why this happening.

   If Not dr.IsDBNull(dr.GetOrdinal(f.field)) Then
        Dim dval = If(dr.IsDBNull(dr.GetOrdinal(f.field)), 0D, dr.GetDecimal(dr.GetOrdinal(f.field)))
        di.Add(New FieldValue With {.Type = f, .Value = If(rpt.allow_negatives, dval, Math.Abs(dval))}, rpt.overide_format)
        If rpt.has_total AndAlso f.totaled Then totals(f.field) += dval
    Else
        di.Add(New FieldValue With {.Type = f, .Value = 0D})
    End If

Upvotes: 0

Views: 1211

Answers (1)

J...
J...

Reputation: 31403

Math.Abs has a decimal overload, so it is probably not that. More likely it is Excel that is getting the calculation wrong. Excel typically throws away anything more than 15 digits of precision.

See : http://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel

To prove this to yourself, type 

 1234567890123456.78

into an Excel cell. You'll see that the number ends up being :

 1234567890123450

Upvotes: 2

Related Questions