CODEWITHSUNDEEP
vb.net
charlie
charlie

Reputation: 481

vb.net variables calculating incorrect value

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:

which should equal 14.85 however sub_total is returning 14.84

Upvotes: 1

Views: 117

Answers (2)

Claudius
Claudius

Reputation: 1911

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.

Upvotes: 2

Jande
Jande

Reputation: 1705

'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

Upvotes: 1

Related Questions