CODEWITHSUNDEEP
mysqljdbc
Rohan Singh Dhaka
Rohan Singh Dhaka

Reputation: 193

Operation not allowed after ResultSet closed VendorError:0

The below code is showing error:Operation not allowed after ResultSet closed i haven't closed any resultset still it is showing error. i am using one ResultSet inside other ResultSet, does that causing problem??

int n=li10.getSelectedIndex();
    final String n1=(String) dl10.elementAt(n);
    String n2=t52.getText();
    String n3="Select Budget,Count1 FROM User Where U_Name=' "+n2+"'";
    String n4="Select Price From Player_Attributes Where Player_Name='"+n1+"'";
    try{
    Connection con=DriverManager.getConnection("jdbc:mysql://127.0.0.1/BFPL","root","ilovepepsi");
    Statement st=con.createStatement();
    ResultSet rs1=st.executeQuery(n4);
    ResultSet rs=st.executeQuery(n3);
    while(rs1.next()){
    int pr=rs1.getInt("Price");
    while(rs.next()){
    int f=rs.getInt("Budget");
    int f1=rs.getInt("Count1");
    if(f1>0 && pr<f ){
    try{
    Connection con5=DriverManager.getConnection("jdbc:mysql://127.0.0.1/BFPL","root","ilovepepsi");
    PreparedStatement st2=con5.prepareStatement("Update User_Team Set Player"+f1+"=? Where User_Name='"+n2+"'");
    st2.setString(1,n1);
    st2.executeUpdate();
    JOptionPane.showMessageDialog(p13,"Table is updated");
    }
    catch(SQLException ae){
    System.out.println("SQLException:"+ae.getMessage());
    System.out.println("SQLState:"+ae.getSQLState());
    System.out.println("VendorError:"+ae.getErrorCode());
    JOptionPane.showMessageDialog(p13,"Error in submitting data!");
    }
    f1=f1-1;
    f=f-pr;
    try{
    Connection con4=DriverManager.getConnection("jdbc:mysql://127.0.0.1/BFPL","root","ilovepepsi");
    PreparedStatement st1=con4.prepareStatement("Update User Set Budget=? Count1=? Where U_Name='"+n2+"'");
    st1.setInt(1,f);
    st1.setInt(2,f1);
    st1.executeUpdate();
    con4.close();
    JOptionPane.showMessageDialog(p13,"Data is successfully inserted in database");
    }
    catch(SQLException ae){
    System.out.println("SQLException:"+ae.getMessage());
    System.out.println("SQLState:"+ae.getSQLState());
    System.out.println("VendorError:"+ae.getErrorCode());
    JOptionPane.showMessageDialog(p13,"Error in submitting data!");
    }

    }
    else{

    JOptionPane.showMessageDialog(p13,"Either your budget is not sufficient or you have reached maximum limit of buying players");

    }

Upvotes: 0

Views: 324

Answers (1)

BalusC
BalusC

Reputation: 1108722

You can open only one result set per statement. So if you open another one on the same statement, then any previously opened one would be implicitly closed. If you still try to access it, then exactly this exception will be thrown.

There are basically 2 solutions to your problem:

  1. Create 2 statements on the same connection and let them each open a single result set.

  2. Rewrite and merge the 2 loose SQL queries into a single SQL query returning exactly the desired results, so that you don't need to create multiple statements. The SQL JOIN clause may come in handy with this.

Unrelated to the concrete problem, you've another major problem in your code. It's leaking away DB resources. You're not explicitly closing all so far opened connections, statements and result sets and even not in the finally block. See also How often should Connection, Statement and ResultSet be closed in JDBC?.

Upvotes: 2

Related Questions