Search results based on drop-down list 1

[Herminio]

I have a search page that i need to retriev the values from the selection the user makes in one drop-down list, if the user selects date, then the search is made by date, if the user selects event the selection is made by event and so one.

Please help me on this

Thanks in advance

Herminio, Portugal

 

[Tarwn]

Previous page:

<select name=&quot;selSearchType&quot;>
   <option value=&quot;&quot;> [Search by?] </option>
   <option value=&quot;date&quot;> Date </option>
   <option value=&quot;event&quot;> Event </option>
   <option value=&quot;place&quot;> Location </option>
</select>
<input type=&quot;text&quot; name=&quot;txtSearchWord&quot;>

Asp portion to build sql string:

Dim searchText
Select Case Request.Form("selSearchType")
   Case "date"
      searchText = " WHERE myDateField = #"&Request.Form("txtSearchWord")&"#"
   Case "event"
      searchText = " WHERE myEventField LIKE '%" & Request.Form("txtSearchWord") & "%'"
   Case "place"
      searchText = " WHERE myPlaceField LIKE '%" & Request.Form("txtSearchWord") & "%'"
   Case Else
      searchText = ""
End Select

DIm sqlString
sqlString = "SELECT * FROM myTable" & searchText

Something like that?
Hope thats helpful,
-Tarwn ------------ My Little Dictionary ---------
Extreme Programming - (1)Trying to code before my second cup of coffee. (2) While(1){ Ctrl+C; Ctrl+V; }
FAQ - Web-ese for "Forget Asking Questions, I am to busy"

 

[Herminio]

Thanks Tarwn, that's just it, i've gave you a star for that tip, but tell me something, if i want to make the selection by date more specific, for example when they choose date form the drop-down i want the user to be able to choose wich month they want to search, and when they choose event i want them to be able to choose the type of event as well, how do i do it? and can i make a generic search based on only date and month, if the user chooses date=january and event=music i want to show all the result that match that criteria, is this possible?

Thanks

Herminio, Portugal

 

[Herminio]

Presume that i have values stored on the DB like this

event_type|event_date
-----------------------------
Music |January,15,2002
Theater |February,22,2002
Music |January,31,2002
...... |......
-----------------------------

if i choose event=music and month=january then the first an third record were showed, did i make my self clear?

 

[Tarwn]

Ah, I see, you want something morte along the lines of a multiple entry search. You could split this all into seperate inputs, similar to how the car sites do with make, model, date, etc

Month: <input type=&quot;text&quot; name=&quot;txtMonth&quot;><br>
AND<br>
Location: <input type=&quot;text&quot; name=&quot;txtPlace&quot;><br>
AND<br>
Event: <input type=&quot;text&quot; name=&quot;txtEvent&quot;><br>

Then you could build your SQL dynamically like so:

dim sqlString
sqlString = "SELECT * FROM myTable"

Dim whereflag
whereflag = false

If Request.Form(&quot;txtMonth&quot;) <> &quot;&quot; Then
   whereflag = true
   sqlString = sqlString & &quot; WHERE month(myDateField) = &quot; & trim(Request.Form(&quot;txtMonth&quot;))
End If
 'etc and so on, always checking the whereflag, if its true you start with an and, if it's false you start with a WHERE

or you can be tricky, and do the above, except instead of repeating the if statement and always checking the whereflag you can do this:

dim sqlString
sqlString = "SELECT * FROM myTable WHERE myEventField LIKE '%" & Request.Form("txtEvent") & "%' AND myPlaceField LIKE '%" & Request.Form("txtPlace") & "%'"

If Request.Form(&quot;txtMonth&quot;) <> &quot;&quot; Then
   sqlString = sqlString & &quot; AND month(myDateField) = &quot; & trim(Request.Form(&quot;txtMonth&quot;))
End If

This will be a little more strenuous on your db, but here is how it works. If there is no entry in event or place, it will simply matcgh all entries. If there is an entry in date, it will match all entries with the same month. The first way is less wasteful of the db processing, but requires you to write more code, ie an if check for every field.
-Tarwn ------------ My Little Dictionary ---------
Extreme Programming - (1)Trying to code before my second cup of coffee. (2) While(1){ Ctrl+C; Ctrl+V; }
FAQ - Web-ese for "Forget Asking Questions, I am to busy"

 

[Herminio]

Can you tell me what's wrong now?

i'm getting this error

Microsoft VBScript error '800a01a8'
Object needed: 'Select * from member'

/search/teste/results.asp, line55

here is my code

----search.htm----

<form method=&quot;get&quot; action=&quot;results.asp&quot;>
<table border=&quot;0&quot; cellpadding=&quot;0&quot; cellspacing=&quot;0&quot; style=&quot;border-collapse: collapse&quot; bordercolor=&quot;#111111&quot; width=&quot;80&quot; id=&quot;AutoNumber1&quot;>
<tr>
<td><b>Colectividade:</b></td>
<td><input type=&quot;text&quot; name=&quot;searchtext&quot; size=&quot;20&quot;></td>
</tr>
<tr>
<td><b>Procurar por:</b></td>
<td><select size=&quot;1&quot; name=&quot;freg&quot;>
<option selected>Nome</option>
<option>Freguesia</option>
<option>Evento</option>
</select></td>
</tr>
</table>
<p><input type=&quot;submit&quot; name=&quot;submit&quot; value=&quot;Procurar&quot;><br>
&nbsp;</p>
</form>

