Operation is not allowed when the object is closed?

Hi,

I am writing a DLL which calls a SQL stored procedure. However when I try to use the recordset that is returned from the execution of the Stored Procedure I get the following error:

"Operation is not allowed when the object is closed"

However I have not closed the recordset or the connection. I know that my SQL SP works, because when I run it in Query Analyzer I get the expected results, and no errors are being returned from the stored procedure when run in the DLL. If someone could point out where I'm going wrong I would be very grateful, as I can't for the life of me work out what's going on.

In addition to the below main code chunk I declare:


Global rsResults As New ADODB.Recordset


...in a module as the recordset has to be used across 2 forms.

Main code chunk:

With cnnCRMIConnection
.Properties("Data Source") = "SellerDetails"
.Open
End With


With cmmCRMICommand
.CommandText = "sp_CRMIRespond_G01"
.CommandType = adCmdStoredProc
.ActiveConnection = cnnCRMIConnection
.Parameters.Refresh
.Parameters(1) = objRespCurrentCompl.Fields(MIAC).Value
.Parameters(2) = Forename
.Parameters(3) = Surname

Set rsResults = New ADODB.Recordset
rsResults.CursorLocation = adUseClient
rsResults.Open cmmCRMICommand, , adOpenStatic

intQueryResult = .Parameters(0)
End With

Two main points, don't use a Global recordset, use Public. Global is an old keyword no longer supported in .Net and future languages..so get into the habit of using Public..it has the same scope. And, why are you setting a New instance of the recordset twice. What you want is this:

Public rsResults as ADODB.Recordset

then in your code, declare a new instance

Set rsResults = New ADODB.recordset

and make sure you ALWAYS close the recordset when you are finished with it...same goes with your ADO Connection.

Thanks MKoslof,

I have tried this and I still get the same error message, really have no idea now! I still can't work out why the recordset seems to close even though I open it using...


rsResults.Open cmmCRMICommand, , adOpenStatic

Thanks for your help, just worked out what was going on - it was a 'feature' of SQL. Thanks again!

Glad you got it to work, good luck