[isaiahbn]

i have a method named getTimings which is used to get time slots store in my database. its in a class called DataBase whenever i run it directtly from the class its implemented in it works perfectly but whenever i call it from another class (graphical user interface ) i keep getting this error
"com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed."
this is the code for the method

public synchronized ResultSet getTimings(String strDate, String strDoctor)
    	throws SQLException
    	{
    		
    		try{
    			 String SQL = "select time_slots from dbo.Timing"
    		    		    + " where timing_id not in " + " ("
    		    		    + " select timing_id from dbo.Appointment "
    		    		    + " inner join Staff on dbo.Appointment.staff_id = dbo.Staff.staff_id"
    		    		    + " where dbo.Appointment.staff_id = '" + strDoctor
    		    		    + "' and appt_date = '" + strDate
    		    		    + "' and active_or_deleted=1" + " )";   //sql query
    			 System.out.println(SQL);
    			    rs = stmt.executeQuery(SQL);   //Resultset object containing query result
    			    return rs;   //return result set
    		}
    		catch(SQLException e)
    		{
    			e.printStackTrace();
    			return null;
    		}
    		
    	}

any suggestions on how to solve this error. i found one where i have to create a new connection each time i want to use it but is there a better way of handling this??

[Martyr2]

This is simply stating that "stmt" hasn't been opened or was closed before this function was called. Look back in your program to make sure you opened up stmt and that you didn't close it after you ran some other query with it.

I think the error is pretty self explanatory.

[baavgai]

You should open and close in one pass. Do NOT leave the connection open.

Also, use bind variables! Building SQL like that is very, very bad.

I'm going to guess this is Java?

protected Connection getConnection() throws SQLException {
	// you code here
}

// a very handy bit of code to have...
protected void close(Connection conn, Statement stmt, ResultSet rs) {
	if (rs!=null) try { rs.close(); } catch (SQLException e) { }
	if (stmt!=null) try { stmt.close(); } catch (SQLException e) { }
	if (conn!=null) try { conn.close(); } catch (SQLException e) { }
}

public synchronized List<String> getTiings(String strDate, String strDoctor) throws SQLException {
	List<String> result = new ArrayList<String>();
	Connection conn = null;
	PreparedStatement prep = null;
	ResultSet rs = null;
	try {
		conn = getConnection();
		prep = conn.prepareStatement(
			"select time_slots from dbo.Timing"
			+ " where timing_id not in " 
			+ " ("
			+ " select timing_id from dbo.Appointment "
			+ " inner join Staff on dbo.Appointment.staff_id = dbo.Staff.staff_id"
			+ " where dbo.Appointment.staff_id = ?"
			+ " and appt_date = ?"
			+ " and active_or_deleted=1" 
			+ " )");
		prep.setString(1, strDoctor)
		prep.setString(2, strDate)
		rs = prep.executeQuery()
		while(rs.next()) { list.add(rs.getString(1)); }
	} finally {
		close(conn, prep, rs);
	}
	return result;
}

Also, a quick note on that query...

select time_slots 
	from dbo.Timing
	where timing_id not in (
		select timing_id 
			from dbo.Appointment 
				inner join Staff 
					on dbo.Appointment.staff_id = dbo.Staff.staff_id
				where dbo.Appointment.staff_id = ?
					and appt_date = ?
					and active_or_deleted=1
		)

A subquery for a not it? Ouch. Slowest way to go. I'd try:

[code]
select a.time_slots 
	from dbo.Timing a
		left outer join (
			select distinct timing_id 
				from dbo.Appointment a
					inner join dbo.Staff b
						on a.staff_id = b.staff_id
							and a.staff_id = ?
							and appt_date = ?
							and active_or_deleted=1
		) b
			on a.timing_id = b.timing_id
	where b.timing_id is null

Hope this helps.

[Goodfix86]

This might not be applicable, however...

Earlier on you mentioned that this works in the base class, but not when you call it from another. If the base class was created in a different project and the connection string is established in the config file, then you must manually copy and paste the connection string in the orginal project's config file into the new new project's config file. I guess I shouldn't say must. You could also use one one of the connection string builders and send it into the connection's constructor method.

I agree with the previous posts. Connections can be pretty valuble. You should open and close them as needed. I don't see a good reason why the connection would already be open when it enters this function.

You could also wrap your connecction open statement (stmt.Open();?) inside of a try catch and send out a message box to warn you if your connection is equal to null. That might help study the program's behavior.