ADO causing problem with SP??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

ADO causing problem with SP???

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-07-16 : 13:59:12

I have the following stored procedure below. Everything works perfectly when I execute it from QA. The problem is when I run it from ASP and try to display the R_MESSAGE column (text) ADO brings back an empty string. I run the exact query from QA and the R_MESSAGE is brought back properly. Why cant I get this to display? I remember reading once something about the order a TEXT column is selected in might be important? Any info would be greatly appreciated. BTW: this is a modified snitz forum page that I am sticking into a SP for performance reasons.

Thanks again,
Mike

CREATE PROCEDURE select_thread
(
@topic_ID int, -- Topic ID
@Page int, -- Which Page
@RecsPerPage tinyint, -- Number of Records to bring back
@userID int
)
AS
SET NOCOUNT ON
--Create temporary table to store records
CREATE TABLE #TempReply
(
ID int IDENTITY,
userID int,
nameOnline varchar(25),
forum_Level tinyint,
forum_Posts int,
REPLY_ID int,
FORUM_ID tinyint,
R_AUTHOR int,
TOPIC_ID int,
R_STATUS tinyint,
R_DATE datetime,
R_MESSAGE text,
online tinyint,
replyCount smallint

)
INSERT INTO #TempReply (userID, nameOnline, forum_Level, forum_Posts, REPLY_ID, FORUM_ID, R_AUTHOR, TOPIC_ID, R_STATUS, R_DATE, R_MESSAGE, online, replyCount)
SELECT tblUserDetails.userID, tblUserDetails.nameOnline, tblUserDetails.forum_Level, tblUserDetails.forum_Posts, FORUM_REPLY.REPLY_ID, FORUM_REPLY.FORUM_ID, FORUM_REPLY.R_AUTHOR, FORUM_REPLY.TOPIC_ID, FORUM_REPLY.R_STATUS, FORUM_REPLY.R_DATE, FORUM_REPLY.R_MESSAGE,

case when exists (select userID from tblActive_Users where tblUserDetails.userid = tblActive_Users.userid) then
'1'
else
'0'
end as Online, (select count(reply_id) FROM forum_reply WHERE topic_ID = @topic_ID)

FROM tblUserDetails, FORUM_REPLY WHERE tblUserDetails.userID = FORUM_REPLY.R_AUTHOR AND TOPIC_ID = @topic_ID AND (FORUM_REPLY.R_STATUS < 3 OR R_AUTHOR = @userID) ORDER BY FORUM_REPLY.R_DATE asc

-- find out the last and first record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
--Now Return the set of paged records, plus indication of further records
SELECT *,
MoreRecords =
(
SELECT COUNT(topic_ID)
FROM #TempReply TR
WHERE TR.ID > @LastRec
)
FROM #TempReply
WHERE ID > @FirstRec AND ID < @LastRec
SET NOCOUNT OFF

GO

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-16 : 14:29:22

The problem is most likely:

SELECT *,
MoreRecords =
(
SELECT COUNT(topic_ID)
FROM #TempReply TR
WHERE TR.ID > @LastRec
)
FROM #TempReply
WHERE ID > @FirstRec AND ID < @LastRec

A text column must be last in the SELECT list for ADO to return it properly. Try this:

SELECT ID,
userID,
nameOnline,
forum_Level,
forum_Posts,
REPLY_ID,
FORUM_ID,
R_AUTHOR,
TOPIC_ID,
R_STATUS,
R_DATE,
online,
replyCount,
MoreRecords =
(
SELECT COUNT(topic_ID)
FROM #TempReply TR
WHERE TR.ID > @LastRec
) ,
R_MESSAGE
FROM #TempReply
WHERE ID > @FirstRec AND ID < @LastRec

As long as it's the last column in the SELECT list you should be fine.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-07-16 : 14:38:19

thanks rob, your correct once AGAIN!

lol

cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources