Storing The ResultSet

[Raymond O'Leary]

Hey Guys me again!
Here I'm trying to store the sole result into a string called ans, and use the string in another query. as dar as i can tell the ans string isn't getting any values? Can anybody shed some light on this? Thanks guys.

 <% Connection con = null;
try{

     Class.forName("oracle.jdbc.driver.OracleDriver");
     con=DriverManager.getConnection("jdbc :-o	 racle:thin:@witnt07.wit.ie:1521 :-o	 rawit","x", "x");
     Statement st=con.createStatement();
     String UName=(String) session.getAttribute("username");
     String strSQL = "SELECT GROUP_ID FROM GROUPVIEW WHERE STUDENT_NUMBER = '" + UName + "'";
   
     ResultSet rs=st.executeQuery(strSQL);
     String ans;
while(rs.next())
{
ans=(String)rs.getString(1);
     }
     rs.close();
     Statement st1=con.createStatement();
     ResultSet rs1= st1.executeQuery("SELECT * FROM GROUPVIEW WHERE GROUP_ID = '" + ans + "'");         

out.println("<font color=midnightblue><H3><B>Project Group Details</b></h3><p>");
out.println("<table border=1 width=400>");
      out.println("<TR><td width=50 align=center><b>Group ID</b></td>");
      out.println("<td width=50 align=center><b>Student Number</b></td>");
      out.println("<td width=50 align=center><b>Student First Name</b></td>");
      out.println("<td width=50 align=center><b>Student Last Name</b></td>");
out.println("<td width=50 align=center><b>Student Last Name</b></td>");
      while (rs.next())
      {
        out.println("<TR><td width=50>"+ rs1.getString(1));
out.println("<td width=50>"+ rs1.getString(2));
out.println("<td width=50>"+ rs1.getString(3));
out.println("<td width=50>"+ rs1.getString(4));
        out.println("</td><td width=50>"+ rs1.getString(5) +"</td></tr>");
      }
      rs1.close();
      out.println("</table>");
      con.close();
             }
 

[eammon bannon]

Does your SQL actually return anything? Have you tried logging what value UName has?
Also, in amongst your out.println stuff, you seem to be trying to use the closed ResultSet rs - this might be where your problem is.

[Raymond O'Leary]

Yeah it returns a value, my problem is putting that value into the string ans and using that value in a further sql statement.

[eammon bannon]

And your second query? Have you tried running it against the DB?
You still seem to be trying to use a closed ResultSet too...

[Raymond O'Leary]

Yeah second query works perfectly
I put this in batween the selects, and the value is printed out.

 while (rs.next()){
out.println(rs.getString(1));
}

Its this bit here! Does it look correct?
[ CODE]
String ans=null;
while(rs.next())
{
ans = rs.getString(1);
}
rs.close(); [/CODE]
and thanks for pointing out the closed rs1 for me.

[Raymond O'Leary]

Ah its working now.
Thanks for your help buddy.

[Stefan Wagner]

strSQL = "SELECT GROUP_ID FROM GROUPVIEW WHERE STUDENT_NUMBER = '" + UName + "'";
strSQL2= "SELECT * FROM GROUPVIEW WHERE GROUP_ID = '" + ans + "'");    

Why don't you use directly:

strSQL2= "SELECT * FROM GROUPVIEW WHERE STUDENT_NUMBER = '" + UName + "'";

[Raymond O'Leary]

All working now thanks for the help guys.
Ray