ASP/SQL - keyword search results with punctuation in IE

[Guest]

Using DWCS3, classic ASP, MS Access, and am a SQL rookie. 

I've created a keyword search form with a textfield called "search".  My SQL statement is as follows:

SELECT *
FROM tbl_LostCats
WHERE Breed LIKE %MMColParam% OR Color LIKE %MMColParam2% OR  Details LIKE %MMColParam3% OR Gender LIKE %MMColParam4% OR Coat LIKE %MMColParam5%
ORDER BY sql_orderby

All MMColParam are the same: Request.Querystring("search")

IN THE DATABASE:

Breed and Color fields sometimes contain slashes; i.e., Tabby/Siamese, Black/white

Details is a memo field with paragraphs containing commas and periods

Gender contains periods; i.e., male, neut.

Coat contains dashes; i.e., long-hair

When performing a search, records are properly returned in FF, Opera, Flock, etc., regardless of punctuation -- BUT in IE, records are only returned if the word is not followed by a comma or period.  This makes no sense.  Does someone know why IE is not ignoring punctuation while the other browsers are? Any help is truly appreciated.

[bregent]

Do you mean the entered search term contains a comma/period, or the data in the database?

What datatypes are you using for your columns?

>Gender contains periods; i.e., male, neut.

Shouldn't this be two columns?

[Guest]

Sorry, Ieft out that small detail.

For example, the search is for a single word like long without commas, periods or dashes.  All browsers return all records with long or long-hair but IE only returns records with long - any records with long-hair are ignored.

Another examples is collar where sometimes collar is in the middle of a sentence - has a red collar with tags - in the middle of a paragraph with a comma - has a red collar, - or at the end of a sentence - has a red collar. All browsers return all records with any matches regardless of the comma or period except for IE which only returns records if the word is without a comma or period.  Very, very odd...

[bregent]

There is very little influence the browser can have over what is primarily server side applicaiton. So what I suspect is that it has something to do with the parameters. Your SQL is using the LIKE predicate but does not include wildcards so I am guessing that the wild cards are either input with the search criteria or added at some point before the SQL expression is built. The first thing I would do is output the values of the input paramters to the screen so you can see the difference between the browsers handling of these fields.

[Guest]

"Your SQL is using the LIKE predicate but does not include wildcards "

I thought I was using wildcards by placing % on either side of MMColParam in my statement - %MMColParam% - so that Request.Querystring("search") would return records with the search word even if one or more characters were on either side of it.

"The first thing I would do is output the values of the input parameters to the screen so you can see the difference between the browsers handling of these fields."

Currently, all parameters include:

Name: MMColParam

Type: Text

Value: Request.Querystring("search")

Default value: %

So I should change the default values to certain values like long-hair and black and run preview tests to see if the browsers match or differ?

Sorry, I truly am a SQL rookie!

[bregent]

> thought I was using wildcards by placing % on either side of

>MMColParam   in my statement - %MMColParam% -

Sorry, I've been  staring at so much code today I looked right through those. That should be fine.

Can you output the querystring value to the screen using IE and FF and see if there is a difference:

Response.Write Request.Querystring("search")

or

Response.Write MMColParam1

Also, can you post a URL so we can observe this behavior?

[bregent]

>Value: Request.Querystring("search")

Also, I don't quite understand this. You have 5 parameters but only 1 search field?

[Guest]

My pages aren't live yet until I correct this problem.  I'm testing via preview in Dreamweaver.

I originally had one parameter for my entire SQL statement:

WHERE Breed LIKE %MMColParam% OR Color LIKE %MMColParam% OR  Details LIKE %MMColParam% OR Gender LIKE %MMColParam%

Parameter:

Name: MMColParam

Type: Text

Value: Request.Querystring("search")

Default Value: %

Testing the recordset returned an error message (which I can't duplicate now) PLUS this browser message:

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

No value given for one or more required parameters.

/mywebsite/lostcats_main.asp, line 59

Line 59: Set rs_lostcats = rs_lostcats_cmd.Execute

Going back into the recordset advanced dialog box, my SQL changed:

WHERE Breed LIKE %MMColParam% OR Color LIKE ? OR Details LIKE ? OR Gender LIKE ?

So I ended up adding a number to the end of each MMColParam and giving them separate (but same) parameters.  That's the only way my page will preview without errors.

Now.....I do have a Request Variable binded to my search field to retain the searched word.  I also have an onclick function on that field to delete whatever's in there as a courtesy to the searcher.  When clicked, however, the cursor "bounces" and looks like it skips a space.  I will look at my code.

[Guest]

Bingo.  It' s not my code, it seems IE IS putting a space after the word.  In FF, it is not.  Now, what on earth can I do to fix THAT?  Is it to trim as you had mentioned?

[bregent]

> Is it to trim as you had mentioned?

Yeah. My guess is that your form field clear routine is adding the space. Just use the VBScript trim() function to remove whitespace from the beginning and end of the string. You'll need to do it for each parameter which is one reason I asked why you used so many. You should be able to use just one so I would work on that problem, if it is still a problem.

[Guest]

Cannot begin to express how much I appreciate today's help after struggling for days on this stuff.

As far as using one parameter vs. many, again, I only get errors when using one. I don't know why because it's only logical to use one if it's going to be the same for all fields, but I get nothing but errors.  At this point, if using 4-5 parameters works, I'm in!

The only information I find on using TRIM() is after the SELECT clause which doesn't work for me since I'm using the asterisk for all fields.  I'm not finding how to use it within the parameter itself.  Sorry to be such a dunce, though I do love learning how all this is put together.  Would it be within the value?

Request.Querystring TRIM("search")

[Guest]

I GOT IT!!!  THANK YOU SO MUCH!

[bregent]

>I GOT IT!!!  THANK  YOU SO MUCH!

You're welcome.

[bregent]

Request.Querystring TRIM("search")

should be

Trim (Request.Querystring ("search"))

[bregent]

One guess is that for whatever reason, IE is inserting a space in the form field data. So any search terms that are followed by a space return results, otherwise they don't.  If that's the case, then you can fix it by trimming the fields before executing the SQL. It's often a good idea to trim whitespace from user input.