11 Replies - 13847 Views - Last Post: 17 July 2012 - 03:12 PM Rate Topic: -----

#1 astrodon  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 14-February 09

Operation not allowed when object is closed

Posted 16 June 2012 - 12:49 PM

    szConfigString = "Provider = SQLOLEDB;Data Source = " & gsz_IPSOURCE & ";User Id='****'; Password ='********';"
    
    sql = "USE ManatronWheelerCustom "
    sql = sql & "SELECT * FROM TaxDistrictRates "
    sql = sql & "WHERE "
    sql = sql & "area = '" & szArea & "' "
    sql = sql & "AND tax_year > " & iBaseYear & " "
    sql = sql & "order by tdl.tax_year desc;"
    
    db.Open szConfigString
    rstax.Open sql, db, adOpenKeyset, adLockReadOnly
    
    rstax.MoveFirst     '   creates error



The sql is correct and returns the expect records in SSMS

The RECORDSET IS OBVIOUSLY OPEN

Why then does the error

Quote

Operation is not allowed when the object is closed.


appear? I have used this code forever and works fine everywhere else.
Whiskey Tango Foxtrot over?

TIA

Is This A Good Question/Topic? 0
  • +

Replies To: Operation not allowed when object is closed

#2 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5498
  • View blog
  • Posts: 11,804
  • Joined: 02-June 10

Re: Operation not allowed when object is closed

Posted 16 June 2012 - 01:47 PM

View Postastrodon, on 16 June 2012 - 01:49 PM, said:

    rstax.Open sql, db, adOpenKeyset, adLockReadOnly



The RECORDSET IS OBVIOUSLY OPEN


No its NOT obviously open. You have issued a command to open it. What if you typo'd the name, would it still be open then?

I can tell my dog to fetch... But that doesn't mean has fetched.

See FAQ # 24 for database tutorials as well as building parameterized queries instead of that crappy string concatination. (Click the SHOW button below)


TOP most asked:
What does this error message mean?
FAQ 2: How do I debug
FAQ 3: How do I make Class1/Form1 talk to Class2/Form2


FAQ (Frequently Asked Questions - Updated May 2012
Spoiler



Was This Post Helpful? 0
  • +
  • -

#3 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 300
  • View blog
  • Posts: 1,773
  • Joined: 26-March 09

Re: Operation not allowed when object is closed

Posted 17 June 2012 - 01:49 AM

Similarly, it may have failed on the DB open also.
Was This Post Helpful? 0
  • +
  • -

#4 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Operation not allowed when object is closed

Posted 17 June 2012 - 07:27 AM

Try looking in your connection object's errors collection.
Was This Post Helpful? 0
  • +
  • -

#5 astrodon  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 14-February 09

Re: Operation not allowed when object is closed

Posted 17 June 2012 - 05:53 PM

DB.state = 1

Name is spelled correctly.

While I will look into the suggestion, it is doubtful the "crappy" concatenation is an issue...
Was This Post Helpful? 0
  • +
  • -

#6 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Operation not allowed when object is closed

Posted 18 June 2012 - 12:43 PM

Well, you've told us that your connection is open. Is rsTax similarly open? What is the contents of db.Errors?
Was This Post Helpful? 0
  • +
  • -

#7 astrodon  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 14-February 09

Re: Operation not allowed when object is closed

Posted 19 June 2012 - 05:50 PM

I finally got around to working on the sclpt this afternoon. I changed nothing and it is working as expected. No errors, no explanation as to why it would not work at all Saturday...

Thanks for the help. I will keep the db.errors suggestion in mind if it acts up again. Kind of creepy how no matter what I tried Saturday it would not work at all. I merely closed everything in disgust Saturday and simply re-opened it today with no changes and it works fine. ARGH!
Was This Post Helpful? 0
  • +
  • -

#8 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Operation not allowed when object is closed

Posted 20 June 2012 - 01:39 PM

Keep in mind that a connection goes through many layers of code, and each layer can possibly throw an error. The Error object will only contain the last error, and that might simply be something like "previous layer returned an error." If you examine the connection's errors collection, you will find all errors that are associated with a single attempt to open a connection.
Was This Post Helpful? 0
  • +
  • -

#9 astrodon  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 14-February 09

Re: Operation not allowed when object is closed

Posted 13 July 2012 - 02:24 PM

Problem was solved by adding CursorLocation = adUseClient to the connection object

    CONN.Open szConnString
    CONN.CursorLocation = adUseClient



Thanks
Was This Post Helpful? 0
  • +
  • -

#10 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Operation not allowed when object is closed

Posted 13 July 2012 - 04:17 PM

One possibility is that your database doesn't support keyset type recordsets, possibly because they are updatable. As soon as you changed the cursor location to client-side, your cursor type became static, which isn't updatable.
Was This Post Helpful? 0
  • +
  • -

#11 astrodon  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 14-February 09

Re: Operation not allowed when object is closed

Posted 17 July 2012 - 07:36 AM

View PostBobRodes, on 13 July 2012 - 05:17 PM, said:

One possibility is that your database doesn't support keyset type recordsets, possibly because they are updatable. As soon as you changed the cursor location to client-side, your cursor type became static, which isn't updatable.


Prior to that I tried Optimistic, ReadOnly. But I am wondering without telling the connection that I wanted the cursor on the client side (I am guessing the actual output is then cached on the local machine somewhere as opposed to being on the server) that it became inaccessible for manipulation beyond serialization en mass because of location?

A lot to learn this one has...
Was This Post Helpful? 0
  • +
  • -

#12 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Operation not allowed when object is closed

Posted 17 July 2012 - 03:12 PM

That's correct. The only client side cursor type that exists is static. If you think about it, it sort of makes sense: think about how many round trips you'd have to make to the server if the client side cursor had to be aware of changes made by other users. You are also correct that the data are cached in the client memory.

I've found that this is typically the only sort of cursor that I use. I also don't use any of the ADO methods that alter data. Keep in mind that you can do any sort of data manipulation that you like independently of the cursor, so the fact that a cursor is or isn't updatable isn't really as important as it might appear. Just use command objects and SQL Action queries (Add Update Delete) to change data. (Best to use stored procedures and use the command object to call them rather than putting "naked SQL" in your commands.) Then whenever you want, refetch the data by reopening your recordset. I'd rather have control of one way of doing things that works well than have to wander in the swamps of the multitude of ways to do things that ADO supports.

If you also set the LockType to adoLockBatchOptimistic, you can close the connection while keeping the recordset open. This "disconnected recordset" is the way that .Net cursors work, presumably because they are the most efficient way to work with data.

This post has been edited by BobRodes: 17 July 2012 - 03:15 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1