The steps involved to search and modifyrecords 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 on a form
<%
if Record.EOF then
response.write "No matching records found"
else
dim mid,mname,maddress
mid=record("memberid")
mname=record("membername")
maddress=record("address")
%>
Member id
Member Name
Member Address
<%
end if
%>
Create another ASP file named update.asp that contains the following code:-
<%
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
DIM RECORD,searchid
searchid=Request.form("txtid")
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
if record.eof then
response.write "no matching record found"
else
record("membername")=request.form("txtname")
record("address")=request.form("txtaddress")
record.update
response.write "record updated"
end if
%>