The textBox Search multiplies the search values [closed]

0
 Private Sub txtSearchByDish_TextChanged(sender As Object, e As EventArgs) Handles txtSearchByDish.TextChanged
    Try
        con = New OleDbConnection(cs)
        con.Open()
        cmd = New OleDbCommand("SELECT  DishName, I.Rate, SUM([Qty]) as ItemQuantity, BillDate, ( I.Rate * SUM([Qty])) As TotalAmount FROM Dish as I, RestaurantBillingInfo as P, RestaurantBillingItems as PD WHERE DishName like '" & txtSearchByDish.Text & "%' Group By DishName, I.Rate, [Qty], BillDate ORder By DishName", con)
        rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        dgw.Rows.Clear()
        While (rdr.Read() = True)
            dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4))
        End While
        con.Close()
    Catch ex As Exception
        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try
End Sub
    
asked by anonymous 07.12.2017 / 09:18

1 answer

0

I was able to solve the problem by modifying my code for:

Private Sub txtSearchByDish_TextChanged(sender As Object, e As EventArgs) Handles txtSearchByDish.TextChanged
    Dim totalAmount As Double
    Dim totalQuantity As Integer
    Try
        con = New OleDbConnection(cs)
        con.Open()
        Dim cb As String = "SELECT DishName, I.Rate, SUM([Qty]) as ItemQuantity, BillDate, ( I.Rate * SUM([Qty])) As TotalAmount FROM Dish as I, RestaurantBillingInfo as P, RestaurantBillingItems as PD WHERE I.ItemID = PD.Item_ID AND PD.B_ID=P.BillId AND DishName like '" & txtSearchByDish.Text & "%' Group By DishName, I.Rate, [Qty], BillDate ORder By DishName"
        cmd = New OleDbCommand(cb, con)
        rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        dgw.Rows.Clear()
        Do While (rdr.Read() = True)
            dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4))
            dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(4))
            totalAmount += rdr(4)
            totalQuantity += rdr(2)
        Loop

        lblQuantity.Text = totalQuantity
        lblTotal.Text = Format(totalAmount, "#,##0.00")
    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        cmd.Dispose()
        con.Close()
    End Try
End Sub
    
07.12.2017 / 11:53