How to calculate depending on database value?

[vmethiraj]

i have two text box's and compare from two columns in database the result auto show another text box and then following below code using my program

Public Sub cal()
        Dim sum As Double
        Dim res As Double
        With Common_Class.GetSrvRec("i_Unit_Con", "", "QTY_UNIT='" & txtitemunit1.Text.Trim & "'")
            If .Fields("QTY_UNIT").Value = txtitemunit1.Text Then
                If .Fields("RATE_UNIT").Value = txtitemunit2.Text Then
                    sum = CInt(txtitemquant.Text) * 1 * CInt(txtitemrate.Text) * CInt(110%) / 100
                    res = sum
                    txtitempmv.Text = res
                Else
                    sum = CInt(txtitemquant.Text) * 1000 * CInt(txtitemrate.Text) * CInt(110%) / 100
                    res = sum
                    txtitempmv.Text = res
                End If
            End If
        End With
    End Sub

[vmethiraj]

Public Sub unitcal()
Dim sum As Double
Dim res As Double
Dim dr As OleDbDataReader
Dim qry As String
Dim conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " & "Data Source=C:\Focus.net\ServerDB.mdb" & ";Persist Security Info=False")
qry = ("SELECT QTY_UNIT,RATE_UNIT,FACTOR FROM i_Unit_Con WHERE QTY_UNIT ='" & txtitemunit1.Text & "' AND RATE_UNIT ='" & txtitemunit2.Text & "'")
Dim cmd As New OleDbCommand(qry, conn)
conn.Open()
dr = cmd.ExecuteReader()
If dr.Read Then
If (dr("QTY_UNIT") = (txtitemunit1.Text) AndAlso dr("RATE_UNIT") = (txtitemunit2.Text)) = (dr("FACTOR") = 1) Then
sum = CInt(txtitemquant.Text) * 1 * CInt(txtitemrate.Text) * CInt(110%) / 100
res = sum
txtitempmv.Text = res
ElseIf (dr("QTY_UNIT") = (txtitemunit1.Text) AndAlso dr("RATE_UNIT") = (txtitemunit2.Text)) = (dr("FACTOR") = 1000) Then
sum = CInt(txtitemquant.Text) * 1000 * CInt(txtitemrate.Text) * CInt(110%) / 100
res = sum
txtitempmv.Text = res

