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

Inserting Form Responses into Databases


For this example, I will assume that you, the coder, is wanting to track user information by entering it into the database. I will not go into creating a logon form, ect. That shall be discussed in lessons for another time. What I will do, however, is show you how to track user information.

Let us say we are programmers for My Host Web Hosting, and we need to be able to track users who fill out a RFI form (Request for Information). We will want to know their Name, Phone Number, E-Mail Address, and their comments.

First, we want to create our database. I will use Access for this example, but it can easily be ported to any other database.

Name Datatype
first name text
last name text
phone number text
email text
comments memo

Now, wouldn't it be really spiffy if we created a nice "wizard" like interface? I think so too. So let's adopt a Microsoft strategy - If it looks right, it works right. (j/k)

So, Let us create a nice hunky dory form, which is extremely weak design wise, but accomplishes the idea. You can touch it up w/ your own design mechanisms. I'm going to explain every part of this code in depth after I display it. I just do this because I make less errors this way ;-)

<%
strMode = Request.Form("pagemode")
If Len(Request("back")) Then
      strMode = strMode - 1
Else
      strMode = strMode + 1
End If
if Len(Request("Cancel")) > 0 then response.redirect "index.asp"
if len(Request("finish")) > 0 then strMode = "4"
%>

<html>
      <head><title>Request for Information</title></head>
      <body>
            <h1>Request for Information</h1><hr>
      <br><form method="post"><br>
<% If strMode = "2" Then %>
      <center><h2>Contact Information</h2></center><hr>
      <br><br>
      Phone Number: <input type=text name="Phone_number" value="<% = Server.HTMLEncode(Request("phone_number")) %>"><br>
      E-Mail Address: <input type=text name="E_mail" value="<% = Server.HTMLEncode(Request("e_mail")) %>"><br>
      <input type=hidden name="first_name" value="<% = Server.HTMLEncode(Request("first_name")) %>">
      <input type=hidden name="last_name" value="<% = Server.HTMLEncode(Request("last_name")) %>">
      <input type=hidden name="comments" value="<% = Server.HTMLEncode(Request("comments")) %>">
      <input type=hidden name="pagenum" value="3">
      <hr>
      <input type=submit name="back" value="&lt; &nbsp; Back"> &nbsp;
      <input type=submit name="next" value="Next &nbsp; &gt;"> &nbsp;
      <input type=submit name="cancel" value="Cancel">

<% ElseIf StrMode = "3" Then %>
      <center><h2>Information</h2></center><hr>
      <br><br>
      Please specify what you would like to know: <br>
      <textarea name="comments"><% = Server.HTMLEncode(Request("comments")) %></textarea>
      <input type=hidden name="first_name" value="<% = Server.HTMLEncode(Request("first_name")) %>">
      <input type=hidden name="last_name" value="<% = Server.HTMLEncode(Request("last_name")) %>">
      <input type=hidden name="phone_number" value="<% = Server.HTMLEncode(Request("phone_number")) %>">
      <input type=hidden name="e_mail" value="<% = Server.HTMLEncode(Request("e_mail")) %>">
      <input type=hidden name="pagenum" value="3">
      <hr>
      <input type=submit name="back" value="&lt; &nbsp; Back"> &nbsp;
      <input type=submit name="finish" value="Finish &nbsp;"> &nbsp;
      <input type=submit name="cancel" value="Cancel">
<% ElseIf strMode="4" Then %>
'Code will be going here shortly
'--------- INSERT DATABASE CODE HERE WHEN INSTRUCTED ----------


'----------------------- END INSERTION ------------------------
<% Else %>
      <center><h2>Personal Information</h2></center><hr>
      <br><br>
      First Name: <input type=text name="first_name" value="<% = Server.HTMLEncode(Request("first_name")) %>"><br>
      Last Name: <input type=text name="last_name" value="<% = Server.HTMLEncode(Request("last_name")) %>"><br>
      <input type=hidden name="phone_number" value="<% = Server.HTMLEncode(Request("phone_number")) %>">
      <input type=hidden name="e_mail" value="<% = Server.HTMLEncode(Request("e_mail")) %>">
      <input type=hidden name="comments" value="<% = Server.HTMLEncode(Request("comments")) %>">
      <input type=hidden name="pagenum" value="1">
      <hr>
      <input type=submit name="next" value="Next &nbsp; &gt;"> &nbsp;
      <input type=submit name="cancel" value="Cancel">
