ResultSet not open. Operation 'moveToInsertRow' not permitted. Verify that autocommit is OFF

Question

i'm having a trouble with this error, when i clicked the button, it'll take the values of the labels and put it in the table from database

void showAll(){
    try{
        rs1 = stmt.executeQuery("SELECT * FROM BORROW_RETURN");
        while(rs1.next())
        {
            String bookpp = rs1.getString("name");
            String emailse = rs1.getString("email");
            String booktee = rs1.getString("book_title");
            String ser_no = rs1.getString("serial_no");
            String borr = rs1.getString("borrowed");
            String ret = rs1.getString("return");
            loginModel3.addRow(new Object[]{bookpp, emailse, booktee, ser_no, borr, ret});
        }}catch(SQLException err){
            System.out.print(err);
    }
}

and this is the connection to the connection to the database

void DoConnect1( ) {
               try{
                String host = "jdbc:derby://localhost:1527/Dafuq7";
                String uName ="Dafuq7";
                String uPass ="Dafuq7";
                con = DriverManager.getConnection(host, uName, uPass);

                //EXECUTE SOME SQL AND LOAD THE RECORDS INTO THE RESULTSET
                    stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                    ResultSet.CONCUR_UPDATABLE);
                String sql = "SELECT * FROM borrow_return";
                rs1 = stmt.executeQuery(sql);

                   }


    catch (SQLException err) {
        System.out.println(err.getMessage() );
    }
}

and upon clicking the button the said error occurs,

private void jButton6ActionPerformed(java.awt.event.ActionEvent evt) {                                         
   String ema = jLabel20.getText();
    String enm = jLabel21.getText();
    String booknm = bttl.getText();
    String snnnn = sernum.getText();
    dateFormat = new SimpleDateFormat("yyyy-MM-dd");
    String dates = dateFormat.format(date_borr.getDate());
    try {

        rs1.moveToInsertRow();
        rs1.updateString( "book_title", booknm );
        rs1.updateString( "serial_no", snnnn );
        rs1.updateString( "name", enm );
        rs1.updateString( "email", ema );
        rs1.updateString( "borrowed", dates );
        JOptionPane.showMessageDialog(null, "HAHA");
        loginModel3.addRow(new Object[]{names, booknm, snnnn, enm, ema, dates});

        con.setAutoCommit(false);
System.out.println(con.getAutoCommit());
rs1.insertRow( );
        stmt.close();
        rs1.close();

        stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
        String sql = "SELECT * FROM accounts";

        rs1 = stmt.executeQuery(sql);
        }
    catch (SQLException err) {
        System.out.println(err.getMessage() );
    }
}

Refer http://stackoverflow.com/questions/4049705/resultset-not-open-verify-autocommit-is-off-apache-debry — Anptk, Oct 16 '14 at 09:10
You should set autocommit off as soon as you create the connection. Don't wait until you have already issued your query; it is too late by then because your query was already auto-committed. — Bryan Pendleton, Oct 16 '14 at 14:00
please, can you be specific on where to put that, i don't know what to do right now, i'm searching for days to find a solution. so can you please. — DOODpls, Oct 16 '14 at 14:27

score 0 · Answer 1 · answered Oct 20 '14 at 17:31

You are setting autoCommit to false after your queries are really committed. You need to set it false once after you open connection or before start executing your queries.

        con = DriverManager.getConnection(host, uName, uPass);
        //EXECUTE SOME SQL AND LOAD THE RECORDS INTO THE RESULTSET
        stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
        ResultSet.CONCUR_UPDATABLE);
        String sql = "SELECT * FROM borrow_return";
        con.setAutoCommit(false);
        rs1 = stmt.executeQuery(sql);

score 0 · Answer 2 · edited Nov 20 '16 at 14:03

https://codedump.io/share/WK0Jtw7GEH3h/1/why-do-i-get-javasqlsqlexception-resultset-not-open-operation-39next39-not-permitted-java-derby-database

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.

ResultSet not open. Operation 'moveToInsertRow' not permitted. Verify that autocommit is OFF

2 Answers2