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=Micr osoft.Jet.OLEDB .4.0;" & "Data Source=C:\Focus .net\ServerDB.m db" & ";Persist Security Info=False")
qry = ("SELECT QTY_UNIT,RATE_U NIT,FACTOR FROM i_Unit_Con WHERE QTY_UNIT ='" & txtitemunit1.Te xt & "' AND RATE_UNIT ='" & txtitemunit2.Te xt & "'")
Dim cmd As New OleDbCommand(qr y, conn)
conn.Open()
dr = cmd.ExecuteRead er()
If dr.Read Then
If (dr("QTY_UNIT") = (txtitemunit1.T ext) AndAlso dr("RATE_UNIT" ) = (txtitemunit2.T ext)) = (dr("FACTOR") = 1) Then
sum = CInt(txtitemqua nt.Text) * 1 * CInt(txtitemrat e.Text) * CInt(110%) / 100
res = sum
txtitempmv.Text = res
ElseIf (dr("QTY_UNIT") = (txtitemunit1.T ext) AndAlso dr("RATE_UNIT" ) = (txtitemunit2.T ext)) = (dr("FACTOR") = 1000) Then
sum = CInt(txtitemqua nt.Text) * 1000 * CInt(txtitemrat e.Text) * CInt(110%) / 100
res = sum
txtitempmv.Text = res

ElseIf (dr("QTY_UNIT") = (txtitemunit1.T ext) AndAlso dr("RATE_UNIT" ) = (txtitemunit2.T ext)) = (dr("FACTOR") = 144) Then
sum = CInt(txtitemqua nt.Text) * 144 * CInt(txtitemrat e.Text) * CInt(110%) / 100
res = sum
txtitempmv.Text = res
ElseIf (dr("QTY_UNIT") = (txtitemunit1.T ext) AndAlso dr("RATE_UNIT" ) = (txtitemunit2.T ext)) = (dr("FACTOR") = 0.001) Then
sum = CInt(txtitemqua nt.Text) * 0.001 * CInt(txtitemrat e.Text) * CInt(110%) / 100
res = sum
txtitempmv.Text = res
ElseIf (dr("QTY_UNIT") = (txtitemunit1.T ext) AndAlso dr("RATE_UNIT" ) = (txtitemunit2.T ext)) = (dr("FACTOR") = 0.01) Then
sum = CInt(txtitemqua nt.Text) * 0.01 * CInt(txtitemrat e.Text) * CInt(110%) / 100
res = sum
txtitempmv.Text = res
ElseIf (dr("QTY_UNIT") = (txtitemunit1.T ext) AndAlso dr("RATE_UNIT" ) = (txtitemunit2.T ext)) = (dr("FACTOR") = 100) Then
sum = CInt(txtitemqua nt.Text) * 100 * CInt(txtitemrat e.Text) * CInt(110%) / 100
res = sum
txtitempmv.Text = res
ElseIf (dr("QTY_UNIT") = (txtitemunit1.T ext) AndAlso dr("RATE_UNIT" ) = (txtitemunit2.T ext)) = (dr("FACTOR") = 12) Then
sum = CInt(txtitemqua nt.Text) * 12 * CInt(txtitemrat e.Text) * CInt(110%) / 100
res = sum
txtitempmv.Text = res
ElseIf (dr("QTY_UNIT") = (txtitemunit1.T ext) AndAlso dr("RATE_UNIT" ) = (txtitemunit2.T ext)) = (dr("FACTOR") = 0.08333) Then
sum = CInt(txtitemqua nt.Text) * 0.08333 * CInt(txtitemrat e.Text) * CInt(110%) / 100
res = sum
txtitempmv.Text = res
ElseIf (dr("QTY_UNIT") = (txtitemunit1.T ext) AndAlso dr("RATE_UNIT" ) = (txtitemunit2.T ext)) = (dr("FACTOR") = 0.45359) Then
sum = CInt(txtitemqua nt.Text) * 0.45359 * CInt(txtitemrat e.Text) * CInt(110%) / 100
res = sum
txtitempmv.Text = res
ElseIf (dr("QTY_UNIT") = (txtitemunit1.T ext) AndAlso dr("RATE_UNIT" ) = (txtitemunit2.T ext)) = (dr("FACTOR") = 2.2046) Then
sum = CInt(txtitemqua nt.Text) * 2.2046 * CInt(txtitemrat e.Text) * CInt(110%) / 100
res = sum
txtitempmv.Text = res
ElseIf (dr("QTY_UNIT") = (txtitemunit1.T ext) AndAlso dr("RATE_UNIT" ) = (txtitemunit2.T ext)) = (dr("FACTOR") = 0.3048) Then
sum = CInt(txtitemqua nt.Text) * 0.3048 * CInt(txtitemrat e.Text) * CInt(110%) / 100
res = sum
txtitempmv.Text = res
ElseIf (dr("QTY_UNIT") = (txtitemunit1.T ext) AndAlso dr("RATE_UNIT" ) = (txtitemunit2.T ext)) = (dr("FACTOR") = 3.2808) Then
sum = CInt(txtitemqua nt.Text) * 3.2808 * CInt(txtitemrat e.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.T ext)) = (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.T ext)) = (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