To review some HTML, let me state the three basic rules of table tags:
- Begin and end the entire table with <TABLE BORDER="1"> and </TABLE>
- Begin and end each row with <TR> and </TR>
- Begin and end each cell with <TD> and </TD>
Using the double quotes for the border value ensures universal readability. However, many common browsers do not require them.
To build tables that display our data, we need to mix HTML table tags into the data that ADO returns. The most basic table contains only one row (one record or one name) and two columns, as follows. This is artificially easy, but a good way to start:
<%
dim oRSst
Set oRSst=server.createobject("ADODB.recordset")
oRSst.open "People", "DSN=Sailors"
response.write "<table BORDER='1'>"
response.write "<tr>"
response.write "<td>"
response.write oRSst("PeopleNameFirst")
response.write "</td>"
response.write "<td>"
response.write oRSst("PeopleNameLast")
response.write "</td>"
response.write "</tr>"
response.write "</table>"
%>
The code sample above lays out the logic very explicitly. In the following listing we condense this to fewer lines of code. Keep in mind that this is still the same very simple 1-row, 2-column table:
<%
dim oRSst2
Set oRSst2=server.CreateObject("ADODB.recordset")
oRSst2.open "People", "DSN=Sailors"
Response.Write "<TABLE BORDER='1'><TR><TD>"
Response.Write oRSst2("PeopleNameFirst")
Response.Write "</TD><TD>"
Response.Write oRSst2("PeopleNameLast")
Response.Write "</TD></TR></TABLE>"
%>
We can also use the Response.Write shortcut to further condense the code that generates this table, as follows. However, it is general good practice not to code your ASPs this way as separating your server side scripting rather than keeping it all together in the same place degrades performance.
<%
dim oRSst3
Set oRSst3=server.CreateObject("ADODB.recordset")
oRSst3.open "People", "DSN=Sailors"
%>
<TABLE BORDER="1">
<TR><TD><%=oRSst3("PeopleNameFirst")%></TD>
<TD><%=oRSst3("PeopleNameLast")%></TD></TR>
</TABLE>
Building Tables with For...Next Loops
It is rare that you have a client that will be satisfied with a one-row table! Tables usually present data on many records. We will explore that technique first with a table that produces three rows as a result of putting the code for a row within a For...Next loop in the code below. Of course, we can only loop within ASP, so we will have to forget about the Response.Write shortcut used above; instead we'll revert to writing the HTML tags from within ASP, and using Response.Write explicitly:
<%
dim iRowCounter
dim oRSfn
set oRSfn=server.CreateObject("adodb.recordset")
oRSfn.open "People", "DSN=Sailors"
oRSfn.MoveFirst
Response.Write "<TABLE BORDER='1'>"
For iRowCounter=1 to 3
Response.Write "<TR><TD>" & oRSfn("PeopleNameFirst") & "</TD>"
Response.Write "<TD>" & oRSfn("PeopleNameLast") & "</TD></TR>"
oRSfn.MoveNext
Next
Response.Write "</TABLE>"
%>
Note in the above listing that we have to be sure that the table tags are outside of the loop but the rows and cell tags are inside the loop. For the purposes of this demo we are assuming that there are at least three records; in the real world that would be a dangerous assumption.
The common errors on the above code include:
- (the usual problems in creating recordsets)
- Putting the <TABLE> or </TABLE> inside the loop
- Leaving out </TABLE>
- Putting more than one <TR> or </TR> in a row
- Forgetting to include the .MoveNext line within the loop
- Trying to retrieve more records than are available in the database
In later sections of the book we discuss even faster and more eloquent ways to build a table.
Try It Out - Building a Table of the First Five Clothing Items
Let's use the Clothier database again, to create a table that shows the Name and department of the first five items.
<TITLE>2726-03-SimpleRS TIO Building a Table of the
First Five Clothing Items</TITLE>
</HEAD>
<BODY>
<H1>Chapter 3 Simple Recordsets<BR>
Try It Out - <BR>
Building a Table of <BR>
the First Five Clothing Items</h1>
<%
dim oRSc
dim iRowCounter
set oRSc=server.CreateObject("adodb.recordset")
oRSc.open "items", "DSN=clothier"
oRSc.MoveFirst
Response.Write "<TABLE BORDER='1'>"
For iRowCounter = 1 to 5
Response.Write "<TR><TD>" & oRSc("ItemName") & "</TD>"
Response.Write "<TD>" & oRSc("ItemDepartment") & "</TD></TR>"
oRSc.MoveNext
next
%>
</TABLE></BODY</html>
The above code produces the following screen.
How It Works - Building a Table of the First Five Clothing Items
The following lines create the recordset and ensure that we are positioned at the first record:
dim oRSc
dim iRowCounter
set oRSc=server.CreateObject("adodb.recordset")
oRSc.open "items", "DSN=clothier"
oRSc.MoveFirst
Then we begin to write the table to the web page. First, we write the <TABLE> tag, which belongs outside the loop:
Response.Write "<TABLE BORDER='1'>"
Now we can loop through the first five records. Note that, like all For...Next Loops we need a counter to keep track of the number of loops that have been performed. In each loop we write the open row tag <TR> then the tags and data for the two cells in the two columns:
For iRowCounter = 1 to 5
Response.Write "<TR><TD>" & oRSc("ItemName") & "</TD>"
Response.Write "<TD>" & oRSc("ItemDepartment") & "</TD></TR>"
oRSc.MoveNext
next
%>
And then we place the table close tag outside of the loop as follows.
</TABLE>
</BODY</html>
