Jimmy Jimmy - 7 months ago 16
SQL Question

Need to display multiple rows using SQL in ASP

I have the following code in the begining of my ASP file

<%
Set rstest = Server.CreateObject("ADODB.Recordset")
sql = "SELECT * FROM Division;"
rstest.Open sql, db
%>


In the body portion of the same ASP I have

<table width="200" border="1">
<tr>
<th>Date/Time</th>
<th>Officer</th>
<th>Comments</th>
</tr>
<tr>
<td><% = Date_Field %></td>
<td><% = First_Name %>&nbsp;<% = Last_Name %></td>
<td><% = Comments %></td>
</tr>
<tr>
<td><% = Date_Field %></td>
<td><% = First_Name %>&nbsp;<% = Last_Name %></td>
<td><% = Comments %></td>
</tr>
</table>


For some reason I only see one duplicate record, even though there are five unique records in my table. Why is this?

Answer

Your code will only display data from the first record in the recordset. You will have to code a loop to get at the rest of them, and build the table rows in that code. Put those table rows into a single variable and then use that variable to fill all the rows at once, in the same manner as the alternate method below.

Alternatively, you could build your table rows in your Sql:

<%  
Set rstest = Server.CreateObject("ADODB.Recordset")
sql = "SELECT '<tr><td>' + Date_Field + '</td><td>' + First_Name + ' ' + Last_Name + '</td><td>' + Comments + '</td></tr>' AS 'Officer_Rows' FROM Division;" 
rstest.Open sql, db
%>

and retrieve the entire rowset as per:

<table width="200" border="1">
<tr>
    <th>Date/Time</th>
    <th>Officer</th>
    <th>Comments</th>
</tr>
<% = Officer_Rows %>
</table>

I should point out that getting your HTML markup from the database call is a VERY BAD IDEA!! @user704988's answer is a far better one than mine.