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

"Rod" <Rod@NO.SPAM> wrote in message
news:eNeD8Uf5DHA.2056@TK2MSFTNGP10.phx.gbl...

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

[Bob Barrows]

Rod wrote:

> 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
[url]http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear[/url]

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[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

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:u2GpnDg5DHA.2656@TK2MSFTNGP11.phx.gbl...

> Rod wrote:

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

[url]http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear[/url]

>
> HTH,
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>