Searching for apostrophes in Access returns no matches

Having been advised that using parameterized SQL statements in Access was more efficient and reliable that an ordinary SQL statement, I decided to give it a try. The problem was that when searching for strings containing an ', it would return only one result, or less than was actually there.

Searching for anything else returns the correct results.

My original SQL statements was as follows:

strSQL = "select id as strID, tableID as strTableID, tagline as strTagline, content AS strContact FROM news where tagline LIKE '%" & searchString & "%' OR content LIKE '% " & searchString & "%' UNION ALL select id as strID, tableID as strTableID, tagline AS strTagline, content AS strContent from databank where tagline LIKE '%" & searchString & "%' OR content LIKE '%" & searchString & "%' ORDER BY strID"

Anyway, I even tried changing instances of ' in the database for ' but again it just returned the one or incorrect no. of results.

Now I have written a query called 'qrySearch' in access as follows:

==============================================
PARAMETERS searchText Text ( 255 );
SELECT id as strID, tableID as strTableID, tagline as strTagline, content AS strContact FROM news where

(((tagline)LIKE[searchText]))


OR

(((content)LIKE[searchText]))


UNION ALL select id as strID, tableID as strTableID, tagline AS strTagline, content AS strContent from databank WHERE

(((tagline)LIKE[searchText]))


OR

(((content)LIKE[searchText]))
ORDER BY strID;
==============================================


My ASP is as follows:


==============================================
Dim objRS, objComm, objParam, strDirector
Set objComm = Server.CreateObject("ADODB.Command")

objComm.ActiveConnection = DB 'strConnect ' fill in the command properties
objComm.CommandText = "qrySearch"
objComm.CommandType = adCmdStoredProc

' now the parameters (actually, there's only one parameter here)
Set objParam = _
objComm.CreateParameter("searchText", adVarChar, adParamInput, 50)
objComm.Parameters.Append objParam

strSearchText = searchString ' you can change this if you like
objComm.Parameters("searchText") = strSearchText

Set objRS = objComm.Execute ' execute the command and generate the recordset

Set objComm = Nothing ' don't need the Command and Parameter objects
Set objParam = Nothing ' ... so we can clean them up
==============================================

It doesn't generate any errors, but now it returns no matches regardless of what I search for, have it an ' in it or not.

PLEASE PLEASE PLEASE can somebody help me?! I'm getting very frustrated and confused now

Hope to hear from you soon, and thanks in advance!

You need to replace single ' with two of them in your search string, something like

objComm.Parameters("searchText") = Replace(strSearchText, " ' ", " ' ' ") 'spaces for readability

Thanks, but I tried that originally, before I even gave parameterized statements a go. Again, returns the wrong number of results, or having just tried it now, no results at all, even when searching for a string containing no apostrohes at all.

Having played with my query 'qrySearch' in access, it seems that doesn't return any results in itself, so the problem must lie there. I thought I had tested this but maybe I am wrong.

Can anyone see what the problem witht he query itself (below) is please?


PARAMETERS searchText Text ( 255 );
SELECT id as strID, tableID AS strTableID, tagline AS strTagline, content AS strContact FROM news WHERE

(((news.tagline)LIKE[searchText]))


OR

(((news.content)LIKE[searchText]))


UNION ALL select id AS strID, tableID AS strTableID, tagline AS strTagline, content AS strContent FROM databank WHERE

(((databank.tagline)LIKE[searchText]))


OR

(((databank.content)LIKE[searchText]))
ORDER BY strID;