The following jsp page is only retrieving one row when it should be displaying several. Our 3 jsp gurus have been unable to find the bug. Can you ?
------------------------------------------------
<%@ page import="java.util.*" %>
<%@ page import="java.sql.*" %>
<%@ page import="com.ngs.database.*" %>
<HTML>
<BODY>
<TABLE>
<TR>
<TD>
Stats
</TD>
</TR>
<%
Connection con=null;
Statement stmt=null;
Statement stmt2=null;
int id = Integer.parseInt( request.getParameter("user") );
try
{
ConnectString cstr = new OracleThinConnectString("montross.ngw.com", "ngen01");
con = ConnectionFactory.buildConnection(cstr, "nn", "sh");
stmt = con.createStatement();
String totalsql = "select count(*) FROM request where userid = ?";
PreparedStatement statement = con.prepareStatement(totalsql);
statement.setInt(1, id);
ResultSet results = statement.executeQuery();
int total_requests = statement.results;
statement.close();
stmt2 = con.createStatement();
String sqlstmt = "select requestid, comments," +
"createdt, closedt, responsecount,userid " +
"FROM request " +
"where userid = ?" +
"order by createdt desc";
statement = con.prepareStatement(sqlstmt);
statement.setInt(1, id);
results = statement.executeQuery();
for (int i=1; results.next(); i++)
{
int db_reqid = results.getInt(1);
String db_comments = results.getString(2);
String db_createdt = results.getString(3);
String db_closedt = results.getString(4);
int db_responsect = results.getInt(5);
int db_user = results.getInt(6);
%>
<%
if (i <= total_requests)
{
%>
<tr>
<td>
<%=db_reqid%>
</td>
<td>
<%=db_comments%>
</td>
<td>
<%=db_createdt%>
</td>
<td>
<%=db_closedt%>
</td>
<td>
<%=db_responsect%>
</td>
</tr>
<tr><td align=center valign=middle colspan=5><hr></td></tr>
<%
} else
{
%>
<tr>
<td coslspan=2>
<font size=3 face="arial,helvetica">Total Requests: <b><%=total_requests%></b></font>
</td>
</tr>
<tr>
<td colspan=5 align=center>
<a href="/buyers/showbuyerprofile.html?user=<%=db_user%>">Return to Buyer Profile</a>
</td>
</tr>
<%
}
%>
<%
if ((i % total_requests) == 0)
{
%>
<%
}
}
} catch(Exception e)
{
%>
<tr>
<td>
<H3>No Request History Available</h3><br> <%=e.getMessage()%>
</td></tr>
<%
}
%>
</table>
</center>
</BODY>
</HTML>
