question

HowardArner-3009 avatar image
0 Votes"
HowardArner-3009 asked ·

Dataset Column Expression Math Error when using convert

OK, this has me stumped. I am just trying to round a column using an expression in datatable column. Here is a program that shows the behavior i am not expecting

     Dim T As New DataTable
     Dim Ty As System.Type
     Ty = System.Type.GetType("System.Double")
     T.Columns.Add("A", Ty)
     T.Columns.Add("B", Ty)
     T.Columns.Add("C", Ty, "A*B")
     T.Columns.Add("D", Ty, "Convert(A*B*100,System.Int64)/100")

     T.Rows.Add(3, 3.331)
     T.Rows.Add(3, 3.333)
     Dim S As String = String.Format("A: {0} B: {1} C:{2} D:{3}", T.Rows(0).Item(0), T.Rows(0).Item(1), T.Rows(0).Item(2), T.Rows(0).Item(3))
     Debug.Print(S)

     S = String.Format("A: {0} B: {1} C:{2} D:{3}", T.Rows(1).Item(0), T.Rows(1).Item(1), T.Rows(1).Item(2), T.Rows(1).Item(3))
     Debug.Print(S)


The output of the above is:

A: 3 B: 3.331 C:9.993 D:9.99
A: 3 B: 3.333 C:9.999 D:10


Notice what is happening on the second case, the number is being truncated. I have tried many variations on the formula and cannot get it to handle it correctly. some kind of voodoo is happening. I tried converting the 100 to a decimal, converting it to a double. I cannot get it to function. What am i missing?

dotnet-visual-basicdotnet-standard
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Viorel-1 avatar image
0 Votes"
Viorel-1 answered ·

Show the expected result and try this expression: "Convert(A*B*100 - 0.49, System.Int64) / 100".

If negative numbers are possible, then try "Convert(A*B*100 + iif(A*B>=0, -0.49, +0.49), System.Int64) / 100".


· 3 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you so much, that is something i just did not think of! I had tried convert, you solution is more elegant. I just wonder what is going on internally that causes it to not function as expected in the second case, but function as expected in the first.

I used your second example in the test code and result is as expected.

A: 3 B: 3.331 C:9.993 D:9.99
A: 3 B: 3.333 C:9.999 D:9.99

0 Votes 0 ·

It seems that Convert does not truncate (remove the part after ‘.’) but round the values: 999.3 becomes 999, and 999.9 becomes 1000.

Subtracting 0.49 before rounding results in truncation: 999.3-0.49=998.81, rounded to 999, and 999.9-0.49=999.41, rounded to 999.

1 Vote 1 ·

Don't you love how they document that behavior? The documentation on convert says nothing about rounding.... nor does it say anything about truncation... oh well... IMHO it should truncate but cest la vie.

0 Votes 0 ·