[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 569
  • Last Modified:

How to use LIKE keyword in VB.net to MS ACCESS DB


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
        myConnection.Open()


        ' 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

            

            
   Try

            Adapter.Fill(DataSet)
           

            '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

            'DataSet.Tables(0).Rows.Count()

        Catch OleDbExceptionErr As OleDbException
            Debug.WriteLine(OleDbExceptionErr.Message)
        Catch InvalidOperationExceptionErr As InvalidOperationException
            Debug.WriteLine(InvalidOperationExceptionErr.Message)



        End Try


        'Cleaning up the Mess
        insertCmd.Dispose()
        insertCmd = Nothing
        Adapter.Dispose()
        Adapter = Nothing
        DataSet.Dispose()
        DataSet = Nothing

        myConnection.Dispose()
        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 !!! :)


0
masterat03
Asked:
masterat03
  • 2
  • 2
1 Solution
 
gregoryyoungCommented:
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
0
 
g_johnsonCommented:
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

0
 
masterat03Author Commented:
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 VB.net
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.

0
 
g_johnsonCommented:
don't know, don't use books.  LOL
just research, trial and error, looking at things closely, etc.
and EE a lot
0
 
masterat03Author Commented:
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 :)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now