vb.net variables calculating incorrect value

Question

i am running this code in vb.net:

Dim sub_total as Double = 0

Dim amount As Double = 0
Dim customer_total As Double = 0

SQL = "SELECT invoice, product, cost, price, commission FROM billing_salesman_commission WHERE invoice = '" & reader2.GetString(0) & "';"
myCommand3.Connection = conn3
myCommand3.CommandText = SQL
reader3 = myCommand3.ExecuteReader
While reader3.Read
    'profit = sell price - cost price
     amount = reader3.GetString(3) - reader3.GetString(2)

     'commission amount = profit * (commission % / 100)
     amount = amount * (reader3.GetString(4) / 100)

     'update the customer total
     customer_total = customer_total + amount

     'insert excel data
     'MsgBox("insert excel")
End While
reader3.Close()

sub_total = sub_total + customer_total

from the query above that creates a loop, i have calculated all of the figures manually on a calculator.

there are 2 unique values for invoice and each one totals the following:

5.44
9.41

which should equal 14.85 however sub_total is returning 14.84

`amount = reader3.GetString(3) - reader3.GetString(2)` is subtracting strings and assigning the result to a double. Please turn on Option Strict. — Ňɏssa Pøngjǣrdenlarp, Apr 05 '16 at 13:31
what should they be other than strings? or could i wrap val() around both of them ? — charlie, Apr 05 '16 at 13:34
When you step through this in a debugger, what are the actual runtime values when it fails? — David, Apr 05 '16 at 13:39
it doesnt fail, its processing through the code fine but its just creating the wrong value — charlie, Apr 05 '16 at 13:40
Can you reproduce the problem without the db? Eg. use hard coded text strings rather than reader3.GetString(3). Also this seems to me to be a rounding error. — Taemyr, Apr 05 '16 at 13:40
I would not recommend using doubles to store currency values. Doubles are more for abstract scientific numbers. See http://stackoverflow.com/questions/618535/difference-between-decimal-float-and-double-in-net. I would recommend using a decimal type - it _may_ be more accurate. — Chris, Apr 05 '16 at 13:42
@charlie uses the decimal data type that is 10 times more accurate — Jande, Apr 05 '16 at 13:42
ive changed `Double` to `Decimal` and its now producing all 0 values — charlie, Apr 05 '16 at 13:45
Not for nothing but rather than using `String` like it is programming duct tape, the reader can fetch back typed values: `amount = amount * reader3.GetDecimal(4)` or Double as the case may be — Ňɏssa Pøngjǣrdenlarp, Apr 06 '16 at 15:28

Claudius · Accepted Answer · 2016-04-05T15:48:52.437

You were trying to subtract strings. For code string is a bunch of letters and signs and digits within string are treated as letters.

amount = reader3.GetString(3) - reader3.GetString(2)

You can fix this few different ways:

amount = (Convert.ToDecimal(reader3.GetString(3)))-(Convert.ToDecimal(reader3.GetString(2))


amount = CDec(reader3.GetString(3)) - CDec(reader3.GetString(2))

best way as it will prevent exception:

UPDATE:

Change these:

Dim sub_total as Double = 0
Dim amount As Double = 0
Dim customer_total As Double = 0

to:

Dim sub_total, amount, customer_total As Decimal

then this should work:

Dim costDec, sellDec, profit, commission as Decimal
If (Decimal.TryParse(reader3.GetString(3), costDec) AND Decimal.TryParse(reader3.GetString(2), sellDec) AND Decimal.TryParse(reader3.GetString(2), commission)) Then
    amount = costDec - sellDec
    profit = amount * (commission/100)
    customer_total = customer_total + profit
End If

Just a comment to above logic, your naming convention is weird as it suggests that you want to calculate total cost for customer when you are only calculating commission of profits.

ive checked some other code, which is this `cdr_client_total + Val(reader4.GetString(1)) / 100` and thats working fine - its stored in my DB as `Decimal 10,2` — charlie, Apr 05 '16 at 14:16
I'm not sure In understand, so your code works now? Val will work but it was made for different scenarios. IsNumeric() will also work — Claudius, Apr 05 '16 at 14:51
its not working - thats a different function i have in my application — charlie, Apr 05 '16 at 14:52

score 1 · Answer 2 · answered Apr 05 '16 at 13:48

'change your code:

 Option strict on

 Dim sub_total as decimal= 0
 Dim amount As decimal= 0
 Dim customer_total As decimal= 0

   SQL = "SELECT invoice, product, cost, price, commission FROM billing_salesman_commission WHERE invoice = '" & reader2.GetString(0) & "';"

  myCommand3.Connection = conn3
  myCommand3.CommandText = SQL
  reader3 = myCommand3.ExecuteReader
     While reader3.Read

 'profit = sell price - cost price
 amount = cdec(reader3.GetString(3)) - cdec(reader3.GetString(2))

 'commission amount = profit * (commission % / 100)
 amount = amount * cdec(reader3.GetString(4) / 100)

 'update the customer total
 customer_total = cdec(customer_total) + cdec(amount)

 'insert excel data
 'MsgBox("insert excel")
 End While
 reader3.Close()

  sub_total = sub_total + customer_total

ive checked some other code, which is this cdr_client_total + Val(reader4.GetString(1)) / 100 and thats working fine - its stored in my DB as Decimal 10,2 — charlie, Apr 05 '16 at 14:29

vb.net variables calculating incorrect value

2 Answers2