Servlet: Invalid Cursor Error on Update

I'm trying to update a column value by looping through the resultset.


ResultSet rs = myStatement.executeQuery("Select * from bulletin_board");
                       
out.println("<HTML>");
out.println("<HEAD><TITLE>Bulletin Board</TITLE><HEAD>");
out.println("<BODY>");

int myid;      
String myapproved = "";
                       
                       
while (rs.next())
{
myid = rs.getInt("bulletin_ID");
myapproved = "Yes";
                                         
                                myStatement.executeUpdate("UPDATE bulletin_board SET approved = '" + myapproved + "' WHERE bulletin_ID = " + myid + "");            
                               out.println("<p>Bulletin_id = " + myid + " ChkApproved = " + myapproved + "</p>");
      
}
                            out.println("</BODY></HTML>");


Error Message:

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]Invalid cursor state at sun.jdbc.odbc.JdbcOdbc
ScottyMacAsked:
Who is Participating?
 
refactorConnect With a Mentor Commented:
The problem could be that two Statement's are needed.   One cannot use Statement myStatement for the update at the same time it is still being used by the result of the Query.  

A book would say that one needs to complete any processing of the current ResultSet object before executing the same statement again.
0
 
hkalsiCommented:
How about using prepared statements..

sql = "update bulletin_board set approved = ? where bulletin_board = ?"

0
 
ScottyMacAuthor Commented:
Great stuff refactor, my buddy came to the same conclusion....it works fine now....thanks....
0
All Courses

From novice to tech pro — start learning today.