ElseIf (dr("QTY_UNIT") = (txtitemunit1.Text) AndAlso dr("RATE_UNIT") = (txtitemunit2.Text)) = (dr("FACTOR") = 144) Then
sum = CInt(txtitemquant.Text) * 144 * CInt(txtitemrate.Text) * CInt(110%) / 100
res = sum
txtitempmv.Text = res
ElseIf (dr("QTY_UNIT") = (txtitemunit1.Text) AndAlso dr("RATE_UNIT") = (txtitemunit2.Text)) = (dr("FACTOR") = 0.001) Then
sum = CInt(txtitemquant.Text) * 0.001 * CInt(txtitemrate.Text) * CInt(110%) / 100
res = sum
txtitempmv.Text = res
ElseIf (dr("QTY_UNIT") = (txtitemunit1.Text) AndAlso dr("RATE_UNIT") = (txtitemunit2.Text)) = (dr("FACTOR") = 0.01) Then
sum = CInt(txtitemquant.Text) * 0.01 * CInt(txtitemrate.Text) * CInt(110%) / 100
res = sum
txtitempmv.Text = res
ElseIf (dr("QTY_UNIT") = (txtitemunit1.Text) AndAlso dr("RATE_UNIT") = (txtitemunit2.Text)) = (dr("FACTOR") = 100) Then
sum = CInt(txtitemquant.Text) * 100 * CInt(txtitemrate.Text) * CInt(110%) / 100
res = sum
txtitempmv.Text = res
ElseIf (dr("QTY_UNIT") = (txtitemunit1.Text) AndAlso dr("RATE_UNIT") = (txtitemunit2.Text)) = (dr("FACTOR") = 12) Then
sum = CInt(txtitemquant.Text) * 12 * CInt(txtitemrate.Text) * CInt(110%) / 100
res = sum
txtitempmv.Text = res
ElseIf (dr("QTY_UNIT") = (txtitemunit1.Text) AndAlso dr("RATE_UNIT") = (txtitemunit2.Text)) = (dr("FACTOR") = 0.08333) Then
sum = CInt(txtitemquant.Text) * 0.08333 * CInt(txtitemrate.Text) * CInt(110%) / 100
res = sum
txtitempmv.Text = res
ElseIf (dr("QTY_UNIT") = (txtitemunit1.Text) AndAlso dr("RATE_UNIT") = (txtitemunit2.Text)) = (dr("FACTOR") = 0.45359) Then
sum = CInt(txtitemquant.Text) * 0.45359 * CInt(txtitemrate.Text) * CInt(110%) / 100
res = sum
txtitempmv.Text = res
ElseIf (dr("QTY_UNIT") = (txtitemunit1.Text) AndAlso dr("RATE_UNIT") = (txtitemunit2.Text)) = (dr("FACTOR") = 2.2046) Then
sum = CInt(txtitemquant.Text) * 2.2046 * CInt(txtitemrate.Text) * CInt(110%) / 100
res = sum
txtitempmv.Text = res
ElseIf (dr("QTY_UNIT") = (txtitemunit1.Text) AndAlso dr("RATE_UNIT") = (txtitemunit2.Text)) = (dr("FACTOR") = 0.3048) Then
sum = CInt(txtitemquant.Text) * 0.3048 * CInt(txtitemrate.Text) * CInt(110%) / 100
res = sum
txtitempmv.Text = res
ElseIf (dr("QTY_UNIT") = (txtitemunit1.Text) AndAlso dr("RATE_UNIT") = (txtitemunit2.Text)) = (dr("FACTOR") = 3.2808) Then
sum = CInt(txtitemquant.Text) * 3.2808 * CInt(txtitemrate.Text) * CInt(110%) / 100
res = sum
txtitempmv.Text = res
Else
End If

End If
conn.Close()
End Sub

[BlackLibrary]

First off...walk away from the horrible if elseif redundancy. Clean it up, and simplify.

Instead, step back and analyze the calcs done.
How many are there?
Can you break them into logical groupings?
Are they defined correctly?
(i.e. dr("RATE_UNIT") = (txtitemunit2.Text)) = (dr("FACTOR") = 0.001)
This does not seem to make any sense to me. RATE_UNIT is a boolean value???

Build a nice select case based on the analysis.

If you post back what the heck: dr("RATE_UNIT") = (txtitemunit2.Text)) = (dr("FACTOR") = 0.001 means, then I'll give it a try with you.

[BlackLibrary]

For instance...

Dim factorCheck As Decimal = dr("FACTOR")
Dim CalcType As Integer
Dim qtyCheck As Boolean = dr("QTY_UNIT") = (txtitemunit1.Text)
If qtyCheck = True Then
    'By refactoring just a little, its clear that this must be true in all of your conditions.
 
    Select Case factorCheck
    Case 1000
        CalcType = "FactorBy1000"
        'I'd use some enumeration instead of strings.  But you see the idea.
    Case 144
        CalcType = "FactorBy144"
    Case 1
        CalcType = "FactorBy1"
    Case 100
        CalcType = "FactorBy100"
 
        'etc...
    Case Else
        'No calc found
        CalcType = "NOT FOUND"
    End Select
 
    Dim bCalcDone = True
 
    Select Case CalcType
    Case "NOT FOUND"
        bCalcDone = False  'or error it out
    Case "FactorBy1"
        sum = CInt(txtitemquant.Text) * 1 * CInt(txtitemrate.Text) * CInt(110%) / 100
        bCalcDone = True
    Case "FactorBy144"
        sum = CInt(txtitemquant.Text) * 144 * CInt(txtitemrate.Text) * CInt(110%) / 100
        bCalcDone = True
    End Select
 
    If bCalcDone = True Then
    res = sum
    txtitempmv.Text = res
    End If
End If