Published: Sunday, April 04, 1999
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="< Back">
<input type=submit name="next" value="Next >">
<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="< Back">
<input type=submit name="finish" value="Finish ">
<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 >">
<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!
This article was written by
Ryan S.
Ryan has been a computer programmer
in the loosest sense since the age of 8. He has been working with ASP
since the age of 13, when it first came out (that he knows of), and is
somewhat advanced at it.
