SQL puzzle

I'm not sure why this doesn't work.

[txbakers]

I have a search page which generates the following SQL statement:

SELECT * FROM texasPML WHERE title like '%Chester%' and composer like '%%' and arranger like '%T%'

I am returned "Chester", "Billings", and "Osterling" which is a valid entry, but Osterling doesn't start with a "T".

When I put "To" in the arranger field, my SQL looks like this:
SELECT * FROM texasPML WHERE title like '%Chester%' and composer like '%%' and arranger like '%To%'
I am returned "Chester" "Billings" "Tolmadge" which is correct.

Why would the letter "T" by itself not be picked up, yet the "To" is?

I'm using Access with ASP.

Thanks.

[Woz]

... but Osterling includes T. You have like "%T%" but you need like "T%"

%T% = includes T
T% = Starts with T
%T = ends with T

try SELECT * FROM texasPML WHERE title like '%Chester%' and composer like '%%' and arranger like 'T%'

Onya
Woz

[txbakers]

Thanks Woz. You got it, I got it too. Osterling does contain T, and that came first in the DB.

So now I'm listing all the possible values in a table, and the user will have to choose the correct match.

I do want the double wildcard in there.

[Woz]

Yeah, one of those "Doh!" moments I seem to have quite a lot as well.

Onya
Woz

[txbakers]

Not quite out of the water yet.

For some reason the query doesn't like Mr. Butler as a composer. Now, he's no Beethoven, but he's still good and should be found.

Even when I enter his entire name the ASP query doesn't find him. The direct query on Access does.

Any further ideas?

[lorax]

What does the search query look like for Butler?

[JuDDer]

SELECT * FROM texasPML WHERE title like '%Chester%' and composer like '%%' and arranger like '%T%'

For some reason the query doesn't like Mr. Butler as a composer.

According to your SQL, you dont have anything specified fr the composer and if Mr.Butler is listed there, he can't be found when searching the table 'arranger'.

Or have I misunderstood?

(edited by: JuDDer at 5:22 pm (utc) on May 16, 2002)

[txbakers]

Just like the other ones:

SELECT * FROM texasPML WHERE title like '%%%' and composer like '%Butler%' and arranger like '%%%'

I should get a list of about 20 titles. If I put a keyword of his in the title and run the query, I get all sorts of correct full titles, but none of his!

[JuDDer]

>> The direct query on Access does.

Does this mean that the query works as it should when being run from Access, but not when running it from your page??

[txbakers]

Yes.

[JuDDer]

That's strange if it's working fine in access but not in your page.
I take it you are copying and pasting it exactly as it is in access?

[lorax]

Do you have any hidden characters in the db field that has his name in it? Look for other obvious errors/omissions. If it works for the other names and this one's in the db just like the others then it has to be a simple answer.

[txbakers]

Can't copy and paste from access - access uses the "*" as a wildcard, and ASP uses the "%" as a wildcard.

I've checked for extra characters and spaces in Butler's entries, but to no avail.

I'm sure it's simple as well, and I'll solve it eventually.

[lorax]

Is this the first composer you've tried to search on? If not change the name of one of the composers (in the web db) you've succesfully searched on to Butler and search again. If the search works and returns the New Butler's records, then there's something about the original Butler record Access doesn't like (which I've seen happen). If it doesn't work then there's something about your code that doesn't like the word Butler. This will help you narrow down where to look.

[txbakers]

Thanks. L.

It loves Dvorak, Brahms, but only brings back 1 Butler.

I'll keep playing......

[bmcgee]

SELECT * FROM texasPML WHERE title like '%%%' and composer like '%Butler%' and arranger like '%%%'

Looks suspicious to me. '%%%' looks like an odd form for a wildcard and that may be throwing it off.

... like '%' ... would be a better way of doing a wildcard for everything.

[txbakers]

SELECT * FROM texasPML WHERE title like '%' and composer like '%Butler%' and arranger like '%'

This yields the same results. Only 1 row returned.

this one, however:
SELECT * FROM texasPML WHERE composer like '%Butler%'

returns the correct number of rows. I think I know what I need to do in the query.....

[txbakers]

Problem Solved.

When I was assigning the variables I was checking for "undefined" but not for "null". The QueryString looked like this: T=&A=&C=Butler T and A were null.

So I modified my variable definition as follows:

if(String(Request.Form("T")) != "undefined" && String(Request.Form("T")) != "null")

A second wrinkle came up though. If the db field was blank, the wild card didn't work either. So I tested each part of the where clause. If the parm was blank I didn't include that part in the SQL statement.