Home > Articles

  • Print
  • + Share This

Storing the Autonomous Content from the User into the Database (ASP Code)

To store the user's hacker phrase in the database, we need to write code that does the following:

  1. Gets whatever the user typed into the text boxes.

  2. Opens the database.

  3. Stores the values in the database.

  4. Closes the database.

Before we do these steps, you need to know where the code goes. The code to store the user's hacker phrase contribution goes just before the HTML code, in the same file (see Listing 6):

Listing 6 Adding Code to Store the User's Hacker Phrase (add_phrase.asp)

<%
... Code to store the user's hacker phrase in the database ...
%>

<HTML>
<HEAD>
<TITLE>add_phrase.asp</TITLE>
<STYLE>
<!--
BODY {font-family:verdana,arial,helvetica;font-size:9pt}
TD  {font-family:verdana,arial,helvetica;font-size:9pt}
A  {color: #572119; font-weight: bold}
-->
</STYLE>
</HEAD>
<BODY>
<CENTER><FONT STYLE="font-size:16pt" COLOR=#572119>
<B>Add Your Own Hacker Phrase!</B>
</FONT>
<HR COLOR=#572119>
<TABLE BGCOLOR=LIGHTGREY><TR><TD>
<B>Instructions</B>. Don't hold back. If you have a great hacker
phrase that you use at school or work, let's hear it. Post the
phrase in the form below for the whole world to see!!!
</TD></TR></TABLE>
<HR COLOR=#572119>
<FORM METHOD=POST ACTION=add_phrase.asp>
<TABLE BORDERCOLOR=#572119 BORDER=1 CELLSPACING=0><TR><TD>
<TABLE CELLSPACING=0>
<TR><TD BGCOLOR=#DFCCAC><B>Phrase</B></TD>
  <TD><INPUT TYPE=TEXT NAME=phrase SIZE=45></TD></TR>
<TR><TD BGCOLOR=#DFCCAC><B>Usage</B></TD>
  <TD><INPUT TYPE=TEXT NAME=usage SIZE=45></TD></TR>
<TR><TD BGCOLOR=#DFCCAC><B>Example</B></TD>
  <TD><TEXTAREA NAME=example ROWS=5 COLS=34></TEXTAREA></TD></TR>
</TABLE>
</TD></TR></TABLE>
<INPUT TYPE=SUBMIT VALUE="SUBMIT HACKER PHRASE">
</FORM>
</CENTER>
</BODY>
</HTML>

Step 1: Getting What the User Typed

Note that every text box has a name. The text box for the hacker phrase is named phrase, the text box for the phrase's usage is named usage, and the big text box for the example of how to use the hacker phrase is named example (refer to Listing 6 above). To extract what the user typed in these text boxes, you use this command:

v_controlname=request.form("control name")

So, if you want to extract the hacker phrase you would type this:

v_phrase=request.form("phrase")

Similarly, to extract what the user typed for usage and example, these are the commands:

v_usage=request.form("usage")
v_example=request.form("example")

One useful variation of these commands is to surround request.form(...) with the trim(...) function as shown in Listing 7. This gets rid of any whitespace at the beginning or end of the string.

Listing 7 Code to Extract and Store the User's Entry

<%
v_phrase = trim(request.form("phrase"))
v_usage  = trim(request.form("usage"))
v_example = trim(request.form("example"))
%>
... HTML code as in Listing 6 ...

Now that we've extracted the values the user typed, we can store them in the database.

Step 2: Opening the Database

Before we open the database, we should do some simple error checking. Specifically, we should check that the user has actually typed something into every text box; a mischievous user may just keep clicking the submit button to fill our database with junk. To check that the user has entered something into every box, we use an IF-THEN statement, comparing the values with a blank string (""), as shown in Listing 8:

Listing 8 Checking for Entries Before Opening the Database

<%
v_phrase = trim(request.form("phrase"))
v_usage  = trim(request.form("usage"))
v_example = trim(request.form("example"))
IF v_phrase<>"" and v_usage<>"" and v_example<>"" THEN
... database stuff goes here ...
END IF
%>
... HTML code as in Listing 6 ...

If the user has indeed entered information in every text box, we then go ahead and open the database as we did in the Week 6 article (see Listing 9):

Listing 9 Code to Open the Database

<%
v_phrase = trim(request.form("phrase"))
v_usage  = trim(request.form("usage"))
v_example = trim(request.form("example"))
IF v_phrase<>"" and v_usage<>"" and v_example<>"" THEN
  set conn=server.createobject("adodb.connection")
  conn.open("DBQ=" & server.mappath("hacker.mdb") & _
       ";Driver={Microsoft Access Driver (*.mdb)};")
... database stuff goes here ...
END IF
%>
... HTML code as in Listing 6 ...

Step 3: Storing the Values in the Database

To store information in the hackerphrase table, we send a SQL INSERT query with the user's information to the database via the execute method in our conn object:

conn.execute(...SQL INSERT query...)

Before we do so, however, we need to a) do some basic error handling and b) calculate a phraseID for the user's hacker phrase. Recall that the hacker phrase generator from the Week 6 article uses the phraseID to randomly select a hacker phrase, so each phrase in the database must have a unique phrase ID.

