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 three pages to update data in 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 update. In the second page we use
a form to display the present data held in the database and allow you to change the details. In the third page we update the database.

Creating a Page to Select the Database Entry to Update

First we need to create a page to display the contents of the database so we can select which entry that we want to update.

I'm not going to go into any detail about this page as it is almost identical to the page we created in the last database tutorial on selecting
an entry to delete. The only difference is the hyperlink created is to the form, 'update_form.asp', that we are going
to create next in this tutorial.

'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 ("<br>")
Response.Write ("<a href=""update_form.asp?ID=" & rsGuestbook("ID_no") & """>")
Response.Write (rsGuestbook("Name"))
Response.Write ("</a>")
Response.Write ("<br>")
Response.Write (rsGuestbook("Comments"))
Response.Write ("<br>")
'Move to the next record in the recordset
rsGuestbook.MoveNext

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

Entering the Data into a Form to be Updated

In this part of the tutorial we need to create a form containing the database entry that we want to update. From this form we can change the details
of the entry from the database.

The first part of this page is where we connect to the database and read the data into the recordset. We have already covered this so I shall not
go into any detail here.

<%
'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
Dim lngRecordNo 'Holds the record number to be updated

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

As you can see in the code above we haven't closed the recordset yet, so we can read in the data from the recordset into the default values of
the text boxes in the form. At the end of the page we close the recordset and reset the server objects.

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

Updating the Entry in the Guestbook Database

Now we've got the page to select the entry out of the way and the form to change the details, we can now make the page to update the selected entry
in the database.

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 rsUpdateEntry 'Holds the recordset for the record to be updated
Dim strSQL 'Holds the SQL query to
query the database
Dim lngRecordNo 'Holds the record number to be updated

Next we need to get the 'ID Number' of the entry to be updated from the database. To read in the ID number we are going
to use the 'Form' method of the ASP 'Request' object that we covered in the first database
tutorial, 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 updated
lngRecordNo = CLng(Request.Form("ID_no"))

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

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.

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.

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 updated, this way the query will only return the record to be updated to the recordset.

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

Set the cursor type we are using to 'adLockOptomistic' so we can move through the recrord set. The integer value for
this is 2.

'Set the cursor type we are using so we can navigate through the recordset
rsUpdateEntry.CursorType = 2

Because we are going to be updating 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 updated. The integer value for this lock type is 3.

'Set the lock type so that the record is locked by ADO when it is updated
rsUpdateEntry.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 update.

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

Now we can update the record in the recordset with the details taken from the form we created earlier in this tutorial. To get the data entered
by the user from the form we use the 'Form' method of the ASP 'Request' object to request
the data entered into the text boxes, 'name' and 'comments'.

'Update the record in the recordset
rsUpdateEntry.Fields("Name") = Request.Form("name")
rsUpdateEntry.Fields("Comments") = Request.Form("comments")

The data has been entered into the recordset so we can save the recordset to the database using the 'Update' method
of the recordset object.

'Write the updated recordset to the database
rsUpdateEntry.Update

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

Now that the database entry has been updated 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, 'update_select.asp' so that another entry can
be selected to be updated 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 update select page in case another record needs deleting
Response.Redirect "update_select.asp"
%>

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

And that's about it, you have now created a way to update entries in the 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.

Accompanying Tutorials in this Series

Now that you have completed this part you should look at the accompanying Tutorials in this series