473,574 Members | 2,457 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Using wildcard w/Access database

[I posted this in the Access forum but the more I think of it, it's probably
more of an ADO issue since I can get it to work in Access but not ASP 30.]

I need to filter an Access 2000 result set in ASP 30 using the ADO
recordset.filte r.

I build the filter in pieces. The first clause of the filter is this...

WHERE word LIKE 'S%'

... to which other clauses are appended with AND.

This all works fine as long as I provide a condition for the first clause
(e.g., word LIKE 'S%').

However, if no condition is specified for the "Word LIKE" clause, I need to
pass a wild card and this is where I have a problem.

I tried constructing the following clauses and encountered the problems
indicated:

sFilter = "word LIKE '%' "
'Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.

sFilter = "word LIKE '*' "
'Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.

sFilter = "word LIKE ""%"" "
'Empty result set

sFilter = "word LIKE ""*"" "
'Empty result set rs.filter=sFilt er

Yet when I try to query directly in Access...

SELECT vWords.Word
FROM vWords
WHERE word LIKE '*';

...it works fine and returns all records.

But this...

SELECT vWords.Word
FROM vWords
WHERE word LIKE '%';

,,,does not return any records
So I am confused about wild cards in Access with ADO.

Without going through complicated logic in the ASP page to test each clause
to build one of several filter sets, how can I pass a wild card to return
all records for a certain condition?

IOW, how do I specify a filter that does this: WHERE word LIKE '*'; in my
ASP 30 page.


Feb 25 '07 #1
5 6475
% is the correct wildcard for ADO.

Why don't you just leave out the LIKE clause entirely when you're not
filtering results?

SELECT vWords.Word
FROM vWords
WHERE word LIKE '*';

Is the same as

SELECT vWords.Word
FROM vWords
Bob Lehmann

"Dave" <da*******@news group.nospamwro te in message
news:er******** *****@TK2MSFTNG P02.phx.gbl...
[I posted this in the Access forum but the more I think of it, it's
probably
more of an ADO issue since I can get it to work in Access but not ASP 30.]

I need to filter an Access 2000 result set in ASP 30 using the ADO
recordset.filte r.

I build the filter in pieces. The first clause of the filter is this...

WHERE word LIKE 'S%'

.. to which other clauses are appended with AND.

This all works fine as long as I provide a condition for the first clause
(e.g., word LIKE 'S%').

However, if no condition is specified for the "Word LIKE" clause, I need
to
pass a wild card and this is where I have a problem.

I tried constructing the following clauses and encountered the problems
indicated:

sFilter = "word LIKE '%' "
'Arguments are of the wrong type, are out of acceptable range, or are
in
conflict with one another.

sFilter = "word LIKE '*' "
'Arguments are of the wrong type, are out of acceptable range, or are
in
conflict with one another.

sFilter = "word LIKE ""%"" "
'Empty result set

sFilter = "word LIKE ""*"" "
'Empty result set rs.filter=sFilt er

Yet when I try to query directly in Access...

SELECT vWords.Word
FROM vWords
WHERE word LIKE '*';

..it works fine and returns all records.

But this...

SELECT vWords.Word
FROM vWords
WHERE word LIKE '%';

,,,does not return any records
So I am confused about wild cards in Access with ADO.

Without going through complicated logic in the ASP page to test each
clause
to build one of several filter sets, how can I pass a wild card to return
all records for a certain condition?

IOW, how do I specify a filter that does this: WHERE word LIKE '*'; in my
ASP 30 page.


Feb 25 '07 #2
Thanks Bob. But any idea why it is not working?

As I tried to explain in the original post, this is a complex WHERE clause.
For subsequent conditions I can evaluate the criteria and either append them
with an AND or just leave them off. However, the first condition (in my
case "WHERE word LIKE") cannot start with an AND.

If I can use a wild card - like I believe I should be able to - it avoids a
lot of ASP code to evaluate all of the conditions necessary to determine
whether or not I need to append AND to my filter string.

With the time I have invested in this it would have been faster to just code
all the logic into the ASP.

But for my own edification, I would like to know why "rs.filter="wor d LIKE
'%'" does not work.

"Bob Lehmann" <no****@dontbot herme.zzzwrote in message
news:e%******** ********@TK2MSF TNGP03.phx.gbl. ..
>% is the correct wildcard for ADO.

Why don't you just leave out the LIKE clause entirely when you're not
filtering results?

SELECT vWords.Word
FROM vWords
WHERE word LIKE '*';

Is the same as

SELECT vWords.Word
FROM vWords
Bob Lehmann

"Dave" <da*******@news group.nospamwro te in message
news:er******** *****@TK2MSFTNG P02.phx.gbl...
>[I posted this in the Access forum but the more I think of it, it's
probably
>more of an ADO issue since I can get it to work in Access but not ASP
30.]

I need to filter an Access 2000 result set in ASP 30 using the ADO
recordset.filt er.

