To read the article online, visit http://www.4GuysFromRolla.com/webtech/022700-1.2.shtml

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


  • Article Information
    Article Title: A Tool for Webmasters: Building an Email Notification System, Part 2
    Article Author: Jeff Knight
    Published Date: Sunday, February 27, 2000
    Article URL: http://www.4GuysFromRolla.com/webtech/022700-1.2.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers