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
LVL 1
Petermcg001Asked:
Who is Participating?
 
Jared_SConnect With a Mentor Commented:
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.
0
 
Petermcg001Author Commented:
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.
0
 
Jared_SCommented:
In SQL % is any string, but in VB.net % is any character.

Try using * instead.
0
 
Petermcg001Author Commented:
Thanks for clarifying that and your help.
0
 
Jared_SCommented:
No problem - happy coding!
0
All Courses

From novice to tech pro — start learning today.