----results.asp----

<%@LANGUAGE=&quot;VBSCRIPT&quot; CODEPAGE=&quot;1252&quot;%>
<%
dim conn
set conn = server.createobject(&quot;adodb.connection&quot
conn.open &quot;dsn=direct&quot;


dim searchtext,search

search = request.QueryString(&quot;searchtext&quot
select case request.QueryString(&quot;freg&quot
case &quot;Nome&quot;
searchtext = &quot; where colect like '%&quot; & search & &quot;%'&quot;
case &quot;Freguesia&quot;
searchtext = &quot; where freguesia like '%&quot; & search & &quot;%'&quot;
case &quot;Evento&quot;
searchtext = &quot; where evento like '%&quot; & search & &quot;%'&quot;
case else
searchtext = &quot;&quot;
end select

dim sqlstring
sqlstring = &quot;Select * from member&quot;
sqlstring = sqlstring & searchtext
conn.execute(sqlstring)

%>





<html>

<head>
<meta http-equiv=&quot;Content-Language&quot; content=&quot;pt&quot;>

<title>Resultados da Pesquisa</title>
</head>

<body>
<table border=&quot;1&quot; cellpadding=&quot;0&quot; cellspacing=&quot;0&quot; style=&quot;border-collapse: collapse&quot; bordercolor=&quot;#111111&quot; width=&quot;80%&quot;>
<tr>
<td width=&quot;100%&quot; colspan=&quot;3&quot;>
<p align=&quot;center&quot;><b>Resultados da Pesquisa por <%=search%> </b></td>
</tr>
<tr>
<td width=&quot;33%&quot;>
<p align=&quot;center&quot;><b>Colectividade</b></td>
<td width=&quot;33%&quot;>
<p align=&quot;center&quot;><b>Freguesia</b></td>
<td width=&quot;34%&quot;>
<p align=&quot;center&quot;><b>Evento</b></td>
</tr>
<% while not sqlstring.eof%>
<tr>
<td width=&quot;33%&quot;><%=sqlstring(&quot;Colect&quot%>&nbsp;</td>
<td width=&quot;33%&quot;><%=sqlstring(&quot;Freguesia&quot%>&nbsp;</td>
<td width=&quot;34%&quot;><%=sqlstring(&quot;Evento&quot%>&nbsp;</td>
</tr>
<% sqlstring.movenext
wend
%>
</table>
</body>

</html>

Could some one help me on this

Thanks in advance

Herminio, Portugal

 

[Tarwn]

Ah, ok, I have looked at this several times now and finally found it. You don't have a recordset for the results to return into.

.
.
.
dim sqlstring
dim rsResults
sqlstring = "Select * from member"
sqlstring = sqlstring & searchtext
Set rsResults = conn.execute(sqlstring)

Without the recordset to get the results your execute was attempting to return results and there was no object to pick it up. That may be the problem. Also check and make sure your DSN is pointing to the correct database and double check your table name.
-Tarwn ------------ My Little Dictionary ---------
Extreme Programming - (1)Trying to code before my second cup of coffee. (2) While(1){ Ctrl+C; Ctrl+V; }
FAQ - Web-ese for &quot;Forget Asking Questions, I am to busy&quot;

 

[Tarwn]

You will want to change the later variables in your html to be rsResults rather than sqlString. sqlString only holds the sql string that will be executed while rsResults holds the values that came back from the db.
-Tarwn ------------ My Little Dictionary ---------
Extreme Programming - (1)Trying to code before my second cup of coffee. (2) While(1){ Ctrl+C; Ctrl+V; }
FAQ - Web-ese for &quot;Forget Asking Questions, I am to busy&quot;

 

[Tarwn]

The actual error was occurring down in your html when the ASP script was attempting to access your sql string as if it was an object. I just ran a test to confirm it and if you switch the 5 occurrences of sqlString in your later html to rsResults and add the portion I entered above, it will work for you.
-Tarwn ------------ My Little Dictionary ---------
Extreme Programming - (1)Trying to code before my second cup of coffee. (2) While(1){ Ctrl+C; Ctrl+V; }
FAQ - Web-ese for &quot;Forget Asking Questions, I am to busy&quot;

 

[Herminio]

It Warked!!

Thanks again Tarwn, one more star for you

 

[Tarwn]

Glad to be of service
-tarwn ------------ My Little Dictionary ---------
Extreme Programming - (1)Trying to code before my second cup of coffee. (2) While(1){ Ctrl+C; Ctrl+V; }
FAQ - Web-ese for &quot;Forget Asking Questions, I am to busy&quot;