Problem with syntax?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

EManning - 21 Jul 2005 20:53 GMT

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.

Reply to this Message

aaron.kempf@gmail.com - 21 Jul 2005 22:32 GMT

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'"

Reply to this Message

Sylvain Lafontaine - 22 Jul 2005 00:39 GMT

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)
...

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC

> Using A2K. I have the following code:
>
[quoted text clipped - 25 lines]
>
> Thanks for any help or advice.

Reply to this Message

EManning - 22 Jul 2005 14:49 GMT

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.

> 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
[quoted text clipped - 51 lines]
> >
> > Thanks for any help or advice.

Reply to this Message