#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    3
    Rep Power
    0

    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...
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Ohio
    Posts
    30
    Rep Power
    11
    Your problem looks to be here:


    conn.open(connString)
    Instead what you need to do is this:

    PHP Code:
    conn.Open() 
    That should solve your problem. Just remember to close your connection when you are done so you do not waste those system resources.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Ohio
    Posts
    30
    Rep Power
    11
    You might also have a problem with this line too:

    Set rs = cmd.Execute()
    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:





    PHP Code:
             set rsName    server.createobject("ADODB.Recordset")
             
    sqlstatement "SELECT ....."
             
    rsName.Open sqlstatementdbConnection01

                    
    if not rsName.eof and not rsName.bof then
                              rsName
    .movefirst

                          variableName1 
    rsName.fields("fieldName")

                     
    end if

              
    rsName.Close
              set rsName 
    nothing 
    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.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    120
    Rep Power
    11
    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.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    3
    Rep Power
    0
    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.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    120
    Rep Power
    11
    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.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2006
    Posts
    1
    Rep Power
    0

    Set nocount on


    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.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    3
    Rep Power
    0

    Thumbs up Update


    Originally Posted by LAF
    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!
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2005
    Posts
    3
    Rep Power
    0

    thank you!!!


    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!!
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2009
    Posts
    1
    Rep Power
    0
    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.
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    1
    Rep Power
    0
    Originally Posted by LAF
    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.
    Thankyou very much, this solve the problem in my case

IMN logo majestic logo threadwatch logo seochat tools logo