Search code examples
javahtmljspjdbcresultset

How to loop with multiple ResultSet

enter image description here
I want to replicate what's on the picture shown above
So I was thinking of using a nested while loop using ResultSet for each loop accessing different tables in a database as shown on the code below 

<%
        String Query2 = "SELECT * FROM transaction";
        rs = st.executeQuery(Query2);
    %>

    <h2 align="center">Member</h2>
    <%
        while(rs.next())
        {
    %>
            <table bgcolor="#000000" align="center" cellspacing="10px">
                <tr>
                    <th>Transaction ID</th>
                    <th>Transaction Date</th>
                    <th>Costumer Name</th>
                    <th> </th>
                </tr>
                <tr>
                    <td><%=rs.getString("Transaction_ID")%></td>
                    <td><%=rs.getString("Transaction_Date")%></td>
                    <td><%=rs.getString("User_Name")%></td>
                    <td> </td>
                </tr>
                <%
                    String Query3 = "SELECT * FROM list WHERE User_Name = '"+rs.getString("User_Name")+"' AND Date = '"+rs.getString("Transaction_Date")+"'";
                    ResultSet rs2 = st.executeQuery(Query3);

                    while(rs2.next())
                    {
                %>
                        <tr>
                            <td>Product</td>
                            <td>Quantity</td>
                            <td>Price</td>
                            <td>Total</td>
                        </tr>
                        <tr>
                            <td><img src="Picture/<%=rs.getString("Image")%>"></td>
                            <td><%=rs.getString("Quantity")%></td>
                            <td><%=rs.getString("Price")%></td>
                            <td><%=rs.getString("Total")%></td>
                        </tr>
                <%
                    }
                %>
            </table>
            <br/>
    <%
        }
    %>


But I got an error saying javax.servlet.ServletException: java.sql.SQLException: Operation not allowed after ResultSet closed
So I tried using another variable for the ResultSet which is rs2 but I still receive the same error message
How do I fix this? Or is there any other method that I could use to do this?
The 2 tables that I use here has the same value on field User_Name and Transaction_Date as shown on Query3

Solution

  • As i can see you are using same Statement object for both queries. As documentation (http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html) says, By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.