'Operation not allowed' when doing .close

[PeteJohnston]

I'm opening a recordset with a different selection so I'm checking first whether it is already open before closing it.

    If dteData.rsAgentDetails.State = adStateOpen Then
        dteData.rsAgentDetails.Close
    End If
    dteData.AgentDetails txtAgentID

The recordset is open according to the values of dteData.rsAgentDetails.State and adStateOpen so I would think it would be happy to do the Close but instead I get the error message

Operation is not allowed in this context

I know it was working yesterday so I must have done something this morning to screw it up but I can't think what I've done. Anyone have any ideas?

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[Trevil]

You didn't show the open statement, so what options are you specifying?

Maybe cursor options (depending also on what you're doing to the recordset).

"Hmmm, it worked when I tested it....

 

[PeteJohnston]

I am using the data environment designer and this is a command (basically just an SQL SELECT) called AgentDetails which has a parameter that is just a WHERE clause to select on a unique agent ID. The dteData.AgentDetails txtAgentID statement executes the command and effectively "opens" a recordset called rsAgentDetails which you can refer to in exactly the same way as a normal recordset. I changed the command to open using optimistic record locking but nothing else apart from that.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[Trevil]

Not for a close, but I did come across mention of the need to set the cursor type correctly. But since you probably didn't mess with that I'm not sure the cause.


"Have a great day today and a better day tomorrow!

 

[zarkon4]

If you have changes made to any of the fields in the record set then you cannot close it until an update is performed
or a cancel on the update is performed.

 

[gmmastros]

An ADO recordset could have several states, not just open or closed.

adStateClosed
adStateOpen
adStateConnecting
adStateExecuting
adStateFetching

I don't know what your exact problem is, but I would check to make sure the recordset is not connecting, executing, or fetching. I suspect that any of these states may be causing your problem. At least it's something to check for.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[PeteJohnston]

zarkon4
Spot on. I had a button that refreshed the controls on a form from a database recordset.

dteData.AgentDetails Agent.Agent
For Each ctrl In Me
    If ctrl.DataMember = "AgentDetails" Then
        Set ctrl.DataSource = dteData
        If Not Error Then ctrl.Refresh
    End If
Next
t

If I had made a change to one of the fields that was when I got the error message. I added

dteData.rsAgentDetails.CancelUpdate

and it solved the problem.

George
Using the data environment establishes the connection without me needing any code so adStateConnecting shouldn't be a problem. In this bit of code I'm just retrieving a single record so adStateExecuting and adStateFetching shouldn't ever be true so I should (hopefully) only have to deal with adStateClosed and adStateOpen


Everyone - thanks for your help

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[zarkon4]

Your welcome.