JSP + Bean + Recordset + DropDown

I am in the process of creating a number of forms some/all of which will contain various drop down lists that need to be populated using data from the database.

I have constructed a Bean that takes an input parameter stating what static data I require, and I am able to include and execute this bean from my JSP page as follows

 <% ResultSet rs = sd.StaticData("TitleData"); %>

Its response as you can see from my code is a ResultSet that I am then trying to put into a Drop Down box, so something like :-

                <% while (rs.next()  )
                   {
                %>
                <option value=<%= rs("id") %> > <%= rs("title") %> </option>
                <%
                   rs.next();
                   }
                   rs.close;
                   set rs=nothing;
                %>
The initial bit of code seems to work offine, there is data in the appropriate table and a sequence that is used by apache to test Db Connections increments by one each time the process is run, signifying a connection.

When I add the second bit of code however, it bums out stating

javax.servlet.ServletException: Connection is closed.  Operation is not permitted.
      org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:673)
      org.apache.jsp.Registration.PersonalDetails_jsp._jspService(PersonalDetails_jsp.java:390)
      org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:133)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
      org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:311)
      org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:301)
      org.apache.jasper.servlet.JspServlet.service(JspServlet.java:248)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:856)


Any thoughts ??
gocraigAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gocraigAuthor Commented:
I have just added code as follows in the JSP

ResultSetMetaData = rsmd = rs.getMetaData();

And have then added a couple of table rows/cells and the code

rsmd.getColumnCount()
rsmd.getColumnName(1)
rsmd.getColumnName(2)

this worked fine and told me the names of the columns that I was expecting, so the result set is getting as far as the JSP.

I then added some manipulation, adding a further row/cell containing

rs.getString(1)

It bummed out again, same error.

Is this not allowed for some reason.The root cause given in the logs is

Connection is closed.  Operation is not permitted.
      org.postgresql.jdbc1.AbstractJdbc1ResultSet.next(AbstractJdbc1ResultSet.java:63)
      org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:135)
      org.apache.jsp.Registration.PersonalDetails_jsp._jspService(PersonalDetails_jsp.java:209)
      org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:133)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
      org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:311)
      org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:301)
      org.apache.jasper.servlet.JspServlet.service(JspServlet.java:248)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:856)

this has obvious references to PostgreSQL (my Db), is it because I am trying to manipulate the Resultset having passed it from Db to Bean to JSP ??
0
kokchoon78Commented:
The error shown because your database connection is closed. May i know where and when you close it ? is it in the sd.StaticData("TitleData"); ?

if so, you can retrieve all the data (rs.getString()) in the StaticData method and return the data as List or HashMap to your jsp page.

so, in your jsp :

<% List data = sd.StaticData("TitleData"); %>

<%
List singleData = null;
for( int i = 0 ; i < data.size() ; i ++ )
{
    singleData = (List) data.get(i);
%>
    <option value=<%= (String)singleData .get(1)%> > <%= (String)singleData .get(2)%> </option>
 <%
 }
 %>

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kennethxuCommented:
you cannot access you resultset after  you close your connection.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

kennethxuCommented:
gocraig, you might want to consider to re-grade, I B grade doesn't seem to be fair to kokchoon78.
http://www.experts-exchange.com/help/qnaFAQ.jsp#3
0
gocraigAuthor Commented:
Thanks for the advice Kenneth, but no.

As per the link you provided, a Grade A is only available when a an expert "provided you with a thorough answer or they provided you with a link to information that thoroughly answered your question".   kokchoon78 provided a guide, and asked for further information that would have enabled a fuller answer.

In fact from another forum I had got the answer that I needed, and implemented it before being notified of the answer here.  On that basis, I felt that a B was appropriate.

Anyway, if experts are going to start argueing about the grades given, doesnt that ruin the whole point.  Isnt this about asking for help and then grading the response you get, not being told you should give an A when you didnt think it appropriate ??

kokchoon78 has received a B, and has not responded that they are unhappy with that, so I will be leaving it there.
0
kennethxuCommented:
That's all right, it's your call as always :-) Just some facts that you might not aware of:
0. I gave such advise only to relatively new member and actully I was trying to help the asker not the expert.
1. A lot of experts will have a look at asker's grading history before they answer the question.
2. An A grade cost asker nothing, but do make difference in the points credit to the expert who tried to help.
3. There is no "thorough answer" exist for some types of question.
0
kokchoon78Commented:
Thank kennethxu, btw, is ok ;-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JSP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.