PDA

View Full Version : SQL Query using LIKE


Ciombe
02-02-2011, 05:55 PM
Hi,
id' like to create a little search engine on my site and i have using this code for the DB connection in my result page called results.asp.

I get the values by a search page which have field called "campotesto1" anche select field called "select1"


<%
Dim rsRicerca__MMColParam
rsRicerca__MMColParam = "-1"
If (Request.Form("campotesto1") <> "") Then
rsRicerca__MMColParam = Request.Form("campotesto1")
End If
%>
<%
Dim rsRicerca__MMColParam1
rsRicerca__MMColParam1 = "1"
If (Request.Form("select1") <> "") Then
rsRicerca__MMColParam1 = Request.Form("select1")
End If
%>
<%
Dim rsRicerca
Dim rsRicerca_cmd
Dim rsRicerca_numRows

Set rsRicerca_cmd = Server.CreateObject ("ADODB.Command")
rsRicerca_cmd.ActiveConnection = MM_dbShoemakers_STRING
rsRicerca_cmd.CommandText = "SELECT * FROM table WHERE field LIKE '%" + Replace(?, "'", "''") + "%' AND select_field = ? ORDER BY name DESC"
rsRicerca_cmd.Prepared = true
rsRicerca_cmd.Parameters.Append rsRicerca_cmd.CreateParameter("param1", 200, 1, 255, rsRicerca__MMColParam) ' adVarChar
rsRicerca_cmd.Parameters.Append rsRicerca_cmd.CreateParameter("param2", 5, 1, -1, rsRicerca__MMColParam1) ' adDouble

Set rsRicerca = rsRicerca_cmd.Execute
rsRicerca_numRows = 0
%>


i use DW CS5 and ASP.

This query doesn't work cause a obtain and error:

"syntax error (missed operator), in query expression 'ield LIKE '%" + Replace(-1, "'", "''") + "%' AND select_field = 1 ORDER BY name DESC"


Where i'm in wrong?

jmichae3
02-03-2011, 10:56 AM
I thought VB or BASIC and probably thus ASP uses & instead of + to join strings?

somehow I don't think this is javascript... looks like BASIC to me. so change

rsRicerca_cmd.CommandText = "SELECT * FROM table WHERE field LIKE '%" & Replace(?, "'", "''") & "%' AND select_field = ? ORDER BY name DESC"


I suggest for your own sanity's sake that you come up with better table and field names, especially after 6 months down the road when you need to look at your code again and you have forgotten what-in-the-world-you-were-thinking-when-you-wrote-this.


good descriptive names for things and good comments makes for readability and debuggability later.

Ciombe
02-03-2011, 01:44 PM
i use ASP VBScript.

i've tried to make change that you suggest.

but now the error is in this code

<%
Dim rsRicerca__MMColParam
rsRicerca__MMColParam = "-1"
If (Request.Form("campotesto1") <> "") Then
rsRicerca__MMColParam = Request.Form("campotesto1")
End If
%>

Ciombe
02-03-2011, 06:00 PM
Works!!!!

this is the good code


<%
Dim rsTrova__MMColParam
rsTrova__MMColParam = "prova"
If (Request.Form("txt_field") <> "") Then
rsTrova__MMColParam = Request.Form("txt_field")
End If
%>
<%
Dim rsTrova__MMColParam1
rsTrova__MMColParam1 = "5"
If (Request.Form("select_name") <> "") Then
rsTrova__MMColParam1 = Request.Form("select_name")
End If
%>
<%
Dim rsTrova
Dim rsTrova_cmd
Dim rsTrova_numRows

Set rsTrova_cmd = Server.CreateObject ("ADODB.Command")
rsTrova_cmd.ActiveConnection = MM_dbMyDB_STRING
rsTrova_cmd.CommandText = "SELECT * FROM MyTable WHERE my_field LIKE '%' + ? + '%' AND my_field2 = ? ORDER BY field3 DESC"
rsTrova_cmd.Prepared = true
rsTrova_cmd.Parameters.Append rsTrova_cmd.CreateParameter("param1", 200, 1, 255, rsTrova__MMColParam) ' adVarChar
rsTrova_cmd.Parameters.Append rsTrova_cmd.CreateParameter("param2", 5, 1, -1, rsTrova__MMColParam1) ' adDouble

Set rsTrova = rsTrova_cmd.Execute
rsTrova_numRows = 0
%>

jmichae3
12-18-2011, 09:11 AM
all real databases seem to have some sort of full text search functions or keywords. try this.
http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html