Do loop within a loop

Question

Could you have a look at this code please and tell me why I get an error :

ADODB.Recordset error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

What I'm trying to do is initially load the first detail from repair log and then inside of the repair history will be date relating to the book code from the first repair log.

Once this has been done I want it to move onto the next repair log and then look inside of the repair history and load the data from that book code.

Something like this:
01/21/2018 4332323 44323322 Cleaning - data from repair log
Process started at 10:am machined cog to spec
looking at alterations - data from repair history

01/21/2018 2232W22 554EREE3 Work Finished - data from repair log
item was checked for dysfunction
item released to gain margins - data from repair history

This is my code:

<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.open connStr
%>                      
    <table class="alt" id="table_detail">
    <thead>
        <tr>
            <th><font color="#FFFFFF">Date</font></th>
            <th><font color="#FFFFFF">Booking Reference</font></th>
            <th><font color="#FFFFFF">Serial Number</font></th>
            <th><font color="#FFFFFF">Status</font></th>
            <th></th>
        </tr>
    </thead>
<%
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.open connStr

    strSQL = "SELECT date_in, Book_code, serial, status FROM repair_log WHERE cust_input = '" & Session("cust_input") & "' ORDER BY date_in DESC"               
    Set rs = conn.Execute(strSQL)

        do until rs.EOF

        date_in = rs("date_in")
        book_code = rs("book_code")
        serial = rs("serial")
        status = rs("status")
        i = i + 1

%>                              
    <tbody>
        <tr onclick="show_hide_row ('hidden_row<%=i%>');">
            <td><%=date_in%></td>
            <td><%=book_code%></td>
            <td><%=serial%></td>
            <td><%=status%></td>
            <td style="vertical-align: middle">
            <img src="../images/detail.png" style="float: left"></td>
        </tr>

<%
    strSQL = "SELECT * FROM repair_history WHERE book_code = '" & book_code & "' ORDER BY id"       
    Set rs = conn.Execute(strSQL)

        do until rs.EOF             
        repair_history = rs("repair_history")
        t = t + 1                                   
%>
        <tr class="hidden_row hidden_row<%=i%>">
            <td colspan=5 style="text-align: Left"><%=t%>. <%=repair_history%></td>
        </tr>
<%
    rs.MoveNext
    loop
%>                                  
    </tbody>    
<%
    rs.MoveNext
    loop
    rs.Close
%>      
</table>

As it stands with the above code I get 1 run and then the error.

If someone could explain why I get the error and the best way to tackle this with an example I would really appreciate it.

Thanks

Possible duplicate of [ADODB.Field error '800a0bcd'](https://stackoverflow.com/questions/13648119/adodb-field-error-800a0bcd) — ahmed abdelqader, Jan 21 '18 at 20:35
You are using the same variable (`rs`) for both loops. Use a different variable for the inner loop. — M4N, Jan 21 '18 at 20:41
Thank you M4N - didn't think of that and that it was be so easy to fix. — Rick Kap, Jan 21 '18 at 20:58
Why do two loops? This is a simple enough query, just use a join to pull the data together in SQL Server. After all, that's what it is there for, to do the heavy lifting for you. — user692942, Jan 22 '18 at 17:54
If your really need to loop inside another loop, that can be costly for your server to keep the conneciton to the database opened. Instead, I recommend you to store your recordset in a **"disconnected recordset"** : More info on http://www.4guysfromrolla.com/webtech/080101-1.shtml AND ON https://support.microsoft.com/en-us/help/289531/how-to-create-ado-disconnected-recordsets-in-asp-using-vbscript-and-js — AlexLaforge, Jan 24 '18 at 17:46
@AlexLaforge why a disconnected recordset using an array is just as effective. But in this case neither is needed just add a join in the SQL query and good bye inner loop! — user692942, Jan 25 '18 at 00:25
@Lankymart I'm only a newbie and don't fully understand how on this instance to use the join function. If you would be kind enough to give me an example I would like to do it the correct way. Thank you — Rick Kap, Jan 25 '18 at 00:30
Lots of useful information here - https://stackoverflow.com/q/565620/692942 but i'd also recommend a SQL tutorial to get the basics of writing SQL queries down to a fine art. — user692942, Jan 25 '18 at 00:35

Do loop within a loop

0 Answers0