Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Strange problem w. missing recordset column (SQL + ASP)

Reply
Thread Tools

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

 
 
Ola Tuvesson
Guest
Posts: n/a
 
      09-28-2004
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",a dInteger,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!
 
Reply With Quote
 
 
 
 
Ray Costanzo [MVP]
Guest
Posts: n/a
 
      09-28-2004
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) om...
> 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",a dInteger,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!



 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      09-28-2004
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.records et")
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.


 
Reply With Quote
 
Ola Tuvesson
Guest
Posts: n/a
 
      09-29-2004
*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#(E-Mail Removed)>...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed) om...
> > 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",a dInteger,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!

 
Reply With Quote
 
Ray Costanzo [MVP]
Guest
Posts: n/a
 
      09-29-2004

"Ola Tuvesson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) om...
>
> 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


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Strange Problem: Templated checkbox column Dev ASP .Net Datagrid Control 0 05-05-2007 07:16 AM
Find Missing Column and Extra Column Rahul Perl Misc 5 03-19-2007 06:48 PM
Problem with comparing a Table View Column with the Table Column? savvy ASP .Net 1 01-18-2006 03:04 PM
ASP Problem Updating Recordset =?Utf-8?B?a2JyYWQ=?= Microsoft Certification 3 04-10-2004 11:42 AM
RecordSet.Move or RecordSet.AbsolutePosition?? Hung Huynh ASP General 8 09-24-2003 11:07 AM



Advertisments