ADODB.Command error '800a0cc1'

[Rod]

I have an ASP page I am working on, using ADO 2.6. I am not using any DTC's
in this page anywhere. I have an ADO connection to a SQL Server 2000
database, I create some ADO Commands utilizing a couple of stored procedures
which take parameters and return recordsets. When I run this I get the
following error message:

-----------------------
ADODB.Command error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name
or ordinal.

/voucher_funds_balance.asp, line 89
-----------------------

Here is the relevant lines of ASP code:

set cmNextDate = Server.CreateObject("ADODB.Command")
cmNextDate.CommandType = adCmdStoredProc
cmNextDate.CommandText = "spGetNextTheDate"
set pParam = cmNextDate.CreateParameter("@DateCode", adSmallInt,
adParamInput)
cmNextDate.Parameters("@DateCode").Value = CInt(1)
cmNextDate.Parameters.Append pParam
set rsNextInvoice = Server.CreateObject("ADODB.Recordset")
rsNextInvoice.Open cmNextDate, cn, adOpenStatic
Response.Write("<tr><td>") 'begin a new row and cell
dtCurrentDateTime = Date() + Time() 'get the current date and time
If rsNextInvoice.EOF then
Response.Write("(Next Invoice Date is not yet determined.)")
End If

Line 89 is "If rsNextInvoice.EOF then". Last I knew the EOF property was
still a property of the ADO Recordset, so I doubt that is the problem.

I have double checked the parameter to the stored procedure, and have even
gone so far as to open up the stored procedure in Query Analyzer, copy it
out of the stored procedure and paste it right into this ASP code, to make
certain that the parameter was NOT wrong.

So, the question is, what's going on here? Why am I getting this error?

Rod

 

[Ray at]

Are you sure that's the line with the error? That line usually indicates
that you're doing something like:


variable = Recordset("ColumnNameThatDoesNotExistInRecordset")

What's after the End If?

Ray at home

 

[Bob Barrows]

I have an ASP page I am working on, using ADO 2.6. I am not using
any DTC's in this page anywhere. I have an ADO connection to a SQL
Server 2000 database, I create some ADO Commands utilizing a couple
of stored procedures which take parameters and return recordsets.
When I run this I get the following error message:

-----------------------
ADODB.Command error '800a0cc1'

Item cannot be found in the collection corresponding to the requested
name or ordinal.

/voucher_funds_balance.asp, line 89
-----------------------

Here is the relevant lines of ASP code:

set cmNextDate = Server.CreateObject("ADODB.Command")
cmNextDate.CommandType = adCmdStoredProc
cmNextDate.CommandText = "spGetNextTheDate"
set pParam = cmNextDate.CreateParameter("@DateCode", adSmallInt,
adParamInput)
cmNextDate.Parameters("@DateCode").Value = CInt(1)
cmNextDate.Parameters.Append pParam
set rsNextInvoice = Server.CreateObject("ADODB.Recordset")
rsNextInvoice.Open cmNextDate, cn, adOpenStatic
Response.Write("<tr><td>") 'begin a new row and cell
dtCurrentDateTime = Date() + Time() 'get the current date and time
If rsNextInvoice.EOF then
Response.Write("(Next Invoice Date is not yet determined.)")
End If

Line 89 is "If rsNextInvoice.EOF then". Last I knew the EOF property
was still a property of the ADO Recordset, so I doubt that is the
problem.

I have double checked the parameter to the stored procedure, and have
even gone so far as to open up the stored procedure in Query
Analyzer, copy it out of the stored procedure and paste it right into
this ASP code, to make certain that the parameter was NOT wrong.

So, the question is, what's going on here? Why am I getting this
error?

Rod

I suspect your problem is the lack of a SET NOCOUNT ON line at the beginning
of your stored procedure. When you run the procedure in QA, do you get the
"x rows were affected" messages? You need to realize that each of these
messages is being sent to the client as resultsets. Use NOCOUNT to turn off
this activity.

It does not look like you are using any output parameters, or reading the
value of the Return parameter, so you do not need to use an explicit Command
object. You can use the "stored-procedure-as-connection-method" technique to
execute this procedure, like this:

set rsNextInvoice = Server.CreateObject("ADODB.Recordset")
rsNextInvoice .CursorType= adOpenStatic
cn.spGetNextTheDate CInt(1), rsNextInvoice

If you are determined to use the Command object, the first step is to use
SQL Profiler to verify that the procedure is getting run. If not, then the
construction of the Command object is in error. You can make your life much
simpler by using one of the many Command object code generators available on
the internet, including mine which is available at
http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear

HTH,
Bob Barrows

 

[Rod]

Hello Bob,

I do have the SET NOCOUNT ON in my stored procedure, right after all of my
DECLARE statements.

You are correct, I do not have any OUTPUT parameters, and I am not
interested in the return value of the stored procedure, so I'll try using
what you've described as the "stored-procedure-as-connection-method".

Rod