No operations allowed after statement closed.

[Pranit Sonawane]

Hi all this is my code

Globally declared variables are

private Connection connection;
    private Statement statement;
    ResultSet rs = null;
    ResultSet rs1 = null;
    ResultSet rs2 = null;
    ResultSet rs3 = null;
    PreparedStatement ps = null;
    PreparedStatement ps1 = null;
    PreparedStatement ps2 = null;

jButton5.setEnabled(false);
        try {
            JFrame frame = new JFrame("Input Dialog Example 3");
            String favoritePizza = (String) JOptionPane.showInputDialog(frame,
                    "Select Option To Search",
                    "Search Options",
                    JOptionPane.QUESTION_MESSAGE,
                    null,
                    pizzas,
                    pizzas[0]);

            if (favoritePizza.equals("Date")) {
                String d = JOptionPane.showInputDialog("Enter Date");
                connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/erpdb", "root", "pranit");
                rs = statement.executeQuery("select id as 'ID',  date as 'Entry Date', name as 'Customer Name', amt as 'Received Amount' from payments where type = 'SL' and date = '" + d + "'");
                rs.beforeFirst();
                jTable4.setModel(DbUtils.resultSetToTableModel(rs));
                jTable4.setRowHeight(25);

            } else if (favoritePizza.equals("Customer Name")) {
                String n = JOptionPane.showInputDialog("Enter Customer Name:");

                connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/erpdb", "root", "pranit");
                rs = statement.executeQuery("select id as 'ID', date as 'Entry Date', name as 'Customer Name', amt as 'Received Amount' from payments where name like '" + n + "%'");
                rs.beforeFirst();
                jTable4.setModel(DbUtils.resultSetToTableModel(rs));
                jTable4.setRowHeight(25);

            } else if (favoritePizza.equals("Date & Customer Name")) {
                String d = JOptionPane.showInputDialog("Enter Date: ");
                String n = JOptionPane.showInputDialog("Enter Name: ");

                connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/erpdb", "root", "pranit");
                rs = statement.executeQuery("select id as 'ID',  date as 'Entry Date', name as 'Customer Name', amt as 'Received Amount' from payments where name like '" + n + "%' and date = '" + d + "'");
                rs.beforeFirst();
                jTable4.setModel(DbUtils.resultSetToTableModel(rs));
                jTable4.setRowHeight(25);

            }
        } catch (Exception e) {
            System.err.println(e);
        } finally {
            if (rs != null) {
                try {
                    CloseConn.close(rs);
                } catch (Exception e) {
                }

            }
            if (statement != null) {
                try {
                    CloseConn.close(statement);
                } catch (Exception e) {
                }

            }
            if (connection != null) {
                try {
                    CloseConn.close(connection);
                } catch (Exception e) {
                }

            }
        }

and I'm getting

No operations allowed after statement closed.

Where am I going wrong. Please Help

Thanks!

[K. Tsang]

In your code, you did:

connection = ....
rs = statement.executeQuery(....)

Where did you assign the variable statement?

You ought to use prepared statements, since you declared them

Just some suggestion:
your connection assignment can be outside the if statements because all connections are the same.
for sql, the where clause is specific to the if condition, the stuff before that is already know before hand as if all records are selected (or no where clause)
The rs.xxx and JTable's setXXX can be outside the if statement since all conditions will do it

Connection connection = ....;
PreparedStatement ps = null;
ResultSet rs = null;
String sql = "select id as 'ID', date as 'Entry Date', "
  +"name as 'Customer Name', amt as 'Received Amount' from payments ";
if (favoritePizza.equals("Date")) {
  String d = ....
  sql += "where type = ? and date = ? ";
  ps = connection.prepareStatement(sql);
  ps.setString(1, "SL");
  ps.setString(2, d);
  rs = ps.executeQuery();
} else if (....) {
  // some code
} else {
  // some code
}

rs.beforeFirst();
jTable4.setModel(DbUtils.resultSetToTableModel(rs));
jTable4.setRowHeight(25);
 

[Dave Tolls]

Where's that exception being thrown?

You might need to change your exception logging to:

catch (SQLException ex) {
    e.printStackTrace();
}

And note you don't want to get in the habit of catching all Exceptions, you should catch specific ones.

[Tim Holloway]

Listen to Dave.

If I were to inherit the task of supporting your code and it was eating exceptions without either handling them or reporting them, you'd better hope that you can run really, really fast.