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]

Re: Trouble with Access Like Query in ASP

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" <randyweb@yahoo .com> wrote in message
news:8d410073.0 312190820.50cae ca8@posting.goo gle.com...[color=blue]
> 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.[/color]

[Rich P]

Re: Trouble with Access Like Query in ASP

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]

Re: Trouble with Access Like Query in ASP

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]

Re: Trouble with Access Like Query in ASP

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]

Re: Trouble with Access Like Query in ASP

Randy <anonymous@devd ex.com> wrote in news:3fe3359a$0 $198$75868355
@news.frii.net:
[color=blue]
> 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.[/color]

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]

Re: Trouble with Access Like Query in ASP

MissingAddress@ Invalid.Com (Lyle Fairfield) wrote in
<Xns945686FABE9 5AFFDBA@130.133 .1.4>:
[color=blue]
>Randy <anonymous@devd ex.com> wrote in news:3fe3359a$0 $198$75868355
>@news.frii.net :
>[color=green]
>> 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.[/color]
>
>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*').[/color]

???

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]

Re: Trouble with Access Like Query in ASP

dXXXfenton@bway .net.invalid (David W. Fenton) wrote in
news:9456C5C73d fentonbwaynetin vali@24.168.128 .74:
[color=blue]
> MissingAddress@ Invalid.Com (Lyle Fairfield) wrote in
><Xns945686FABE 95AFFDBA@130.13 3.1.4>:
>[color=green]
>>Randy <anonymous@devd ex.com> wrote in news:3fe3359a$0 $198$75868355
>>@news.frii.ne t:
>>[color=darkred]
>>> 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.[/color]
>>
>>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*').[/color]
>
> ???
>
> But not:
>
> LastName Like '*son'
>
> Is that correct?[/color]

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.microso ft.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]

Re: Trouble with Access Like Query in ASP

MissingAddress@ Invalid.Com (Lyle Fairfield) wrote in
<Xns9456E9470B7 C0FFDBA@130.133 .1.4>:
[color=blue]
>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.microso ft.com:80/support/kb/articles/Q225/0/48.
>ASP&NoWebC ontent=1>[/color]

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]

Re: Trouble with Access Like Query in ASP

dXXXfenton@bway .net.invalid (David W. Fenton) wrote in
news:94579BCB0d fentonbwaynetin vali@24.168.128 .90:
[color=blue]
> MissingAddress@ Invalid.Com (Lyle Fairfield) wrote in
><Xns9456E9470B 7C0FFDBA@130.13 3.1.4>:
>[color=green]
>>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.microso ft.com:80/support/kb/articles/Q225/0/48.
>>ASP&NoWebC ontent=1>[/color]
>
> 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.[/color]

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]

Re: Trouble with Access Like Query in ASP

MissingAddress@ Invalid.Com (Lyle Fairfield) wrote in
<Xns9457A8EFB9C 6EFFDBA@130.133 .1.4>:
[color=blue]
>dXXXfenton@bwa y.net.invalid (David W. Fenton) wrote in
>news:94579BCB0 dfentonbwayneti nvali@24.168.12 8.90:
>[color=green]
>> MissingAddress@ Invalid.Com (Lyle Fairfield) wrote in
>><Xns9456E9470 B7C0FFDBA@130.1 33.1.4>:
>>[color=darkred]
>>>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.microso ft.com:80/support/kb/articles/Q225/0/4
>>>8. ASP&NoWebC ontent=1>[/color]
>>
>> 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.[/color]
>
>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.[/color]

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]

Re: Trouble with Access Like Query in ASP

dXXXfenton@bway .net.invalid (David W. Fenton) wrote in
news:9457D5716d fentonbwaynetin vali@24.168.128 .86:
[color=blue]
> MissingAddress@ Invalid.Com (Lyle Fairfield) wrote in
><Xns9457A8EFB9 C6EFFDBA@130.13 3.1.4>:
>[color=green]
>>dXXXfenton@bw ay.net.invalid (David W. Fenton) wrote in
>>news:94579BCB 0dfentonbwaynet invali@24.168.1 28.90:
>>[color=darkred]
>>> MissingAddress@ Invalid.Com (Lyle Fairfield) wrote in
>>><Xns9456E947 0B7C0FFDBA@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.microso ft.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.[/color]
>>
>>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.[/color]
>
> So, there's no method with SQL for finding matches of the "ENDS
> WITH" variety.[/color]

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.Exec ute("SELECT Count(*) FROM
tbl2002Transact ions WHERE fldDescription LIKE '%s'").Fields(0 ).Value

MsgBox CurrentDb.OpenR ecordset("SELEC T Count(*) FROM tbl2002Transact ions
WHERE fldDescription LIKE '*s'").Fields(0 ).Value

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

tbl2002Transact ions 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)