In terms of basic error handling, we need to replace every occurrence of a single quote with two single quotes (if we don't do this, the database gets confused, and let's leave it at that!). The Visual Basic replace() function takes as parameters a string, a pattern to replace, and a replacement pattern:

new_string=replace(old_string, pattern_to_replace, replacement_pattern)

We can use this function to replace every occurrence of a single quote (the pattern to replace) with two single quotes (replacement pattern) as shown in Listing 10:

Listing 10 Basic Error-Handling Code

<%
v_phrase = trim(request.form("phrase"))
v_usage  = trim(request.form("usage"))
v_example = trim(request.form("example"))
IF v_phrase<>"" and v_usage<>"" and v_example<>"" THEN
  set conn=server.createobject("adodb.connection")
  conn.open("DBQ=" & server.mappath("hacker.mdb") & _
       ";Driver={Microsoft Access Driver (*.mdb)};")

  v_phrase = replace(v_phrase , "'", "''")
  v_usage  = replace(v_usage , "'", "''")
  v_example = replace(v_example, "'", "''")

... database stuff goes here ...
END IF
%>
... HTML code as in Listing 6 ...

Next, to calculate the phraseID for the user's new hacker phrase, we send the following SQL query to the database:

SELECT MAX(phraseID) as maxID from HackerPhrase

This query returns the biggest phraseID in the database. We then increment the returned phraseID by 1 as shown in Listing 11:

Listing 11 Calculating a phraseID for the User's Hacker Phrase

<%
v_phrase = trim(request.form("phrase"))
v_usage  = trim(request.form("usage"))
v_example = trim(request.form("example"))
IF v_phrase<>"" and v_usage<>"" and v_example<>"" THEN
  set conn=server.createobject("adodb.connection")
  conn.open("DBQ=" & server.mappath("hacker.mdb") & _
       ";Driver={Microsoft Access Driver (*.mdb)};")

  v_phrase = replace(v_phrase , "'", "''")
  v_usage  = replace(v_usage , "'", "''")
  v_example = replace(v_example, "'", "''")

  set rs=conn.execute _
     ("SELECT max(phraseID) as maxID from HackerPhrase")
  v_phraseID=rs("maxID")+1

... database stuff goes here ...
END IF
%>
... HTML code as in Listing 6 ...

Finally, we can store the hacker phrase into the database! The general syntax of the SQL INSERT query is as follows:

INSERT INTO table (fields) VALUES (values)

Listing 12 shows our specific code:

Listing 12 Storing the User's Hacker Phrase in the Database

<%
v_phrase = trim(request.form("phrase"))
v_usage  = trim(request.form("usage"))
v_example = trim(request.form("example"))
IF v_phrase<>"" and v_usage<>"" and v_example<>"" THEN
  set conn=server.createobject("adodb.connection")
  conn.open("DBQ=" & server.mappath("hacker.mdb") & _
       ";Driver={Microsoft Access Driver (*.mdb)};")

  v_phrase = replace(v_phrase , "'", "''")
  v_usage  = replace(v_usage , "'", "''")
  v_example = replace(v_example, "'", "''")

  set rs=conn.execute _
     ("SELECT max(phraseID) as maxID from HackerPhrase")
  v_phraseID=rs("maxID")+1

  conn.execute("INSERT INTO hackerphrase "       & _
         " (phraseID, phrase, usage, example) " & _
         " VALUES ("              & _
         " " & v_phraseID & " ,"         & _
         "'" & v_phrase  & "',"         & _
         "'" & v_usage  & "',"         & _
         "'" & v_example & "')"          )
... database stuff goes here ...
END IF
%>
... HTML code as in Listing 6 ...

And with that statement, the hacker phrase is stored in the database.

Step 4: Closing the Database

To complete our code, we simply close all our database variables and redirect the user back to the home page (default.asp, see Listing 13):

Listing 13 Closing the Database and Redirecting to the Home Page

