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! 4 1892
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" <bl******@hotmail.com> wrote in message
news:f0**************************@posting.google.c 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!
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.
*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:<uE*************@TK2MSFTNGP12.phx.gbl>... 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" <bl******@hotmail.com> wrote in message news:f0**************************@posting.google.c 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!
"Ola Tuvesson" <bl******@hotmail.com> wrote in message
news:f0**************************@posting.google.c 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Rob Meade |
last post by:
Lo all,
Ok - this is what I was aiming to do, and then I thought - naahhh, that cant
be right!
query database
results to recordset
results to array using GetRows
update values in one column...
|
by: HenryW |
last post by:
I have a section of ASP code that dynamically builds a table. And per
usual it writes out to the table like so:
Response.Write("<td><font face=""Verdana"" size=""2""><b> Status...
|
by: belacyrf |
last post by:
Here's the code:
-------------------------------------------------------------------
accessID = request("accessID")
strSQL = "SELECT * From PendingAccRequests Where AccessID = "&accessID
...
|
by: shank |
last post by:
1) I'm getting this error: Syntax error (missing operator) in query
expression on the below statement. Can I get some advice.
2) I searched ASPFAQ and came up blank. Where can find the "rules"...
|
by: Dave |
last post by:
Hi all,
I've been trying to figure this out for the last day and a half and
it has me stumped. I've got a web application that I wrote that
keeps track of trading cards I own, and I'm moving it...
| |
by: Roger Withnell |
last post by:
How do I find the maximum value of a recordset column? I'd rather do it
this way than open a new recordset with Max(column).
Thanks in anticipation.
Posted Via Usenet.com Premium Usenet...
|
by: Jim in Arizona |
last post by:
I'm going insane! I don't know if it's just that the .net 2.0 framework is
buggy or if it really is my code.
This is pretty hard to explain since I can't even begin to nail down why
this is...
|
by: zMisc |
last post by:
Are there any tricks in updaitng a JOINed recordset?
I joned to tables and when I try to change a field on the recordset and
update it, I get this error:
"Unknown column 'CCDE' in 'where...
|
by: mail |
last post by:
Urgent help needed!
I moved an application from ASP+ACCESS to ASP+MS SQLSERVER and I have
the following problem:
If the join on two tables results on duplicate colum names (which
appear in...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |