A Tool for Webmasters: Building an Email Notification System

By Jeff Knight

This documentation assumes you have access to a server with IIS and SMTP installed and are able to read and write ASP to the server's web directory. The directory to which the ASP code is saved must allow scripts to run. You should have a good handle on code-level HTML and a general understanding of basic ASP.

- continued -

Imagine that you want to create a web application that allows users to submit suggestions and questions; you need the input to be saved to a database. Each time a new question or suggestion is entered, you'd like to get an automatic email containing a link to a web page that lists only the information that was most recently entered into the database. This article demonstrates how to build such a system!

How to Create a Basic Database Entry Form
First you need to create your database. Open Access, select "new database-->blank database." Name your database name.mdb and save it under c:\data\intranet\name.mdb, where the C-drive is the root of whatever server you are working on. Under Tables create a new table ("design view") and under Field type inputname. Hit save and name the table name. You will get some prompt asking you if you want a primary key; say "yes." Close Access. Next, we need to create a simple form for a user to type in their name and submit it. Open your HTML editor and enter the following:

<html> <head><title>Basic Form</title></head> <body><br> <p ALIGN="center"><form ACTION="formaction.asp" METHOD="post"> Please enter your last name: <br> <input TYPE="text" NAME="name"> <input TYPE="submit" VALUE="enter!"> </form> </body> </html>

Save this page as BasicForm.html to a folder on your server (i.e. c:\inetpub\wwwroot\myStuff\).

Confirmation: How to input the contents of the Form into a Database
Next we need to create a confirmation page. Open your HTML editor and enter the following code:

<html> <head><title>Form Action</title></head> <body> <p ALIGN="center"><h2>Thank you for submitting your data.</h2></b> <h3>An email is being generated and send to you.</h3> <a HREF="basicform.html">back</a></p> <% Dim conn, rs Set conn=Server.CreateObject("ADODB.Connection") Set rs=Server.CreateObject("ADODB.Recordset") conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\data\intranet\name.mdb" rs.Open "name", conn, 2, 3 rs.addnew rs("inputname")=Request.Form("name") rs.Update rs.MoveLast strId=rs("id") rs.Close conn.Close Set rs=nothing Set conn=nothing Response.Write "ID#: " & strId %> </body> </html>

Save this page as FormAction.asp

The purpose of this page is to harvest the information that was typed into the textbox in out last page, open the connection to the name database, insert the data into the database, and, finally, retrieve the ID number that Access assigned to the data. This last part is especially important, since it gives us a way to identify this data apart from everything else in the database.

Test to make sure your code is working properly. Open your web browser and enter the path to basicform.html.(i.e. http://myserver/code/basicform.html) Complete the form and hit "enter!" Now open your name database and check to see if the data was submitted correctly. You should see whatever name you entered into the textbox under inputname. You will also note on the formaction.asp page an ID number posted which corresponds to the ID in the database. We'll come back to this shortly.

Going through the code in formaction.asp step by step, we first declare our variants using the Dim command. This is not strictly necessary, but will be required if you choose to use the Option Explicit method. (To learn more about Option Explicit, be sure to read: Using Option Explicit.)

Next, we specify that conn is connection, and rs is the recordset in the connection. In particular, the conn is Driver ... This connection may also be set up using the ODBC connection, in which case conn.open DSN="ODBC_NAME_OF_THE_DATABASE_CONNECTION. A third approach is to use an include file containing the Driver string. (To learn more about include files, be sure to read: The Low-down on Includes.)

rs.Open specifies that table we want opened (name), includes the connection we just set (conn) and variables (0,1,2,3, or 4) indicating the nature of the connection. The next couple lines deal with getting the ID. rs.Update updates the database and, finally, we close the connection and close the file out.

rs.addnew must be used if information is going to be added to the database. If all you need to do is get data, this won't be necessary. The next line rs("inputname")=Request.Form("name") sets the column inputname in the recordset of our database equal to the value of the textbox from the page basicform.html.

In Part 2, we will look at how to retrieve the ID number of the data that was sent to the database, and, ultimately, how to send the confirmation email.

Read Part 2!