Gathering Information
The first thing we must do is to get users to "opt-in" for our information. In my book, Active Server Pages 3.0 from Scratch, I talked about building an online science fiction magazine. Let's take the example a bit further and allow users to sign up for information on their favorite genres.
In order to make things simple, we'll track users by email address. Also, because we'll want users to be able to manage their own subscriptions, we'll need to have them choose a password. We'll also need to collect the basic information on the topics in which they're interested. We'll wind up with a page something like signup.asp. View Listing 1
){kind=link}
Of course, you can make the page look however you like, as long as it asks for the information you need.
Now that we have the request page, we need to create a place to store the information. We could store all of our information in a single table, but that would make it difficult to maintain if we wanted to change or add categories for our users. Instead, we'll store our user information in one table, and their choices in another. We can create our tables like this:
create table users ( first_name varchar(50), last_name varchar(50), email varchar(100), pass varchar(50), mailtype varchar(4) ) create table attributes ( email varchar(100), attribute varchar(50), att_value varchar(50) )
You can also create tables directly in whatever software you are using, such as Oracle Designer.
Now that we have our tables, we're ready to insert our information. Right now, we won't worry about editing existing information, but we'll build the page knowing that we'll need to handle it eventually. The page looks like Listing 2, which we’ll call signup_action.asp.
With this page, we're not doing anything except saving the user's information to the database. We begin by opening the connection to the database in the normal way, using these lines:
set maildb = server.createobject("ADODB.Connection")
maildb.open "maildb"
Naturally, the second line must contain the DSN information that you or your administrator specified on your server.
Next we must add the user's information to the database. In order to avoid dealing with single or double quotes in people's names, we'll use the object model rather than a simple insert statement. First, we create the recordset, as editable, and then we add a new record and populate it. Finally, we update the database with the new information and deallocate the memory.
userSet.open "select * from users where 1=2", maildb, _
adOpenDynamic, adLockPessimistic, adCmdText
userSet.addNew
userSet("first_name") = request("first_name")
userSet("last_name") = request("last_name")
userSet("email") = request("email")
userSet("pass") = request("pass")
userSet("mailtype") = request("mailtype")
userSet.update
userSet.close
set userSet = Nothing
Finally, we insert each of the media and genre choices into the database.
for each attrib in request("media")
sqlText = "insert into attributes (email, attribute, att_value) " & _
" values " & _
"('"& request("email") &"', '"& attrib &"', 'on')"
maildb.execute(sqlText)
next
Then, once we've cleared the connection object, we're ready to move on.