Skip to main content
A
Anonymous
May 24, 2011
Question

Search query returning ALL records

  • May 24, 2011
  • 1 reply
  • 1047 views

DW CS3 - MS Access - ASP/VBScript

I have a search form for records to display on the same page with keywords highlighted.  The search is returning ALL records and highlighting keywords throughout rather than returning specific records with the searched word.  What am I missing?  I'm sure it's something terribly simple.....

          <input name="search" type="text" id="search" value="<%= Request.QueryString("search") %>" />

          SELECT item, item, item, item

          FROM tbl_name
          WHERE item OR item OR item LIKE %MMColParam%
          ORDER BY sql_orderby, Date DESC

          Name: MMColParam

          Type: Text

          Value: Request.Querystring("search")

          Default Value: %

This topic has been closed for replies.

1 reply

B
bregent
Participating Frequently
May 24, 2011

The Where clause is not valid standard SQL and will fail to run in most DBMS's, however, it will run in MS Access.  The first two conditions are the same and will evaluate to TRUE for every row, so all rows will be returned. Why do you have more than one column named item in your select list?

WHERE item OR item

The correct syntax should be:

  SELECT item

          FROM tbl_name
          WHERE item LIKE %MMColParam%
          ORDER BY sql_orderby, Date DESC

A
Anonymous
May 24, 2011

I was using the word "item" as an example for multiple columns without actually naming them - they are not the same.  I should've used this example:

SELECT shoes, socks, hats, gloves

FROM tbl_apparel

WHERE shoes OR socks OR hats LIKE %MMColParam%

ORDER BY sql_orderby, Date DESC

In the past, I had four duplicate query parameters for four columns which worked fine.  But since I only have one search term, I thought I could eliminate three of the duplicate parameters and use just one with the OR statement.

In the past, you questioned me on this.  You stated, "You only have one search term and so all of the parameters have the same value, but DW still wants to creates 4 parameters. If you were coding this by hand you wouldn't do it that way, but DW's one-size-fits-all code generates four seperate parms. It's nothing to worry about."

A
Anonymous
May 24, 2011

Experimenting more, I can see that using OR between items will not work as I thought, and I'll have to go back to creating separate query parameters for each item which returns records appropriately via a keyword search.

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