lesson9

ASP Lesson 9: Editing and Updating Form Data

Now we have a form with data from our database. Other than being able to view the data in a form, the other obvious use is to edit and modify our data.

Here is a modified version of the form we used in Lesson 6, filled with the first record in our database. You should notice that the Student ID field is not present on this form. Since the Student ID is the key value in our database, the user shouldn't be able to modify that value. Since the Grades table also references the Student ID, if we were to change the Student ID in the Students table, we would also need to change the Student ID in the Grades table. We will submit the form to update.asp. The modified form is presented below:

We also need to tell the form where to send the data by changing the <form> properties. We will post the form to the update.asp page. Thus, the form is defined as follows:

On the receiving end, in update.asp, we need to gather our data then use an update query to update the record in our database. The following represents the update query we will use:

sql="UPDATE Students SET " & _
    "FName='"   & request("txtfname")   & "'," & _
    "LName='"   & request("txtlname")   & "'," & _
    "Address='" & request("txtaddress") & "'," & _
    "City='"    & request("txtcity")    & "'," & _
    "ST='"      & request("txtst")      & "'," & _
    "Zipcode=" & request("txtzipcode") & " " & _
    "WHERE SID='" & request("txtsid")

Once the query is built, we execute it and the record will be updated.

Exercise 9.1 - frmUpdate.asp

<form method="post" action="update.asp">
<input type="hidden" name="txtsid" value="<%=rs("sid")%>">
<table border="1" bgcolor="#C0C0C0" bordercolorlight="#FFFFFF">
<tr>
   <td>
      <b>Student Data</b>
   </td>
</tr>
<tr>
   <td>
   <table border="0" cellspacing="1" cellpadding="1">
   <tr>
   <td>
      <font size="2">First Name</font>
   </td>
   <td colspan="2">
      <font size="2">Last Name</font>
   </td>
   </tr>
   <tr>
   <td>
      <input type="text" size="20" value="<%=rs("FName")%>"
       name="txtfname">
   </td>
   <td colspan="2">
      <input type="text" size="20" value="<%=rs("LName")%>"
       name="txtlname">
   </td>
   </tr>
   <tr>
   <td>
      <font size="2">Address</font>
   </td>
   </tr>
   <tr>
   <td colspan="3">
      <input type="text" size="40" value="<%=rs("Address")%>"
       name="txtaddress"></td>
   </tr>
   <tr>
   <td>
      <font size="2">City</font>
   </td>
   <td>
      <font size="2">ST</font>
   </td>
   <td>
      <font size="2">Zipcode</font>
   </td>
   </tr>
   <tr>
   <td>
      <input type="txttext" size="20" value="<%=rs("City")%>"
       name="city">
   </td>
   <td>
      <input type="txttext" size="2" value="<%=rs("St")%>"
       name="st">
   </td>
   <td>
      <input type="txttext" size="10" value="<%=rs("Zipcode")%>"
       name="zipcode">
   </td>
   </tr>
   </table>
   </td>
</tr>
<tr>
   <td>
   <table cellpadding="0" cellspacing="0" width="100%" border="0">
   <tr>
      <td align="left">
         <input type="reset" name="btnClear" value="Clear">
      </td>
      <td align="right">
         <input type="submit" name="btnSubmit" value="Submit">
      </td>
   </tr>
   </table>
   </td>
</tr>
</table>
</form>

<%
sid=request("txtsid")
fname=request("txtfname")
lname=request("txtlname")
address=request("txtaddress")
city=request("txtcity")
st=request("txtst")
zipcode=request("txtzipcode")

sql="UPDATE Students SET " & _
    "SID='"     & sid      & "', " & _
    "FName='"   & FName   & "', " & _
    "LName='"   & LName   & "', " & _
    "address='" & address & "', " & _
    "city='"    & city    & "', " & _
    "st='"      & st       & "', " & _
    "Zipcode=" & zipcode & " " & _
    "WHERE sid='" & SID & "'"

cn.execute(sql)
closeCN
%>
<html>

<head>
<title>Update Complete</title>
</head>

<body>
<h1>Update Complete.</h1>
</body>
</html>