Add a record
 Up
You have already learned how to display the contents offan online Access database using ASP. This page explains how to add records to your online database - later pages explain how to modify existing records and how to delete selected records. Each of these operations will require two pages:
bulletthe first page will display a form allowing the user to make the necessary selections or enter the necessary information. This information will be sent with the request for the second page
bulletthe ASP will carry out its magic as the html instructions for making the second page are assembled by the server. Not once again that the ASP scripts are run BEFORE the second page is sent to the browser client by the server - no ASP script is sent WITH the second page.

Reminder:The instructions below assume that you have an 'include file' called dbconnect .asp that contains the information needed for a connection to the database to be established, and also that the database contains a table called 'main' with fields named as described previously (text1, text2 etc).

Add a record

Adding a record using ASP will require two pages:

bulletthe first page (an html page, not an ASP page containing VB script) will use a form to collect information and pass it to the second 'page'
bulletthe second page (not really a page, just a VB script) will take the information sent by the first page and add it to the database. Nobody will see this second page for as soon as the script has run it will jump (redirect) back to the viewselected.asp page.

Make a new page and save it immediately at the root level with the name addrecordform.htm. Switch to HTML view and replace any existing code with the code below (don't forget to paste the copied code first into NotePad, copying and pasting again from Notepad in order to lose any unwanted formatting code):

<html><head><title>Add a Record</title></head><body>
<h1>Add a Record</h1>
<form name="form" method="post" action="addrecord.asp">
<pre>
Family Name: <input type="text" name="FamilyName" size="20">
 First Name: <input type="text" name="FirstName" size="20">
      Email: <input type="text" name="Email" size="20">
      Phone: <input type="text" name="Phone" size="20">
<input type="submit" name="Submit" value="Submit"></pre>
</form>
</body></html>

The above code should be easy to understand - it makes a form containing 4 text boxes and a 'Submit' button. The form will post (send) its information to a second page called addrecord.asp (note the extension). The <pre> tag above tells the computer to use the 'formatted' paragraph style which makes it easier to line up the text boxes neatly. Switching to page view, your page should look something like this:

                    

You could just as easily have made the form in page view (Insert>Form>One-line text box etc), not forgetting to set names of each field appropriately (FirstName etc) and to set the form properties to 'Send to Other' with options set as 'addrecord.asp' and 'Post'.

Now it's time to make the ASP page that will accept the information sent by the form page and then add it to the database. Make a new page and save it immediately at the root level with the name addrecord.asp. Switch to HTML view and replace any existing code with the code below:

<!--#include file="dbconnect.asp"-->
<%
Dim objRS 'Holds the recordset
Dim strSQL 'Holds the SQL query for the database

'Create a recordset object
Set objRS = Server.CreateObject("ADODB.Recordset")

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

'copy values from the table into the recordset and apply a lock to allow
'include a type 3 lock that allows changes to be saved back to the database
objRS.Open strSQL, objConn, ,3 
'Tell the recordset we are adding a new record to it
objRS.AddNew

'Add a new record to the recordset
objRS.Fields("text1") = Request.Form("FamilyName")
objRS.Fields("text1") = Request.Form("FirstName")
objRS.Fields("text1") = Request.Form("Email")
objRS.Fields("text1") = Request.Form("Phone")

'Write the updated recordset to the database
objRS.Update

'Reset server objects
objRS.Close
Set objRS = Nothing
objConn.close
Set objConn = Nothing

'Redirect to the viewselected.asp page
Response.Redirect "viewselected.asp"
%>

The most important lines in the above code are the ones in bold here.

bullet

The first bold line sets up the SQL query that contains the * wildcard and will therefore extract all the fields from the database (the database actually contains additional fields such as an 'ID' field).

bullet

The second bold line fills the recordset with the data matching the SQL query. Don't forget that a recordset is simply a set or group of records that have been extracted (or, more accurately, copied) from the database. The number at the end of this line is required since we want to modify the database (by adding an extra record) and we are allowed to do this only if the database is temporarily 'locked' to stop other people changing it at the same time as us (this would confuse the database). The number 3 in this line tells the computer to use a type 3 or 'optimistic' lock - it's the type that we will use every time we want to change a database in any way.

Now you're ready for the 'delete a record' lesson.
 

Previous Up Next