<% End If %>
    </form>
    </body>
</html>

Now we have our form created. If you are curious about all this code, check out http://www.asptoday.com/articles/19990210.htm. A simply amazing article which cannot go unnoticed.

Now after the user visits the form, and fills out all the information, they click the finish button. If you notice where I say

if len(Request("finish")) > 0 then strMode = 4

means that if they have clicked the finish button, then their page is #4. This is not discussed right now, but in your final copy it will go where you are instructed to. Now, what are we wanting to accomplish? We already know that. Now, I will write straight to the database, but in a corporate environment, this will not be something you will want. You will want to trim the data, make it look nice, et cetera. Which means you should probably incorporate Doug Dean's wonderful component. You can read his article here. Now, I will assume that you have inserted appropriate code. Here is what we will do:

'This creates a server object
set cnADO = Server.CreateObject("ADODB.Connection")

'This creates our DSN reference. Modify for your needs
myDSN = "DSN=myDSN"

'This completes the object creation, and connects our database.
cnADO.Open myDSN

mySQL = "INSERT INTO myDatabase ([first name], [last name], [phone number], email, comments) VALUES " & _      "('" & first_name & "','" & last_name & "','" & phone & "','" & email & _      "','" & comments & "')"

This long LONG line creates our SQL statement. I am assuming that the variables (first_name, last_name, et cetera) were already passed through Doug's component, or set based on Request.Form("value") queries, if you want to be risky. Now, let's analyze it. The INSERT INTO means what it says, INSERT INTO the database. Next we define what table we will be inserting into. Next we define what records we will be updating, in this case, all of them. And finally, our VALUES statement shows that we are beginning the actual data, and then we place it. It is very simple. I will draw a nice little graphic for you ;-)

Also, be sure to include ' ' around any values which are text fields, and [ ] around fields with spaces ( I believe that is correct ) INSERT INTO myDatabase (fields,'field s',fields) VALUES (values,'values',values)
|----1----| |---2----| |----------3------------| |--4-| |----------5-----------|

    1) Our statement which states we are INSERTING into a table
    2) Our table name
    3) The records/fields which we will be inserting
    4) Statement preceding our actual values
    5) Our actual values, with ' ' around fields that are text.

In the My Company example, we used a TextArea. You always want to do this when dealing with memo fields, namely our comments field. Now that I have explained all this SQL statement, time to finish the code.

'This line is where it actually does the SQL
set rsADO = cnADO.Execute(mySQL)

That's it! Yeah. 5 Lines! And we just inserted all our data into our record. And look at it, we even have a nice form to go with it. Now, you can add a page that shows them what they entered, but that is entirely your choice. And now, I leave you, with a perfectly adapted code for anything.

By the way, for the people who never think for themselves, NOW is when you insert the datbase code into where I said insert it.

You could take this code as a foundation and code any sort of databasing application. Heck! I even used this sort of framework for the school's logon page. As you can see, it's not that hard, you just need to understand it.

God Bless from Ryan S

Attachments:

  • Code to this Example

    SubNotes/FootNotes:
    Note 1)
    If you have absolutely no idea what Request.Form does, then let me explain. When you create a form using the POST method, then Request.Form("name") will return the value of a form object entitled "name." If you used the GET method, you would use Request.QueryString("name"). So let's say on our form I didn't want to use Doug's component (which is not really smart), I would do this before creating my server object for databases:

    first_name = Request.Form("First_Name")
    last_name = Request.Form("last_name")
    phone = Request.Form("Phone_Number")
    email = Request.Form("e_mail")
    comments = Request.Form("comments")

    Make sense? I hope so. If any of this confuses you, drop me a note at mmffml@fetchmail.com

    Note 2)
    Any companies willing to take me on for a summer internship/job I would love to know. I am sure many company IT managers read these, and you can add a trained professional like me to your staff. If you are in the Georgia area and hiring, email me at mmffml@fetchmail.com and I will gladly send my resume. Thank you!


  • Article Information
    Article Title: Inserting Form Responses into a Database
    Article Author: Ryan S.
    Published Date: Sunday, April 04, 1999
    Article URL: http://www.4GuysFromRolla.com/webtech/040499-1.shtml


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