Solved

Wildcard in dataset filter query

Posted on 2013-01-08
5
286 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 500 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

IP addresses can be stored in a database in any of several ways.  These ways may vary based on the volume of the data.  I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.   …
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now