Trouble with Access Like Query in ASP

[Randy Weber]

I am getting unexpected results with a Like Query using ASP and an
Access database.

This query -
SELECT PN, Qty From Inventory Where PN Like 'SSW-10%';
returns what I expect:
PN Qty
SSW-101-01-T-D 1780
SSW-101-01-T-D 1780
SSW-102-01-G-D 8900

.... but this one -
SELECT PN, Qty From Inventory Where PN Like 'SSW-101%';
returns 0 results, even though looking at the results above, it should
return 2 of the records.

The only difference is the additional character '101%' versus '10%'.
Offline, it returns what I expect.

Thanks in advance.

[Jerry Boone]

What about...

Like 'SSW-101' + '%'

or

Like 'SSW-101' & '%'

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
Secure Hosting and Development Solutions for ASP, ASP.NET, SQL Server, and
Access

"Randy Weber" <ra******@yahoo.com> wrote in message
news:8d**************************@posting.google.c om...

I am getting unexpected results with a Like Query using ASP and an
Access database.

This query -
SELECT PN, Qty From Inventory Where PN Like 'SSW-10%';
returns what I expect:
PN Qty
SSW-101-01-T-D 1780
SSW-101-01-T-D 1780
SSW-102-01-G-D 8900

... but this one -
SELECT PN, Qty From Inventory Where PN Like 'SSW-101%';
returns 0 results, even though looking at the results above, it should
return 2 of the records.

The only difference is the additional character '101%' versus '10%'.
Offline, it returns what I expect.

Thanks in advance.

[Rich P]

I'm must inquiring if you are using '%' as a wildcard. If you are -
Access does not support % as a wildcard. That would be a Sql Server
wildcard. Need to use * for Access. If this is not your case, then
disregard this reply.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Randy]

I tried both and got the same results:
This did not return results:
SELECT PN, Qty From Inventory Where PN Like 'SSW-101' & '%'
and this returned two as before:
SELECT PN, Qty From Inventory Where PN Like 'SSW-10' & '%'

Note: just for kicks, I used the wildcard on both sides as in:
SELECT PN, Qty From Inventory Where PN Like '%101%'
... and it returned the results I expected.
Thanks in advance.

Randy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Randy]

I am using MS Access and and ADO connection to the database. With ASP,
the wildcard character has to be the %, not * as usual.
Thanks in advance.

Randy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Lyle Fairfield]

Randy <an*******@devdex.com> wrote in news:3fe3359a$0$198$75868355
@news.frii.net:

I tried both and got the same results:
This did not return results:
SELECT PN, Qty From Inventory Where PN Like 'SSW-101' & '%'
and this returned two as before:
SELECT PN, Qty From Inventory Where PN Like 'SSW-10' & '%'

Note: just for kicks, I used the wildcard on both sides as in:
SELECT PN, Qty From Inventory Where PN Like '%101%'
.. and it returned the results I expected.

from ADO help:

In a LIKE clause, you can use a wildcard at the beginning and end of the
pattern (for example, LastName Like '*mit*') or only at the end of the
pattern (for example, LastName Like 'Smit*').

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[David W. Fenton]

Mi************@Invalid.Com (Lyle Fairfield) wrote in
<Xn*******************@130.133.1.4>:

Randy <an*******@devdex.com> wrote in news:3fe3359a$0$198$75868355
@news.frii.net:

I tried both and got the same results:
This did not return results:
SELECT PN, Qty From Inventory Where PN Like 'SSW-101' & '%'
and this returned two as before:
SELECT PN, Qty From Inventory Where PN Like 'SSW-10' & '%'

Note: just for kicks, I used the wildcard on both sides as in:
SELECT PN, Qty From Inventory Where PN Like '%101%'
.. and it returned the results I expected.

from ADO help:

In a LIKE clause, you can use a wildcard at the beginning and end
of the pattern (for example, LastName Like '*mit*') or only at the
end of the pattern (for example, LastName Like 'Smit*').

