Member Avatar for analys

Hello,
I'm having error when I try to filter my data (name)
In my Personal table, I have a column for name for ex: Mary Elizabeth Roger
When I type the name in searchbox, it appears as syntax error.
Here's my code

Private Sub SearchPD_Record()
        Dim da As New OleDbDataAdapter
        Dim dt As New DataTable

        'try catch block is used to catch the error
        Try
            connection.Open()
            command.Connection = connection
            command.CommandText = "SELECT * FROM Personal"
            command.ExecuteNonQuery()

            If Me.combosearch.Text = "Name" Then
                command.CommandText = command.CommandText & " where name like '%" & Me.txtsearch.Text
            Else
                command.CommandText = command.CommandText & " where idno =" & Me.txtsearch.Text
            End If
            da.SelectCommand = command
            da.Fill(dt)

            Me.dgrid.DataSource = dt
            If dt.Rows.Count = 0 Then
                MsgBox("No record found!")
            End If

        Catch ex As Exception
            MsgBox(ErrorToString)
        Finally
            connection.Close()
        End Try
    End Sub
  • 2 Contributors
  • 3 Replies
  • 187 Views
  • 53 Minutes Discussion Span
  • Latest Post Latest Post by Reverend Jim
Member Avatar for Reverend Jim

Add the closing single quote as

command.CommandText = command.CommandText & " where name like '%" & Me.txtsearch.Text & "'"
Member Avatar for analys

Thanks, it works!
But if I'm about to type Mary only then it couldn't find any record.
What should I do?

Member Avatar for Reverend Jim

You need a wildcard at each end like

    command.CommandText = command.CommandText & " where name like '%" & Me.txtsearch.Text & "%'"
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.