I build the filter in pieces. The first clause of the filter is this...

WHERE word LIKE 'S%'

.. to which other clauses are appended with AND.

This all works fine as long as I provide a condition for the first clause
(e.g., word LIKE 'S%').

However, if no condition is specified for the "Word LIKE" clause, I need
to
>pass a wild card and this is where I have a problem.

I tried constructing the following clauses and encountered the problems
indicated:

sFilter = "word LIKE '%' "
'Arguments are of the wrong type, are out of acceptable range, or are
in
>conflict with one another.

sFilter = "word LIKE '*' "
'Arguments are of the wrong type, are out of acceptable range, or are
in
>conflict with one another.

sFilter = "word LIKE ""%"" "
'Empty result set

sFilter = "word LIKE ""*"" "
'Empty result set rs.filter=sFilt er

Yet when I try to query directly in Access...

SELECT vWords.Word
FROM vWords
WHERE word LIKE '*';

..it works fine and returns all records.

But this...

SELECT vWords.Word
FROM vWords
WHERE word LIKE '%';

,,,does not return any records
So I am confused about wild cards in Access with ADO.

Without going through complicated logic in the ASP page to test each
clause
>to build one of several filter sets, how can I pass a wild card to return
all records for a certain condition?

IOW, how do I specify a filter that does this: WHERE word LIKE '*'; in
my
ASP 30 page.



Feb 25 '07 #3
Dave wrote:
Thanks Bob. But any idea why it is not working?

As I tried to explain in the original post, this is a complex WHERE
clause. For subsequent conditions I can evaluate the criteria and
either append them with an AND or just leave them off. However, the
first condition (in my case "WHERE word LIKE") cannot start with an
AND.
There is an old hack that used "WHERE 1=1" as the beginning of each sql
statement being built this way for that very reason. It allows all
subsequest conditions to begin with the word "AND"

However, I don't recommend it. It's not really that hard to construct a
statement without resorting to this hack, no matter how complicated it is.
>
If I can use a wild card - like I believe I should be able to - it
avoids a lot of ASP code to evaluate all of the conditions necessary
to determine whether or not I need to append AND to my filter string.
Using LIKE with the wildcard as the first character in the comparison string
prevents the query engine from using an index: it has to scan every row. I
would prefer to write the extra code to make sure the query executes as
efficiently as possible. It's hard enough to use Access as an asp backend
without deliberately executing poorly performing sql statements.
With the time I have invested in this it would have been faster to
just code all the logic into the ASP.

But for my own edification, I would like to know why "rs.filter="wor d
LIKE '%'" does not work.

Wait a minute. Are you building a WHERE clause for a sql statement or
setting a recordset object's Filter property? These are two decidedly
different things. For example, with the Filter property, the first character
in the comparison string _cannot_ be a wild card - only the last.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Feb 25 '07 #4
Okay so this is the issue:
with the Filter property, the first character in the comparison string
_cannot_ be a wild card - only the last.
So the syntax with ADO is more restrictive than with the native Access
(which will accept "word LIKE '*'")

I guess that's good to know athough I consulted a lot of sources on the use
of wildcards none of them warned on this limitation.

Thanks
Dave


"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcomwrote in message
news:OT******** ******@TK2MSFTN GP05.phx.gbl...
Dave wrote:
>Thanks Bob. But any idea why it is not working?

As I tried to explain in the original post, this is a complex WHERE
clause. For subsequent conditions I can evaluate the criteria and
either append them with an AND or just leave them off. However, the
first condition (in my case "WHERE word LIKE") cannot start with an
AND.

There is an old hack that used "WHERE 1=1" as the beginning of each sql
statement being built this way for that very reason. It allows all
subsequest conditions to begin with the word "AND"

However, I don't recommend it. It's not really that hard to construct a
statement without resorting to this hack, no matter how complicated it is.
>>
If I can use a wild card - like I believe I should be able to - it
avoids a lot of ASP code to evaluate all of the conditions necessary
to determine whether or not I need to append AND to my filter string.
Using LIKE with the wildcard as the first character in the comparison
string prevents the query engine from using an index: it has to scan every
row. I would prefer to write the extra code to make sure the query
executes as efficiently as possible. It's hard enough to use Access as an
asp backend without deliberately executing poorly performing sql
statements.
>With the time I have invested in this it would have been faster to
just code all the logic into the ASP.

But for my own edification, I would like to know why "rs.filter="wor d
LIKE '%'" does not work.

Wait a minute. Are you building a WHERE clause for a sql statement or
setting a recordset object's Filter property? These are two decidedly
different things. For example, with the Filter property, the first
character in the comparison string _cannot_ be a wild card - only the
last.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Feb 26 '07 #5
Dave wrote:
Okay so this is the issue:
> with the Filter property, the first character in the comparison
string _cannot_ be a wild card - only the last.

