| |
|
|
|
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<!-- Copyright (c) 2002 AnyKey Solutions Ltd. All rights reserved. -->
<html>
<head>
<meta http-equiv="author" content="Timothy McKay">
<title>Newsletter Signup ASP using ADO Example</title>
</head>
<style>
<!-- .normal {
font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;
font-size: 12px;
text-decoration : none;
color: #000066;
} .error {
font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;
font-size: 12px;
text-decoration : none;
color: #dd0000;
} .header {
font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;
font-size: 24px;
text-decoration : bold;
color: #000066;
}
-->
</style>
<body text="#c0c0c0" bgcolor="#0080ff">
<p class=header>Newsletter Signup
<p class=normal>To signup for our newsletter, enter your name
below and click the button.<br><br>
If your email address already exists, you will be removed from
the distribution list.
<form action="signup.asp" method="POST">
<b>Your Email Address:</b>
<input type="text" size="35" name="emailaddress"><br><br>
<input type="submit" name="submit"
value="Subscribe/Unsubscribe">
</form>
<p class=normal>
<b>NOTE : </b><br><br>
<i>Your email address is a sacred thing. We will not abuse
your email address by giving it to anybody for any reason.</i>
</body>
</html>
|
|
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<!-- Copyright (c) 2002 AnyKey Solutions Ltd. All rights reserved. -->
<html>
<head>
<meta http-equiv="author" content="Timothy McKay">
<title>Newsletter Signup ASP using ADO Example</title>
</head>
<style>
<!-- .normal {
font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;
font-size: 12px;
text-decoration : none;
color: #000066;
} .error {
font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;
font-size: 12px;
text-decoration : none;
color: #dd0000;
} .header {
font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;
font-size: 24px;
text-decoration : bold;
color: #000066;
}
-->
</style>
<body text="#c0c0c0">
<%
DIM Conn, Rs
Set Conn = CreateObject("ADODB.Connection")
Set Rs = CreateObject("ADODB.Recordset")
|
Up to this point, there haven't been any big surprises. These statements might need
a little explaining though.
As you can see, I defined two variables, Conn and Rs. These variables will
become references to two objects; the ADDODB.Connnection and ADODB.Recordset objects,
specifically.
The ADODB.Connection object is the heartbeat that you have to the ODBC DataSource on the
server. The connection handles the authentication to the database, caching, and all
sorts of nifty things we don't need to worry about today.
The ADODB.Recordset object is where things liven up a bit. The Recordset object supports
some basic data handling methods and properties, including :
- Open Method Can be used to open a table, or to execute SQL statements
of your choosing.
- Close Method This will do pretty much that. One trait of non-row-returning SQL statements, like
INSERT, DELETE, or
UPDATE is that they do not return a result set,
and therefore you do not call the Close Method, or you will get an error.
- MoveFirst Method Moves the record pointer to the first record in the record set.
- MoveLast Method Moves the record pointer to the last record in the record set.
- MoveNext Method Moves the record pointer to the next record in the record set.
- MovePrevious Method Moves the record pointer to the record immediately prior to the current one in the record set.
- BOF Property Boolean. Lets you know if the current record pointer is at the Beginning of record set.
- EOF Property Boolean. Lets you know if the current record pointer is at the End of the record set.
|
Conn.Open "DataSourceName"
|
This is an invocation of the ADODB.Connection Open method. The "DataSourceName" must exist on the
server as a valid System DSN pointing to the database that the script expects or the script will fail.
If you want to try this at home, you can download the entire scripts along with a sample database, below.
|
DIM sEmailAddress
sEmailAddress = Request.Form("emailaddress")
If Len(sEmailAddress)=0 Then
Response.Write "<p class=header>Signup Error!"
Response.Write "<p class=error><B>No email " & _
"address was supplied!</B><BR><BR>"
Response.Write "Go back, and try again..."
Response.Write "<a class=link href=newsletter.htm>" & _
" (Click Here)</a><BR><BR>"
Else
sEmailAddress = LCase(Trim(sEmailAddress))
Rs.Open "SELECT * FROM Addresses WHERE Email = '" & _
sEmailAddress & "'", Conn
If Rs.Eof then
|
|
As you can see, we're starting to use the ADODB.Recordset object now (Rs.Open & Rs.Eof). I'm opening a row-returning SQL
SELECT statement. This is a test to see whether the email address exists
in the system or not. If it does not exist, we will add it; otherwise, we will remove it.
|
Response.Write "<p class=header>"
Response.Write "Adding To Newsletter"
Rs.Close
Rs.Open "INSERT INTO Addresses (Email) " & _
" VALUES ('" & sEmailAddress & _
"')", Conn
Response.Write "<p class=normal>"
Response.Write "<b>" & sEmailAddress & "</B>"
Response.Write " was successfully added!<BR><BR>"
Else
Response.Write "<p class=header>"
Response.Write "Removing From Newsletter"
Rs.Close
Rs.Open "DELETE FROM Addresses " & _
" WHERE Email='" & _
sEmailAddress & "'", Conn
Response.Write "<p class=normal>"
Response.Write "<b>" & sEmailAddress & "</B>"
Response.Write " was successfully removed!<BR><BR>"
End If
End If
Conn.Close
|
This is probably the most important statement in this entire script.
Most servers are configured to handle a maximum number of open data connections per
user session. If you open (but never close) data connections then your visitor is
only a few page impressions away from a lovely server crash.
That won't (or shouldn't) bring the server crashing down, but what it will do is
deadlock any further attempts to access that ODBC DataSource. Indeed, the DataSource
is crashed due to too many open connections.
Data connections can be forced shut, from the server-end. An IIS administrator would
need to Unload the scripts for that Virtual Directory or Folder. Check with your
Domain Host's technical support if you need to have the data connections forced shut.
|
%>
</body>
</html>
|
|
If you would like to try this example, but don't want to cut & past the code, then
download the file FormToADOSample.zip, which is a WinZip Archive (ZIP file), containing:
- Newsletter.htm This HTML file has a form which launches the ASP to post to the database.
- Signup.Asp This ASP file has the VBScript code which uses ADO to update records in an Access Database.
- FormToADOSample.mdb This is an Access Database (created using Access 97) containing a single Table, "Addresses".
REMEMBER!
Before this example will work, you will need to open the ODBC Administrator (should be in your
Windows Control Panel or Administrator Options within the Control Panel) and create the following Data Source Name:
Data Source Name = DataSourceName
Path = [?? pick a folder on your system ??] \ FormToADOSample.mdb
|
There you have it. One newsletter signup script in ASP that posts to an Access Database via ADO!
I'd love to hear your feedback. Feel free to write me Timothy McKay .
|
|
|
info@chinookwebs.com
403.257.1357
