Using "like" in a query submitted from a web page 1

[AlexNeil]

Hi, I think this is more of a web question than a Microsoft Access one ...

I have a search page that uses the contents of txtSearch in the "where" clause of a SQL query as follows:-

SQLQuery = _ 
  "select Name from MyTable
   where Description like '*" & txtSearch & "*'"

Trouble is, it doesn't return any records! Well, it will return matching records only when the contents of txtSearch exactly matches the contents of the Description field -- which means I may as well of used an "=" sign instead of the "like" operator.

If I output the contents of SQLQuery onto the page, and then copy that into an Access query then it works fine. It just doesn't work when it's submitted from the page.

Any ideas??

Alex

 

[BillNielsen]

What database are you querying? MS Access?

Have you tried:

SQLQuery = _
"select Name from MyTable
where Description like *" & chr(34) & txtSearch.value _
& chr(34) & "*"

I use the chr(34) for '"' because at times I've found the same thing where for some reason jet engine doesn't interpret my string correctly.

I hope this helps,
Bill N

 

[Boomerang]

Hi Alex,

I'm not known with MS access, but in SQL server the syntax is: '%" & txtSearch & "%'"

Further I don't understand the underscore after the =. If this is because you use Vbscript/ASP to code a new line than it's & _

Erik

 

[AlexNeil]

I'll try Bill's ideas tonight and let you know how it goes. Erik: I typed the code in from memory, so you're right in pointing out that the "&" is missing from the line concatenation.

Thank you both for your suggestions,
Alex

 

[AlexNeil]

Well I tried the ideas above but it didn't want to play! I've tried hard-coding the query string, building it bit by bit, and even entering the whole lot through an edit box.

The only thing that I've noticed that might be a clue is that if I use single quotes then it returns 0 records:

Select Name from MyTable Where Desc Like '*abcde*'

And if I use double quotes it errors, saying that it expected 1 parameter but revieved none:

Select Name from MyTable Where Desc Like "*abcde*"

(If I use no quotes, then it creates a syntax error at the first asterix).

Any ideas??

 

[FengShui1998]

AlexNeil,

You should be using '%' signs for wild cards in an SQL query string, not '*'. Also use single quotes for strings.

SQLQuery = "select Name from MyTable where Description " & _
" like '%" & txtSearch & "%'"

Also, you need to make sure txtSearch has characters in it or it will return all the records for Name. Are you also using the right cursors for the query?

fengshui1998

 

[AlexNeil]

It's an MS-Access database ... so I think this requires * for wild card searches, as opposed to SQL Server or something.

I'll have a play around with different cursors tonight and see if that works.

Thanks.

 

[efrost2]

You're right wild cards in Access is the *, however if you are working with either ADO or ASP then you need to use % as wild cards (even with an Access DB) - not sure why, but it works.
Hope this helps

 

[AlexNeil]

Brilliant! Got it to work using % signs. Thanks a lot to Bill, Erik, Fengshui1998, and efrost2 for you help.

Alex