Adding Data to an Access Database using Classic ASP
In this tutorial we are going to be adding data to the Guestbook database made in the
tutorial Part: 1 Connecting to an Access Database.
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 this tutorial we use an HTML form to take a site visitors name and comments and add these to the database. We will then use the page '' made in the first database tutorial to display the contents of the database (don't worry if you didn't do the first database tutorial, the '' and the '' page are included in the zip file for this tutorial).
Creating an HTML Page to take User Input
First we need to quickly create an HTML page with a form on it to take the input from the user.
In this page we will have two text boxes, one called '' and the other called, '',
we will then use the post method to send the page to the file, '' that we are going to be creating
later in this tutorial, which will add the user input into the database.
|
|
|
|
<html>
<head>
<title>Guestbook Form</title>
</head>
<body bgcolor="white" text="black">
<form name="form" method="post" action="add_to_guestbook.asp">
Name: <input type="text" name="name" maxlength="20">
<br>
Comments: <input type="text" name="comments" maxlength="50">
<input type="submit" name="Submit" value="Submit">
</form>
</body>
</html> |
|
|
|
|
Save the page as '' in the same folder as the Guestbook database.
Adding Data to the Guestbook Database
Now we've got the form, to input the data through, out of the way we can make the the page that does all the work, adding the data to 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.
|
|
|
|
<%
Dim adoCon
Dim rsAddComments
Dim strSQL |
|
|
|
|
Next we need to create a database connection object on the server using the ADO Database 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 '' to open the database ''.
You'll notice the ASP method '' 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. '', 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.
|
|
|
|
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("guestbook.mdb") |
|
|
|
|
Next create an ADO recordset object which will hold the records from the database and the new record to be added to the database.
|
|
|
|
Set rsAddComments = Server.CreateObject("ADODB.Recordset") |
|
|
|
|
To query a database we need to use SQL (Structured Query Language). In the next line we initialise the variable '' with an SQL query to read in the fields '' and '' form the '' table.
|
|
|
|
strSQL = "SELECT tblComments.Name, tblComments.Comments FROM tblComments;" |
|
|
|
|
Set the cursor type we are using to '' so we can move through the recrord set. The integer value for
this is 2.
|
|
|
|
rsAddComments.CursorType = 2 |
|
|
|
|
Because we are going to be saving an updated recordset back to the database we need to set the LockType of the recordset to '' so that the recordset is locked, but only when it is updated. The integer value for this lock type is 3.
|
|
|
|
rsAddComments.LockType = 3 |
|
|
|
|
Now we can open the recordset and run the SQL query on the database returning the results of the query to the recordset.
|
|
|
|
rsAddComments.Open strSQL, adoCon |
|
|
|
|
Once the recordset is open we can add a new record onto the end of the recordset. In the next line we let the recordset know we are adding a new
record to it.
Now we can add a new record to the recordset. The details taken from the form we created at the start of this tutorial are entered into there relevant
fields in the recordset. To get the data entered by the user from the form we use the '' method of the ASP '' object to request the data entered into the text boxes, '' and ''.
|
|
|
|
rsAddComments.Fields("Name") = Request.Form("name")
rsAddComments.Fields("Comments") = Request.Form("comments") |
|
|
|
|
The data has been entered into the recordset we can save the recordset to the database using the '' method of
the recordset object.
We have finished using the database in this script so we can now close the recordset and reset the server objects.
|
|
|
|
rsAddComments.Close
Set rsAddComments = Nothing
Set adoCon = Nothing |
|
|
|
|
Now that the database is updated we are going to use the '' method of the ASP response object to redirect to
the '' page we created in the first database tutorial, Connecting
to an Access Database, so we can display the updated database. Note that if you are going to use the '' method you must remember to redirect before any HTML is written.
|
|
|
|
Response.Redirect "guestbook.asp"
%> |
|
|
|
|
Now call the file '' and save it to the same directory as the database and the '' page, don't forget the '' extension.
And that's about it, you have now created a simple Guestbook for your website, to find out how to delete any of the comments from the Guestbook
database read the next tutorial on, Deleting Data from 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 '' installed on your system and if
you are using the, '', make sure the permissions are correct for the database and the directory the
database in.
Part 3: Deleting Data from an Access Database (Guestbook Pt.3) >>
Accompanying Tutorials in this Series
Now that you have completed this part you should look at the accompanying Tutorials in this series
|