Strange problem w. missing recordset column (SQL + ASP)

Discussion in 'ASP General' started by Ola Tuvesson, Sep 28, 2004.

  1. Ola Tuvesson

    Ola Tuvesson Guest

    I'm having a really weird problem. When running the SP below in query
    analyzer the calculated column "Subscribed" is returned as expected:

    -------------

    CREATE PROCEDURE get_mailinglists(

    @intCustomerID AS int
    )
    AS

    SET NOCOUNT ON

    SELECT GenreID,GenreName,
    (CASE
    WHEN
    EXISTS(
    SELECT CustomerID
    FROM Mailinglists
    WHERE CustomerID = @intCustomerID AND ListID = GenreID)
    THEN 1
    ELSE 0
    END) AS Subscribed
    FROM Genres
    ORDER BY GenreName ASC
    GO

    -------------

    Resulting recordset in QA:

    9 Electro 1
    8 House 0

    -------------

    But strangely I'm unable to access the "Subscribed" column from within
    an ASP page:

    -------------

    Set objMailinglists = Server.CreateObject("ADODB.Command")
    Set objMailinglists.ActiveConnection = objConn
    objMailinglists.CommandText = "get_mailinglists"
    objMailinglists.CommandType = adCmdStoredProc

    objMailinglists.Parameters.Append
    objMailinglists.CreateParameter("@intCustomerID",adInteger,adParamInput,,intCustomerID)

    objMailinglists.Execute

    Set objMailinglists = objConn.Execute("SELECT * FROM Genres ORDER BY
    GenreName ASC")

    Do Until objMailinglists.EOF
    Response.Write("<input type=""checkbox"" name=""mailinglists""
    value=""" & objMailinglists("GenreID") & """ class=""noBorder""")
    If objMailinglists("Subscribed") = 1 Then Response.Write("
    checked")
    Response.Write(" /> " & objMailinglists("GenreName") & "<br />")
    objMailinglists.MoveNext
    Loop

    objMailinglists.Close
    Set objMailinglists = Nothing

    -------------

    This gives me "ADODB.Recordset error '800a0cc1' Item cannot be found
    in the collection corresponding to the requested name or ordinal" on
    the line where the column "Subscribed" is accessed. Comment it out and
    it works fine.

    I've tried renaming the column, referencing it with
    objMailinglists(2), changing various things in the SP and generally
    pulling my hair. Nothing helps, "Subscribed" steadfastly refuses to
    show up.

    I must be missing something really obvious here, I've done many SPs
    with calculated columns and have never run into anything like this
    before. Someone, please help!
    Ola Tuvesson, Sep 28, 2004
    #1
    1. Advertising

  2. objMailingLists isn't returning the results of that stored procedure,
    because you have this line:

    Set objMailinglists = objConn.Execute("SELECT * FROM Genres ORDER BY
    GenreName ASC")

    You're turning objMailingLists into a recordset object with the results of
    that SELECT * query.

    Ray at work


    "Ola Tuvesson" <> wrote in message
    news:...
    > I'm having a really weird problem. When running the SP below in query
    > analyzer the calculated column "Subscribed" is returned as expected:
    >
    > -------------
    >
    > CREATE PROCEDURE get_mailinglists(
    >
    > @intCustomerID AS int
    > )
    > AS
    >
    > SET NOCOUNT ON
    >
    > SELECT GenreID,GenreName,
    > (CASE
    > WHEN
    > EXISTS(
    > SELECT CustomerID
    > FROM Mailinglists
    > WHERE CustomerID = @intCustomerID AND ListID = GenreID)
    > THEN 1
    > ELSE 0
    > END) AS Subscribed
    > FROM Genres
    > ORDER BY GenreName ASC
    > GO
    >
    > -------------
    >
    > Resulting recordset in QA:
    >
    > 9 Electro 1
    > 8 House 0
    >
    > -------------
    >
    > But strangely I'm unable to access the "Subscribed" column from within
    > an ASP page:
    >
    > -------------
    >
    > Set objMailinglists = Server.CreateObject("ADODB.Command")
    > Set objMailinglists.ActiveConnection = objConn
    > objMailinglists.CommandText = "get_mailinglists"
    > objMailinglists.CommandType = adCmdStoredProc
    >
    > objMailinglists.Parameters.Append
    > objMailinglists.CreateParameter("@intCustomerID",adInteger,adParamInput,,intCustomerID)
    >
    > objMailinglists.Execute
    >
    > Set objMailinglists = objConn.Execute("SELECT * FROM Genres ORDER BY
    > GenreName ASC")
    >
    > Do Until objMailinglists.EOF
    > Response.Write("<input type=""checkbox"" name=""mailinglists""
    > value=""" & objMailinglists("GenreID") & """ class=""noBorder""")
    > If objMailinglists("Subscribed") = 1 Then Response.Write("
    > checked")
    > Response.Write(" /> " & objMailinglists("GenreName") & "<br />")
    > objMailinglists.MoveNext
    > Loop
    >
    > objMailinglists.Close
    > Set objMailinglists = Nothing
    >
    > -------------
    >
    > This gives me "ADODB.Recordset error '800a0cc1' Item cannot be found
    > in the collection corresponding to the requested name or ordinal" on
    > the line where the column "Subscribed" is accessed. Comment it out and
    > it works fine.
    >
    > I've tried renaming the column, referencing it with
    > objMailinglists(2), changing various things in the SP and generally
    > pulling my hair. Nothing helps, "Subscribed" steadfastly refuses to
    > show up.
    >
    > I must be missing something really obvious here, I've done many SPs
    > with calculated columns and have never run into anything like this
    > before. Someone, please help!
    Ray Costanzo [MVP], Sep 28, 2004
    #2
    1. Advertising

  3. Ola Tuvesson wrote:
    > I'm having a really weird problem. When running the SP below in query
    > analyzer the calculated column "Subscribed" is returned as expected:
    >
    > -------------
    >
    > CREATE PROCEDURE get_mailinglists(
    >
    > @intCustomerID AS int
    > )
    > AS
    >
    > SET NOCOUNT ON
    >
    > SELECT GenreID,GenreName,
    > (CASE
    > WHEN
    > EXISTS(
    > SELECT CustomerID
    > FROM Mailinglists
    > WHERE CustomerID = @intCustomerID AND ListID = GenreID)
    > THEN 1
    > ELSE 0
    > END) AS Subscribed
    > FROM Genres
    > ORDER BY GenreName ASC
    > GO
    >
    > -------------
    >
    > Resulting recordset in QA:
    >
    > 9 Electro 1
    > 8 House 0
    >
    > -------------
    >
    > But strangely I'm unable to access the "Subscribed" column from within
    > an ASP page:
    >
    > -------------
    >



    You're making this too difficult:

    SET objMailinglists=server.createobject("adodb.recordset")
    objConn.get_mailinglists intCustomerID, objMailinglists
    Do Until objMailinglists.EOF
    etc.

    Bob Barrows

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Sep 28, 2004
    #3
  4. Ola Tuvesson

    Ola Tuvesson Guest

    *Blush* oooops... :) I KNEW it was something I'd overlooked... The
    list used to be retreived with a query from the ASP but I changed to
    an SP so I could do some other things. I've stared at the code so long
    I completely missed that I still had that line in there... Gotta stop
    working so late at night.

    Anyway, many thanks for your reply, problem solved! Although my sense
    of self esteem took a beating....

    "Ray Costanzo [MVP]" <my first name at lane 34 dot commercial> wrote in message news:<uEO#>...
    > objMailingLists isn't returning the results of that stored procedure,
    > because you have this line:
    >
    > Set objMailinglists = objConn.Execute("SELECT * FROM Genres ORDER BY
    > GenreName ASC")
    >
    > You're turning objMailingLists into a recordset object with the results of
    > that SELECT * query.
    >
    > Ray at work
    >
    >
    > "Ola Tuvesson" <> wrote in message
    > news:...
    > > I'm having a really weird problem. When running the SP below in query
    > > analyzer the calculated column "Subscribed" is returned as expected:
    > >
    > > -------------
    > >
    > > CREATE PROCEDURE get_mailinglists(
    > >
    > > @intCustomerID AS int
    > > )
    > > AS
    > >
    > > SET NOCOUNT ON
    > >
    > > SELECT GenreID,GenreName,
    > > (CASE
    > > WHEN
    > > EXISTS(
    > > SELECT CustomerID
    > > FROM Mailinglists
    > > WHERE CustomerID = @intCustomerID AND ListID = GenreID)
    > > THEN 1
    > > ELSE 0
    > > END) AS Subscribed
    > > FROM Genres
    > > ORDER BY GenreName ASC
    > > GO
    > >
    > > -------------
    > >
    > > Resulting recordset in QA:
    > >
    > > 9 Electro 1
    > > 8 House 0
    > >
    > > -------------
    > >
    > > But strangely I'm unable to access the "Subscribed" column from within
    > > an ASP page:
    > >
    > > -------------
    > >
    > > Set objMailinglists = Server.CreateObject("ADODB.Command")
    > > Set objMailinglists.ActiveConnection = objConn
    > > objMailinglists.CommandText = "get_mailinglists"
    > > objMailinglists.CommandType = adCmdStoredProc
    > >
    > > objMailinglists.Parameters.Append
    > > objMailinglists.CreateParameter("@intCustomerID",adInteger,adParamInput,,intCustomerID)
    > >
    > > objMailinglists.Execute
    > >
    > > Set objMailinglists = objConn.Execute("SELECT * FROM Genres ORDER BY
    > > GenreName ASC")
    > >
    > > Do Until objMailinglists.EOF
    > > Response.Write("<input type=""checkbox"" name=""mailinglists""
    > > value=""" & objMailinglists("GenreID") & """ class=""noBorder""")
    > > If objMailinglists("Subscribed") = 1 Then Response.Write("
    > > checked")
    > > Response.Write(" /> " & objMailinglists("GenreName") & "<br />")
    > > objMailinglists.MoveNext
    > > Loop
    > >
    > > objMailinglists.Close
    > > Set objMailinglists = Nothing
    > >
    > > -------------
    > >
    > > This gives me "ADODB.Recordset error '800a0cc1' Item cannot be found
    > > in the collection corresponding to the requested name or ordinal" on
    > > the line where the column "Subscribed" is accessed. Comment it out and
    > > it works fine.
    > >
    > > I've tried renaming the column, referencing it with
    > > objMailinglists(2), changing various things in the SP and generally
    > > pulling my hair. Nothing helps, "Subscribed" steadfastly refuses to
    > > show up.
    > >
    > > I must be missing something really obvious here, I've done many SPs
    > > with calculated columns and have never run into anything like this
    > > before. Someone, please help!
    Ola Tuvesson, Sep 29, 2004
    #4
  5. "Ola Tuvesson" <> wrote in message
    news:...
    >
    > Anyway, many thanks for your reply, problem solved! Although my sense
    > of self esteem took a beating....


    lol! No, it shouldn't have. I'm sure we've all done such things... :]

    Ray at homee
    Ray Costanzo [MVP], Sep 29, 2004
    #5
    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. Hung Huynh
    Replies:
    8
    Views:
    283
    Bob Barrows
    Sep 24, 2003
  2. Aaron Bertrand - MVP

    ASP and SQL Server Recordset

    Aaron Bertrand - MVP, Jan 21, 2004, in forum: ASP General
    Replies:
    2
    Views:
    118
    Tom Kaminski [MVP]
    Jan 21, 2004
  3. Darren Smith
    Replies:
    2
    Views:
    148
    Darren Smith
    Jan 28, 2004
  4. AlanMF
    Replies:
    6
    Views:
    240
    AlanMF
    Nov 15, 2005
  5. Rahul
    Replies:
    5
    Views:
    150
    J. Gleixner
    Mar 19, 2007
Loading...

Share This Page