...

View Full Version : Dynamic lists and searching



si992
04-20-2006, 04:02 PM
Hiya,
more than likely im being a bit simple about this but im pretty new to asp/sql so please forgive me!

Im making a search page for the database im working on which is fine if i want to search single fields or two fields but where im stuck is if i want to search accross all fields but be able to leave some blank, ie search by author and company but not project name. At the momment i have to select something from each.

The other problem i have is that im using dynamic lists to populate the search fields (a couple of the fields have about 300 items in them) but when i come to view it everythings ok except it displays the last item in the list. How would i go about making it so that it displays blank or "please Select" as the default?

hope someone can help and thanks in advance!
Si

angst
04-20-2006, 04:06 PM
can you show us your code?

si992
04-20-2006, 04:10 PM
Yeah, heres the bit relating to part of it...

<form id="search3" name="search3" method="post" action="results3.asp">
<label>
<div align="center">Customer
<select name="cust1" id="cust1">
<%
While (NOT rssearch2.EOF)
%>
<option value="<%=(rssearch2.Fields.Item("customer").Value)%>" <%If (Not isNull((rssearch2.Fields.Item("customer").Value))) Then If (CStr(rssearch2.Fields.Item("customer").Value) = CStr((rssearch2.Fields.Item("customer").Value))) Then Response.Write("selected=""selected""") : Response.Write("")%> ><%=(rssearch2.Fields.Item("customer").Value)%></option>
<%
rssearch2.MoveNext()
Wend
If (rssearch2.CursorType > 0) Then
rssearch2.MoveFirst
Else
rssearch2.Requery
End If
%>
</select>
</div>
</label>
<div align="center">&nbsp;&nbsp;&nbsp;
</div>
<label>
<div align="center">Author
<select name="auth1" id="auth1">
<%
While (NOT rssearch3.EOF)
%>
<option value="<%=(rssearch3.Fields.Item("author").Value)%>" <%If (Not isNull((rssearch3.Fields.Item("author").Value))) Then If (CStr(rssearch3.Fields.Item("author").Value) = CStr((rssearch3.Fields.Item("author").Value))) Then Response.Write("selected=""selected""") : Response.Write("")%> ><%=(rssearch3.Fields.Item("author").Value)%></option>
<%
rssearch3.MoveNext()
Wend
If (rssearch3.CursorType > 0) Then
rssearch3.MoveFirst
Else
rssearch3.Requery
End If
%>
</select>
</div>
</label>
<label>
<div align="center">
<input type="submit" name="Submit3" value="Search" />
</div>
</label>
</form>


and the SQL section i have in my results page is....

SELECT *
FROM Table1
WHERE customer = 'MMColParam' AND author = 'MMColParam2'

Thanks!

angst
04-20-2006, 04:13 PM
umm, what type of data base are you using?
and are you trying to do this in ASP.NET? Also where are the select statements?

these are things when you mention when you first post.

si992
04-20-2006, 04:23 PM
umm, what type of data base are you using?
and are you trying to do this in ASP.NET? Also where are the select statements?

these are things when you mention when you first post.

Sorry, only been using Dreamweaver for a week and a bit and this is the first time ive ever posted on a forum! im using ASP connecting to an access database. Heres a simple page but i have more complicated projects on the go but still face the same problems.



<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/connfaelog.asp" -->
<%
Dim rssearch2
Dim rssearch2_numRows

Set rssearch2 = Server.CreateObject("ADODB.Recordset")
rssearch2.ActiveConnection = MM_connfaelog_STRING
rssearch2.Source = "SELECT Distinct customer FROM Table1 ORDER BY customer ASC"
rssearch2.CursorType = 0
rssearch2.CursorLocation = 2
rssearch2.LockType = 1
rssearch2.Open()

rssearch2_numRows = 0
%>
<%
Dim rssearch3
Dim rssearch3_numRows

Set rssearch3 = Server.CreateObject("ADODB.Recordset")
rssearch3.ActiveConnection = MM_connfaelog_STRING
rssearch3.Source = "SELECT DISTINCT author FROM Table1 ORDER BY author ASC"
rssearch3.CursorType = 0
rssearch3.CursorLocation = 2
rssearch3.LockType = 1
rssearch3.Open()

rssearch3_numRows = 0
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Search for Report</title>
<link href="faereport.css" rel="stylesheet" type="text/css" />
</head>

<body>
<h1 align="center">Search </h1>
<p align="center"><a href="admin_control_2.asp">Back</a></p>
<h3 align="center">&nbsp;</h3>
<form id="search3" name="search3" method="post" action="results3.asp">
<label>
<div align="center">Customer
<select name="cust1" id="cust1">
<%
While (NOT rssearch2.EOF)
%>
<option value="<%=(rssearch2.Fields.Item("customer").Value)%>" <%If (Not isNull((rssearch2.Fields.Item("customer").Value))) Then If (CStr(rssearch2.Fields.Item("customer").Value) = CStr((rssearch2.Fields.Item("customer").Value))) Then Response.Write("selected=""selected""") : Response.Write("")%> ><%=(rssearch2.Fields.Item("customer").Value)%></option>
<%
rssearch2.MoveNext()
Wend
If (rssearch2.CursorType > 0) Then
rssearch2.MoveFirst
Else
rssearch2.Requery
End If
%>
</select>
</div>
</label>
<div align="center">&nbsp;&nbsp;&nbsp;
</div>
<label>
<div align="center">Author
<select name="auth1" id="auth1">
<%
While (NOT rssearch3.EOF)
%>
<option value="<%=(rssearch3.Fields.Item("author").Value)%>" <%If (Not isNull((rssearch3.Fields.Item("author").Value))) Then If (CStr(rssearch3.Fields.Item("author").Value) = CStr((rssearch3.Fields.Item("author").Value))) Then Response.Write("selected=""selected""") : Response.Write("")%> ><%=(rssearch3.Fields.Item("author").Value)%></option>
<%
rssearch3.MoveNext()
Wend
If (rssearch3.CursorType > 0) Then
rssearch3.MoveFirst
Else
rssearch3.Requery
End If
%>
</select>
</div>
</label>
<label>
<div align="center">
<input type="submit" name="Submit3" value="Search" />
</div>
</label>
</form>
<p align="center"></p>
<p align="center"><a href="admin_control.asp"></a></p>
</body>
</html>
<%
rssearch2.Close()
Set rssearch2 = Nothing
%>
<%
rssearch3.Close()
Set rssearch3 = Nothing
%>

Thanks
Si



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum