Database error

[jasonwucinski]

Hi all,
I've connected my program up to a database. When i issue one statement against the DB it works fine but if i issue more then one, i get an error. heres the partial code i have:

 private Statement rsStatement;
    private ResultSet rsDistricts;
    private ResultSet rsCountDistricts;
    private  Connection conn;
....

public void SetUpTree(){
        try {
        getRS("SELECT * FROM tblDistricts ORDER BY DistrictID");
        }
        catch (Exception e) {
           this.setColor(Color.red);           
           this.setLable("Error in SetUpTree1(): " + e.toString());
        }
        try {         
           
         int getCount=  CountMyRS("SELECT COUNT (DistrictID) FROM tblDistricts;");
            System.out.print("count" + getCount);     
            
        }
        catch (Exception e) {
           this.setColor(Color.red);
           this.setLable("Error in SetUpTree2(): " + e.toString());
        }  

    }
 public int CountMyRS(String SQL) throws Exception {
        try{
            conn = connectToDatabase("ADR_DB.accdb");
            rsStatement = conn.createStatement();
            rsCountDistricts = rsStatement.executeQuery(SQL);
            rsCountDistricts.next();
            int count=rsCountDistricts.getInt(1);
            conn.close();
            return count;
              }
        catch(Exception e){
           this.setColor(Color.red);
           this.setLable("Error in CountMyRS: " + e.toString());
           conn.close();
           return 0;
        }  
    }
    
        public void getRS (String SQL) throws Exception {
            conn = connectToDatabase("ADR_DB.accdb");
            rsStatement = conn.createStatement();
            rsDistricts = rsStatement.executeQuery(SQL);
            conn.close();
            rsStatement.close();
           
    }
    
    public Connection connectToDatabase(String dbName) throws Exception {
        if (conn != null) {
            return conn;
        }
        String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
        if (System.getProperty("java.vendor").equals("Microsoft Corp.")) {
            //Microsoft VM users
            driver = "com.ms.jdbc.odbc.JdbcOdbcDriver";
        }
        String curDir = System.getProperty("user.dir");       
        
        String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ="
                + curDir
                + "\\"
                + dbName;

        String username = "";
        String password = "";
        Class.forName(driver);    
        
        return DriverManager.getConnection(url, username, password);
    }

I think i'm closing all connections after use so I'm not sure what the problem is. If i just run one of the two methods (getRS or CountMyRS) they both work fine. it's just when i try to run them both one after the other. the error in nondescript: it in the second method run (depending on the order) and it says : java.sql.SQLException: General error. anyone have any ideas?

Thanks
jason

[doWhile]

Seems you are trying to use a Connection which has already been closed. conn is created in either method, then closed in that method. When you call connectToDatabase again in the next method call, conn is not null and returns that reference (which has already been closed). Easy fixes include recreating the reference, or closing only after you are finished with the connection

[jasonwucinski]

yup, that did it. i was worried about closing the connection and didnt bother to see if it was still open. thanks