A Tool for Webmasters: Building an Email Notification System, Part 2
By Jeff Knight
Read Part 1
In
Part 1 we discussed how to create the initial
database and form entry page. In this part, we will continue the discussion.
How to Retrieve the ID Number of the Data that was sent to the Database
This is accomplished with the lines:
rs.MoveLast
strId=rs("id")
|
The code is very simple: rs.MoveLast moves the database pointer to the last
entry (i.e. the data that was most recently entered), and strId=rs("id") gets it
and sets it equal to the variable strID. Make sure you place this code
immediately after rs.Update but before rs.Close.
How to generate an autoemail that sends a URL of the Form
The following is the code used to create an AutoEmail off the server using SMTP:
<%
Set objCDOMail = Server.CreateObject("CDONTS.NewMail")
objCDOMail.From = "EmailMail"
'objCDOMail.To = strEmail
objCDOMail.To = "me@home.com"
objCDOMail.Subject = strSubject
strSubject="Here is the subject"
strBody = "Here is some text in the body"
strBody = strBody & Chr(13) & "Now here's the link:" & Chr(13)
strBody= strBody & "http://investments/jknight/nameconfirm.asp?strId=" & strID
objCDOMail.Body = strBody
objCDOMail.Send
Set objCDOMail = Nothing
%>
|
Append this code at the end of your page FormAction.asp
Together, the complete FormAction.asp file should look like this:
<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 sent to you.</h3>
<a HREF="basicform.html">back</a></p>
<%
Dim conn, rsName
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
%>
<%
'THE AUTOEMAIL-----------------------
Set objCDOMail = Server.CreateObject("CDONTS.NewMail")
objCDOMail.From = "AutoEmail"
'objCDOMail.To = strEmail
objCDOMail.To = "youremail@home.com"
objCDOMail.Subject = strSubject
strBody = "Here is some text in the body"
strBody = strBody & Chr(13) & "Now here's the link:" & Chr(13)
strBody= strBody & "http://myserver/nameconfirm.asp?strId=" & strID
objCDOMail.Body = strBody
objCDOMail.Send
Set objCDOMail = Nothing
%>
</body>
</html>
|
A page containing this code will automatically generate an Email. The important
section of this code is the URL: we append the variant strID to
*.asp?strID=
and, since strID is the value of the row of data that was just entered in the
database, this URL will allow us to pass a key to the row of the database in the
URL itself. The next step is to create a page that retrieves this value and uses
it to access the appropriate record(s) in the database.
How to create a page that gets the name/value pair from the URL
In order to get the value of *.asp?name=value from the URL, we have to use the
Request.QueryString collection. The code in the following page accomplishes this:
<html><head><title>nameconfirm.asp</title></head>
<%
strId=Request.QueryString("strId")
Set conn=Server.CreateObject("ADODB.Connection")
Set rs=Server.CreateObject("ADODB.Recordset")
conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\data\database\name.mdb"
strSQL="SELECT * from name where id=" & strId
rs.Open strSQL, conn, 2,3
Response.Write "You requested ID#: " & strID & "<BR>"
Response.Write "the name for ID# " & strID & " is " & rs("inputname")
%>
</body>
</html>
|
Save this file as nameconfirm.asp.
What makes the nameconfirm.asp page display just the value for the ID appended
to the URL is the use of the ID number as a variable in the SQL statement that
creates the recordset (conn.Open takes care of opening the database):
strSQL="SELECT * from name where id=" & strId
|
Only those records will be opened which correspond to strID.
Read Part 1
