ASP Lesson 8: Retreiving Data From a Database

If you want to edit data in a database, it is necessary to load the data into an exisiting form, make the changes, then update the record. Again, we will use the form illustrated in Figure 6.3 from Lesson 6.

Student Information
Student ID Number:
First Name Last Name
Address
City ST Zipcode

To fill this form with data, we need to create a SQL SELECT query, open the connection, execute the query, then assign the resultset to the default values of the form elements. Here is a code snippit that creates the query:

<%
sql = "SELECT * FROM Students"
openCN
rs.Open sql, cn
%>

Next we need to assign the returned results as the default values for the form elements. Here is a code snippit that does just that:

<input type="text" name="txtFName" value="<%=rs("FName")%> " size="20">

If we put it all together, this is what the above form would look like if it were filled with the data:

Student Information
Student ID Number:
First Name Last Name
Address
City ST Zipcode

Exercise 8.1 - Display Table

<form>
<%
OpenCN
sql = "SELECT * FROM Students"
rs.Open sql, cn%>
<table width="325" border="2" cellpadding="2"
<tr>
<td align="left" width="334">>Student Information</td>
</tr>
<tr>
<td align="right" width="334"><table border="0" width="324" cellspacing="1">
<tr>
<td width="108" align="right">Student ID:</td>
<td width="208"><input type="text" name="txtSID" size="8" value="<%=rs("sid")%>"></td>
</tr>
<tr>
<td width="108" align="right">First Name:</td>
<td width="208"><input type="text" name="txtFName" size="15" value="<%=rs("fname")%>"></td>
</tr>
<tr>
<td width="108" align="right">Last Name:</td>
<td width="208"><input type="text" name="txtLName" size="20" value="<%=rs("lname")%>"></td>
</tr>
<tr>
<td width="108" align="right">Address:</td>
<td width="208"><input type="text" name="txtAddress" size="20" value="<%=rs("address")%>"></td>
</tr>
<tr>
<td width="108" align="right">City:</td>
<td width="208"><input type="text" name="txtCity" size="15" value="<%=rs("city")%>"></td>
</tr>
<tr>
<td width="108" align="right">State:</td>
<td width="208"><input type="text" name="txtST" size="2" value="<%=rs("st")%>"></td>
</tr>
<tr>
<td width="108" align="right">Zipcode:</td>
<td width="208"><input type="text" name="txtZipcode" size="10" value="<%=rs("zipcode")%>"></td>
</tr>
</table>
</td>
</tr>
<tr>
<td align="center"><input type="reset" value="Clear" name="B1"> <input type="submit"
value="Submit" name="btnSubmit"></td>
</tr>
</table>
<%  rs.Close
    CloseCN
%>
</form>

Apply what you have learned.

Use the form you created in Lesson 6 and fill the form with one record from the Grades table. When building your query, use student ID number 00000001.