Now let's look at the steps to allow the user to modify an existing record.

These are basically the same as when adding a new record, except in this case, you need some way to let the user choose which record to update.

You can either:

• provide an HTML Select element populated with values from a given field, as described in Part II: Selecting information from a database
• use a modified version of the search methods described in Part III: Letting users search for information
• or use a table-view with a hyperlink for each record using one of the fields' values, as shown below:

<a href="edit.asp?recno=<%=recno%>"><%= rsGuests( "MessageFrom" )%></a>

Editing an Existing Record Run Sample | View Source

Now, let's see how to get to the selected record. There may be other ways, but here's how it's done in the samples.

As shown above, each of the hyperlinks' href attribute passes along ( in its query string ) the value of an incremental record counter ( recno ), which is dynamically assigned as the table-view is built.

As the page loads, the script checks whether the Request.Querystring is not empty, which is the case when a selection has been made.

<% if not isEmpty( Request.QueryString ) then recno=Request.QueryString( "recno" ) %>

If so ( actually we test if recno is not empty ), the program flows to the snippet below, which moves the record pointer a number of given rows.

<% 'move pointer to selected record
recno=Request.Querystring( "recno" )-1
set rsGuests=Session( "rsGuests" )
rsGuests.MoveFirst
rsGuests.Move recno

Any way you choose, the next step is to create a form consisting of the set of fields that needs to be updated.

We use the same form as in the AddNew method, except that the form fields' initial values are populated with the current record's fields data. For example:

<input maxlength=254 name='msgFrom' value="<%= rsGuests( "MessageFrom" ) %>">

In the same way, when the form is submitted, the script gathers the form's data set and saves the changes to the current record using ADO's Update method.

rsGuests.Update fldsArray, valsArray

Editing an Existing Record using SQL UPDATE Run Sample | View Source

There is one other way to edit an existing record using the SQL UPDATE statement.

SQL UPDATE creates an update query that changes the values of given fields in a specified table based on specified criteria.

In this case, you specify the name of each of the fields that a value is to be assigned to, and a value for that field.

UPDATE tableName SET fieldName=newValue WHERE criteria;

The UPDATE statement has these parts:

WARNING: If the WHERE clause is not included, SQL applies the UPDATE to ALL records.

In the same manner as INSERT INTO, SQL UPDATE is applied as a parameter of the Execute method of the Connection object, in contrast with the ADO Update method, which is applied to a Recordset.

' define the update query
strUpdate="UPDATE messages set _
   MessageFrom='"&msgFrom&"', _
   Email='"&msgEmail&"', _
   MessageSubject='"&msgSubject&"', _
   MessageBody='"&msgBody&"' _
   WHERE MessageID="&msgID&""

' execute the update
cnGuests.Execute ( strUpdate )

UPDATE is especially useful for changing multiple records or when the records to change are in multiple tables. You can update one or more fields at the same time.

UPDATE does not generate a result set. After you update records using an update query, you cannot undo the operation.

You may want to check first which records are going to be updated by examining the results of a select query that uses the same criteria, and then run the update query.

Which to use and when? For updating single records, you can use either ADO Update or SQL UPDATE, but SQL UPDATE has the added functionality for use in update queries involving multiple records.