Using Like in SQL Statement

Hi,

I am trying to use the like statement in an SQL statement but I am unable to!

This works without the "Like" command:

da.SelectCommand = New OleDbCommand("Select * from Customers Where Name= '" & Me.txtSearch.Text & "'", ocon)

This is what I am trying to get to work:

da.SelectCommand = New OleDbCommand("Select * from Customers Where Name= Like '" & Me.txtSearch.Text & "*" & "'", ocon)

Any Idea's

Darrell....

You have to bookend the like parameters with a %. See below.

da.SelectCommand = New OleDbCommand("Select * from Customers Where Name= Like '%" & Me.txtSearch.Text & "*" & "%'", ocon)

Use it as follows:
Instead of the * character use % character and no = character in front of the like operator.

da.SelectCommand = New OleDbCommand("Select * from Customers Where Name Like '" & Me.txtSearch.Text & "%" & "'", ocon)

I hope this helps.

Regards,

Maurice

You use the '%' as the wilcard character.

If you want to search for a string starting with 'ABC' then you would use:
Select * from Customers where LName like 'ABC%'

If you want to search for a string ending with 'ABC' then you would use:
Select * from Customers where LName like '%ABC'

If you want to search for a string containing 'ABC' then you would use:
Select * from Customers where LName like '%ABC%'

P.S. 'Name' is a reserved word and you should consider changing the name of that field to something else in your database.

HTH,
Greg

Thanks for your feedback but I cannot get it to work?

You will see below the original code that works and I have coded out the part that does not work.

Here is the full code:

Public Sub OpenSearch()
On Error Resume Next

Dim frm As New frmCustDet()
With frm
'Here we are going to Open the Customer Recordset for the Item
Dim ocon As OleDbConnection = New OleDbConnection(gc_Connect)
ocon.Open()

Dim ds As New DataSet()

Dim da As OleDbDataAdapter = New OleDbDataAdapter()

'This part works fine
da.SelectCommand = New OleDbCommand("Select * from Customers Where Name= '" & Me.txtSearch.Text & "'", ocon)

'This part I cannot get to work
'da.SelectCommand = New OleDbCommand("Select * from Customers Where Name= Like '%" & Me.txtSearch.Text & "*" & "%'", ocon)
da.Fill(ds)

Dim dr As DataRow

'Now we have the Record - Hopefully
If ds.Tables(0).Rows.Count > 0 Then
dr = ds.Tables(0).Rows(0)
.lngPkid = dr("PKID")
.txtName.Text = dr("Name") & ""
.txtStreet.Text = dr("Street") & ""
.txtArea.Text = dr("Area") & ""
.txtTown.Text = dr("Town") & ""
.txtCounty.Text = dr("County") & ""
.txtPostCode.Text = dr("PostCode") & ""
.txtTelNo.Text = dr("TelNo") & ""
.txtDateAdded.Text = dr("DateAdded") & ""
.ChkActive.Checked = dr("ChkActive") & ""
End If

dr = Nothing
da.Dispose()
ds.Dispose()
ocon.Close()
.ShowDialog(Me)
'Me.RefreshCustomers()
End With

End Sub

Many Thanks

Darrell...

Hi Darell,

You still got that * and = character after the name in the select statement.
Use a % character for the wildcard search.

So the next line will work.
'da.SelectCommand = New OleDbCommand("Select * from Customers Where Name Like '%" & Me.txtSearch.Text & "%'", ocon)

REMOVE the '=' after Name. STRONGLY CONSIDER USING a different fieldname other than Name as it is a RESERVED word.

ALSO, unless you are searching for an '*' in the name, REMOVE IT! The * is not a wildcard character, the % is.


'This part I cannot get to work
'da.SelectCommand = New OleDbCommand("Select * from Customers Where Name= Like '%" & Me.txtSearch.Text & "*" & "%'", ocon)
da.Fill(ds)


Your code should be this:
da.SelectCommand = New OleDbCommand("Select * from Customers Where SomeName Like '%" & Me.txtSearch.Text & "%'", ocon)

HTH,
Greg

Hi

Thanks for that, it now works. I have also changed the field name as you recommended.

Can you help with one other thing?

If the search brings up more than 1 customer with the same name the VB form will only show the first customer. In MS Access when you run the search if there is more than one customer returned you are able to scroll between records with first, next, last and add new buttons at the bottom of the form. Can this be done in VB.NET also?

Many Thanks

Darrell...

Yes it can but you will have to use a dataset instead of a datareader if you want something other than forward-only reading.

You can do it a couple of ways. You can use the Data Form Wizard and it will bind the fields with the controls automatically and generate the sql commands for you...

OR.. (which is the way I like to do it - harder but you have more control over it.)

You can add the buttons yourself and then iterate through the dataset to rerieve the information.

Personally, I would start with the Data Form Wizard and let it do most of the work for you, and then go back and tweak anything that you want specifically.

HTH,
Greg