Question 63952 in PhpMyAdmin Question posted in
The official documentation can be found here.

can fix my error can somebody look at it? – Phpmyadmin

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
Tags:

4

Answers


  1. Chosen as BEST ANSWER
    0

    Hi guys thank you for responding to this question i already fix it to and i change the code a little bit and remove the sql inject vulnerability.

    This fix my problem

    SELECT * FROM tbl_pcperipherals WHERE ProductName LIKE  @field1 '%'"

    Here the change in my code and sql inject vulnerability removed and change to this.

           Private Sub Btnsearch_Click(sender As Object, e As EventArgs) Handles btnsearch.Click
    Try
        ListView1.Items.Clear()
        'Safe in SQL iNJECT'
        objcmd = New MySql.Data.MySqlClient.MySqlCommand("SELECT * FROM tbl_pcperipherals WHERE ProductName LIKE  @field1 '%'", objconn)
        'Unsafe Vulnerable in SQL INJECT'
        'strsql = "SELECT tbl_pcperipherals WHERE ProductName LIKE concat(@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
        End While
        objcmd.Dispose()
        objdr.Close()
    Catch ex As Exception
        MsgBox(ex.Message)
        Me.fillsview()
    End Try
End Sub

  2. 0

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

        "SELECT * from tbl_pcperipherals WHERE ProductName LIKE concat(@field1, '%')"
    Login or Signup to reply.
  3. 0

    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
    Login or Signup to reply.
  4. 0

    I believe if you use the code like this one will solve your issue:

    Dim strsql = string.format("SELECT tbl_pcperipherals WHERE ProductName LIKE '%{0}%'",txtsearch.Text);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search