p2p.wrox.com: Archives - asp

asp_databases thread: query returns different results for ASP and Access

Message #1 by "Ken Mosher" <vorlon_ken@h...> on Fri, 22 Sep 2000 18:50:32 +0100

I have the following ASP code, which runs without any error message:

- - - - - - - - - - - - - - -

  set conno = Server.CreateObject("ADODB.Connection")

  set rso = Server.CreateObject("ADODB.Recordset")



  TableName = "Members"

  strconnect = "DSN=Membership"

  conno.open strconnect



  rso.open "select * from [" & TableName & "] where (City = 'Willimantic'

or City like '*Windham*') order by LastName asc", conno, adOpenDynamic,

adLockReadOnly, adCmdText

- - - - - - - - - - - - - - -



The problem is that it's returning the *wrong* results when run under ASP.

It's returning all the records for "Willimantic" but no records for 

"*windham*".



If I paste this exact code into the SQL window of a MS Access query (and

substitute "Members" for the variable, of course) I get the correct

results

- members both in Willimantic and (windham,  north windham, south windham,

windham).



I tried changing the WHERE clause to just "like 'willi*'" and I got back 

zero records, no errors.  It should have returned all the "Willimantic"

members.



Any clues?  The query is running under PWS and Win98. Driver is

"Microsoft Access Driver (*.mdb)" [odbdjt32.dll] version 4.00.3711.08.

MS Office 97 Pro is installed.



Thanks,

Ken

Message #2 by Ian Nutt <iann@w...> on Fri, 22 Sep 2000 19:50:42 +0100

Ken, 



The * character is not a wildcard in SQL.  The characters for a wildcard in

SQL are: 

 % (to represent zero or more characters)

  _ (to represent exactly one character)



HTH, Ian



______________________________

Ian Nutt, Wrox Press Ltd

iann@w...   

http://www.wrox.com

http://www.asptoday.com

http://www.wroxconferences.com

http://p2p.wrox.com









-----Original Message-----

From: Ken Mosher [mailto:vorlon_ken@h...]

Sent: Friday, September 22, 2000 6:51 PM

To: ASP Databases

Subject: [asp_databases] query returns different results for ASP and

Access





I have the following ASP code, which runs without any error message:

- - - - - - - - - - - - - - -

  set conno = Server.CreateObject("ADODB.Connection")

  set rso = Server.CreateObject("ADODB.Recordset")



  TableName = "Members"

  strconnect = "DSN=Membership"

  conno.open strconnect



  rso.open "select * from [" & TableName & "] where (City = 'Willimantic'

or City like '*Windham*') order by LastName asc", conno, adOpenDynamic,

adLockReadOnly, adCmdText

- - - - - - - - - - - - - - -



The problem is that it's returning the *wrong* results when run under ASP.

It's returning all the records for "Willimantic" but no records for 

"*windham*".



If I paste this exact code into the SQL window of a MS Access query (and

substitute "Members" for the variable, of course) I get the correct

results

- members both in Willimantic and (windham,  north windham, south windham,

windham).



I tried changing the WHERE clause to just "like 'willi*'" and I got back 

zero records, no errors.  It should have returned all the "Willimantic"

members.



Any clues?  The query is running under PWS and Win98. Driver is

"Microsoft Access Driver (*.mdb)" [odbdjt32.dll] version 4.00.3711.08.

MS Office 97 Pro is installed.



Thanks,

Ken

Message #3 by Imar Spaanjaars <Imar@S...> on Fri, 22 Sep 2000 20:43:15 +0200

You need to put an % sign in the LIKE clause, like this:



City like '*Windham%"



Think this will help....





Imar







At 06:50 PM 9/22/2000 +0100, you wrote:

>I have the following ASP code, which runs without any error message:

>- - - - - - - - - - - - - - -

>   set conno = Server.CreateObject("ADODB.Connection")

>   set rso = Server.CreateObject("ADODB.Recordset")

>

>   TableName = "Members"

>   strconnect = "DSN=Membership"

>   conno.open strconnect

>

>   rso.open "select * from [" & TableName & "] where (City = 'Willimantic'

>or City like '*Windham*') order by LastName asc", conno, adOpenDynamic,

>adLockReadOnly, adCmdText

>- - - - - - - - - - - - - - -

>

>The problem is that it's returning the *wrong* results when run under ASP.

>It's returning all the records for "Willimantic" but no records for

>"*windham*".

>

>If I paste this exact code into the SQL window of a MS Access query (and

>substitute "Members" for the variable, of course) I get the correct

>results

>- members both in Willimantic and (windham,  north windham, south windham,

>windham).

>

>I tried changing the WHERE clause to just "like 'willi*'" and I got back

>zero records, no errors.  It should have returned all the "Willimantic"

>members.

>

>Any clues?  The query is running under PWS and Win98. Driver is

>"Microsoft Access Driver (*.mdb)" [odbdjt32.dll] version 4.00.3711.08.

>MS Office 97 Pro is installed.

>

>Thanks,

>Ken

>

