Wildcard in dataset filter query

Hello

I'm adding a query with a filter to a dataset with the designer.

My query is:-

SELECT Counter, SiteRef, ccount, SiteName, AddressLine1, AddressLine2, AddressLine3, Town, Postcode
FROM SiteDetails
WHERE (Postcode = @POSTCODETOFIND )

This works OK but I'm struggling to get the LIKE and wildcard option to work.

Please could you suggest the correct syntax.

Thanks

Petermcg001

Asked: 2013-01-08

Advertise Here

.NET Programming

1 Solution

LVL 12

Jared_SCommented: 2013-01-08

If your passing the wildcard to sql as part of a variable, then it's pretty easy to handle.

If you're trying to add it to your variable within your sql statement it has to be handled a little differently.

ex.

This works:

declare @name as varchar(15)
set @name = 'smit%'
select * from staff where lastname like @name

This, or any variation of this, won't work:

declare @name as varchar(15)
set @name = 'smit'
select * from staff where lastname like (@name + '%')

So with your code you can do this:

SELECT Counter, SiteRef, ccount, SiteName, AddressLine1, AddressLine2, AddressLine3, Town, Postcode
FROM SiteDetails
WHERE (Postcode = @POSTCODETOFIND ) OR (Postcode LIKE @POSTCODETOFIND )

as long as you're setting the wildcard character in your variable prior to passing it to sql.

LVL 1

Petermcg001Author Commented: 2013-01-08

Hello, thanks for looking at this.

With your help I've got it to work but I'm struggling to understand the following.

If I use:-

" Dim strFind As String
strFind = Trim(txtPostcode.Text) + "%"

Me.SiteDetailsTableAdapter.FillBy(Me.OilDistributionSystemDataSet.SiteDetails, strFind)"

it doesn't work. However if I use:-

Dim strFind As String
strFind = Trim(txtPostcode.Text) + "%%%%%%"

Me.SiteDetailsTableAdapter.FillBy(Me.OilDistributionSystemDataSet.SiteDetails, strFind)

it does work. I thought the "%" should match any string.