ASP Lesson 7: Saving Form Data to a Database
With the ability to create forms safely under our belt, now comes the time to learn how to take information entered by the user and insert it into the database. This lesson introduces the SQL INSERT clause.
Inserting Data
Lesson 6 illustrated a rather important, but somewhat useless, method to handle form submission. Lets look at a more useful method for processing form submissions: the SQL INSERT clause.
As you might guess, INSERT is used to... INSERT data into a database. The syntax for the INSERT clause is as follows:
INSERT INTO [table name] ([field list])
VALUES ([value list])
The field list is optional, but if you are not going to enter all the fields in your target table, then it's wise to include the field list. The value list needs to be in the same order as the field list, unless the field list is omitted, in which case, the value list needs to be in the same order as the fields listed in the table. I recommend always using a field list/value list combination. That way you are assured that the data will be put into the proper fields.
For this example, we will use the form illustrated in Figure 6.3 from Lesson 6, with one
modification. We need to change the script that processes the form. Locate the line
<form action="addstudent.asp" method="post">
The code for addstudent.asp is as follows:
<!--#include file="subConn.inc"-->
<%
sql = "INSERT INTO Students (" & _
"sid" & _
",fname"
& _
",lname"
& _
",address" & _
",city"
& _
",st" & _
",zipcode" & _
")
" & _
"VALUES (" & _
"'" &
Request.form("txtSID") & "'" & _
",'" &
Request.Form("txtFName") & "'" & _
",'" &
Request.Form("txtLName") & "'" & _
",'" & Request.Form("txtAddress")
& "'" & _
",'" &
Request.Form("txtCity") & "'" & _
",'" &
Request.Form("txtST") & "'" & _
"," &
Request.Form("txtZipcode") & ")"
openCN
cn.Execute(sql)
closeCN
%>
Error Checking
Thats all it takes to add a student to the Students table. There is a problem with this script: there is no error checking. What if you try to enter a student twice? Since Student ID is the key field for Students, anytime a new entry is created with the same Student ID an error will occur. Our task is to check for a duplicate key value prior to inserting the record. Illustration 7.1 contains the new script that checks for duplicate student ID's:
Exercise 7.2
<!--#include file="adovbs.inc"-->
<!--#include file="subConn.inc"-->
<%
'store SID in a variable since we are going to
' use it more than once.
sid = Request.form("txtSID")
OpenCN
sql = "SELECT sid FROM GB_STUDENT " & _
"WHERE sid='" & sid & "'"
' count the number of records returned
rs.Open sql, cn, adOpenKeyset
found=rs.RecordCount
rs.Close
' determine the next process:
' if 1 record is returned then
' redirect them to the data entry screen
' Otherwise enter the record.
if found = 1 then
session("msg")="Student exists."
nextpage= "frmStudent.asp"
else
' Now build the SQL statement
sql = "INSERT INTO Students (" & _
"sid" & _
",fname" & _
",lname" & _
",address" &_
",city" & _
",st" & _
",zipcode" & _
")
" & _
"VALUES (" & _
"'" &
sid & "'" & _
",'" &
Request.Form("txtFName") & "'" & _
",'" &
Request.Form("txtLName") & "'" & _
",'" &
Request.Form("txtAddress") & "'" & _
",'" &
Request.Form("txtCity") & "'" & _
",'" &
Request.Form("txtST") & "'" & _
","
&Request.Form("txtZipcode") & ")"
' Then set the Connection object,
' execute the query and close the connection.
cn.Execute(sql)
closeCN
session("msg")="Record Added."
nextpage="frmStudent.asp"
end if
' Once the script is finished processing,
' send them back to the data entry form.
response.redirect nextpage
%>
Apply what you have learned.
Using the form and script you created in Lesson 6, modify the script to insert the record into the database.