Dreamweaver Club Forums

Dreamweaver Club Forums (http://www.dreamweaverclub.com/forum//index.php)
-   ASP (http://www.dreamweaverclub.com/forum//forumdisplay.php?f=187)
-   -   SQL Query using LIKE (http://www.dreamweaverclub.com/forum//showthread.php?t=39002)

Ciombe 02-02-2011 05:55 PM

SQL Query using LIKE
 
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"

HTML Code:

<%
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
Code:

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

HTML 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/...xt-search.html


All times are GMT. The time now is 03:10 AM.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Copyright 2006 DreamweaverClub.com