Message #4 by "Jason A. Greenfeld" <jgreenfeld@r...> on Fri, 22 Sep 2000 14:56:50 -0400

In the part where you do LIKE '*Windham*', try replacing the * with %. I

think the % sign is the wildcard for SQL statements



ex:

Select * from tblUsers where first_name like '%Ann%'



This should return Ann, LeAnn, Anna, Annabell, etc...





Jason Greenfeld

Rubicon Technologies, Inc.

http://www.rubicontechnologies.com







-----Original Message-----

From: Ken Mosher [mailto:vorlon_ken@h...]

Sent: Friday, September 22, 2000 1:51 PM

To: ASP Databases

Subject: [asp_databases] query returns different results for ASP and

Access





I have the following ASP code, which runs without any error message:

- - - - - - - - - - - - - - -

  set conno = Server.CreateObject("ADODB.Connection")

  set rso = Server.CreateObject("ADODB.Recordset")



  TableName = "Members"

  strconnect = "DSN=Membership"

  conno.open strconnect



  rso.open "select * from [" & TableName & "] where (City = 'Willimantic'

or City like '*Windham*') order by LastName asc", conno, adOpenDynamic,

adLockReadOnly, adCmdText

- - - - - - - - - - - - - - -



The problem is that it's returning the *wrong* results when run under ASP.

It's returning all the records for "Willimantic" but no records for

"*windham*".



If I paste this exact code into the SQL window of a MS Access query (and

substitute "Members" for the variable, of course) I get the correct

results

- members both in Willimantic and (windham,  north windham, south windham,

windham).



I tried changing the WHERE clause to just "like 'willi*'" and I got back

zero records, no errors.  It should have returned all the "Willimantic"

members.



Any clues?  The query is running under PWS and Win98. Driver is

"Microsoft Access Driver (*.mdb)" [odbdjt32.dll] version 4.00.3711.08.

MS Office 97 Pro is installed.



Thanks,

Ken

Message #5 by trwall@m... on Fri, 22 Sep 2000 20:06:13 +0100

When submitting a query through ASP to an Access database, change the "*"

wildcard to a "%" wildcard.

Message #6 by "The Vorlon" <vorlon_ken@h...> on Fri, 22 Sep 2000 16:17:47 EDT

Thank you, thank you, it worked!  How would I ever have found this out?  

I've always used "*" when programming sql statements in VB or when in Access 

itself and it's always worked fine.  The need to use "%" was not documented 

in the Wrox ASP 2.0 book - hmmph. That's not very helpful for us ASP 

beginners...

-Ken





>From: trwall@m...

>Reply-To: "ASP Databases" <asp_databases@p...>

>To: "ASP Databases" <asp_databases@p...>

>Subject: [asp_databases] Re: query returns different results for ASP and 

>Access

>Date: Fri, 22 Sep 2000 20:06:13 +0100

>

>When submitting a query through ASP to an Access database, change the "*"

>wildcard to a "%" wildcard.

Message #7 by "Ken Schaefer" <ken@a...> on Sun, 24 Sep 2000 13:32:22 +1000

http://www.adopenstatic.com/faq/likequeries.asp



You'll probably also want to read:



http://www.adopenstatic.com/faq/whyOLEDB.asp

and

http://www.adopenstatic.com/faq/selectstarisbad,asp



Also, always allocate your SQL string to a variable and then pass the

variable into the objRS.Open method.



That way you can response.write your SQL string to the screen in case you

need to troubleshoot it.



Cheers

Ken



----- Original Message -----

From: "Ken Mosher" <vorlon_ken@h...>

To: "ASP Databases" <asp_databases@p...>

Sent: Saturday, September 23, 2000 3:50 AM

Subject: [asp_databases] query returns different results for ASP and Access





> I have the following ASP code, which runs without any error message:

> - - - - - - - - - - - - - - -

>   set conno = Server.CreateObject("ADODB.Connection")

>   set rso = Server.CreateObject("ADODB.Recordset")

>

>   TableName = "Members"

>   strconnect = "DSN=Membership"

>   conno.open strconnect

>

>   rso.open "select * from [" & TableName & "] where (City = 'Willimantic'

> or City like '*Windham*') order by LastName asc", conno, adOpenDynamic,

> adLockReadOnly, adCmdText

> - - - - - - - - - - - - - - -

>

> The problem is that it's returning the *wrong* results when run under ASP.

> It's returning all the records for "Willimantic" but no records for

> "*windham*".

>

> If I paste this exact code into the SQL window of a MS Access query (and

> substitute "Members" for the variable, of course) I get the correct

> results

> - members both in Willimantic and (windham,  north windham, south windham,

> windham).

>

> I tried changing the WHERE clause to just "like 'willi*'" and I got back

> zero records, no errors.  It should have returned all the "Willimantic"

> members.

>

> Any clues?  The query is running under PWS and Win98. Driver is

> "Microsoft Access Driver (*.mdb)" [odbdjt32.dll] version 4.00.3711.08.

> MS Office 97 Pro is installed.

>

> Thanks,

> Ken

Return to Index

P2P Archives