Inserting Information Into a Database
Today
we will be looking at how we can insert data into a database. We will
be starting a project to build a username & password protected
web page. The first part is entering a user into our database. We
will start by designing an Access database that will hold the information
about our users. Please note that if you want more information in
your database than we are using, you can always add more fields. For
now, we will only be using 3 fields in our users table. Start by opening
Access and creating a new database. Call it whatever you want. Then
double click on "Create Table in Design View." One thing
we must keep in mind when designing this table is that we don't want
any user to have the same username as another. Therefore, we create
2 primary keys. One for the userID (which is an autonumber field)
and one for the actual username. Below is a screen shot of our table.
The
first field is the userID field and the datatype will be set to AutoNumber.
This automatically increments each entry by one number. The second
field is the username field and the datatype will be text. The third
field is the password field and the datatype will be text also. To
create both the userID and username fields as primary keys, click
on the area where you see the key and drag down so both rows are highlighted
in this key column. Right click and select "Primary Key."
Save this table with the name users.
Now
that we have our users table set up in our database, we will have
to add the info to it. This is where it gets fun. Before proceeding,
make sure that you have a connection set up to your database. If you
don't know how to do it, refer to our tutorial on connecting to a
database.
We
need to create 2 pages to insert data into our database. The first
will be a regular form page, but still ASP to report any errors. The
second page will be an ASP page that will actually do the work of
inserting the data into our database. Let's start by creating our
form. Name this page createuser.asp.
<%
@Language=VBScript%>
<% Option Explicit %>
<html>
<head><title>Create User</title></head>
<body>
<%
If request("error")="1" then
Response.Write "You must fill out both form fields to enter someone
into the database."
Else If request("error")="2" then
Response.Write "Someone has already registered that username. Please
try another."
Else If request("success")="1" then
Response.Write "You have successfully added a user to the database."
End If
End If
End If
%>
<form action="insertuser.asp" method="post">
Please type in the desired username. <input type="text"
name="username"><br>
Please type in the desired password. <input type="text"
name="password"><br>
<input type="submit" name="submit" value="submit">
</form>
</body>
</html>
You
might notice something new above. We used the request object that
we talked about in our introduction. The reason for this is so that
if we try to register someone who requests a username that is already
in use, the insertuser.asp page will redirect us to this page and
ask us to try again. Otherwise, if the user was successfully added,
it will tell us that.
Now
we need to create the page that will take the form data and enter
it into the database. Name this page insertuser.asp.
<%
@Language=VBScript%>
<% Option Explicit %>
<!--#include file="connection.asp"-->
<%
'The above
file includes our connection to our database. Please refer to our
previous tutorial on creating a connection if you are not sure how
to do this.
%>
<!--#include file="adovbs.inc"-->
<%
'The above include files is necessary when performing database work,
so always make sure to include it in your pages.
'Declare our variables
Dim username, password, duplicate, objConn, objRS
'Assign the values from the form to a variable
username=trim(request.form("username"))
password=trim(request.form("password"))
duplicate=false
If username = "" then
response.redirect "createuser.asp?error=1"
else
If password = "" then
response.redirect "createuser.asp?error=1"
End If
End If
'Now we create our recordset object that will create the user's
record
Set objRS = Server.CreateObject("ADODB.Recordset")
'Here we open our table which is users and our connection which is
objConn. The other portions of the text will be discussed in a later
tutorial
objRS.Open "users", objConn , , adLockOptimistic,
adCmdTable
'Now we run a check to make sure that no one already has the
same username as the one we're trying to insert
We use a Do While Loop to test for a duplicate and use the
vbTextCompare function to make sure they are not the same. This loop
will check each record before allowing us to insert a new one.
Do While Not (objRS.EOF OR duplicate) If (StrComp(objRS("username"),
username, vbTextCompare) = 0) Then
'Now we tell the page what to do if it does find a duplicate.
We tell it to redirect us back to the form and pass an error number
of 2 which we coded into the form page earlier
duplicate=true
response.redirect "createuser.asp?error=2"
End If
objRS.MoveNext
Loop
'Assuming we have not found any duplicate records, we call the AddNew
method to add a new record
objRS.AddNew
'Now we add our variables to the fields in our users table
objRS.Fields("username") = username
objRS.Fields("password") = password
'Finally we call the update method which completes the addition
of the new record. Without calling the update method, it would not
successfully add the record, so don't forget this part!
objRS.Update
'Now we close our recordset and connection objects to clean
things up
objRS.Close
Set objRS = nothing
objConn.Close
Set objConn = nothing
'Finally, we redirect the user back to the form telling them that
they have successfully added a user.
Response.redirect "createuser.asp?success=1"
%>
That's
all there is to it. Now you know how to create a form to add information
into your database. I'm sure you can think of many more fields to
add in here such as first name, last name, phone number and more to
get a complete listing of your users. But for now, this functions
just fine in order to show you how to create this application. See
ya next time!
~Geoff Swartz
The
files for this tutorial are avialable for download here.
Just unzip them to your Inetpub>wwwroot directory.
Exercise:
Write
an insert application that adds someone's email address and the date
they registered to your member list for monthly newsletters. Remember
to make sure that no duplicates are in the database by using the do
while loop statement. If you need a hint on the date, go back to our
introduction tutorial to see how the date object is used. | 
