Reusing Command Object Redux... Run-time Error 3021 
Author Message
 Reusing Command Object Redux... Run-time Error 3021

I noticed this thread from late July.
I was (am still) having the same problem -- trying to reuse a
command object.  I tried the suggested code, and am still
encountering a run-time error.  The error occurs on the 2nd
time that I try to execute a stored procedure with new parameters.

Apparently, the SP executes, but when I try to navigate the
recordset with MOVEFIRST, I get:

"Run-time error '3021':
Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record."

Any ideas?  The code is below.  The connection and the command are
created in the class initialization code, and then when info is
submmitted from a form, the subroutine, GetZipCodes, is called.

TIA,
Rusty Alderson

'----------------------------------------------------------------
Private Sub Class_Initialize()

'----------------------------------------------------------------
' Open connection.
'----------------------------------------------------------------

    Set cnn1 = New ADODB.Connection
    strCnn = "Provider=SQLOLEDB.1;Password=<omitted>;Persist Security
Info=True;User ID=sa;Initial
Catalog=Geography;Data Source=ALDERSON\THIRD_COAST"
    cnn1.Open strCnn
    cnn1.CursorLocation = adUseClient

'----------------------------------------------------------------
'  Open command object
'----------------------------------------------------------------

    Set cmdZipProximity = New ADODB.Command

    cmdZipProximity.CommandText = "spZipProximity"
    cmdZipProximity.CommandType = adCmdStoredProc

    Set prmZipProximity = cmdZipProximity.CreateParameter("ZipCenter",
adVarChar, adParamInput)
    prmZipProximity.Size = 5
    cmdZipProximity.Parameters.Append prmZipProximity

    Set prmZipRadius = cmdZipProximity.CreateParameter("Radius",
adInteger, adParamInput)
    cmdZipProximity.Parameters.Append prmZipRadius

    Set cmdZipProximity.ActiveConnection = cnn1

'----------------------------------------------------------------
'  Other Initialization Stuff...
'----------------------------------------------------------------

End Sub

'----------------------------------------------------------------
Public Sub GetZipCodes()

    Mark ("GetZipCodes()")

    cmdZipProximity.Parameters("zipcenter").Value =
Form4.ZipCodeInput.Text
    cmdZipProximity.Parameters("zipcenter").Size = 5

    cmdZipProximity.Parameters("Radius").Value =
CStr(Form4.RadiusInput.Text)

    Set rstZipProximity = cmdZipProximity.Execute

    rstZipProximity.MoveFirst  ' Run-time error 3021 here!!!
    Form4.Location.Text = rstZipProximity!Proximity
    Form4.Distance.Text = CStr(rstZipProximity!Miles)

End Sub
'----------------------------------------------------------------



Sat, 28 Feb 2004 21:20:42 GMT  
 Reusing Command Object Redux... Run-time Error 3021
Hi,

Before execute MoveFirst check if you have any records in
result recordset. Probably there is no any records there.

Val

Quote:
>-----Original Message-----
>I noticed this thread from late July.
>I was (am still) having the same problem -- trying to
reuse a
>command object.  I tried the suggested code, and am still
>encountering a run-time error.  The error occurs on the
2nd
>time that I try to execute a stored procedure with new
parameters.

>Apparently, the SP executes, but when I try to navigate
the
>recordset with MOVEFIRST, I get:

>"Run-time error '3021':
>Either BOF or EOF is True, or the current record has been
deleted.
>Requested operation requires a current record."

>Any ideas?  The code is below.  The connection and the
command are
>created in the class initialization code, and then when
info is
>submmitted from a form, the subroutine, GetZipCodes, is
called.

>TIA,
>Rusty Alderson

>'---------------------------------------------------------
-------
>Private Sub Class_Initialize()

>'---------------------------------------------------------
-------
>' Open connection.
>'---------------------------------------------------------
-------

>    Set cnn1 = New ADODB.Connection
>    strCnn

= "Provider=SQLOLEDB.1;Password=<omitted>;Persist Security

