For a search box on a website (classic ASP)? I have a very simple SQL query which works fine:?
'******************* Query 1 in ASP ****************
? strSQL = "SELECT DISTINCT [login to view URL], [login to view URL], [login to view URL], [login to view URL], [login to view URL] "_
? ? & "FROM Artikelen " _
? ? & "WHERE ([login to view URL] LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
? ? & "OR [login to view URL] LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
? ? & "OR [login to view URL] LIKE '%" & Replace(strSearch, "'", "''") & "%') " _? ?
? ? & "AND (Artikelen.ArtikelofProduct='a' OR Artikelen.ArtikelofProduct='p') " _? ? ? ? ? ?
? ? & "ORDER BY Datum DESC;"
? Set rstSearch = [login to view URL](strSQL)
'*********************
Now I want to have the possibility to also check the database for a search term which exist of two words and seperated with a comma.
But when I change the code to this (see below) i get, no results.
'******************* Query? 2 in ASP ****************
? strSQL = "SELECT DISTINCT [login to view URL], [login to view URL], [login to view URL], [login to view URL], [login to view URL] "_
? ? & "FROM Artikelen " _
? ? & "WHERE ([login to view URL] LIKE '%" & Replace(Trim(Replace(strSearch, "'", "''"))," ","%' OR [login to view URL] LIKE '%") & "%' " _
? ? & "OR [login to view URL] LIKE '%" & Replace(Trim(Replace(strSearch, "'", "''"))," ","%' OR [login to view URL] LIKE '%") & "%' " _
? ? & "OR [login to view URL] LIKE '%" & Replace(Trim(Replace(strSearch, "'", "''"))," ","%' OR [login to view URL] LIKE '%") & "%' " _
? ? & "AND (Artikelen.ArtikelofProduct='a' OR Artikelen.ArtikelofProduct='p') " _? ? ? ? ? ?
? ? & "ORDER BY Datum DESC;"
? Set rstSearch = [login to view URL](strSQL)
'********************
What am I doing wrong?
## Deliverables
If I print the SQL query the outcome however is very simular, but only the first query results in data.
'******************** SQL query 1 which results in data **********************
SELECT DISTINCT [login to view URL], [login to view URL], [login to view URL], [login to view URL], [login to view URL] FROM Artikelen WHERE ([login to view URL] LIKE '%koeling%' OR [login to view URL] LIKE '%koeling%' OR [login to view URL] LIKE '%koeling%' AND (Artikelen.ArtikelofProduct='a' OR Artikelen.ArtikelofProduct='p') ORDER BY Datum DESC;
'******************** SQL query? 2 which results in? an empty recordset? **********************
SELECT DISTINCT [login to view URL], [login to view URL], [login to view URL], [login to view URL], [login to view URL] FROM Artikelen WHERE ([login to view URL] LIKE '%koeling%' OR [login to view URL] LIKE '%zorg%' OR [login to view URL] LIKE '%koeling%' OR [login to view URL] LIKE '%zorg%' OR [login to view URL] LIKE '%koeling%' OR [login to view URL] LIKE '%zorg%' AND (Artikelen.ArtikelofProduct='a' OR Artikelen.ArtikelofProduct='p') ORDER BY Datum DESC;