i can fix my error can somebody look at it?-CodePudding

so im trying to make a search records for database but i got error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE ProductName LIKE 'Monitor' '%'" at line 1

Private Sub Btnsearch_Click(sender As Object, e As EventArgs) Handles btnsearch.Click
    Try
        ListView1.Items.Clear()
        strsql = "SELECT tbl_pcperipherals WHERE ProductName LIKE @field1 '%'"
        objcmd = New MySql.Data.MySqlClient.MySqlCommand(strsql, objconn)
        With objcmd
            .Parameters.AddWithValue("@field1", txtsearch.Text)
        End With
        objdr = objcmd.ExecuteReader
        While (objdr.Read)
            With ListView1.Items.Add(objdr("ProductID"))
                .SubItems.add(objdr("ProductName"))
                .subitems.add(objdr("ProductBrand"))
                .subitems.add(objdr("ProductCategory"))
                .subitems.add(objdr("ProductQuantity"))
                .subitems.add(objdr("ProductDescription"))
                .subitems.add(objdr("ProductManufacturer"))
                .subitems.add(objdr("Stock"))
                .subitems.add(objdr("Supplier"))
                .subitems.add(objdr("ContactNo"))
            End With
            objcmd.Dispose()
            objdr.Close()
        End While
    Catch ex As Exception
        MsgBox(ex.Message)
        Me.fillsview()
    End Try
End Sub

CodePudding user response：

you are bulding the LIKE matching string in wrong way .. try sue

    "SELECT * from tbl_pcperipherals WHERE ProductName LIKE concat(@field1, '%')"

CodePudding user response：

I don't know why you are using a ListView. A DataGridView has a DataSource property so it is easier to code.

Declare your disposable database objects locally in a Using block. Your select statement lacks a field list and a From clause.

Your code closes the reader after the first record is read because objdr.Close() is inside the While loop. Anyway, it is not good to hold the connection open while you update the user interface. Connections should be opened at the last minute and closed as soon as possible. A DataReader requires an open connection. If you Load a DataTable you can close the connection and then fill the ListView.

Private Sub Btnsearch_Click(sender As Object, e As EventArgs) Handles btnsearch.Click
    Try
        ListView1.Items.Clear()
        Dim dt As New DataTable
        Dim strsql = "SELECT * From tbl_pcperipherals WHERE ProductName LIKE @field1"
        Using objconn As New MySqlConnection("Your connection string"),
                objcmd As New MySqlCommand(strsql, objconn)
            objcmd.Parameters.AddWithValue("@field1", txtsearch.Text & "%")
            objconn.Open()
            Using reader = objcmd.ExecuteReader
                dt.Load(reader)
            End Using 'closes and disposes reader
        End Using 'disposes objcmd and closes and disposes objconn
        For Each row As DataRow In dt.Rows
            With ListView1.Items.Add(row("ProductID").ToString)
                .SubItems.Add(row("ProductName").ToString)
                .SubItems.Add(row("ProductBrand").ToString)
                .SubItems.Add(row("ProductCategory").ToString)
                .SubItems.Add(row("ProductQuantity").ToString)
                .SubItems.Add(row("ProductDescription").ToString)
                .SubItems.Add(row("ProductManufacturer").ToString)
                .SubItems.Add(row("Stock").ToString)
                .SubItems.Add(row("Supplier").ToString)
                .SubItems.Add(row("ContactNo").ToString)
            End With
        Next
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub