Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Wildcard in dataset filter query

Posted on 2013-01-08
5
Medium Priority
?
315 Views
Last Modified: 2013-01-08
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
0
Comment
Question by:Petermcg001
  • 3
  • 2
5 Comments
 
LVL 12

Accepted Solution

by:
Jared_S earned 2000 total points
ID: 38755549
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
 
LVL 1

Author Comment

by:Petermcg001
ID: 38755620
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
 
LVL 12

Expert Comment

by:Jared_S
ID: 38755726
In SQL % is any string, but in VB.net % is any character.

Try using * instead.
0
 
LVL 1

Author Comment

by:Petermcg001
ID: 38755740
Thanks for clarifying that and your help.
0
 
LVL 12

Expert Comment

by:Jared_S
ID: 38755912
No problem - happy coding!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

772 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