Multiple ResultSet Problem

[pawan768]

I use two ResultSet , One in the another one.

try
        {
    con1 = DriverManager.getConnection ("jdbc:odbc:java");
                        smt1=con.createStatement();
    if(sele.equals("ALL"))
        {
			rs=smt.executeQuery("select * from Stations where State='"+st+"'");
                        }
    else
        {
			rs=smt.executeQuery("select * from Stations where City='"+sele+"'");
                        }
        while(rs.next())
{
           stid=rs.getString(1);
           ofid=rs.getString(2);
           name=rs.getString(3);
           num=rs.getString(4);
           des=rs.getString(5);
           dis=rs.getString(6);
           %>
<tr style="border: blue dotted thick">
    <td style="border: blue dotted thin" align="center" ><a style="color: red;" href="view.jsp?<%=stid%>" ><%=stid%></a></td>
    <td style="border: blue dotted thin" align="center"><%=ofid%></td>
    <td style="border: blue dotted thin" align="center"><%=dis%></td>
    <td style="border: blue dotted thin; " align="center">
        <%=name%> </td>
    <td style="border: blue dotted thin" align="center"><%=num%></td>
    <td style="border: blue dotted thin" align="center"><%=des%></td>
    <%
rs1=smt.executeQuery("Select Count(*) from Officer where Station_ID='"+stid+"'");
while(rs1.next()){
coun=rs1.getString(1);

}
%>
    <td style="border: blue dotted thin" align="center"><%=coun%></td>
</tr>

  <%
   }
    }

catch(Exception ex)
        {
    out.println(ex);
        }

But it give an Error -ResultSet Closed ..

Please Help me out , and tell me how use Resultset Within the Resultset????

[masijade]

Because you are using the same statement to open multiple result sets, see the API docs for Statement where it clearly states that opening a second resultset will automatically close the first.

[pawan768]

Because you are using the same statement to open multiple result sets, see the API docs for Statement where it clearly states that opening a second resultset will automatically close the first.

But need some data coming from the another table! So how it come

[masijade]

By using another statement object, obviously.

[pawan768]

By using another statement object, obviously.

I done it already but not working properly ! ( Same Error)

[masijade]

Or by learning some SQL and doing a join.

[masijade]

I done it already but not working properly ! ( Same Error)

Then you haven't done it correctly. Post that,hopefully without scriptlets.

[pawan768]

Then you haven't done it correctly. Post that,hopefully without scriptlets.

Sir , Now i use JSTL SQL for the 2nd Table Please tell me how to use the String value in the JSTL SQL statement

String ofid=null;
        String name=null;
        String num=null;
        String des=null;
        String dis=null;
        String coun=null;
       
try
        {
  
    if(sele.equals("ALL"))
        {
			rs=smt.executeQuery("select * from Stations where State='"+st+"'");
                        }
    else
        {
			rs=smt.executeQuery("select * from Stations where City='"+sele+"'");
                        }
        while(rs.next())
{
           stid=rs.getString(1);
           ofid=rs.getString(2);
           name=rs.getString(3);
           num=rs.getString(4);
           des=rs.getString(5);
           dis=rs.getString(6);
           %>
<tr style="border: blue dotted thick">
    <td style="border: blue dotted thin" align="center" ><a style="color: red;" href="view.jsp?<%=stid%>" ><%=stid%></a></td>
    <td style="border: blue dotted thin" align="center"><%=ofid%></td>
    <td style="border: blue dotted thin" align="center"><%=dis%></td>
    <td style="border: blue dotted thin; " align="center">
        <%=name%> </td>
    <td style="border: blue dotted thin" align="center"><%=num%></td>
    <td style="border: blue dotted thin" align="center"><%=des%></td>
    <c:set var="x" value=""></c:set>

    <s:query var="r" sql="Select Count(*) as sum from Officer where Station_ID='<%=stid%>'">


        </s:query>

        <c:forEach var="row" items="${r.rows}">

<td style="border: blue dotted thin" align="center"><c:out value="${row.sum}"></c:out></td>
        </c:forEach>
</tr>

  <%
   }
    }

catch(Exception ex)
        {
    out.println(ex);
        }
        }