Error occure while using two resultset in same table

Question

I am a beginner. In my project I am using Java and Mysql. While doing a code I got an error. That code is mentioning below

<%
       ResultSet rs=s.fetchtask(userid);
       while(rs.next())
       {
%> 
         <table border="1">
             <tr><td>
              <% int qnid=(rs.getInt("question_id")); 
                 ResultSet rs1=s.fetchqn(qnid);
                 String qn=rs1.getString("question");
              %> 
                  <% out.println(qn);%>
               </td></tr>
         </table>
      <%
          }
       %>

But when I am not using the second fetch its working

<%
       ResultSet rs=s.fetchtask(userid);
       while(rs.next())
       {
%> 
         <table border="1">
             <tr><td>
              <% int qnid=(rs.getInt("question_id"));     
              %> 
                  <% out.println(qnid);%>
               </td></tr>
         </table>
      <%
          }
       %>

When I am using this code there is no problem. Please somebody help me. The error showing is

org.apache.jasper.JasperException: An exception occurred processing JSP page /setter.jsp at line 142

 139:                        // ResultSet rs1 = st.executeQuery("SELECT * FROM temp_qb  WHERE question_id="+qnid+"");
 140:                         ResultSet rs1=s.fetchqn(qnid);
 141:                           //  ResultSet rs1=s.fetchqn(qnid);
 142:                         String qn=rs1.getString("question");
 143:                          %> 
 144:                          <% //out.println(qn);
 145:                          %>

What is `s` with the method `fetchqn(int)`? I'd bet that's where the problem lies. — MrLore, Aug 27 '13 at 19:18
its the object created for the java page where the data base queries are written — Salini L, Aug 27 '13 at 19:23
Please ignore JSP for now. JSP has got completely nothing to do with your concrete problem and is only making things to look complicated and confusing. Put that Java/JDBC code in a normal Java class with a method which returns `List`. Test that normal Java class using another normal Java class with a `main()` method. Once you get the desired functionality to work, just import and call the very same normal Java class in a servlet (or scriptlet if you want to practice a practice which is discouraged more than a decade ago). See also http://stackoverflow.com/q/5003142/ — BalusC, Aug 27 '13 at 19:25
@Salini can you please show where you created the object for `s` ? — Suresh Atta, Aug 27 '13 at 19:32
this code is given at the top(inside body tag)of the same page. — Salini L, Aug 28 '13 at 04:26
The link in my previous comment was not posted for decoration. Click it. — BalusC, Aug 28 '13 at 14:09

score 1 · Accepted Answer · answered Aug 29 '13 at 19:28

I have solved it. The problem was 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. SO by using another statement it can be solved.

score 0 · Answer 2 · answered Aug 27 '13 at 19:57

You are not checking the result set to make sure you have the data. That could lead to exceptions.

With so little to go on, I suggest you try this and see if you still get the same error.

<%
       ResultSet rs=s.fetchtask(userid);
       while(rs.next())
       {
%> 
         <table border="1">
             <tr><td>
              <% int qnid=(rs.getInt("question_id")); 
                 ResultSet rs1=s.fetchqn(qnid);
                 while(rs1.next())
                 { 
                    String qn=rs1.getString("question");
                    out.println(qn);
                 } 
              %>
               </td></tr>
         </table>
<%
       }
%>

Also make sure you are using the correct column name to fetch the data from the result set.
Take care to close the result set and db connections properly once you are done using them.

Finally,

Please avoid using scriplets if you are building something new.

It is also getting error in the first while loop, means on the line while(rs.next()) — Salini L, Aug 28 '13 at 04:38
@Salini it will be great if you **edit your question** and post the stacktrace of the exception thrown in your `ResultSet rs=s.fetchtask(userid);` — Luiggi Mendoza, Aug 28 '13 at 05:18

Error occure while using two resultset in same table

2 Answers2