Problem using SELECT statement twice in SP, to ASP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem using SELECT statement twice in SP, to ASP

Author

Topic

huge
Starting Member

31 Posts

Posted - 2002-03-09 : 13:09:45

I have an ASP page which I pass (from the database) all the messages that a person has received. When I use a single SELECT statement it works fine, however when I have both of them I get the following error in ASP:

Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.

My Code for the Sproc is:

CREATE PROCEDURE sproc_checkinbox
(
@username varchar(16)
)
AS
DECLARE @NumMessages int
SELECT @NumMessages = COUNT(*) FROM Messages WHERE Recipient=@username

SELECT @NumMessages as "NumMessages"
SELECT * FROM Messages WHERE Recipient=@username
GO

Now inside QA it works fine and is able to show me both my table (Messages) and my column that has the number of messages (NumMessages). But in ASP this craps out, and I get an error pointing to the second SELECT statement. The first one will always not return any errors (in ASP) but the second always will. This doesnt matter if I reverse the order its the same, therefore the coding is correct but I guess you cant have 2 SELECT statments in this fashion.

Please help
HuGe

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-09 : 13:24:17

try putting 'set nocount on' at the top of the SP.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

huge
Starting Member

31 Posts

Posted - 2002-03-09 : 13:46:02

Hey thanks for replying,
unfortunately that didnt do anything.

I thought I would include my ASP code, although I dont think this will help much. The only problem I cant think of in the script is that I am recursing through all of the messages (outputting all messages the user has received just subject, from whom, and when sent). I thought this may be a problem because I loop through this by saying (While NOT Recordset.EOF 'In ASP).
So if they are the same recordset and two pieces of data being outputted, one that is one row (the number of messages from the table), the other several rows that are being recursed through.

Dont know if you understand, I hate working Saturdays, lol.

Here is the ASP code:

<%
'-----------------------------------------------------
'|||||||| Get Data from DAtabase ||||||||||
'-----------------------------------------------------
Set Conn = Server.CreateObject("ADODB.connection")
conn.open "DSN=AdultMatch; uid=mikem; pwd=mikem"
mySQL = "Execute sproc_CheckInbox '" & username_user & "'"
Set ORs = Conn.Execute(mySQL)
%>

Now the calling of the Recordset

<%WHILE NOT Ors.EOF %>
<tr bgcolor="#FFCC33">
<td width="10%">
<div align="center">

<input type="checkbox" name="check_<%=i%>" value="checkbox">
</div>
</td>
<td width="45%"><font face="MS Sans Serif" size=2><b><%=Ors("NumMessages")%>
<a href="readmessage.asp?SID=<%=SID%>&UID=<%=UserID%>&messageID=<%=oRs("MessageID")%>"><%=oRs("MsgSubject")%></a></b></font></td>
<td width="20%"><div align="center"><%=oRs("Sender")%></div></td>
<td width="25%"><%=oRs("MsgDateSent")%></td>
</tr>
<%oRs.MoveNext
WEND%>

Thanks in advance for any help.

HuGE

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-09 : 14:11:15

Why not have the number of messages as an output parameter rather than a recordset?
To get the two recordsets (didn't spot this before shold have read more carefully) you will have to go through the recordsets collection and you will have to support multiple recordsets.
I would also advise a client side cursor.

See
www.nigelrivett.com
Call stored procedures from ASP

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-03-11 : 08:13:51

I am not sure if you have a particular reason for having the fist Select statement other than to get the Recordcount.

quote:
DECLARE @NumMessages int
SELECT @NumMessages = COUNT(*) FROM Messages WHERE Recipient=@username

If this is the only reason for this statement, then you could take
this Select statement out and on the asp side, do a Ors.RecordCount.
This would give the number of messages in the Second Select statement. After getting this count, you could loop through the results.

If you must leave this in the SP, then you need to modify you ASP code to access both recordsets. The way your code is set up now is to read information from the First Select Statement (the Count). The way to access the second Select Statement is Ors.NextRecordset.

If this does not fix your problem, please let me know and I can write some specific code.

Jeremy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources