ADODB error - Object was open 1

[keithf01]

Has anyone run into this?
I use the same recordset over in a processing loop by just closing it.


' CHECK RECORDSET STATE, IF OPEN THEN CLOSE
If objAdoMP08RecordSet3.State = adStateOpen Then
objAdoMP08RecordSet3.Close
End If

strQuery = "select pieces_per_pad_qty, green_style from design_screen_area_uv01 where screen_nbr = '" & strScreenNbr & "' and chip_style = '" & strChipStyle & "'"

' OPEN THE RECORD SET
objAdoMP08RecordSet3.Open strQuery


The problem is when it hits the recordset.open it throws an error: -2147217915 'Object was open.'

When I step through the code the value for
objAdoMP08Recordset3.state is 0 which is closed in the
line of code before setting the query.

The recordeset object only gets created once before the processing loop:



' ***** INSTANTIATE THE RECORDSET OBJECT *****
Set objAdoMP08RecordSet3 = New ADODB.Recordset
'PASS THE ACTIVE CONNECTION TO THE RECORDSET OBJECT
objAdoMP08RecordSet3.ActiveConnection = ObjAdoMP08Connection
'SET THE CURSORTYPE TO "STATIC"
objAdoMP08RecordSet3.CursorType = adOpenStatic



Thanks,
Keith

 

[kellyval]

Could you please put your code in the

tabs instead of making them blue - I can't read it against the purple background.

 

[keithf01]

Sorry.

Has anyone run into this?
I use the same recordset over in a processing loop by just closing it.

' CHECK RECORDSET STATE, IF OPEN THEN CLOSE 
If objAdoMP08RecordSet3.State = adStateOpen Then
    objAdoMP08RecordSet3.Close
End If
                        
strQuery = "select pieces_per_pad_qty, green_style from design_screen_area_uv01 where screen_nbr = '" & strScreenNbr & "' 
and chip_style = '" & strChipStyle & "'"
                        
' OPEN THE RECORD SET
objAdoMP08RecordSet3.Open strQuery

The problem is when it hits the recordset.open it throws an error: -2147217915 'Object was open.'

When I step through the code the value for
objAdoMP08Recordset3.state is 0 which is closed in the
line of code before setting the query.

The recordeset object only gets created once before the processing loop:

' ***** INSTANTIATE THE RECORDSET OBJECT *****
Set objAdoMP08RecordSet3 = New ADODB.Recordset
'PASS THE ACTIVE CONNECTION TO THE RECORDSET OBJECT
objAdoMP08RecordSet3.ActiveConnection = ObjAdoMP08Connection
'SET THE CURSORTYPE TO "STATIC"
objAdoMP08RecordSet3.CursorType = adOpenStatic

 

[gmmastros]

I quick google search suggests that you should set your recordset object to nothing before trying to use it again.

[google]Object was open[/google]

-George

"the screen with the little boxes in the window." - Moron

 

[kellyval]

I am wondering, has this worked for you in the past or is it a new idea that you cannot get to work? I am personally pretty stumped by this - if the recordset is being closed then it's closed... Maybe it's the way loops work in VB. You could try putting the connection inside the loop and also closing it at the end of the loop but ofc this is a bit more long winded :|

 

[Andrzejek]

I re-use the same recordset many times in my code, but when I open the rst I need to specify which connection I use:

[green]' OPEN THE RECORD SET[/green]
objAdoMP08RecordSet3.Open strQuery[blue], Cn[/blue]

Maybe that will help....?



Have fun.

---- Andy

 

[keithf01]

The weird thing is is that this is a program that has worked in the past. It's been running for at least 2 years.
It gets this error sometimes and works other times.
It only seems to get it when I'm running it in debug mode.
It's a scheduled task that works fine the rest of the time.
I have a big processing loop, of about 3000 records and the
recordset is closed and opened during each loop.

Thanks for the input.

Keith

 

[keithf01]

I guess that re-specifying the connection wouldn't be too much overhead but like I said, it normally works.

Keith

 

[genomon]

You can test for it and bypass if needed:
If rsRecset Is Nothing Then......

Two vultures board an airplane, each carrying two dead raccoons. The
stewardess looks at them and says, "I'm sorry, gentlemen, only one
carrion allowed per passenger."

 

[BobRodes]

What is the source of the error? (check the value for Also, keep in mind that subtracting the vbObjectError constant from -2147217915 yields 3589. A google search for error 3589 doesn't yield much, but a lot of Object defined errors are documented under their actual number before the vbObjectError constant is applied.

 

[strongm]

>A google search for error 3589 doesn't yield much

That's because the genuine error number really is 80040E05. You shouldn't really be subtracting vbObjectError from errors that come from other sources.

 

[dilettante]

There's a good chance you should be calling Execute on the Connection instead of calling Open on the closed Recordset anyway. I doubt this re-use is buying you much.

Still, the error looks strange. Is it possible that Records are in edited state while the Recordset is in immediate (not batch) update mode? I.e. are you missing calls to Update or CancelUpdate?

 

[BobRodes]

<You shouldn't really be subtracting vbObjectError from errors that come from other sources

What do you mean by "other sources" exactly? OLE DB?

 

[SBerthold]

I think it is a memory leak, or lack of releasing the used memory, in the connection object, which looks like it didn't cause as many problems under MDAC 2.7 as it does with MDAC 2.8. It has been known to happen in the past, depending on the OLE DB provider.

As gmmastros mentioned, after closing the recordset, set it to Nothing:

Set myrecordset = Nothing

This however may not always help, because the problem, or memory leak, or lack of memory release, may be happening in the OLE DB provider, and not the ADOX object.

I would therefore consider opening the recordset once, and then using the Recordset.Find method within the loop.

 

[dilettante]

I don't think we've seen enough of the code to tell what's going on here.

 

[BobRodes]

I've checked around using 80040e05, and I've found several people who have asked for help on the same problem and gotten no answers.

 

[strongm]

>What do you mean by "other sources" exactly?

At a bare minuimum, any COM interface. COM errors (HRESULTs) are more complex than simple error numbers (and there are plenty of standard DLLs whose functions return HRESULTs as well)

The problem is that because VB was designed to hide away the complexities of Windows initially and COM latterly, vbObjectError is very poorly documented. It is certainly never properly linked to COM, except in the very loosest sense when the documentation advises that you should use vbObjectError when raising errors in any objects that you have written. And at least MS make the point that "The resulting value [of adding vbOjectError to your user-defined error number] is the one you should document for your users", although with little explanation as to why.

We need to look to winerror.h to get the info we are interested in:
[tt]

// Values are 32 bit values layed out as follows:
//
// 3 3 2 2 2 2 2 2 2 2 2 2 1 1 1 1 1 1 1 1 1 1
// 1 0 9 8 7 6 5 4 3 2 1 0 9 8 7 6 5 4 3 2 1 0 9 8 7 6 5 4 3 2 1 0
// +---+-+-+-----------------------+-------------------------------+
// |Sev|C|R| Facility | Code |
// +---+-+-+-----------------------+-------------------------------+
//
// where
//
// Sev - is the severity code
//
// 00 - Success
// 01 - Informational
// 10 - Warning
// 11 - Error
//
// C - is the Customer code flag
//
// R - is a reserved bit
//
// Facility - is the facility code
//
// Code - is the facility's status code[/tt]

MSDN disagrees slightly with this, and says

An HRESULT value is made up of the following fields:

A 1-bit code indicating severity, where zero represents success and 1 represents failure.

A 4-bit reserved value.

An 11-bit code indicating responsibility for the error or warning, also known as a facility code.

A 16-bit code describing the error or warning.

and this actually seems to be the more accurate, in that we only need to worry about the high bit to see if we have an error or not

A definition of the facility codes is also given in winerror.h:
[tt]
#define FACILITY_NULL 0
#define FACILITY_RPC 1
#define FACILITY_DISPATCH 2
#define FACILITY_STORAGE 3
#define FACILITY_ITF 4
#define FACILITY_WIN32 7
#define FACILITY_WINDOWS 8
#define FACILITY_SSPI 9
#define FACILITY_SECURITY 9
#define FACILITY_CONTROL 10
#define FACILITY_CERT 11
#define FACILITY_INTERNET 12
#define FACILITY_MEDIASERVER 13
#define FACILITY_MSMQ 14
#define FACILITY_SETUPAPI 15
#define FACILITY_SCARD 16
#define FACILITY_COMPLUS 17
#define FACILITY_AAF 18
#define FACILITY_URT 19
#define FACILITY_ACS 20
#define FACILITY_DPLAY 21
#define FACILITY_UMI 22
#define FACILITY_SXS 23
#define FACILITY_WINDOWS_CE 24
#define FACILITY_HTTP 25
#define FACILITY_BACKGROUNDCOPY 32
#define FACILITY_CONFIGURATION 33[/tt]

These facilities are all predefined by Microsoft, apart from ITF, which is reserved for user-defined COM errors raised by interfaces, and Microsoft recommend that the values used for those errors range from 0x0200 to 0xffff (512 to 65535). Now, this is all beginning to look a bit familiar ...

Let's see, if the high bit is set because we are looking at an error condition ... &H80000000
oh, and the facility code for user-defined errors ... &H80040000

which is -2147221504

Ooh - that's vbObjectError ...

 

[BobRodes]

Outstanding! Nice piece of analysis; I've never been quite clear on that. However, I don't understand something. If you shouldn't use vbObjectError with any COM interface, that means that in your own classes you shouldn't use it, too. Where SHOULD you use it exactly?

 

[strongm]

No, no, no, I'm saying that you shouldn't subtract vbObjectError from HRESULTs that you get, because the HRESULT will be the documented error code - which is why you Google finds a fair amount of hits for -2147217915 (or &H80040E05) but not for 3589; the former is the correct value, the latter is not ...

As for your own code, you should always add vbObjectError to your status codes in your objects (if you are propogating the error to a client) to ensure that you are providing a proper HRESULT.

If you are raising errors and handling them within your object, then use what you like ...

 

[dilettante]

' User-defined error numbers should be between 513 and 65535
' plus vbObjectError Numbers below 513 are reserved.
' The vbObjectError is equivalent to FACILITY_ITF (&H80040000).
' You need to add this constant because everything made public
' in VB is on an interface.

PRB: CallByName Fails to Return the Correct Error Information

Seems to indicate that errors 0-512 (&H200) are reserved, even with vbObjectError.


I typically bias mine by &H4000 because I saw an indication that there is another reserved range as well. I've seen 4000 (decimal) used as the first user defined value too, but it strikes me as a mistake. I admit this is basically voodoo though, as I can't find a reference to cite.