Stored procedure recordset not opening

[JamesM]

I have SQL Server 2005 as my database and Microsoft Access 2002. I'm trying
to open a recordset from a lengthy stored procedure in VBA. I have no
trouble returning data from simple SQL queries, but I keep getting an error
that the recordset is not open when attempting to get a recordset from a
particular stored procedure. The stored procedure executes and returns a
recordset in a Query window in SSMS.

I've been working on this off and on for a week. I would truly appreciate
any hints on where to look for the source of the problem. I have tried
several methods of calling the stored procedure. Every one returns the same
error. Here is the code I am currently using:

Dim oRsSales As ADODB.Recordset
Dim cmdSQL As New ADODB.Command
Dim cmdConn As New ADODB.Connection

Set cmdConn = New ADODB.Connection
cmdConn.ConnectionString = CurrentProject.Connection
cmdConn.Open

Set oRsSales = New ADODB.Recordset

With cmdSQL
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdStoredProc
.CommandText = "sp_SalesByRep"
.Parameters.Append .CreateParameter("@year1", adInteger, adParamInput, 1,
6)
.Parameters.Append .CreateParameter("@year3", adInteger, adParamInput, 1,
8)
End With

Set oRsSales = cmdSQL.Execute()


If Not (oRsSales.BOF = True And oRsSales.EOF = True) Then


On this last line I get the following error:

Operation is not allowed when the object is closed.

 

[Sylvain Lafontaine]

Probably because you forgot to put a SET NOCOUNT ON instruction at the very
beginning of your stored procedure (SP). Also, you shouldn't use the "sp_"
prefix for your own SPs; as this one has a special meaning for SQL-Server.

 

[Sylvain Lafontaine]

Also, you shouldn't use the New operator for dimensioning any complex
object; particularly when it's part of the ADO hierarchy. For example,
instead of:

Dim cmdSQL As New ADODB.Command

you should use:

Dim cmdSQL As ADODB.Command
Set cmdSQL = New ADODB.Command

Second, probably that you have already noticed but the cmdConn object is
totally useless here and finally - just to be sure that you know this - the
names (ie., "@year1" and "@year3") for the parameters have no importance at
all: what counts here is the *order* in which you create your parameters
objects; this must be done in exactly same order as the order of the
parameters as they are declared in the stored procedure. I the same way, if
you have any Return value at the end of the SP, this parameter must be the
first one to be declared when you create the parameters objects.

 

[JamesM]

No, I do have a SET NOCOUNT ON instruction at the very beginning of the
stored procedure.

Given this, do you have any other suggestions?

James

 

[JamesM]

Thanks for the additional information.

James

Also, you shouldn't use the New operator for dimensioning any complex
object; particularly when it's part of the ADO hierarchy. For example,
instead of:

Dim cmdSQL As New ADODB.Command

you should use:

Dim cmdSQL As ADODB.Command
Set cmdSQL = New ADODB.Command

Second, probably that you have already noticed but the cmdConn object is
totally useless here and finally - just to be sure that you know this - the
names (ie., "@year1" and "@year3") for the parameters have no importance at
all: what counts here is the *order* in which you create your parameters
objects; this must be done in exactly same order as the order of the
parameters as they are declared in the stored procedure. I the same way, if
you have any Return value at the end of the SP, this parameter must be the
first one to be declared when you create the parameters objects.

 

[Sylvain Lafontaine]

The first thing to do would probably have been to show the code of your SP.

Check the state of the recordset by first testing if an object has been
returned or not, using the IsObject() function or testing for Nothing (don't
remember which one you must use) and after that, see if the recordset is
closed or not by checking its State property. You must do this before
trying to test with EOF and BOF. These two instructions will work on an
empty recordset but not on a closed one or when there is no recordset at
all.

If there is a recordset that have been returned but it's closed, then check
is there is another one that follow by using the .NextRecordset method and
testing for Nothing.

if (Not rs is Nothing) then

Do While ((rs.State and adStateOpen) <> adStateOpen)

set rs = rs.NextRecordset()

if (rs is Nothing) then Exit Do

if (Not rs.EOF) then

' --- Your piece of code here ---

end if

Loop

End if

 

[Sylvain Lafontaine]

BTW, testing for Nothing or with IsObject() won't work if you are using the
syntaxe "Dim rs As New ADODB.Recordset".

 

[JamesM]

The first thing to do would probably have been to show the code of your SP.

Thank you. I will do so next time. I apologize if I am getting on your
nerves by not following a recommended posting format. I am not trying to be
difficult, but may be succeeding anyway. I'm fairly new to the idea of
posting code problems on a message board.

Check the state of the recordset by first testing if an object has been
returned or not, using the IsObject() function or testing for Nothing (don't
remember which one you must use) and after that, see if the recordset is
closed or not by checking its State property. You must do this before
trying to test with EOF and BOF. These two instructions will work on an
empty recordset but not on a closed one or when there is no recordset at
all.

Thank you for the tip. I will try this.

If there is a recordset that have been returned but it's closed, then check
is there is another one that follow by using the .NextRecordset method and
testing for Nothing.

if (Not rs is Nothing) then

Do While ((rs.State and adStateOpen) <> adStateOpen)

set rs = rs.NextRecordset()

if (rs is Nothing) then Exit Do

if (Not rs.EOF) then

' --- Your piece of code here ---

end if

Loop

End if

Thanks very much. I appreciate the assistance. I had been pulled off this
particular project for a few days but will get back to it this evening and
try your suggestions. Thanks again for taking the time to respond.

James

 

[td]

Sylvain, Thank you for these tidbits - stuff like this is VERY useful. But
1. why do you use Dim + SET instead of one step?
2. If you use the names of the Parms when setting them why do they need to
be set in the same order?
td

 

[Sylvain Lafontaine]

Sylvain, Thank you for these tidbits - stuff like this is VERY useful. But

1. why do you use Dim + SET instead of one step?

Because with the one step syntaxe, you're asking VBA to garantee you that
the variable will never be set to nothing and will always point to a valid
object; even if you try to set it yourself to nothing (directly or
indirectly by calling a function that might return a null (or nothing)
pointer under some circonstances instead of a valid object). Access will
add a test before every use of the variable to see if it points to nothing
and if yes, will recreate automatically a new object (each time).

2. If you use the names of the Parms when setting them why do they need to
be set in the same order?

Last time I checked, the names of the Parms are only used localy (in the VBA
code) to give you access to each item in the parameters collection by its
name (if you use this feature, of course). They are not used to determine
the relationship between each item in the collection and its associated
parameter in the subroutine call.