Steve said:
The link is:
www.skyhaipoker.ca/standings.asp
No names or scores are plugged into the table yet. At the top of page
(beneath "Overall Standings" title) I want the visitor to have a
search option. They would type there name in a search box and there
scores come up. It beats having to flip through 5 pages to find there
scores.
Any help? Thanks!
Well, I said I would try
The code below does a search on my guestbook (site in my signature)
This page will do the work as is, provided it is saved as an .asp file. You
can put a link to it in your home page (or wherever)
But of course you need to alter it to your needs
1.
I have set strDBPath to an entry in global.asa
Application("guestbook_ConnectionString") = "DRIVER={Microsoft Access Driver
(*.mdb)};DBQ=URL=fpdb/guestbook.mdb"
So you need to set up an entry on global.asa, changing the path to that to
your DB.
I tried simply setting
strDBPath = "DRIVER={Microsoft Access Driver
(*.mdb)};DBQ=URL=fpdb/guestbook.mdb"
but I got this error:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver]General error Unable to open
registry key 'Temporary (volatile) Jet DSN for process 0x8a0 Thread 0xf74
DBC 0x15faebc4 Jet'.
/search.asp, line 44
where line 44 is
cnnSearch.Open strDBPath
I have idea why. It seems to me that the two are equivalent. Maybe it is
already open or something. I am hoping someone else can give some more info.
2.
Change
<th><b>Name</b></th>
<th><b>Comments</b></th>
<th><b>Timestamp</b></th>
to the titles for the fields you want from your DB
possibly
<th ALIGN="LEFT"><b>Player Name</b></th>
<th ALIGN="LEFT"><b>Dickens</b></th>
<th ALIGN="LEFT"><b>Post Office</b></th>
<th ALIGN="LEFT"><b>Ivy Arms</b></th>
<th ALIGN="LEFT"><b>Bryden's</b></th>
<th ALIGN="LEFT"><b>Total</b></th>
3.
Change
<td valign="top"><%=FP_FieldVal(rstSearch,"name")%></td>
<td valign="top"><%=FP_FieldVal(rstSearch,"comments")%>
<td><%=FP_FieldVal(rstSearch,"Timestamp")%></td>
where the names in "" are those of our fields you want from your DB
(I would only be guessing here as to the field names)
4. The line
<!--#include file="_fpclass/fpdblib.inc"-->
includes a library which is usually generated by the DB wizard.
If you dont have it, I will post it separately
Either that or change the code in 3. to
<td valign="top"><%= rstSearch("name")%></td>
<td valign="top"><%= rstSearch("comments")%></td>
<td valign="top"><%= rstSearch("Timestamp")%></td>
where the names in () are those of our fields
Sorry it seems so complex, but as someone else said, if you use the DB
Wizard it should do what you want
=== This is the code ========
<html>
<!-- search.asp -->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Search Guestbook</title>
</head>
<%
' Declare our variables... always good practice!
Dim strURL ' The URL of this page so the form will work
' no matter what this file is named.
Dim cnnSearch ' ADO connection
Dim rstSearch ' ADO recordset
Dim strDBPath ' path to our Access database (*.mdb) file
Dim strSQL ' The SQL Query we build on the fly
Dim strSearch ' The text being looked for
' Retrieve the URL of this page from Server Variables
strURL = Request.ServerVariables("URL")
' Set the DB Path
strDBPath = Application("guestbook_ConnectionString")
' Retrieve the term being searched for.
strSearch = Request("search")
%>
<body>
<!--#include file="_fpclass/fpdblib.inc"-->
<p>Search by name. (% returns all)</p>
<form action="<%= strURL %>" method="get">
<input name="search" value="<%= strSearch %>" />
<input type="submit" />
</form>
<% If strSearch <> "" Then
' Create an ADO Connection to connect to the database.
Set cnnSearch = Server.CreateObject("ADODB.Connection")
' Open the connection
cnnSearch.Open strDBPath
' Build our query based on the input.
strSQL = "SELECT * " _
& "FROM Results " _
& "WHERE name LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "ORDER BY name;"
' Execute our query using the connection object.
Set rstSearch = cnnSearch.Execute(strSQL)
' Display a table of the data in the recordset.
%>
<table border="1">
<tr>
<th><b>Name</b></th>
<th><b>Comments</b></th>
<th><b>Timestamp</b></th>
</tr>
<% Do While Not rstSearch.EOF %>
<tr>
<td valign="top"><%=FP_FieldVal(rstSearch,"name")%></td>
<td valign="top"><%=FP_FieldVal(rstSearch,"comments")%>
<td><%=FP_FieldVal(rstSearch,"Timestamp")%></td>
</tr>
<% rstSearch.MoveNext
Loop %>
</table>
<% ' Close our recordset and connection and dispose of the objects
rstSearch.Close
cnnSearch.Close
Set rstSearch = Nothing
Set cnnSearch = Nothing
End If %>
</body>
</html>
======================
