ASP Pages - Editing Records

Page  3  of  3

Updating the Entry in the 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 text editor and enter the following code.

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 earlier. 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.

'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 to hold the records from the database.

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

To query a database we need to use SQL (Structured Query Language). In the next line we initialize 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.

'Initialize 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 'adLockOptimistic' so we can move through the recordset. 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 values taken from the form we created earlier in this session. To get the data 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.

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

Now that the database has been updated we're 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, '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" %>

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

You now have a method of updating your existing records.

<< Back

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

asp web hosting by HyperlinkHost.com