Edit a record

Don't do this lesson until you have first learned how to add a record using ASP and to delete a record using ASP.

We have already modified the viewselected.asp page so that it includes an 'Edit' hyperlink for each record that jumps to a page called updateform.asp and includes in the querystring the ID value of the chosen record. We'll make the updateform.asp page now so that the user can enter the new values to be saved into this record, and then we'll need to make another page (which the user will never see) to actually modify the record in the database.

Create a new page and save it immediately with the name editform.asp. Switch to HTML view and replace any existing code with the following:

<!--#include file="dbconnect.asp"-->
<%
Dim objRS 'Holds the recordset for the record to be updated
Dim strSQL 'Holds the SQL query for the database
Dim lngID 'Holds the record number to be updated

'Get ID from querystring and convert it into a long integer
lngID = CLng(Request.QueryString("ID"))

Set objRS = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT * FROM main WHERE id =" & lngID

objRS.Open strSQL, objConn     'Open the recordset with the SQL query
%>
<html><head><title>Update Form</title></head>
<h1>Update Record</h1>
<form name="form" method="post" action="updaterecord.asp">
Last Name:
<input type="text" name="FamilyName" value="<%=objRS("text1")%>">
First Name:
<input type="text" name="FirstName" value="<%=objRS("text2")%>">
<br>
Email:
<input type="text" name="Email" value="<%=objRS("text3")%>">
Phone:
<input type="text" name="Phone" value="<%=objRS("text4")%>">
<input type="hidden" name="ID" value="<%Response.write objRS("id") %>">
<input type="submit" name="Submit" value="Submit">
</form>

</html>
<%
'Reset server objects
objRS.Close
Set objRS = Nothing
objConn.close
Set objConn = Nothing
%>

Nothing too difficult here - in fact this code is very similar to the addrecordform.htm code except that we have extra code here to make the existing values appear in the text boxes and we have lines of code that get the id value that was passed through the querystring, convert it into a number format (a long integer) and then pass it on to the next page in a 'hidden field' within the form.

The SQL query extracts from the table only the one record that has an ID value matching the one that was passed in the querystring. The html code creates the four text boxes and fills them with the values found in the current record of the recordset (which only contains one record since only one record matched the SQL query). The form also includes a 'hidden field' containing the ID number - we don't want the user to be able to change this number but we do need to pass this number along to the next page along with the other information from the form. The form will post its data to a page called updaterecord.asp, which we will now create.

Create a new page and save it immediately with the name updaterecord.asp. Switch to HTML view and replace any existing code with the following:

<!--#include file="dbconnect.asp"-->
<%
Dim objRS 'Holds the recordset for the record to be updated
Dim strSQL 'Holds the SQL query for the database
Dim lngID 'Holds the record number to be updated

'Read in the record number to be updated
lngID = CLng(Request.Form("ID"))

Set objRS = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT * FROM main WHERE id =" & lngID

'Open the Results table using the SQL query held in the strSQL variable
objRS.Open strSQL, objConn,,3 'uses type 3 or 'optimistic' lock

'Update the record in the recordset
objRS("text1") = Request.Form("FamilyName")
objRS("text2") = Request.Form("FirstName")
objRS("text3") = Request.Form("Email")
objRS("text4") = Request.Form("Phone")
objRS.Update 'Write the updated recordset to the database

objRS.Close
Set objRS = Nothing
objConn.close
Set objConn = Nothing

'Return to the viewselected.asp page
Response.Redirect "viewselected.asp"
%>

Again the SQL query makes a recordset consisting of a single record, since only one record can match the ID value that was passed from the hidden field of the previous page. The recordset is opened with a lock so that changes to the recordset can be saved back to the database. The fields in the current record are modified to match the values that were sent from the form on the previous page and the recordset (consisting of a single record) is then copied back to the database with the statement objRS.Update.

You have now mastered the basic operations that can be applied to databases and you now have the potential to build more complex databases and to interrogate them with more sophisticated SQL queries. To go further, I recommend you buy Sams Teach Yourself Active Server Pages 3.0 for about 40 Euros from www.amazon.co.uk . Don't work too hard, though!