Adding quotes to a search gets me "Expected STRING. SQLSTATE=42000" 1

[ColonelBlue]

Hello and Happy Holidays.

I have a search engine that I have built upon and customized, but I won't say I understand all of it nor am I an expert. But I know enought to make it work.
Having that said, I have my search engine working well except that when I place quotes in the search box, I get this error. Furthermore when I enter a search like 'Cairo' for example, it finds anything with that literate word, but if I searched with 'Cai', nothing comes up.

Any help would be so greatly appreciated.
Thanks in advance.

Error:
Microsoft OLE DB Provider for Indexing Service error '80040e14'

Incorrect syntax near '&'. Expected STRING. SQLSTATE=42000

[B]Relevant Code:[/B]

Function BuildQuery(strScope, strQuery)
	Dim strPropertyName
	Dim SQL 'SQL string to search against
	Dim strQText
	Dim blnAddedQ
	Dim intQPos
	SQL = "SELECT DocTitle,Filename, Vpath, Size, Write, Characterization, Rank, DocKeywords, DocComments FROM "
	If strScope = "" Then
		SQL = SQL & "SCOPE() "
	Else
		SQL = SQL & "SCOPE('DEEP TRAVERSAL OF " & QUOT & strScope & QUOT & "')"
	End if
	strQText = strQuery
	If InStr(strQText, " ") > 0 Or InStr(strQText, "'") > 0 Then
		blnAddedQ = False
		If Left(strQText, 1) <> QUOT Then
			strQText = QUOT & strQText
			blnAddedQ = True
		End If
		If Right(strQText, 1) <> QUOT Then
			strQText = strQText & QUOT
			blnAddedQ = True
		End If
		If blnAddedQ Then
			intQPos = Instr(2, strQText, QUOT)
			Do While intQPos > 0 And intQPos < Len(strQText)
				strQText = Left(strQText, intQPos - 1) & " " & Mid(strQText, intQPos + 1)
				intQPos = Instr(2, strQText, QUOT)
			Loop
		End If
	End If	
	'SQL = SQL & " WHERE CONTAINS (DocTitle, 'FORMSOF(INFLECTIONAL, " & strQText & ")')"
SQL = SQL & " WHERE CONTAINS (DocTitle, 'FORMSOF(INFLECTIONAL,  "&strQText&")')"
SQL = SQL & " OR CONTAINS (DocKeywords, 'FORMSOF(INFLECTIONAL, " & strQText & ")')"
SQL = SQL & " OR CONTAINS (DocComments, 'FORMSOF(INFLECTIONAL, " & strQText & ")')" 
SQL = SQL & " OR CONTAINS (Filename, 'FORMSOF(INFLECTIONAL, " & strQText & ")')"
SQL = SQL & " OR CONTAINS ('FORMSOF(INFLECTIONAL, " & strQText &" )')"
SQL = SQL & " AND (Filename LIKE '%.html'"
SQL = SQL & " OR Filename LIKE '%.asp'"
SQL = SQL & " OR Filename LIKE '%.pdf'"
SQL = SQL & " OR Filename LIKE '%.doc'"
SQL = SQL & " OR Filename LIKE '%.xls'"
SQL = SQL & " OR Filename LIKE '%.ppt'"
SQL = SQL & " OR Filename LIKE '%.htm')"
SQL = SQL & " ORDER BY " & strOrder & " DESC"
BuildQuery = SQL
End Function

 

[hmckillop]

Try

Debug.Print SQL

as your last line before END Function. Past this into SQl Server Query Analyser and then see what the problem is from there.
Alternatively post the output of this onto this post and someone should be able to help you.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[ColonelBlue]

Hello hmckillop and thank you for the response.

I appended the code as directed above but I got this message:

Microsoft VBScript runtime error '800a01b6' 

Object doesn't support this property or method: 'Debug.Print'

I'm sorry to say my skills with SQL statements in the aforementioned code are just novice.

 

[hmckillop]

All I am trying to do is to get the number output to the screen so you can cut and paste it into the Query Analyser.

Try

Document.write (SQL)

this should display it on the webpage once the function is called. Copy this and then repost.

Cheers

"I'm living so far beyond my income that we may almost be said to be living apart