So the syntax with ADO is more restrictive than with the native Access
(which will accept "word LIKE '*'")
First of all, let's get your terminology straight so you will be better
equipped to ask for help in the future:
it's not "native Access": it's "sql language". In particular, since you are
using an mdb file, the Jet database engine is used to manage it and the
varianat of sql used is called "JetSQL". All relational databases use some
variant of the sql language.

In addition, the use of the Filter property in web applications is highly
discouraged. Think about what has to happen in order for ADO to filter
records itself: it has to retrieve ALL the records in your database table,
pulling them across the network and building an inefficient recordset
container to hold them. Then, it applies the filter. Oh!, the records are
all still held in memory: ADO only "shows" you the records than meet the
filter criteria you set.

Now, look at the difference when using a sql statement to filter and
retrieve your records: the sql statement is sent to the database engine
(Jet) to be executed. The database engine analyzes the query and creates the
most efficient query plan it can come up with based on the query criteria
and the indexes you have built on the table to assist the database engine's
query optimizer. Using that plan, it quickly retrieves ONLY the records that
match the criteria and passes them to the client application (ADO). Less
network traffic, less memory resources consumed on the web server, less time
connected to the database, better chance to avoid the problems most people
encounter when using Jet as the backend for their web applications.

Again, it's hard enough to use Jet as your web application's backend without
deliberately doing things to make it work harder.
>
I guess that's good to know athough I consulted a lot of sources on
the use of wildcards none of them warned on this limitation.
:-)
Well, you must have skipped the ADO documentation
(http://msdn.microsoft.com/library/en...reference.asp).
This is well-documented here:
http://msdn.microsoft.com/library/en...b7a9be4c22.asp

I suspect they deliberately made the Filter property so restrictive in order
to discourage people from using it.

Further points to consider: most tutorials and books I've seen commit the
crime of teaching beginners to use dynamic sql. Sometimes, dynamic sql
cannot be avoided, but it should be the last tool drawn out of the tool
chest instead of the first. The major problem with dynamic sql is it leaves
web applications that use it vulnerable to hackers using a tchnique called
sql injection:

http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/...e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&l...TNGP12.phx.gbl

http://groups.google.com/groups?hl=e...tngp13.phx.gbl
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Feb 26 '07 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
3014
by: DaveS | last post by:
Hello, I'm using Oracle 9.2 and would like to automatically save a copy of each deleted record in XML format to another database table. There would be one XML record for each deleted record. I'd like to store the XML record in a CLOB. I've created a table and added a trigger to capture the delete event but I'm not sure how to create the...
4
38799
by: Lucky | last post by:
I have a vb.net windows app that connects to an access database. The database has 1 table. One of the columns is called "Address". A user enters any address into a textbox (txtValue) then clicks the Find button (cmdFind) and a datagrid displays all the rows with this address. The problem is that it will only find the the address if the user...
3
8511
by: MasterChief | last post by:
I have a form that uses the POST method to call up test.asp and it passes what is typed into the text box. Since is uses the Like command the user can enter stuff like %Constant% to get something that is like what the user typed in. When I call up the test.asp page it is grabbing the text fine but isn't running the SQL command the correct way....
5
3031
by: Dan Hardy | last post by:
I am trying to search for a field where the wildcard needs to be in the field name i.e.: select * from userlist where fun??? = true order by lasNam, firNam However, Access won't let me do this. Can anyone help show me how to do this? Thanks.
1
7255
by: deko | last post by:
I have a form where users can enter a string with asterisks to perform a wildcard search. Currently, the string entered by the user looks like this: *somestring* The purpose is to match any database field containing "somestring". Is there a way to avoid the need for the asterisks? This would make it easier for the users and also I...
2
2134
by: Ken Yee | last post by:
First a little background: I've written an httphandler to handle wildcard extensions (i.e., I want to handle all URLs that come in rather than just URLs w/ a specific file extension so I can give friendly names to various dynamic web pages). To do this, I've written an httphandler and put it in the web.config for my webapp (stored in the...
10
6715
by: Robert | last post by:
How do you get an accurate count of the number of records returned from a query when using linked tables. I have an access 2003 database as a front end to another access 2003 database that contains only tables. I have linked the tables for the front end to the back end database. I am trying to set the recordsource of a form to a query...
8
8038
by: Roland Hall | last post by:
In Access you use "*" + + "*", + can be replaced with & Calling a parameterized query in Access requires % be used in place of *, however, all that I have read show dynamic SQL passed to Access: WHERE LIKE '" & ASPvar & "' % ORDER BY ... However, my call is similar to: conn.qMyLookup strVar, rs
0
2659
by: savage678 | last post by:
Hi Everyone, I am new to this forum and am i dire need of some help. I am trying to use wildcard searches in infopath. I have it connected to an access database using data connection. I have some code which will allow me to do it but i dont know how to change the code. Could someone please help me. I have added the code for you to. ...
0
7738
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8258
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7833
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8118
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6481
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5321
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3756
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2254
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1081
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.