ASP Search...

dodgyone · Feb 19, 2001

I'm trying to set up a search page by where there is a drop down list which points at a partiular field in an MS Access DB table and a text box to enter the search criteria...

My code is as follows...

<%
view = request.querystring("view&quot

' set up database conection
' this connection does not require an ODBC reference
path = Trim(Server.MapPath("/fpdb&quot

)
conn_string = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" + path + "\images.mdb"
Set con = Server.CreateObject("ADODB.Connection&quot

con.open conn_string

if view = "post" then
text = "reference goes here"

end if
%>
<html>

<head>
<%
if view="upload" then
response.write("<meta http-equiv='refresh' content='4; url=images.asp?view=read'>&quot

end if
%>

<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">

<SCRIPT ID=clientEventHandlersVBS LANGUAGE=vbscript>

</SCRIPT>
<title></title>
</head>

<body vlink="#0000FF" alink="#0000FF">

<a href="../image%20database/IMAGE%20Scan.asp?view=read"><img border="0" src="../image%20database/imagedb.jpg"></a>

 

<%
'------- this bit is th econfusing part
if view= "read" then
search = request.querystring("filter&quot

' ------- if no filter show all records
if search="" then
mode="full"
mySQL = "SELECT top 100 * FROM pic_table ORDER BY id desc;"

' ----- if filter set to specific words then special SQL applies
elseif search="PRESETSEARCH_TOP200" then
mode="filtered"
mySQL = "SELECT top 200 * FROM pic_table ORDER BY id desc;"
elseif search="PRESETSEARCH_TOP350" then
mode="filtered"
mySQL = "SELECT top 350 * FROM pic_table ORDER BY id desc ;"
elseif search="PRESETSEARCH_TOP500" then
mode="filtered"
mySQL = "SELECT top 500 * FROM pic_table ORDER BY id desc;"
else
' ------- show filtered recordset only
search="%"+search+"%"
mode="filtered"
mySQL = "SELECT * FROM pic_table where cadastre like " & chr(39) &cstr(search) & chr(39)&" or id like " & chr(39) &cstr(search) & chr(39)&" or details like " & chr(39) &cstr(search) & chr(39) &" or keywords like " & chr(39) &cstr(search) & chr(39)&" or author like " & chr(39) &cstr(search) & chr(39)&" ORDER BY id desc;"
end if
'---- this is the bit which I'm unsure about
sql1 = "SELECT DISTINCT id,cadastre,author,details,keywords FROM [pic_table] where"

if field="id" then
sql2=" [pic_table]." & cstr(field) & " = " & cstr(data)
elseif field="cadastre" then
sql2=" [pic_table].[cadastre] like '%"& cstr(data)& "%'"
elseif field="author" then
sql2=" [pic_table].[author] like '%"& cstr(data)& "%'"
elseif field="details" then
sql2=" [pic_table].[details] like '%"& cstr(data)& "%'"
elseif field="details" then
sql2=" [pic_table].[details] like '%"& cstr(data)& "%'"
else
sql2=" [pic_table]." & cstr(field) & " like '" & cstr(data) & "%'"
end if

sql3=" Order by [Cadastre];"

sql = sql1 & sql2 & sql3

Set rs = Server.CreateObject("ADODB.Recordset&quot

rs.Open mySQL, con, 0, 1
%>

<hr>
Options:  <a href="../image%20database/IMAGE%20Scan.asp?view=read">Image
Quick Scan</a>       <a href="

http://172.16.2.18/IDCNET Live/Image Database Copy/IMAGE.asp?view=filter">Search

for a Reference</a>      <a href="

http://172.16.2.18/IDCNET Live/starter image.htm">Leave

the database</a>

<hr>
  <%if mode="filtered" then%>
<a href="../image%20database/IMAGE%20Scan.asp?view=read"><img border="0" src="../images/blinktriangle.gif" alt="You are currently viewing a filtered list from your previous search.... click on this to return to main IMAGE DATABASE page" align="left"></a>
   
...you are currently viewing a filtered list... 
 
[<a href="

http://172.16.2.18/IDCNET Live/Image Database Copy/IMAGE.asp?view=filter">RE-SEARCH</a>

]   
[<a href="../image%20database/IMAGE%20Scan.asp?view=read">QUICK SCAN</a>] 
<%end if%> 
<%if mode<>"filtered" then%>Showing latest
100
images uploaded...<%end if%>

 


<%
do until rs.EOF
new_date=rs("posted&quot

%>

<div align="center">
<center>
<table width="625" height="20" cellpadding="0">
<tr>
<td width="1" bgcolor="#FCFFF0" bordercolor="#99A6FB" rowspan="4" align="center" style="border: 1 solid #99A6FB"><a href="IMAGE_VIEW.asp?view=<%=rs("id&quot

%>"><img src="../NetVisual/thumbnails/thmb_<%=rs("piclocation&quot

%>" width="100" bgcolor="#C9D5F5" 24" height="100" alt="<%=cstr(rs("id&quot

)%>" align="center"></a></td>
<td width="295" bgcolor="#FCFFF0" bordercolor="#99A6FB" style="border: 1 solid #99A6FB">Image
ID: <a href="IMAGE_VIEW.asp?view=<%=rs("id&quot

%>"><%=rs("id&quot

%></a> </td>
<td width="504" bgcolor="#FCFFF0" bordercolor="#99A6FB" style="border: 1 solid #99A6FB" rowspan="2">Details: <%=rs("details&quot

%> </td>
</tr>

<tr>
<td width="295" bgcolor="#FCFFF0" bordercolor="#99A6FB" style="border: 1 solid #99A6FB">Cadastre:

<%=rs("cadastre&quot

%></td>
</tr>

<tr>
<td width="295" bgcolor="#FCFFF0" bordercolor="#99A6FB" style="border: 1 solid #99A6FB">Date
Uploaded: <%=rs("posted&quot

%></td>
<td width="504" bgcolor="#FCFFF0" bordercolor="#99A6FB" style="border: 1 solid #99A6FB" rowspan="2">Keywords:<%=rs("keywords&quot

%> </td>
</tr>

<tr>
<td width="295" bgcolor="#FCFFF0" bordercolor="#99A6FB" style="border: 1 solid #99A6FB">User
ID: <%=rs("author&quot

%></td>
</tr>

<tr>
<td width="265" bgcolor="#FFFFFF" bordercolor="#99A6FB" align="center" colspan="2">
 
</td>
</tr>

<%
rs.movenext
loop
rs.close
%>
</table>

</center>
</div>

 

<%elseif view = "filter" then
%>

<div align="center">
<table border="0" width="511">
<tr>
<td width="503">
Select
a category from the drop-down box and enter your search criteria...
<center>

<form method="GET" action="IMAGE.asp" name="FrontPage_Form1" target="_self">
<select size="1" name="D1">
<option value="Cadastre" selected>Cadastre</option>
<option value="ID">ID</option>
<option value="Author">Author</option>
<option value="Details">Details</option>
<option value="Keywords">Keywords</option>
</select><input type="text" name="view" size="6" value="read" style="color: #FFFFFF; position: absolute; z-index: -3; font-family: Abadi MT Condensed; font-size: 8pt; background-color: #FFFFFF; left: 575; top: 20"><input type="text" name="filter" size="37"><input type="submit" value="Search" name="button1"></a>
 

 

 -
Special
Searches-


<a href="../image%20database/image%20scan.asp?view=read&filter=PRESETSEARCH_TOP200">latest
200</a><a href="../image%20database/image%20scan.asp?view=read&filter=PRESETSEARCH_TOP350"> 
latest 350 
</a><a href="../image%20database/image%20scan.asp?view=read&filter=PRESETSEARCH_TOP500">latest</a><a href="../image%20database/image%20scan.asp?view=read&filter=PRESETSEARCH_TOP500">
500</a> 

<a href="../image%20database/IMAGE_USER.asp?view=filter">Top
100 for specific User ID</a>
<a href="../image%20database/GroupSearch.htm">View
images from an Upload Group</a>
 
 <a href="../image%20database/IMAGE%20Scan.asp?view=read">Go to
Image Quick Scan</a>
</form>

</center>

</table>

</div>



<%
else

'error trap if nothing selected but got here by mistake - eg no view value after .asp in url
if view="" then
view="0"
end if

mySQL = "SELECT * FROM pic_table where id="
mySQL=mySQL + cstr(view)
mySQL=mySQL + " ORDER BY id desc"
Set rs = Server.CreateObject("ADODB.Recordset&quot

rs.Open mySQL, con, 0, 1
%>





 



<div align="center">
<center>

<table border="1" width="791" height="104">
<tr>
<td width="102" rowspan="4" height="98">
<a href="IMAGE_VIEW.asp?view=<%=rs("id&quot

%>"><img src="../NetVisual/thumbnails/thmb_<%=rs("piclocation&quot

%>" width="100" bgcolor="#C9D5F5" 24" height="100" alt="<%=rs("piclocation&quot

%>"></a>
</td>
<td width="189" height="16">Image
ID:</td>
<td width="333" height="16"> <%=rs("id&quot

%></td>
<td width="97" height="80" rowspan="4"></td>
<td width="151" height="35" rowspan="2">Details:</td>
<td width="1518" height="35" rowspan="2"> <%=rs("details&quot

%></td>
</tr>
<tr>
<td width="189" height="19">User
ID:</td>
<td width="333" height="19"> <%=rs("author&quot

%></td>
</tr>
<tr>
<td width="189" height="26">Date
Uploaded:</td>
<td width="333" height="26"> <%=rs("posted&quot

%>
</td>
<td width="151" height="45" rowspan="2">Keywords: </td>
<td width="1518" height="45" rowspan="2"> <%=rs("keywords&quot

%></td>
</tr>
<tr>
<td width="189" height="19">Cadastre:</td>
<td width="333" height="19"> <%=rs("cadastre&quot

%></td>
</tr>
</table>

</center>
</div>

<img border="0" src="../image%20database/bd10263_.gif"> 
<a href="IMAGE_EDIT.asp?view=<%=rs("id&quot

%>">edit details</a> 

 

 

<a href="IMAGE.asp?view=read">Return
to contents page</a>

<%
end if
%>

</body>

As you can see it's all actioned on the one ASP page. Could someone please help. I'm not the most technical person so please go easy on me...

Cheers in advance,

Marcus

hblackorby · Feb 19, 2001

I'm confused about what your trying to do. SELECT DISTINCT will pull one single record with a the matching field. It ensures that all records in your recordset are unique. It probably isn't a good idea to SELECT DISTINCT and pull out other fields too. It'll pull the first row it finds with the matching query, which may not be the row you want. I usually use SELECT DISTINCT on one field to generate a list of items to populate a select box then use SELECT * WHERE BLAH=selectedBLAH to pull matching records out.

Maybe someone else can be more helpful. Your code is pretty long to run through it all without a database to look at. Harold Blackorby
hblackorby@scoreinteractive.com
St. Louis, MO

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

ASP Search...

dodgyone

Technical User

hblackorby

Programmer

Similar threads

Part and Inventory Search

Sponsor