Textbox Search multiplies search values

Asked

Viewed 39 times

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
vb.net ms-access

  • Please ,explain what is wanting to do your problem, it was not very clear only with the title and the code snippet. As I understood each character typed in the textbox you go to the database, open connection and make a query? is Windows Forms? Or Web? Don’t forget to give objects Device, or use Using

  • Windows Forms; is a form that displays the sales report, and contains a textbox to search or show only a few items. Ex: if you type FISH, it will show only the list with items named FISH. I hope I have clarified.... And my problem is: by typing FISH it multiplies the same Item 9 times.....

  • Where does it multiply? that which I don’t understand, it multiplies in the time it does the While and add in Your Datagridview ?

  • Exactly ... PS: the search textbox works as a search filter.

  • Only with this information we can not help. This code is full of errors. Interestingly you know how to do it right (https://answall.com/q/261141/101), but you didn’t do it.

1 answer

0


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

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

  • He’s still full of trouble, you’re just not seeing.

  • And why don’t you tell me where the problem is???

  • 'Cause there’s a lot of them and I already told you you can do it right.

  • ok thanks for your GREAT contribution.

Browser other questions tagged vb.net ms-access

You are not signed in. Login or sign up in order to post.