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.
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.
Creating our createuser.asp page
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.
<% Option Explicit %>
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."
<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">
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.
Creating our insertuser.asp page
Now we need to create the page that will take the form data and enter it into the database. Name this page insertuser.asp.
<% Option Explicit %>
'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.
'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
If username = "" then
If password = "" then
'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
'Assuming we have not found any duplicate records, we call the AddNew method to add a new record
'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!
'Now we close our recordset and connection objects to clean things up
Set objRS = nothing
Set objConn = nothing
'Finally, we redirect the user back to the form telling them that they have successfully added a user.
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!
Times Viewed: 17