How to use LIKE keyword in to MS ACCESS DB

Posted on 2006-04-20
Last Modified: 2011-09-20

I know this is very simple and I have done it in other databases, but for some crazy reason it doesnt work
when i try to use it in VB to access data in MS Access.

Ok this is the scenario I have 1 text box which i want the user to retrieve last names of other users in the
database, but just incase some one doesnt know how to spell correctly a person last name I would like to use
a Wild card Key like the keyword 'LIKE' to retrieve anything that resembles what the user put in the text box.

this is my sample code:


Dim connString As String = " Provider = Microsoft.Jet.OLEDB.4.0; Data Source = X:\members.mdb;"
        Dim myConnection As OleDbConnection = New OleDbConnection ' Stores a connection
        Dim insertCmd As OleDbCommand = New OleDbCommand     'Stores object of OLEDB Command

        Dim LastName = txtLastName.Text.Trim

        'Opening the DB Connection
        myConnection.ConnectionString = connString

        ' Now to create a select command to search Customer ID in the DB
        'Trying to put a search command with the user input from the text box

        With insertCmd

            .Connection = myConnection
            .CommandText = "Select LastName  From UserTable " & _  *****''''Here is my problem!!!
                            " Where LastName Like %@LastName "

            .CommandType = CommandType.Text

            .Parameters.Add(New OleDbParameter("@LastName", OleDbType.Char, 25))
            .Parameters("@LastName").Value = LastName

      End With




            'To bind the Dataset to the DataGrid :)
            grdResults.DataSource = DataSet

            'Tell the DataGrid which table in the Dataset to use
            grdResults.DataMember = DataSet.Tables(0).TableName

            'Setting the Alternating Colors property to the Grid
            grdResults.AlternatingBackColor = Color.WhiteSmoke

            'Set the GridLineStyle Property :)
            grdResults.GridLineStyle = DataGridLineStyle.None

            'Set the SelectionBackColor and the Selection ForeColor Properties
            grdResults.SelectionBackColor = Color.LightGray
            grdResults.SelectionForeColor = Color.Black


        Catch OleDbExceptionErr As OleDbException
        Catch InvalidOperationExceptionErr As InvalidOperationException

        End Try

        'Cleaning up the Mess
        insertCmd = Nothing
        Adapter = Nothing
        DataSet = Nothing

        myConnection = Nothing


Keep in mind that everything else works just fine.
Why!?!..well because I changed the SQL code from

.CommandText = "Select LastName  From UserTable " & _  
                            " Where LastName=@LastName "

This is good, but this then means the user has to be specific to a person last name if not then it doesnt retrieve, which of course makes sense based on the SQL statement.

//////////////** Also to keep ahead of the game//////
I have tried a few other ways and it still doesnt work

            .CommandText = "Select LastName  From UserTable " & _  
                            " Where LastName Like ' %@LastName'  "

And tried it this way:

  .CommandText = "Select LastName  From UserTable " & _  
                            " Where LastName Like ' %@LastName% ' "

tried it like this :

  .CommandText = "Select LastName  From UserTable " & _  
                            " Where LastName Like %@LastName  "


So please tell me where Am i going wrong LOL

Thankyou in advance for helping a noob out !!! :)

Question by:masterat03
    LVL 37

    Expert Comment

    Try this

                .Connection = myConnection
                .CommandText = "Select LastName  From UserTable " & _  *****''''Here is my problem!!!
                                " Where LastName Like @LastName "

                .CommandType = CommandType.Text

                .Parameters.Add(New OleDbParameter("@LastName", OleDbType.Char, 25))
              .Parameters("@LastName").Value = "%" & LastName
    LVL 4

    Accepted Solution

    try this:

            With insertCmd

                .Connection = myConnection
                .CommandText = "Select LastName  From UserTable " & _  
                                " Where LastName Like @LastName "

                .CommandType = CommandType.Text

                .Parameters.Add(New OleDbParameter("@LastName", OleDbType.Char, 25))
              .Parameters("@LastName").Value = "%" & LastName & "%"

         End With


    Author Comment

    Thankyou G_johnson for the help it worked out like a charm.

    I was wondering if there is a good book out there that contains SQL code to use in
    Because im always getting trouble with this part. Even though I know a decent amount of SQL
    I always get my hands tied when it comes to this part.

    LVL 4

    Expert Comment

    don't know, don't use books.  LOL
    just research, trial and error, looking at things closely, etc.
    and EE a lot

    Author Comment

    LOL yeah so far the same with myself as well. Usually search Google and now I'm starting to get heavily addicted to EE LOL
    as always thankyou for your help.

    Take care :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    I'm currently working for a company where I have to upgrade over 50 VB6 programs to VB.NET 2008.  So far I'm about half way through, and I've learned quite a few tricks that drastically improve the performance of VB.NET apps. Because there are a…
    If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now