Not able to search for the # character in my ASP SQL statement

[ggs54]

I have a database with a size field in it. That field contains both the single-quote (') and the pound sign (#). I have a search form set up and it works properly with one exception. Whenever I use the # sign, it appears that the query doesn't recognize it. No error is generated and no records are found. The # sign can be located anywhere in the field e.g. #5 or 5-6#

I have tried many variations on the SQL statement with no luck. Here are a couple of my attempts.

...AND Size LIKE '%::Size::%')"
...AND Size LIKE '%Size%')"

The SQL manual that I have gives an example of escaping a character using the ESCAPE clause in the SQL statement e.g.
...AND Size LIKE '%Size%' ESCAPE '#')"
This produces a syntax error and I am assuming that my IIS server doesn't recognize the ESCAPE keyword.

Any thoughts?

 

[robi2]

What is the Size thing in you sql?

if its an asp variable you need to do it like this:

...AND Size LIKE '%" & Size & "%'"

 

[ggs54]

Size is the field name in the form that I am collecting user input from.

In addition to the two examples on my original message, I have tried the following with no luck.

...AND Size LIKE '%::" & Request.Form("Size&quot & "::%')"
...AND Size LIKE '%" & Request.Form("Size&quot & "%')"

strSize = Request.Form("Size&quot
...AND Size LIKE '%" & strSize & "%')"

This last example works correctly, except if the user includes a # sign in their input. If they input #5, no records are returned. If they input only the # sign, 100 records are returned, which is my designated maximum, regardless of what is contained in the size field. It's as if the # sign was being ignored.

 

[unseth]

you are going to have trouble because the like operator expects the # sign to mean any single digit.

you can try

try replacing the users input # with [#]. That might work.

i.e.

tosearch = Request("size&quot
Replace(search,"#","[#]&quot (note: i might not have syntax correct)
..." and size like '%" & tosearch & "%')"

 

[ggs54]

Hi, unseth.

Your suggestion works perfectly! I can't believe that this isn't documented in my FrontPage, ASP or SQL manuals. I did find it in my VB manual and , since this is my first ASP project, didn't think to look their.

Thanks, again.