Now let's look at the steps to allow a record to be removed from a Recordset.

Similarly, as when editing a record, we let the user choose which record to delete, except in this case, we need not provide an entry form.

To confirm that the selection is correct, though, we render the record's data and provide a form with just Delete and Cancel buttons.

As in the AddNew and Update methods, the Delete button ( which is really a submit button ), actually posts back to the same page, but here we simply call ADO's Delete method

rsGuests.Delete

Deleting an Existing Record Run Sample | View Source

and then call the self.location.replace method, in effect to refresh the underlying data set as the page reloads.

Clicking the Cancel button also calls the location.replace method that simply reloads the page with no changes.

The Delete method removes the current record or a group of records.

recordset.Delete affectRecords

affectRecords is an optional affectEnum value that determines how many records the Delete method will involve, and can be one of the following constants.

Using the Delete method marks the current record or a group of records in a Recordset object for deletion. If the Recordset object does not allow record deletion, an error occurs.

In immediate update mode, deletions occur in the database as soon as the method is called. Otherwise, the records are marked for deletion from the cache and the actual deletion happens when you call the UpdateBatch method. You can use the Filter property to view the deleted records.

Retrieving field values from the deleted record generates an error. After deleting the current record, the deleted record remains current until you move to a different record. Once you move away from the deleted record, it is no longer accessible.

If the attempt to delete records fails because of a conflict with the underlying data ( for example, a record has already been deleted by another user ), the provider returns warnings to the Errors collection but does not halt program execution. A run-time error occurs only if there are conflicts on ALL the requested records.

If you nest deletions in a transaction, you can recover deleted records with the RollbackTrans method. If you are in batch update mode, you can cancel a pending deletion or group of pending deletions with the CancelBatch method.

Removing an Existing Record using SQL DELETE Run Sample | View Source

There is one other way to delete an existing record using the SQL DELETE statement.

SQL DELETE creates a delete query that removes records from one or more tables listed in the FROM clause that satisfy the WHERE clause.

DELETE FROM tableName WHERE criteria

The DELETE statement has these parts:

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

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

'identify the record
msgID=Request.Form( "msgID" )

'define the delete query
strDelete="DELETE FROM messages WHERE MessageID="&msgID&""

'execute the delete
cnGuests.Execute ( strDelete )

DELETE is especially useful for removing multiple records or when the records to remove are in multiple tables.

A delete query deletes entire records, not just data in specific fields. If you want to delete values in a specific field, create an update query that changes the values to Null.

After you remove records using a delete query, you cannot undo the operation. If you want to know which records are to be deleted, first examine the results of a select query that uses the same criteria, and then run the delete query.

Which to use and when? For removing single records, you can use either ADO Delete or SQL DELETE, but SQL DELETE has the added functionality for use in delete queries involving multiple records.

Well, that's about it for this session. Hopefully at least, you've gained insight on the different ways to add, edit or delete records from a database.

When using SQL UPDATE and DELETE, maintain backup copies of your data at all times. If you inadvertently update or delete the wrong records, you cannot undo the operation and the only way to retrieve them is from your backup copies.

Of course, the samples provided can further be improved, again on performance grounds. Because here we use separate pages to clearly show each method, we need to redundantly open and close the database.

Ideally, table update methods are grouped into one page, which can have Add, Edit and Delete buttons, as well as provide for search, paging and/or record navigation. This way, opening and closing the connection is done only once.

Alternatively, for record selection, we could have used the AbsolutePosition property to move to the chosen record based on its ordinal position in the Recordset. Unfortunately, not all providers support the functionality for this property to be available.

And before you go, here's something you might want to think about.

One of our editors here, Bob Dombrosky, has written an article on pro-active error-handling, that describes a scheme for notifying someone via e-mail, or even thru a cell phone, when some error pops-up in an application.

With a little imagination, you can adopt that concept to notify you whenever any of the tables in your databases are updated. Now that would be really neat.

Next in this series:

• Part V: Navigating and Paging thru the Recordset
  • ADO AbsoluePosition, RecordCount properties
  • ADO Move method
  • ADO MoveFirst, MoveLast, MoveNext and MovePrevious methods
  • ADO CacheSize property
• Dividing a Recordset into pages
  • ADO PageSize, AbsolutePage, and PageCount properties

If you haven't done so, I suggest you start with:

• ADO Primer: Part I
  • Basic database concepts
  • Connecting to a database
  • The Recordset object

ADO Primer: Navigating and Paging thru the Recordset