Using wildcard w/Access database

[Dave]

[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.filter.

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=sFilter

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.

 

[Bob Lehmann]

% 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]

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="word LIKE
'%'" does not work.

% 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

[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.filter.

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=sFilter

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.

 

[Bob Barrows [MVP]]

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="word
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.

 

[Dave]

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]]

Okay so this is the issue:


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-us/ado270/htm/mdmscadoapireference.asp).
This is well-documented here:
http://msdn.microsoft.com/library/en-us/ado/html/80263a7a-5d21-45d1-84fc-34b7a9be4c22.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/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

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

Access:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]


Bob Barrows