ADODB.Command error '800a0cc1'

Discussion in 'ASP General' started by Rod, Jan 28, 2004.

  1. Rod

    Rod Guest

    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
     
    Rod, Jan 28, 2004
    #1
    1. Advertising

  2. Rod

    Ray at Guest

    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" <> wrote in message
    news:...
    > 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, Jan 29, 2004
    #2
    1. Advertising

  3. Rod

    Bob Barrows Guest

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

    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"
     
    Bob Barrows, Jan 29, 2004
    #3
  4. Rod

    Rod Guest

    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" <> wrote in message
    news:...
    > 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
    >

    http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear
    >
    > 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, Jan 29, 2004
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ern edwar
    Replies:
    0
    Views:
    304
    ern edwar
    Nov 15, 2005
  2. ern edwar

    ADODB.Command error '800a0d5d'

    ern edwar, Nov 15, 2005, in forum: ASP General
    Replies:
    0
    Views:
    342
    ern edwar
    Nov 15, 2005
  3. Replies:
    1
    Views:
    243
    Bob Barrows [MVP]
    Nov 28, 2007
  4. Lutz Mueller
    Replies:
    0
    Views:
    251
    Lutz Mueller
    Oct 5, 2010
  5. redziller

    Client ADODB.Command unavailable

    redziller, Sep 14, 2005, in forum: Javascript
    Replies:
    0
    Views:
    101
    redziller
    Sep 14, 2005
Loading...

Share This Page