ASP Pages - Deleting Records

Page 2 of  2

Deleting Data from a Database

This page contains no HTML so we can start writing the asp straight away, still don't forget the server side script tags, <% .... %>.

First we need to dimension the variables used in the script, so open your text editor and enter the following code.

Next we need to get the 'ID Number' of the entry to be deleted from the database. This ID number was passed to the page we are writing by the script we wrote at the beginning of the session, by adding the '?ID= Entry ID Number' at the end of the URL.

To read in the ID number we are going to use the 'QueryString' method of the ASP 'Request' object. We're also going to use the 'CLng' VBScript function to convert the ID number to the data type, 'Long Integer'.

'Read in the record number to be deleted lngRecordNo = CLng(Request.QueryString("ID"))

Next we need to create a database connection object on the server using the ADO Database connection object.

'Create an ADO connection object Set adoCon = Server.CreateObject("ADODB.Connection")

Now we need to open a connection to the database. 

'Set an active connection to the Connection object using a DSN-less connection adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("guestbook.mdb") 'Set an active connection to the Connection object using DSN connection adoCon.Open "DSN=guestbook"

Next create an ADO recordset object which will hold the records from the database.

'Create an ADO recordset object Set rsDeleteEntry = Server.CreateObject("ADODB.Recordset")

To query a database we need to use SQL (Structured Query Language). In the next line we initialise the variable 'strSQL' with an SQL query to read in all the fields from the 'tblComments' table where the 'ID_no' = the entry to be deleted, this way the query will only return one record to the recordset.

'Initialise the strSQL variable with an SQL statement to query the database strSQL = "SELECT tblComments.* FROM tblComments WHERE ID_no=" & lngRecordNo

Because we are going to be deleting the record held in the recordset we need to set the LockType of the recordset to 'adoLockOptimistic' so that the recordset is locked when it is deleted. The integer value for this lock type is 3.

'Set the lock type so that the record is locked by ADO when it is deleted rsDeleteEntry.LockType = 3

Now we can open the recordset and run the SQL query on the database to get the database entry that we want to delete.

'Open the recordset with the SQL query rsDeleteEntry.Open strSQL, adoCon

Once the recordset is open and contains the entry we want to delete, we can delete the entry from the database by using the 'Delete' method of the 'Recordset' object.

'Delete the record from the database rsDeleteEntry.Delete

We have finished using the database in this script so we can now close the recordset and reset the server objects.

'Reset server objects rsDeleteEntry.Close Set rsDeleteEntry = Nothing Set adoCon = Nothing

Now that the database record has been deleted we are going to use the 'Redirect' method of the ASP response object to redirect back to the page we wrote at the beginning of this session, 'delete_select.asp' so that another entry can be selected to be deleted from the database. Note that if you are going to use the 'Response.Redirect' method you must remember to redirect before any HTML is written.

'Return to the delete select page in case another record needs deleting Response.Redirect "delete_select.asp" %>

Name the file 'delete_entry.asp' and save it to the same directory as the Guestbook database. Don't forget the '.asp' extension.

<< Back

Main Page  |  Hosting Plans  |  E-Commerce  |  Sign Up Now!  |  Site Map

asp web hosting by HyperlinkHost.com