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

[Ola Tuvesson]

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

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

 

[Bob Barrows [MVP]]

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

You're making this too difficult:

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

Bob Barrows

 

[Ola Tuvesson]

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

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