access returns results but not when through ASP

whyulil · June 8th, 2004, 08:11 AM

I have a query which is searching 2 tables and due to the many to many relationship i am joining 3 tables.
This is the SQL statement:

SELECT Title, Qualification, Levels, Apprentice, HigherEd, Courses.ID, International, Sixth, Skills, Thumbnail, SubjectArea.Subject, subjectArea.ID FROM SubjectArea INNER JOIN (Courses INNER JOIN SubCrsJoin ON Courses.ID = SubCrsJoin.fkCourse) ON SubjectArea.ID = SubCrsJoin.fkSub WHERE (Title LIKE '*maths*' OR Courses.Subtitle LIKE '*maths*' OR SubjectArea.Subject LIKE '*maths*' OR SubjectArea.Keywords LIKE '*maths*' ) AND (HigherEd = True OR Sixth = True OR Skills = True OR Apprentice = True OR International = True) ORDER BY SubjectArea.Subject, Title;

i know, its a bit big. when i run it in access it works, no problems but when i run it through asp i get an empty record set. ASP is definatly searching the correct database, but i cannot figure out why it wont work through ASP ive tried it with both % and * as the wild cards but still no results.
As always thanks
Andy

shahchi1 · June 8th, 2004, 08:30 AM

When you do a LIKE query using ADO against an Access database you can't use * as a wildcard character. Instead you need to use %.

whyulil · June 8th, 2004, 10:49 AM

Ive tried both just to be sure. (access's default i think is * but the SQL spec sez its %)

as i said it works with some terms but not others. searching for art returns results however maths does not.

the only thing i can think is that the query is too slow and times out before it finishes if this is the caser would upgrading to SQL server fix it. does anybody have any ideas of how to optimise a database?

shahchi1 · June 8th, 2004, 11:18 AM

If possible, you should try to optimize your database *Design* as well as SQL Query.
Wrox has a book (Beginning SQL) some neat stuff.
Also, it depends on how big your database is, but (if > 10 MB)you should migrate to SQL Server

happygv · June 8th, 2004, 11:18 PM

Hi whyulil,

Like operator works like searching for the defined string within the column in question for every row. So that is always a slower way of finding things from database.

When you search something like

Code:

Select * from TABLENAME where STRINGFIELD="MY String"

You search for a value that is the value of the column.

But when you say

Code:

Select * from TABLENAME where STRINGFIELD LIKE "%MY String%"

This "My String" can appear in any position within the column and the db has to search patterned string within a string for every row. So obviously this is slow.

Imagine when you search for something with LIKE operator, in a db that has 1 million rows. So you cannot expect it to be faster. And see the number of LIKE operators that you use in your query. That is really going to take time be it Access or SQL server.

I would suggest you to try a simple query with one LIKE operator

Code:

SELECT Title FROM SubjectArea WHERE Title LIKE '%maths%'

SELECT Title FROM SubjectArea WHERE Title LIKE '%art%'

Also check for case sensitivity. Some DBs like SQL server ones are not CASE sentitive.

And test that out running in Access directly then using ASP, compare the results in both cases. If ASP doesn't return result and if you generate query based on values from ASP page, may be the Query that you generate is not generated as expected. So just after the query is generated, immediately do a RESPONSE.WRITE of that query string and copy paste that in Access to see if that gives desired output, else you will have to re-write the query in ASP.

Hope that helps.
Cheers!

_________________________
-Vijay G

Strive for Perfection