I have two tables in my database named "branch" and "course". I want to display contents of one column of each table in a select option. I can retrieve one column successfully but when i try to retrieve data from more than one table it gives me an Exception which says "result set is closed".
Here's my code:-
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<html>
<head>
<title>Type of Test</title>
</head>
<body>
<%
Connection con=null;
Statement s=null;
ResultSet r=null;
ResultSet r1=null;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:online_testing");
s=con.createStatement();
r=s.executeQuery("select * from branch");
r1=s.executeQuery("select * from course");
}
catch(Exception e)
{
response.setContentType("text/html");
out.println(e.toString());
}
%>
<form action="Decidetest" method="post">
<table align="center">
<tr>
<td>Select Branch:-</td>
<td>
<select name="branch">
<%
while(r.next()){
String codeValue = r.getString("code");
%>
<option value="<%=codeValue%>"><%=codeValue%></option>
<%
}
r.close();
%>
</select>
</td>
</tr>
<tr>
<td>Select Course:-</td><td>
<select name="branch">
<%
while(r1.next()){
String courseValue = r1.getString("course");
%>
<option value="<%=courseValue%>"><%=courseValue%></option>
<%
}
r1.close();
s.close();
con.close();
%>
</select>
</td>
</tr>
<tr><td><input type="submit"></td><td><input type="reset"></td></tr>
</table>
</form>
</div>
</body>
</html>
Try and make it 2 separate statements: