Hi Sasi and Marada,

Thanks for the help.  It worked fine.
Mystery solved.

Cheers,

Takuya

-----Original Message-----
From: Sasi [mailto:sasisundar@h...]
Sent: Tuesday, April 10, 2001 6:39 AM
To: Pro_JavaServer_Pages
Subject: [pro_jsp] Re: Multiple ResultSet


Hello!,

Use two different statement variables instead of one.....in your case you 
are using the same statement variable for both queries...in this case one 
query will be closed automatically when another query is done with the 
same statement variable.....

 try this:

    Connection con=DriverManager.getConnection("jdbc:odbc:.....");
    Statement stmt1=con.createStatement();
    Statement stmt2=con.createStatement();
    ResultSet rs = stmt1.executeQuery("SELECT * FROM tasks T INNER JOIN
    employees E ON T.employee_id = E.employee_id;");
    while ( rs.next() )
    {
      out.println("<TR>");
      out.println("<TD>" + rs.getInt("task_id") + "</TD>");
      out.println("<TD>" + rs.getString("task_description") + "</TD>");
      out.println("<TD>" + rs.getString("employee_last_name") + " " +
     rs.getString("employee_first_name") + "</TD>");
      out.println("<TD>" + rs.getDate("task_due_date") + "</TD>");
      out.println("<TD>");
      out.println("<SELECT>");

     ResultSet rs2 = stmt2.executeQuery("SELECT * FROM tasks_status;");
     while ( rs2.next() )
     {
      out.println("<OPTION>" + rs2.getString("task_status_name") +
    "</OPTION>");
     }
     out.println("</SELECT>");
     out.println("</TD>");
     out.println("<TD> </TD>");
     out.println("</TR>");
    }

Cheers!

Ms. Sasi Sundar

> Hi guys,
> 
> I am creating a task management intranet page and having a bit of a 
problem
> with using multiple resultsets.
> What I am trying is to do is to produce a list of all existing tasks 
with a
> list box for task status which allows the user to update it.  Therefore, 
i
> need one resultset for all existing tasks on the database and another
> resultset for all task status.
> 
> What happens when i run the code below is that it only shows the first 
task
> and ignore the rest of the tasks.  I've tried commenting out the rs2 and 
it
> just works fine and shows all tasks.
> 
> Am I not supposed to nest a resultset inside another?
> Please have a look.
> 
> Cheers,
> 
> Takuya
> 
> 
> 
> 
> ResultSet rs = statement.executeQuery("SELECT * FROM tasks T INNER JOIN
> employees E ON T.employee_id = E.employee_id;");
> while ( rs.next() )
> {
>   out.println("<TR>");
>   out.println("<TD>" + rs.getInt("task_id") + "</TD>");
>   out.println("<TD>" + rs.getString("task_description") + "</TD>");
>   out.println("<TD>" + rs.getString("employee_last_name") + " " +
> rs.getString("employee_first_name") + "</TD>");
>   out.println("<TD>" + rs.getDate("task_due_date") + "</TD>");
>   out.println("<TD>");
>   out.println("<SELECT>");
>   ResultSet rs2 = statement.executeQuery("SELECT * FROM tasks_status;");
>   while ( rs2.next() )
>   {
>     out.println("<OPTION>" + rs2.getString("task_status_name") +
> "</OPTION>");
>   }
>   out.println("</SELECT>");
>   out.println("</TD>");
>   out.println("<TD> </TD>");
>   out.println("</TR>");
> }
> 
> 
>