logo-dw

Go Back   Dreamweaver Club Forums > Hand Coders Forum > ASP
Register FAQ Members List Search Today's Posts Mark Forums Read

Reply
 
Thread Tools Display Modes
Old 02-02-2011, 05:55 PM   #1
Ciombe
 
Join Date: Feb 2011
Posts: 4
Default 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?
Ciombe is offline   Reply With Quote
Old 02-03-2011, 10:56 AM   #2
jmichae3
 
Join Date: Dec 2010
Posts: 366
Default

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.
jmichae3 is offline   Reply With Quote
Old 02-03-2011, 01:44 PM   #3
Ciombe
 
Join Date: Feb 2011
Posts: 4
Default

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 is offline   Reply With Quote
Old 02-03-2011, 06:00 PM   #4
Ciombe
 
Join Date: Feb 2011
Posts: 4
Default

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
%>
Ciombe is offline   Reply With Quote
Old 12-18-2011, 09:11 AM   #5
jmichae3
 
Join Date: Dec 2010
Posts: 366
Default

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
jmichae3 is offline   Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 11:13 PM.


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