800a0e78 - Operation is not allowed when the object is closed

I get the error "ADODB.Recordset error '800a0e78' - Operation is not allowed when the object is closed" when I try to check if I've reached EOF with my returned recordset. It works perfectly in query analyzer, perhaps the parameters (smalldatetime fields) need to be formatted differently through ASP?



' DB Connection
Dim connString, conn, spName, cmd, rs
' dbConn() is a function to auto create the connection string - it works
connString = dbConn("MCRF", "Research_Support", "I")

Set conn = Server.CreateObject("ADODB.Connection")
conn.open(connString)

Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandType = 4 '// 4 means that the command is a stored procedure name
spName = "usp_myStoredProcedure"
cmd.CommandText = spName
cmd.Parameters(1) = "10/01/2001 12:00:01 AM"
cmd.Parameters(2) = "9/30/2003 11:59:59 PM"

' State is open.
Response.Write(cmd.State)

Set rs = cmd.Execute()

' Tried just in case...same error though
'Set rs = Conn.Execute ("EXEC usp_myStoredProcedure @BeginDate='10/01/2001 12:00:01 AM', @EndDate='9/30/2002 11:59:59 PM'")

' This is where it fails.
If NOT rs.EOF Then
' The rest of my page begins here...

Your problem looks to be here:




Instead what you need to do is this:



That should solve your problem. Just remember to close your connection when you are done so you do not waste those system resources.

You might also have a problem with this line too:



I'm really not the one to let you know how to fix this though.

I set my db calls in a different fashion. This is how I go about setting up a recordset:







dbConnection is where I set my connection to my db in a file I include on any page that makes a call to the database. Setting my recordsets up this way I have run into very few problems. along the way.

I think my earlier advice might help out some but I don't think it will get it working 100% for you. I'm sure someone else out there will have a more complete answer for you. Hope this helps some.

not real sure about this, but does smalldatetime support AM/PM format or does it use military time (ie, 5:00 PM = 17:00)?

you could try dropping the AM/PM and just doing it in 24 hr format - see if that works.

just a suggestion, 5% chance of me being right - but worth a try i suppose.

Thank you for the assistance everyone - it turns out a table in the stored procedure had a permissions problem. Our DBA is looking into the issue now, hopefully that will resolve it. The reason my code was failing was because an empty recordset was being sent back, if anyone knows a better way to trap that error than the rs.EOF (Which doesn't work with an empty recordset apparently -- please let me know.

i'm not sure of the State of a recordset if nothing is returned.... try a response.write of the recordset state -- if it's not Open then you can just check that.

I believe this will fix your problem. Just put in the stored procedure SET NOCOUNT ON and it removes the header information that sql sends before it actually sets the data that you loop through in asp. Do a search for SET NOCOUNT ON for a more percise explanation. This fixed our problem.

Please see above, it was simply a permissions issue on the table. Gotta love error handling with vbscript. Thanks for the assist though!

Thank you so much for this response. I've been banging my head trying to figure out what this problem was and all it took to fix it was to Set NoCount on setting in my storedprocedure.
Thanks for the help!!

Wow, thank you very much LAF. I've been hours working on this error. Simply putting Set Nocount ON on my Stored Procedure bright up my day.