|
OK - searchvalue is what I have passed from the previous form. I think i've balls'd up my SQL statement? As that appears in text on this page -so if I submit value "jones" I get the state\ent printed as....."SELECT * FROM data WHERE detail LIKE 'jones%'"
The table is called "data" and field is called "name". My primarky Key is callled ID_Num.
Any ideas would be ace ........... its been like 5 years since i've done ASP ol>
<%@ Language=vbscript %>
<% option explicit %>
<%
Dim MyConn, SQL, oRs, srchval, srchval2, strConn, javalink, rColour, srchcriteria, Counter
srchval = Replace(Request.Form("searchvalue"), "'", "''")
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Provider="Microsoft.Jet.OLEDB.4.0"
'local drive contains a version of the database
strConn= Server.MapPath("fileplan.mdb")
MyConn.Open strConn
'Get Surname and Christain Name(s) (optional)
SQL = "SELECT * FROM data WHERE name LIKE '"&srchval&"%'"
Response.Write SQL
Response.End
set oRs=MyConn.Execute(SQL)
%>
<html>
<head>
<title>Record Serach Results</title>
<SCRIPT language="JavaScript">
<!--
function openwin(thelink)
{
window.open(thelink,'Details','width=650,height=500,toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbars=yes,copyhistory=yes,re sizable=yes');
}
//-->
</SCRIPT>
<%
if oRs.EOF <> true then Counter = 0 'Set the number of records read to zeo %> <H2>Number of records found <table border="0" cellpadding="5"> <tr><th> <p>ID_No: <th> <p>Name <th> <p>Address <th> <p>Birthday <th> <p>Notes <p> <% rColour = "#D6D6D6" 'set the first line to grey or gray if oRs.EOF <> true then 'and srchval <> "" then ' if it not EOF and the two boxes not empty then read and write do javalink = "javascript:openwin('details.asp?IDNum="& oRs("ID_No") &"')" Response.Write "<tr bgcolor='" & rColour &"'><td><p align=center><a href='#' onclick="&javalink&">" _ & oRs("ID_No") & "</a>" Response.Write "<td><p align=center>" & oRs("name") _ & "<td><p align=center>" & oRs("address") _ & "<td><p align=center>" & oRs("birthday") _ & "<td><p align=center>" & oRs("notes") oRs.MoveNext if (rColour="#D6D6D6") then 'first line gray 84 other rColour="#FFFFFF" 'lines white else rColour="#D6D6D6" end if 'end if rColour = Counter = Counter + 1 'count the number of records loop until oRs.EOF 'loop until end of file else
end if 'end if oRs.EOF <> true
else
Response.Write "NO RECORDS FOUND CONTAINING THIS SEARCH ITEM"
end if 'end if of writing files to table
Response.Write"<tr><p></p>" ' write up the number of records found
Response.Write"<H2>"&Counter&"<H2>" ' with the search item
%>
</table>
<FORM><INPUT TYPE="button" VALUE="Back" onClick="history.go(-1);return true;"> </FORM>
</html>
<%
'Tidy things up, free up memory by closing the connection object
oRs.Close
MyConn.Close
Set oRs = Nothing
Set MyConn = Nothing
%>
|