Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-09-17 : 15:35:16
|
Oded writes "
Hi there,
I have made a simple search engine for our Web based database system (around SQL SERVER 7, IIS4 and NT Server 4).
My search query accepts a search phrase from the user
via a SUBMIT button, textbox and form on the web.
Then, it dynamically generates a SELECT statement
which compares the search phrase to the Joined tables
(there are about 4-5 joined relations participating whose
fields must be searched).
The problem is that whenever the user enters a search
word which contains a single apostrophe '
we get the following error in when we try to open the recordset based on the select statement:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Line 1: Incorrect syntax near 'box'.
searchResults.asp, line 348
the user can enter things like "Mike Graphics"
any number of words. The phrase will be broken into single
words which will each be compared to all of the columns in the
select statement columns, in the following manner:
(select simplified somewhat. The searched columns are COL1
COL2 COl3, and the parsed user criteria words are "Mike" "Graphics", but of course there could be any number
of them. (separated by the AND). If the user typed more search
words, there would be accordingly more lines like the ones after the WHERE (each for each word).
SELECT ...(many joins here)
WHERE
(' ' + ISNULL(col1, '') + ' ' + ISNULL(col2,'') + ' ' + ISNULL(col3, '') + ' ' LIKE '% Mike %'
AND
(' ' + ISNULL(col1, '') + ' ' + ISNULL(col2,'') + ' ' +
ISNULL(col3, '') + ' ' LIKE '% Graphics %'
ORDER BY COL1" |
|
|
|
|
|
|
|
SQL SERVER Search Query