Home > Mobile >  My search bar isn't working and I cant figure out why. VB.net/Access
My search bar isn't working and I cant figure out why. VB.net/Access

Time:12-02

    Private Sub Button1_Click_1(sender As Object, e As EventArgs) Handles Button1.Click

    Try
        '  Dim i As Integer
        Dim sql As String
        Dim cmd As New OleDb.OleDbCommand
        conn.Open()
        sql = “Select * from tblBooks where AUTHOR like '" & TextBox1.Text & "'"
        cmd.Connection = conn
        cmd.CommandText = sql

    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        conn.Close()
    End Try
End Sub

my search button is currently looking like this. Id appreciate any help

CodePudding user response:

Do not declare connections outside of the method where they are used. Connections, commands and DataReaders have a Dispose method which must be called to release unmanaged resources. The language provides Using blocks to close and dispose these objects.

Never concatenate strings to build sql strings. Always use parameters to avoid sql injection.

You set up a command but you never executed it and even if you did execute it you haven't provided any way to display the return.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim dt As New DataTable
    Dim sql = "Select * from tblBooks where AUTHOR like @Author;"
    Try
        Using conn As New OleDbConnection("Your connection string"),
            cmd As New OleDbCommand(sql, conn)
            cmd.Parameters.Add("@Author", OleDbType.VarChar).Value = $"*{TextBox1.Text}*"
            conn.Open()
            Using reader = cmd.ExecuteReader
                dt.Load(reader)
            End Using 'Closes and disposes reader
        End Using 'Closes and disposes connection and disposes command
        DataGridView1.DataSource = dt
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

CodePudding user response:

As is, it will only return an exact match. Try adding wildcards:

sql = "Select * from tblBooks where AUTHOR like '*" & TextBox1.Text & "*'"
  • Related