News:

GinGly.com - Used by 85,000 Members - SMS Backed up 7,35,000 - Contacts Stored  28,850 !!

Main Menu

Deleting Data From An Access Database Using ASP

Started by sukishan, Aug 20, 2009, 02:49 PM

Previous topic - Next topic
Go Down Pages1

sukishan

Aug 20, 2009, 02:49 PM
To make the Microsoft Access database tutorials on Connecting, Adding, Deleting, and Updating, more interesting we are going to use these tutorials to make a simple Guestbook.

In the first database tutorial, Part: 1 Connecting to an Access Database, we learned how to connect to a database and display the contents of a database table in a web page.

In the second database tutorial, Part 2: Adding Data to an Access Database, we learned how to add data to the database created in the first database tutorial and then use the page 'guestbook.asp' made in the first database tutorial to display the updated contents of the database.

In this tutorial we are going to create two pages to delete data from the 'Guestbook' database made in the first database tutorial. The first page is used to display the contents of the database so you can select which entry you want deleted and the second page is where all the work is done to delete the entry from the database.



Creating a Page to Select the Database Entry to Delete
First we need to create a page to display the contents of the database so we can select which entry that we want to delete.

I'm not going to go into two much detail on this page as it is almost identical to the page 'guestbook.asp' we created in the first database tutorial.

The only difference is that we are selecting all fields from the table 'tblComments' in the Guestbook database so we are using the SQL query with the wild card, 'tblComments.*' to get all the fields from the table.

The other difference is when we are displaying the contents of the database in the web page using the 'Response.Write' method we are now creating a hyperlink to the 'delete_entry.asp' page which we will be creating later in this tutorial to delete the entry.

   
<html>
<head>
<title>Delete Entry Select</title>
</head>
<body bgcolor="white" text="black">
<%
'Dimension variables
Dim adoCon          'Holds the Database Connection Object
Dim rsGuestbook     'Holds the recordset for the records in the database
Dim strSQL          'Holds the SQL query for the database
           'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")

'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"

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

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT tblComments.* FROM tblComments;"

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

'Loop through the recordset
Do While not rsGuestbook.EOF
     'Write the HTML to display the current record in the recordset
     Response.Write ("
")
     Response.Write ("<a href=""delete_entry.asp?ID=" & rsGuestbook("ID_no") & """>")
     Response.Write (rsGuestbook("Name"))
     Response.Write ("</a>")
     Response.Write ("
")
     Response.Write (rsGuestbook("Comments"))
     Response.Write ("
")

     'Move to the next record in the recordset
     rsGuestbook.MoveNext
Loop

'Reset server objects
rsGuestbook.Close
Set rsGuestbook = Nothing
Set adoCon = Nothing
%>
</body>
</html>   
   

Save this page as 'delete_select.asp' in the same folder as the Guestbook database.


Deleting Data from the Guestbook Database
Now we've got the page to select the entry out of the way, we can now make the page to delete the selected entry from the 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 favourite text editor and enter the following code.

   
<% 'Dimension variables
Dim adoCon          'Holds the Database Connection Object
Dim rsDeleteEntry   'Holds the recordset for the record to be deleted
Dim strSQL          'Holds the SQL query to query the database
Dim lngRecordNo     'Holds the record number to be deleted 
   

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 tutorial, 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 are 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. There are a couple of ways of doing this either by using a system DSN or a DSN-less connection. First I am going to show you how to make a DSN-less connection as this is faster and simpler to set up than a DSN connection.

To create a DSN-less connection to an Access database we need tell the connection object we created above to open the database by telling the connection object to use the 'Microsoft Access Driver' to open the database 'guestbook.mdb'.

You'll notice the ASP method 'Server.MapPath' in font of the name of the database. This is used as we need to get the physical path to the database. Server.MapPath returns the physical path to the script, e.g. 'c:\website\', as long as the database is in the same folder as the script it now has the physical path to the database and the database name.

   
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("guestbook.mdb") 
   

If on the other hand you want to use a slower DSN connection to the database then you will need to replace the line above with the one below. Also if you don't know how to setup a system DSN you will need to read my tutorial on, Setting up a System DSN.

   
'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 and the new record to be added to 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 entry 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 tutorial, '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"
%>   
   

Now call the file 'delete_entry.asp' and save it to the same directory as the Guestbook database and the 'delete_select.asp' page, don't forget the '.asp' extension.

And that's about it, you have now created a way to delete entries from a database, to find out how to change entries in the Guestbook database read the next tutorial on, Updating Data in an Access Database.

If you find that you are getting errors connecting to the database then please read through the Access Database Errors FAQ's, practically make sure you have the correct 'ODBC Drivers' installed on your system and if you are using the, 'NTFS file system', make sure the permissions are correct for the database and the directory the database in.
A good beginning makes a good ending
Go Up Pages1