Problem with syntax?

[EManning]

Using A2K. I have the following code:

Dim rstSSN As ADODB.Recordset

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "spFindResidentInRotation"

Set prm = cmd.CreateParameter("@AcademicYear", adVarChar, adParamInput,
9, _
Me.cboAcademicYear.Column(1))
cmd.Parameters.Append prm

Set prm = cmd.CreateParameter("@SSN", adVarChar, adParamInput, 9, _
Me.cboResidents.Column(0))
cmd.Parameters.Append prm

Set rstSSN = cmd.Execute()

If rstSSN.BOF And rstSSN.EOF Then
<snip>
End If

I get a 3704 error "operation is not allowed when the object is closed".
Can someone tell me why I'm getting this error, please? I have similar
coding in another part of my adp that works just fine.

Thanks for any help or advice.

 

[aaron.kempf]

for starters, you should just use CurrentProject.connection in order to
do that..

so set cmd.ActiveConnection = currentProject.connection instead and it
should work

why dont you just go Docmd.RunSQL "EXEC mySproc, myparam1,
'mytextParam2'"

 

[Sylvain Lafontaine]

You must have forgotten to put the SET NOCOUNT ON statement at the beginning
of the SP or you have an ANSI warning (or you have both of them) about Null
value(s) inside an aggregat function or a Set. Run your SP in Query
Analyser and look in the message tab section.

Use something like the following to move past the closed message's
recordset(s):

Do While (rs.State <> adStateOpen)
set rs = rs.NextRecordset()
if (rs is Nothing) then Exit Do
Loop

if (Not rs is Nothing) then
if (Not rs.EOF) then
' ---- Make your job here ----
end if
end if


Instead of (rs.State <> adStateOpen) you can also use a bit comparaison to
make things sure but I'm not sure about the VBA syntax; it should be
something like:

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

 

[EManning]

Thanks to both of you for taking time to reply.

It worked fine after putting SET NOCOUNT ON in the sp. But why?
(rhetorical question). I have virtually identical code in another module
and it works just fine without NOCOUNT. Both open rstSSN and look for the
occurrence of an SSN in the resultset. I read up on NOCOUNT but don't
really understand why I need it in one part but not the other. I'll do some
more reading and post a question if I can't figure it out.