Can't use the AND statement

Is this just something with Dreamweaver?

[dickbaker]

I've got a search page where people will enter the brand and model, and anything listed in the database that matches will be shown on the results page.

Problem is, Dreamweaver MX won't let me use the AND statement. I've written SELECT ID, brand, model FROM my_database WHERE brand LIKE '%MMColParam%' AND model LIKE '%MMColParam2%' The value of the two column parameters are Request.Form("brand") and Request.Form("model").

When I go to do the search, the results come up empty. However, if I change the AND to OR, the results come up. Problem is that I can mix models and brands and come up with a search result that's not correct. For whatever reason, the model always seems to take precedence.

Any ideas on how to handle this problem?

Thanks much for any replies.

[duckhunter]

Your statement looks right. Have you tried to run each piece independently? Seems like one or the other would return no records.

SELECT ID, brand, model FROM my_database WHERE brand LIKE '%MMColParam%'

Then

SELECT ID, brand, model FROM my_database WHERE model LIKE '%MMColParam2%'

Make sure your request variables are Trimmed for trailing spaces.

[mattglet]

try throwing some parens around it, to make sure your rules of operation are being followed correctly.

SELECT ID, brand, model FROM my_database WHERE (brand LIKE '"%MMColParam%"') AND (model LIKE '"%MMColParam2%"')

-Matt

[dickbaker]

Matt, if I use your queries, I get the same results as when I don't use parentheses.

Duckhunter, can you use two "Select" queries within a single recordset?

Admittedly, I've only done a couple of database-driven sites, but I've used the AND statement together with parameter values successfully. Those sites used Access databases. This site uses MS SQL. Don't know if that makes a difference.

Just when I thought I was home free.

Dang, there goes the weekend!

[defanjos]

I think duckhunter mentioned running the two select statements as a troubleshooting tool. First, use one statement only, if you get results, that particular query is not the problem. Try for the other statement, if you get no results, that is your problem. If they both deliver results, then the problem is something else.

[duckhunter]

Exactly. I'm guessing that you have trailing spaces in one of your request variables or one of your fields is a Char instead of a varchar.

ie: it might be searching for "SONY " and not "SONY"

You might have to RTRIM(brand) or RTRIM(model) to strip of trailing spaces if you are using a CHAR field type instead of VarChar. I know you have the % but CHAR fields can behave strangely. Use VarChar whenever possible.

[dickbaker]

Hi, all. Thanks for the replies. I took Duckhunter's advice and tested each variable individually. Turns out that one of the field names was incorrect.

Sometimes it's the little, but obvious, things you don't notice...

This forum is great. The amount of knowledge here is unbelievable.