Connection is busy with results for another hstmt

Question

I am trying to iterate ResultSet values to insert query in while loop. The code snippet is as follows:

    String sel="select roll_no from Nursery";
    rs=stmt.executeQuery(sel);
    stmt1=con.createStatement();
    int aa;
    while(rs.next())
    {
        aa=rs.getInt(1);
        stmt1.executeUpdate("insert into [Nursery_FirstTerminal_marks](roll_no)values("+aa+")");
        //stmt.close();
    }

    JOptionPane.showMessageDialog(null, "Data has been inserted");
}
    catch(Exception e)
    {
        System.out.println(e);
        // JOptionPane.showMessageDialog(null, e);
    }

but the below given error is thrown.

java.sql.SQLException: [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt

Can you please suggest how to get out of it..

Answer 1

This is caused by you have a opening result set open against the same connection. For example, you execute a SqlCommand to select all rows from a table, while the result set is not drained, you try to execute another SqlCommand using the same connection, you will hit this error message.

To solve this, you have two choices:

a. Make sure you read the rest data from the pending result set before you send the next SqlCommand.

b. Use MARS (Multiple Active ResultSet) connection setting to enable multiple active result set in a connection.