???

But not:

LastName Like '*son'

Is that correct?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Lyle Fairfield]

dX********@bway.net.invalid (David W. Fenton) wrote in
news:94***************************@24.168.128.74:

Mi************@Invalid.Com (Lyle Fairfield) wrote in
<Xn*******************@130.133.1.4>:

Randy <an*******@devdex.com> wrote in news:3fe3359a$0$198$75868355
@news.frii.net:

I tried both and got the same results:
This did not return results:
SELECT PN, Qty From Inventory Where PN Like 'SSW-101' & '%'
and this returned two as before:
SELECT PN, Qty From Inventory Where PN Like 'SSW-10' & '%'

Note: just for kicks, I used the wildcard on both sides as in:
SELECT PN, Qty From Inventory Where PN Like '%101%'
.. and it returned the results I expected.

from ADO help:

In a LIKE clause, you can use a wildcard at the beginning and end
of the pattern (for example, LastName Like '*mit*') or only at the
end of the pattern (for example, LastName Like 'Smit*').

???

But not:

LastName Like '*son'

Is that correct?

I believe that it is.

But, this is not a simple issue. Although the help file uses "*" in its
example, it seems that "%" is the character that must be used.

And MS has different answers in different articles. Here is one from KB
225048 which outlines Issues Migrating from DAO/Jet to ADO/Jet.

**** begin quote ****
Wild Card Characters
The query wild-card characters are different in DAO than in ADO. DAO exposes
the following characters for use with the SQL LIKE operator:

Character Function
* Match any string
? Match any character
# Match any digit
[a-cf] Match any of 'a' through 'c' or 'f'
[~a-c] Match anything but of 'a' through 'c'

ADO exposes the following ANSI wildcard characters:

Character Function
% Match any string
_ Match any character

Wildcards and Stored Queries
If you have a stored QueryDef in an MDB file, created through Access or DAO,
that uses wildcard characters, it will not return any records if run under
ADO. The OLEDB provider for Jet recompiles the SQL and tells the query engine
to use the ANSI wildcard characters (see table above).

If you create a QueryDef in a Jet 4.0 database using the ADO CREATE PROCEDURE
or CREATE VIEW statements and ANSI wildcards, the queries will not run
correctly under DAO 3.6. More information on ANSI query issues is in the
"Access 2000 and Legacy Application Compatibility" section later in this
article.

**** end quote ****

Although I am an ADO enthusiast this article raises issues that have made me
reconsider any notion I may have had about using ADO with JET.

<http://support.microsoft.com/default.aspx?
scid=http://support.microsoft.com:80/support/kb/articles/Q225/0/48.ASP&NoWebC
ontent=1>
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[David W. Fenton]

Mi************@Invalid.Com (Lyle Fairfield) wrote in
<Xn*******************@130.133.1.4>:

Although I am an ADO enthusiast this article raises issues that
have made me reconsider any notion I may have had about using ADO
with JET.

<http://support.microsoft.com/default.aspx?
scid=http://support.microsoft.com:80/support/kb/articles/Q225/0/48.
ASP&NoWebC ontent=1>

Lots of MS's documentation is wrong, Lyle.

Surely you could try it and verify for yourself?

I'd be interested to know the answer.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Lyle Fairfield]

dX********@bway.net.invalid (David W. Fenton) wrote in
news:94***************************@24.168.128.90:

Mi************@Invalid.Com (Lyle Fairfield) wrote in
<Xn*******************@130.133.1.4>:

Although I am an ADO enthusiast this article raises issues that
have made me reconsider any notion I may have had about using ADO
with JET.

<http://support.microsoft.com/default.aspx?
scid=http://support.microsoft.com:80/support/kb/articles/Q225/0/48.
ASP&NoWebC ontent=1>

Lots of MS's documentation is wrong, Lyle.

