Skip to main content
A
Anonymous
February 13, 2012
Question

need SQL help

  • February 13, 2012
  • 1 reply
  • 893 views

CS3 - Classic ASP

I have a keyword search form on my page.  I can search any keyword within Details and return records, but not by City. What am I doing wrong?  Following is a much shortened example of my actual SQL for simplification.

SELECT City, Details

FROM tbl_Cities, tbl_Main

WHERE tbl_Cities.CityID = tbl_Main.CityID AND Details LIKE %MMColParam% OR City LIKE %MMColParam2%  (have also tried tbl_Cities.City)

Name:    MMColParam

Type:     Text

Value:    Request.Querystring("search")

Default value: %

Name:    MMColParam2

Type:     Text

Value:    Request.Querystring("search")

Default value: %

tbl_Cities = CityID (number) and City (text)

tbl_Main = MainID (number), Details (text), and CityID (number). 

This topic has been closed for replies.

1 reply

B
bregent
Participating Frequently
February 13, 2012

ALWAYS, ALWAYS, ALWAYS use parenthesis when combining AND with OR in a SQL Where clause.

SELECT City, Details

FROM tbl_Cities, tbl_Main

WHERE tbl_Cities.CityID = tbl_Main.CityID AND (Details LIKE %MMColParam% OR City LIKE %MMColParam2%)

This may not be your only problem, but start with there and let us know the results.

A
Anonymous
February 13, 2012

  Parenthesis are in my actual SQL - just removed in the example.  Sorry, I knew I'd get in trouble for that.

Everything has worked fine until I added a parameter for an item that basically comes from another table.  All working parameters are based on text fields within the main table.  Cities is in the main table as an ID (number).  It's the only one I can't get to work...

More of my actual SQL:

WHERE (tbl_Cities.CityID = tbl_Main.CityID AND tbl_Types.TypeID = tbl_Main.TypeID AND tbl_Types.Type = MMColParam) AND (Details LIKE %MMColParam2% OR Breed LIKE %MMColParam3% OR City LIKE %MMColParam4%)

A
Anonymous
February 13, 2012

I got it..... I had to remove the wildcard symbols.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices