Need to display multiple rows using SQL in ASP

Question

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?

If you have multiple records, you'll need to loop through the record set. But there's no loop in the code... — David
– David, Commented Aug 1, 2013 at 21:59

MaxiWheat · Accepted Answer · 2013-08-01 23:24:57Z

4

Try code below. I think this should work.

<%  
    Set rstest = Server.CreateObject("ADODB.Recordset")
    sql = "SELECT * FROM Division;" 
    rstest.Open sql, db
%>
    <table width="200" border="1">
        <tr>
            <th>Date/Time</th>
            <th>Officer</th>
            <th>Comments</th>
        </tr>
<%
    if rstest.EOF then
    response.write "No Records Found!"

    Do While NOT rstest.Eof
%>
    <tr>
        <td><% = Date_Field %></td>
        <td><% = First_Name %>&nbsp;<% = Last_Name %></td>
        <td><% = Comments %></td>
    </tr>
<%
    rstest.MoveNext()
    Loop
    End If
    rstest.Close
    Set rstest=nothing
    db.Close
    Set db=nothing
%>  
    </table>

edited Aug 1, 2013 at 23:24

MaxiWheat

6,2497 gold badges53 silver badges77 bronze badges

answered Aug 1, 2013 at 23:00

user704988

4361 gold badge9 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user704988 Over a year ago

Just commenting realizing I did not explain what changed here. As other fellows noted, you only retrieved first record from DB and expected whole table. You wanted to use a loop and fetch respective data from there and wrap HTML outside of it. Just be sure you close/unset your recordsets and DB connection once you are done. never keep sessions/objects open.

Spanky Over a year ago

Is that why I keep getting the error '800a0e78' Operation is not allowed when the object is closed. Message? How can I prevent this message?

Jeremy Schrader · Accepted Answer · 2016-04-29 14:34:47Z

Thanks! this really helped after a day of looking around for the correct solution. I like how you broke it up so that you can move it around to header, content, footer areas.

I have a slight variation that worked for me. Note that this has the full connection string in it. Also, the one thing that took a little bit of digging was finding out that we had to put brackets around the table name in order for it to work.

<% 
'declare the variables 
Dim Connection
Dim ConnString
Dim Recordset
Dim SQL

'define the connection string, specify database driver
ConnString="DRIVER={SQL Server};SERVER=localhost;UID=xxxx;" & _ 
"PWD=xxxx;DATABASE=xxxx"

'declare the SQL statement that will query the database
SQL = "SELECT * FROM [xxxx]"

'create an instance of the ADO connection and recordset objects
Set Connection = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")

'Open the connection to the database
Connection.Open ConnString

'Open the recordset object executing the SQL statement and return records 
Recordset.Open SQL,Connection
%>

<%
'first of all determine whether there are any records 
If Recordset.EOF Then 
Response.Write("No Users Found.") 
Else 
'if there are records then loop through the fields 
Do While NOT Recordset.Eof  
%>


<div class="row">
    <div class="col-xs-4">USERNAME</div>
    <div class="col-xs-4">PASSWORD</div>
    <div class="col-xs-4">STATUS</div>
<% 
Response.write "<div class='col-xs-4'>"    
Response.write Recordset("userName")
Response.write "</div><div class='col-xs-4'>"    
Response.write Recordset("psword")
Response.write "</div><div class='col-xs-4'>"    
Response.write Recordset("stat")
Response.write "</div>"    
Recordset.MoveNext     
Loop
End If
%>
</div>

<%
'close the connection and recordset objects to free up resources
Recordset.Close
Set Recordset=nothing
Connection.Close
Set Connection=nothing
%>

Cyberherbalist · Accepted Answer · 2013-08-01 23:32:57Z

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.

Collectives™ on Stack Overflow

Need to display multiple rows using SQL in ASP

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related