The steps involved to search and display records from a database are as follows:-
Create a form that contains a search option
Create an ASP file named search.asp that contains the following code:-
Step 1 : Open a connection to the database
DIM CONNECT,SQL
SET CONNECT=SERVER.CreateObject("ADODB.CONNECTION")
STR="PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & SERVER.MapPath("customerdata.MDB") (NOTE: customerdata.mdb is the name of the access database)
CONNECT.Open STR
Step 2: create a recordset and retrieve the search textbox value
DIM RECORD,searchid
searchid=Request.form("txtsearch")
searchid=" ' " & searchid & " ' " (NOTE: if idno is a string,then enclose it in quotes)
SET RECORD=SERVER.CreateObject("ADODB.RECORDSET")
sqlsearch="select * from member where memberid=" & searchid
RECORD.Open sqlsearch,CONNECT,2,3
%>
Step 3:- Retrieving records from the table and displaying them
<%
if Record.EOF then
response.write "No matching records found"
else
WHILE NOT RECORD.EOF
Response.Write RECORD("MEMBERID") & "
"
Response.Write RECORD("MEMBERNAME") & "
"
Response.Write RECORD("ADDRESS") & "
"
RECORD.MoveNext
WEND
end if
%>