XandarX
01-10-2005, 10:54 PM
I built a search engine that queries an access database.
It works perfectly, but I want to enhance it and I do not know how.
It searches a database of music cds and shows Title, Artist, Disc Number, Track Number and price. I would to be able to click the disk number and show what is on that cd.
If you could help me out I would appreciate it.
Here is my code:
<%
option explicit
function iif(byval c, byval t, byval f)
if c then
iif = t
else
iif = f
end if
end function
const maxRecords = 800
dim dbPath
dbPath = server.MapPath("/") & iif(instr(server.MapPath("/"),"")>0,"","/")
dbPath = dbPath & "/Search/db.mdb"
Dim FieldSelected, SortFieldSelected
FieldSelected = request.form("Field") : if FieldSelected = "" then FieldSelected = "Title"
SortFieldSelected = request.form("SortField") : if SortFieldSelected = "" then SortFieldSelected = "Artist"
%>
<html>
<head>
<title>Karaoke Search Engine</title>
<meta name="GENERATOR" content="LuTechASP">
</head>
<body>
<h3><font face="Arial">
<span style="font-weight: 400"><font size="3"><a target="_top" href="http://www.mysite.com">Back to home page.</a></font></span>
<a target="_top" href="http://www.mysite.com"><img border="0" src="back-arrow.jpg" width="64" height="40"></a> CD+G Karaoke Search</font></h3>
<p>When searching by artist, please use LastName, FirstName (including comma).<br>
If you would like to see what is on a certain disk, Search In DiskNo and enter the disk number in the search field.</P>
<form method="POST" action="search.asp">
<table border="0" height="29">
<TR>
<td width="81" height="25" valign="middle"><font face="Arial">
Search In:</TD><TD width="297">
Search For:</TD>
<TD width="119">Sort List By:</TD>
<TD width="257">Click To Search:</TD>
</select></font></td></TR>
<tr>
<td width="81" height="25" valign="middle"><font face="Arial"><select name="Field" size="1">
<option <% = iif(FieldSelected="Title", "selected", "") %> value="Title">Title</option>
<option <% = iif(FieldSelected="Artist", "selected", "") %> value="Artist">Artist</option>
<option <% = iif(FieldSelected="DiscNo", "selected", "") %> value="DiscNo">DiscNo</option>
<option <% = iif(FieldSelected="Description", "selected", "") %> value="Description">Manufacturer</option>
</select></font></td>
<td height="25" width="297" valign="middle"><font face="Arial"><input type="text"
name="SearchWord" value="<% = iif(request.form("SearchWord")="", "enter search term here...", request.form("SearchWord")) %>" size="25"></TD>
<TD width="119"><small>
<select name="SortField" size="1">
<option <% = iif(SortFieldSelected="Title", "selected", "") %> value="Title">Title</option>
<option <% = iif(SortFieldSelected="Artist", "selected", "") %> value="Artist">Artist</option>
<option <% = iif(SortFieldSelected="Description", "selected", "") %> value="Description">Manufacturer</option>
<option <% = iif(SortFieldSelected="DiscNo", "selected", "") %> value="DiscNo">DiscNo</option>
</select></small></font></td>
<td width="257" height="25" valign="middle"><font face="Arial"><input type="submit" value="Search" name="Lu2"></font></td>
</tr>
</table>
</form>
<%
if request.form("SearchWord") <> "" then
dim conn, rs, sql
conn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & dbPath
set rs = createobject("ADODB.Recordset")
sql = "SELECT TOP " & (maxRecords + 1) & " * " & _
" FROM Lu2 " & _
" WHERE InStr (" & request.form("Field") & ", " & chr(34) & replace(replace(request.form("SearchWord"),"*","%"), "'", "''") & chr(34) & ") > 0 " & _
" ORDER BY " & request.form("SortField")
'response.write sql
rs.open sql, conn, 1, 1
if not rs.EOF then
%>
<table border="1" width="100%">
<tr>
<td width="20%" bgcolor="#000000"><font face="Arial" color="#FFFFFF"><strong><small><small>Title</small></small></strong></font></td>
<td width="20%" bgcolor="#000000"><font face="Arial" color="#FFFFFF"><strong><small><small>Artist</small></small></strong></font></td>
<td width="20%" bgcolor="#000000"><font face="Arial" color="#FFFFFF"><strong><small><small>Description</small></small></strong></font></td>
<td width="20%" bgcolor="#000000"><font face="Arial" color="#FFFFFF"><strong><small><small>DiscNo</small></small></strong></font></td>
<td width="20%" bgcolor="#000000"><font face="Arial" color="#FFFFFF"><strong><small><small>TrackNo</small></small></strong></font></td>
<td width="20%" bgcolor="#000000"><font face="Arial" color="#FFFFFF"><strong><small><small>Price</small></small></strong></font></td>
</tr>
<%
Do While not rs.EOF
%>
<tr>
<td width="20%" valign="top"><small><font face="Arial"><% = "" & rs.fields("Title") %></font></small> </td>
<td width="20%" valign="top"><small><font face="Arial"><% = "" & rs.fields("Artist") %></font></small> </td>
<td width="20%" valign="top"><small><font face="Arial"><% = "" & rs.fields("Description") %></font></small> </td>
<td width="15%" valign="top"><small><font face="Arial"><a href="search.asp?Field=DiscNo&SearchWord=<% = "" & rs.fields("DiscNo") %>&SortField=TrackNo" request.form("SearchWord")><% = "" & rs.fields("DiscNo") %></a></font></small> </td>
<td width="15%" valign="top"><small><font face="Arial"><% = "" & rs.fields("TrackNo") %></font></small> </td>
<td width="15%" valign="top"><small><font face="Arial"><A href=http://www.mysite.com/shopaddtocartnodb.asp?productname=<% = "" & rs.fields("DiscNo") %>&quantity=1&Price=<% = "" & rs.fields("Price") %> target="_top"><% = "" & rs.fields("Price") %></font></small> </td>
</tr>
<%
rs.MoveNext
Loop
%>
</table>
<%
else
%>
<font face="Arial"><small><b><p>No matching records found.</b></small></font>
<%
end if
if rs.recordcount > maxRecords then
%> <br>
<font face="Arial"><small><b>Too many matching records. Please narrow your search criteria.</b></small></font> <%
end if
rs.close
end if
%> </p>
</body>
</html>
It works perfectly, but I want to enhance it and I do not know how.
It searches a database of music cds and shows Title, Artist, Disc Number, Track Number and price. I would to be able to click the disk number and show what is on that cd.
If you could help me out I would appreciate it.
Here is my code:
<%
option explicit
function iif(byval c, byval t, byval f)
if c then
iif = t
else
iif = f
end if
end function
const maxRecords = 800
dim dbPath
dbPath = server.MapPath("/") & iif(instr(server.MapPath("/"),"")>0,"","/")
dbPath = dbPath & "/Search/db.mdb"
Dim FieldSelected, SortFieldSelected
FieldSelected = request.form("Field") : if FieldSelected = "" then FieldSelected = "Title"
SortFieldSelected = request.form("SortField") : if SortFieldSelected = "" then SortFieldSelected = "Artist"
%>
<html>
<head>
<title>Karaoke Search Engine</title>
<meta name="GENERATOR" content="LuTechASP">
</head>
<body>
<h3><font face="Arial">
<span style="font-weight: 400"><font size="3"><a target="_top" href="http://www.mysite.com">Back to home page.</a></font></span>
<a target="_top" href="http://www.mysite.com"><img border="0" src="back-arrow.jpg" width="64" height="40"></a> CD+G Karaoke Search</font></h3>
<p>When searching by artist, please use LastName, FirstName (including comma).<br>
If you would like to see what is on a certain disk, Search In DiskNo and enter the disk number in the search field.</P>
<form method="POST" action="search.asp">
<table border="0" height="29">
<TR>
<td width="81" height="25" valign="middle"><font face="Arial">
Search In:</TD><TD width="297">
Search For:</TD>
<TD width="119">Sort List By:</TD>
<TD width="257">Click To Search:</TD>
</select></font></td></TR>
<tr>
<td width="81" height="25" valign="middle"><font face="Arial"><select name="Field" size="1">
<option <% = iif(FieldSelected="Title", "selected", "") %> value="Title">Title</option>
<option <% = iif(FieldSelected="Artist", "selected", "") %> value="Artist">Artist</option>
<option <% = iif(FieldSelected="DiscNo", "selected", "") %> value="DiscNo">DiscNo</option>
<option <% = iif(FieldSelected="Description", "selected", "") %> value="Description">Manufacturer</option>
</select></font></td>
<td height="25" width="297" valign="middle"><font face="Arial"><input type="text"
name="SearchWord" value="<% = iif(request.form("SearchWord")="", "enter search term here...", request.form("SearchWord")) %>" size="25"></TD>
<TD width="119"><small>
<select name="SortField" size="1">
<option <% = iif(SortFieldSelected="Title", "selected", "") %> value="Title">Title</option>
<option <% = iif(SortFieldSelected="Artist", "selected", "") %> value="Artist">Artist</option>
<option <% = iif(SortFieldSelected="Description", "selected", "") %> value="Description">Manufacturer</option>
<option <% = iif(SortFieldSelected="DiscNo", "selected", "") %> value="DiscNo">DiscNo</option>
</select></small></font></td>
<td width="257" height="25" valign="middle"><font face="Arial"><input type="submit" value="Search" name="Lu2"></font></td>
</tr>
</table>
</form>
<%
if request.form("SearchWord") <> "" then
dim conn, rs, sql
conn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & dbPath
set rs = createobject("ADODB.Recordset")
sql = "SELECT TOP " & (maxRecords + 1) & " * " & _
" FROM Lu2 " & _
" WHERE InStr (" & request.form("Field") & ", " & chr(34) & replace(replace(request.form("SearchWord"),"*","%"), "'", "''") & chr(34) & ") > 0 " & _
" ORDER BY " & request.form("SortField")
'response.write sql
rs.open sql, conn, 1, 1
if not rs.EOF then
%>
<table border="1" width="100%">
<tr>
<td width="20%" bgcolor="#000000"><font face="Arial" color="#FFFFFF"><strong><small><small>Title</small></small></strong></font></td>
<td width="20%" bgcolor="#000000"><font face="Arial" color="#FFFFFF"><strong><small><small>Artist</small></small></strong></font></td>
<td width="20%" bgcolor="#000000"><font face="Arial" color="#FFFFFF"><strong><small><small>Description</small></small></strong></font></td>
<td width="20%" bgcolor="#000000"><font face="Arial" color="#FFFFFF"><strong><small><small>DiscNo</small></small></strong></font></td>
<td width="20%" bgcolor="#000000"><font face="Arial" color="#FFFFFF"><strong><small><small>TrackNo</small></small></strong></font></td>
<td width="20%" bgcolor="#000000"><font face="Arial" color="#FFFFFF"><strong><small><small>Price</small></small></strong></font></td>
</tr>
<%
Do While not rs.EOF
%>
<tr>
<td width="20%" valign="top"><small><font face="Arial"><% = "" & rs.fields("Title") %></font></small> </td>
<td width="20%" valign="top"><small><font face="Arial"><% = "" & rs.fields("Artist") %></font></small> </td>
<td width="20%" valign="top"><small><font face="Arial"><% = "" & rs.fields("Description") %></font></small> </td>
<td width="15%" valign="top"><small><font face="Arial"><a href="search.asp?Field=DiscNo&SearchWord=<% = "" & rs.fields("DiscNo") %>&SortField=TrackNo" request.form("SearchWord")><% = "" & rs.fields("DiscNo") %></a></font></small> </td>
<td width="15%" valign="top"><small><font face="Arial"><% = "" & rs.fields("TrackNo") %></font></small> </td>
<td width="15%" valign="top"><small><font face="Arial"><A href=http://www.mysite.com/shopaddtocartnodb.asp?productname=<% = "" & rs.fields("DiscNo") %>&quantity=1&Price=<% = "" & rs.fields("Price") %> target="_top"><% = "" & rs.fields("Price") %></font></small> </td>
</tr>
<%
rs.MoveNext
Loop
%>
</table>
<%
else
%>
<font face="Arial"><small><b><p>No matching records found.</b></small></font>
<%
end if
if rs.recordcount > maxRecords then
%> <br>
<font face="Arial"><small><b>Too many matching records. Please narrow your search criteria.</b></small></font> <%
end if
rs.close
end if
%> </p>
</body>
</html>