How do I write this: WHERE word LIKE '*' in ADO?

D

Dave

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
and build 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.
 
R

Rod

You would be better not to use that part of the where clause if you are not
doing a LIKE comparison.

Use code something like this, assiming that S is the string you are using to
compare word with

sLink = " WHERE "
If S <> "" Then ' use Not IsNull(S) if appropriate - eg. it is the contents
of a text box
sFilter = sLink & "word LIKE "S*"
sLink = " AND "
End If

You can do this for each condition that you test for. If a condition is to
be tested then change the sLink variable to " AND " afterwards. The first
condition set begins with WHERE. Subsequent conditions are linked with AND.

This way you only test for conditions for which there are criteria. You do
not need to worry about wildcards at all.

Rod.
 
J

Jamie Collins

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 withADO.

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

FWIW the syntax

WHERE word LIKE '%'

works for me i.e. returns all rows where column 'word' is not null.

Rather than dynamic SQL, the best way IMO to avoid the 'complicated
logic' on the client side is to create a SQL PROCEDURE in back end and
pass parameter values e.g.

CREATE TABLE Test (
col1 VARCHAR(12)
)
;
INSERT INTO Test VALUES ('Six')
;
INSERT INTO Test VALUES ('Seven')
;
INSERT INTO Test VALUES ('Eight')
;
INSERT INTO Test VALUES (NULL)
;
CREATE PROCEDURE ProcTest (
arg_first_letter CHAR(1) = NULL
)
AS
SELECT word
FROM Test
WHERE word LIKE arg_first_letter & '%'
;
EXECUTE ProcTest 'S'
;
EXECUTE ProcTest
;

Jamie.

--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top