- Show quoted text -

Quote:
>Info=True;User ID=sa;Initial
>Catalog=Geography;Data Source=ALDERSON\THIRD_COAST"
>    cnn1.Open strCnn
>    cnn1.CursorLocation = adUseClient

>'---------------------------------------------------------
-------
>'  Open command object
>'---------------------------------------------------------
-------

>    Set cmdZipProximity = New ADODB.Command

>    cmdZipProximity.CommandText = "spZipProximity"
>    cmdZipProximity.CommandType = adCmdStoredProc

>    Set prmZipProximity = cmdZipProximity.CreateParameter
("ZipCenter",
>adVarChar, adParamInput)
>    prmZipProximity.Size = 5
>    cmdZipProximity.Parameters.Append prmZipProximity

>    Set prmZipRadius = cmdZipProximity.CreateParameter
("Radius",
>adInteger, adParamInput)
>    cmdZipProximity.Parameters.Append prmZipRadius

>    Set cmdZipProximity.ActiveConnection = cnn1

>'---------------------------------------------------------
-------
>'  Other Initialization Stuff...
>'---------------------------------------------------------
-------

>End Sub

>'---------------------------------------------------------
-------
>Public Sub GetZipCodes()

>    Mark ("GetZipCodes()")

>    cmdZipProximity.Parameters("zipcenter").Value =
>Form4.ZipCodeInput.Text
>    cmdZipProximity.Parameters("zipcenter").Size = 5

>    cmdZipProximity.Parameters("Radius").Value =
>CStr(Form4.RadiusInput.Text)

>    Set rstZipProximity = cmdZipProximity.Execute

>    rstZipProximity.MoveFirst  ' Run-time error 3021
here!!!
>    Form4.Location.Text = rstZipProximity!Proximity
>    Form4.Distance.Text = CStr(rstZipProximity!Miles)

>End Sub
>'---------------------------------------------------------
-------
>.



Sat, 28 Feb 2004 21:52:52 GMT  
 Reusing Command Object Redux... Run-time Error 3021
Use Profiler utility to see is the parameters beeing passed to the sp are
the expecting.


Quote:
> I noticed this thread from late July.
> I was (am still) having the same problem -- trying to reuse a
> command object.  I tried the suggested code, and am still
> encountering a run-time error.  The error occurs on the 2nd
> time that I try to execute a stored procedure with new parameters.

> Apparently, the SP executes, but when I try to navigate the
> recordset with MOVEFIRST, I get:

> "Run-time error '3021':
> Either BOF or EOF is True, or the current record has been deleted.
> Requested operation requires a current record."

> Any ideas?  The code is below.  The connection and the command are
> created in the class initialization code, and then when info is
> submmitted from a form, the subroutine, GetZipCodes, is called.

> TIA,
> Rusty Alderson

> '----------------------------------------------------------------
> Private Sub Class_Initialize()

> '----------------------------------------------------------------
> ' Open connection.
> '----------------------------------------------------------------

>     Set cnn1 = New ADODB.Connection
>     strCnn = "Provider=SQLOLEDB.1;Password=<omitted>;Persist Security
> Info=True;User ID=sa;Initial
> Catalog=Geography;Data Source=ALDERSON\THIRD_COAST"
>     cnn1.Open strCnn
>     cnn1.CursorLocation = adUseClient

> '----------------------------------------------------------------
> '  Open command object
> '----------------------------------------------------------------

>     Set cmdZipProximity = New ADODB.Command

>     cmdZipProximity.CommandText = "spZipProximity"
>     cmdZipProximity.CommandType = adCmdStoredProc

>     Set prmZipProximity = cmdZipProximity.CreateParameter("ZipCenter",
> adVarChar, adParamInput)
>     prmZipProximity.Size = 5
>     cmdZipProximity.Parameters.Append prmZipProximity

>     Set prmZipRadius = cmdZipProximity.CreateParameter("Radius",
> adInteger, adParamInput)
>     cmdZipProximity.Parameters.Append prmZipRadius