<%
v_phrase = trim(request.form("phrase"))
v_usage  = trim(request.form("usage"))
v_example = trim(request.form("example"))
IF v_phrase<>"" and v_usage<>"" and v_example<>"" THEN
  set conn=server.createobject("adodb.connection")
  conn.open("DBQ=" & server.mappath("hacker.mdb") & _
       ";Driver={Microsoft Access Driver (*.mdb)};")

  v_phrase = replace(v_phrase , "'", "''")
  v_usage  = replace(v_usage , "'", "''")
  v_example = replace(v_example, "'", "''")

  set rs=conn.execute _
     ("SELECT max(phraseID) as maxID from HackerPhrase")
  v_phraseID=rs("maxID")+1

  conn.execute("INSERT INTO hackerphrase "       & _
         " (phraseID, phrase, usage, example) " & _
         " VALUES ("              & _
         " " & v_phraseID & " ,"         & _
         "'" & v_phrase  & "',"         & _
         "'" & v_usage  & "',"         & _
         "'" & v_example & "')"          )

  rs.close
  set rs=nothing
  conn.close
  set conn=nothing
  response.redirect ("default.asp")
END IF
%>
... HTML code as in Listing 6 ...

Listing 14 shows the complete file with both HTML form and database code:

Listing 14 The Entire Autonomous Content Mechanism (add_phrase.asp)

<%
set conn=server.createobject("adodb.connection")
conn.open("DBQ=" & server.mappath("hacker.mdb") & _
     ";Driver={Microsoft Access Driver (*.mdb)};")

v_phrase = trim(request.form("phrase"))
v_usage  = trim(request.form("usage"))
v_example = trim(request.form("example"))

if v_phrase<>"" and v_usage<>"" and v_example<>"" then
 v_phrase = replace(v_phrase , "'", "''")
 v_usage  = replace(v_usage , "'", "''")
 v_example = replace(v_example, "'", "''")
 set rs=conn.execute _
    ("select max(phraseID) as maxID from hackerPhrase")
 v_phraseID=rs("maxID")+1
 conn.execute("insert into hackerphrase "       & _
        " (phraseID, phrase, usage, example) " & _
        " VALUES ("              & _
        " " & v_phraseID & " ,"         & _
        "'" & v_phrase  & "',"         & _
        "'" & v_usage  & "',"         & _
        "'" & v_example & "')"          )
 rs.close
 set rs=nothing
 conn.close
 set conn=nothing
 response.redirect("default.asp")
end if
%>

<HTML>
<HEAD>
<TITLE>add_phrase.asp</TITLE>
<STYLE>
<!--
BODY {font-family:verdana,arial,helvetica;font-size:9pt}
TD  {font-family:verdana,arial,helvetica;font-size:9pt}
A  {color: #572119; font-weight: bold}
-->
</STYLE>
</HEAD>
<BODY>
<CENTER><FONT STYLE="font-size:16pt" COLOR=#572119>
<B>Add Your Own Hacker Phrase!</B>
</FONT>
<HR COLOR=#572119>
<TABLE BGCOLOR=LIGHTGREY><TR><TD>
<B>Instructions</B>. Don't hold back. If you have a great hacker
phrase that you use at school or work, let's hear it. Post the
phrase in the form below for the whole world to see!!!
</TD></TR></TABLE>
<HR COLOR=#572119>
<FORM METHOD=POST ACTION=add_phrase.asp>
<TABLE BORDERCOLOR=#572119 BORDER=1 CELLSPACING=0><TR><TD>
<TABLE CELLSPACING=0>
<TR><TD BGCOLOR=#DFCCAC><B>Phrase</B></TD>
  <TD><INPUT TYPE=TEXT NAME=phrase SIZE=45></TD></TR>
<TR><TD BGCOLOR=#DFCCAC><B>Usage</B></TD>
  <TD><INPUT TYPE=TEXT NAME=usage SIZE=45></TD></TR>
<TR><TD BGCOLOR=#DFCCAC><B>Example</B></TD>
  <TD><TEXTAREA NAME=example ROWS=5 COLS=34></TEXTAREA></TD></TR>
</TABLE>
</TD></TR></TABLE>
<INPUT TYPE=SUBMIT VALUE="SUBMIT HACKER PHRASE">
</FORM>
</CENTER>
</BODY>
</HTML>
<%
conn.close
set conn=nothing
%>

And we're done with the autonomous content mechanism. To summarize, we've implemented the important content portions of the autonomous business model (the solid lines in Figure 9).

Figure 9 Autonomous business model: What we've implemented so far (solid lines) versus what we're going to cover next (dashed lines).

In my next article, we'll put all this "hard core" technology aside and focus on making money—adding the revenue mechanisms for our autonomous business model (the dashed lines in Figure 9). See you next week.

  • + Share This
  • 🔖 Save To Your Account

Related Resources

There are currently no related titles. Please check back later.