Surely you could try it and verify for yourself?

I'd be interested to know the answer.

Well I think you enquired about "'*son".

My experience is that this won't work in ADO.

First, because "*" is used instead of "%".

(and this is not covered in help)

*********************

Secondly because the trailing wild card is not there.

So it must be "%son%" or "son%".

And neither "*son" (2 reasons) not "%son" (1 reason) will work.
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[David W. Fenton]

Mi************@Invalid.Com (Lyle Fairfield) wrote in
<Xn*******************@130.133.1.4>:

dX********@bway.net.invalid (David W. Fenton) wrote in
news:94***************************@24.168.128.9 0:

Mi************@Invalid.Com (Lyle Fairfield) wrote in
<Xn*******************@130.133.1.4>:

Although I am an ADO enthusiast this article raises issues that
have made me reconsider any notion I may have had about using
ADO with JET.

<http://support.microsoft.com/default.aspx?
scid=http://support.microsoft.com:80/support/kb/articles/Q225/0/4
8. ASP&NoWebC ontent=1>

Lots of MS's documentation is wrong, Lyle.

Surely you could try it and verify for yourself?

I'd be interested to know the answer.

Well I think you enquired about "'*son".

My experience is that this won't work in ADO.

First, because "*" is used instead of "%".

(and this is not covered in help)

*********************

Secondly because the trailing wild card is not there.

So it must be "%son%" or "son%".

And neither "*son" (2 reasons) not "%son" (1 reason) will work.

So, there's no method with SQL for finding matches of the "ENDS
WITH" variety.

Of course, I already understood that. I was more concerned with the
issue that was bothering you, the non-portability of saved Access
queries when used with ADO.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Lyle Fairfield]

dX********@bway.net.invalid (David W. Fenton) wrote in
news:94***************************@24.168.128.86:

Mi************@Invalid.Com (Lyle Fairfield) wrote in
<Xn*******************@130.133.1.4>:

dX********@bway.net.invalid (David W. Fenton) wrote in
news:94***************************@24.168.128.90 :

Mi************@Invalid.Com (Lyle Fairfield) wrote in
<Xn*******************@130.133.1.4>:

Although I am an ADO enthusiast this article raises issues that
have made me reconsider any notion I may have had about using
ADO with JET.

<http://support.microsoft.com/default.aspx?
scid=http://support.microsoft.com:80/support/kb/articles/Q225/0/4
8. ASP&NoWebC ontent=1>

Lots of MS's documentation is wrong, Lyle.

Surely you could try it and verify for yourself?

I'd be interested to know the answer.

Well I think you enquired about "'*son".

My experience is that this won't work in ADO.

First, because "*" is used instead of "%".

(and this is not covered in help)

*********************

Secondly because the trailing wild card is not there.

So it must be "%son%" or "son%".

And neither "*son" (2 reasons) not "%son" (1 reason) will work.

So, there's no method with SQL for finding matches of the "ENDS
WITH" variety.

This is a bit murky. Help and KB articles say that only "son%" and %son% are
allowed. And the beginning of this thread noted that "%son" did not work.

BUT ....

MsgBox CurrentProject.Connection.Execute("SELECT Count(*) FROM
tbl2002Transactions WHERE fldDescription LIKE '%s'").Fields(0).Value

MsgBox CurrentDb.OpenRecordset("SELECT Count(*) FROM tbl2002Transactions
WHERE fldDescription LIKE '*s'").Fields(0).Value

both display 33 which is the number of records with fldDescription ending in
"s".

tbl2002Transactions is a simple JET table. The database is not split. DAO is
3.6. ADO is 2.7. Access is XP.

LIKE is not something I use a great deal as almost all of my db work is about
numbers only. When I did use LIKE (way back when) I checked my SQL on many
examples to be sure I was getting what I thought I was getting. I think this
is good practice, in general. But I am not an expert on wildcards or LIKE.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)