>     Set cmdZipProximity.ActiveConnection = cnn1

> '----------------------------------------------------------------
> '  Other Initialization Stuff...
> '----------------------------------------------------------------

> End Sub

> '----------------------------------------------------------------
> Public Sub GetZipCodes()

>     Mark ("GetZipCodes()")

>     cmdZipProximity.Parameters("zipcenter").Value =
> Form4.ZipCodeInput.Text
>     cmdZipProximity.Parameters("zipcenter").Size = 5

>     cmdZipProximity.Parameters("Radius").Value =
> CStr(Form4.RadiusInput.Text)

>     Set rstZipProximity = cmdZipProximity.Execute

>     rstZipProximity.MoveFirst  ' Run-time error 3021 here!!!
>     Form4.Location.Text = rstZipProximity!Proximity
>     Form4.Distance.Text = CStr(rstZipProximity!Miles)

> End Sub
> '----------------------------------------------------------------



Sat, 28 Feb 2004 22:21:39 GMT  
 Reusing Command Object Redux... Run-time Error 3021

Quote:

> Hi,

> Before execute MoveFirst check if you have any records in
> result recordset. Probably there is no any records there.

> Val

Thanks for the reply.  I have done this. The stored procedure
with the supplied parameters returns a couple dozen rows.

--Rusty



Sun, 29 Feb 2004 22:54:12 GMT  
 Reusing Command Object Redux... Run-time Error 3021

Quote:

> Use Profiler utility to see is the parameters beeing passed to the sp are
> the expecting.

Thanks for the reply.  Yes the parameters are correct.  The
form asks for a zip code (5 characters) and a distance (integer).
The first time these parameters are supplied the procedure
executes properly and a result set is returned.  The second
time the form supplies parameter (with known good zip code and
a valid distance) the EXECUTE statement seems to work, but the
MOVEFIRST has a run-time error.  I can restart the app and use
the parameters that failed, and they will return the expected
result set.  Then supply the parameters the succeeded the first
time, and this time they will fail -- simply because it is the
second evocation of the procedure.

Has anyone been successful in reusing command objects like this?

--Rusty



Sun, 29 Feb 2004 23:02:05 GMT  
 Reusing Command Object Redux... Run-time Error 3021
Rusty,

- Are you using SET NOCOUNT ON/OFF in the sp?
- Could you check the status of the recordset after you execute the command?

This is from BOL:

Reuse Command Objects
If you are going to use a particular stored procedure, view, or SQL
statement several times, don't create a new Command object each time. Use a
static variable or a module-level variable to keep a reference to each
Command object that you will reuse.

Note:  This technique might not work well for VBScript running in Active
Server Pages, or for applications written for Microsoft Component
Transaction Services.

Let us know the results.



Quote:
> > Use Profiler utility to see is the parameters beeing passed to the sp
are
> > the expecting.

> Thanks for the reply.  Yes the parameters are correct.  The
> form asks for a zip code (5 characters) and a distance (integer).
> The first time these parameters are supplied the procedure
> executes properly and a result set is returned.  The second
> time the form supplies parameter (with known good zip code and
> a valid distance) the EXECUTE statement seems to work, but the
> MOVEFIRST has a run-time error.  I can restart the app and use
> the parameters that failed, and they will return the expected
> result set.  Then supply the parameters the succeeded the first
> time, and this time they will fail -- simply because it is the
> second evocation of the procedure.

> Has anyone been successful in reusing command objects like this?

> --Rusty



Mon, 01 Mar 2004 00:37:51 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. run time error 3021, no current record

2. Run-Time Error 3021

3. Run Time Error 3021

4. Run Time Error 3021

5. Run time error 3021

6. Run-time error "3021"

7. Run-Time Error 3021

8. Module runtime error 3021, no current record.

9. Runtime error 3021 - No current record

10. Runtime error 3021 Opening DAO Query

11. runtime error 3021 "No current record"

12. runtime error 3021?

 

 
Powered by phpBB® Forum Software