Active Topics

Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic

   Hi all...
This isn't really a bug but a question of functionality.  The active topics page doesn't show all of the active topics! mainly in the case of forums being locked for members (registered users) only.
What's strange to me is that if i'm logged in as a member, i should be able to see all of the posts that i have access to... not just the ones that the "guests" can see...
Well i found some mods that were supposed to do this but they just went and grabbed the group permissions and didn't take into account "guest" (read = 1) "members" (read<=2) and "admin" (read=all) in the forums themselves.
so i decided to work out some fancy SQL statement that would fix it... and it works great! (passing in the userID and the ActiveFrom)

CREATE PROCEDURE  [dbo].[wwfspActiveTopics] ( 
@AuthorID int, 
@dblActiveFrom int
) 
as 
set nocount on
IF  @AuthorID = 1 --admin (get everything)
 SELECT     
  --dbo.tblCategory.Cat_ID AS Cat_ID,dbo.tblCategory.Cat_name AS Cat_name, 
  dbo.tblForum.Forum_name AS Forum_name, dbo.tblForum.Password AS Password, 
  dbo.tblForum.Forum_code AS Forum_code, dbo.tblForum.[Read] AS [Read], dbo.tblTopic.* 
 FROM dbo.tblCategory RIGHT OUTER JOIN 
   dbo.tblForum ON dbo.tblCategory.Cat_ID = dbo.tblForum.Cat_ID RIGHT OUTER JOIN 
    dbo.tblTopic ON dbo.tblForum.Forum_ID = dbo.tblTopic.Forum_ID 
 WHERE 
  AND (tblTopic.Last_entry_date > GetDate() - @dblActiveFrom )
 ORDER BY 
      tblCategory.Cat_order ASC, tblForum.Forum_Order ASC, tblTopic.Last_entry_date DESC
ELSE IF  @AuthorID = 2  --guest (only get read = 1 for all)
 SELECT     
  --dbo.tblCategory.Cat_ID AS Cat_ID,dbo.tblCategory.Cat_name AS Cat_name, 
  dbo.tblForum.Forum_name AS Forum_name, dbo.tblForum.Password AS Password, 
  dbo.tblForum.Forum_code AS Forum_code, dbo.tblForum.[Read] AS [Read], dbo.tblTopic.* 
 FROM dbo.tblCategory RIGHT OUTER JOIN 
   dbo.tblForum ON dbo.tblCategory.Cat_ID = dbo.tblForum.Cat_ID RIGHT OUTER JOIN 
    dbo.tblTopic ON dbo.tblForum.Forum_ID = dbo.tblTopic.Forum_ID 
 WHERE 
  (tblForum.[Read] = 1 OR (tblTopic.Forum_ID in (select Forum_ID from tblPermissions where Author_ID=@AuthorID or Group_ID in (select Group_ID from tblAuthor where Author_ID= @AuthorID)  and [Read]=1))) 
  AND (tblTopic.Last_entry_date > GetDate() - @dblActiveFrom )
  AND NOT tblForum.Forum_ID in (select Forum_ID from tblPermissions where Author_ID=@AuthorID or Group_ID in (select Group_ID from tblAuthor where Author_ID= @AuthorID)  and [Read]=0)
 ORDER BY 
      tblCategory.Cat_order ASC, tblForum.Forum_Order ASC, tblTopic.Last_entry_date DESC
ELSE IF @AuthorID  > 2 --Registered user (only get read =< 2 applies)
 SELECT     
  --dbo.tblCategory.Cat_ID AS Cat_ID,dbo.tblCategory.Cat_name AS Cat_name, 
  dbo.tblForum.Forum_name AS Forum_name, dbo.tblForum.Password AS Password, 
  dbo.tblForum.Forum_code AS Forum_code, dbo.tblForum.[Read] AS [Read], dbo.tblTopic.* 
 FROM dbo.tblCategory RIGHT OUTER JOIN 
   dbo.tblForum ON dbo.tblCategory.Cat_ID = dbo.tblForum.Cat_ID RIGHT OUTER JOIN 
    dbo.tblTopic ON dbo.tblForum.Forum_ID = dbo.tblTopic.Forum_ID 
 WHERE 
  (tblForum.[Read] <= 2 OR (tblTopic.Forum_ID in (select Forum_ID from tblPermissions where Author_ID=@AuthorID or Group_ID in (select Group_ID from tblAuthor where Author_ID= @AuthorID)  and [Read]<=2))) 
  AND (tblTopic.Last_entry_date > GetDate() - @dblActiveFrom )
  AND NOT tblForum.Forum_ID in (select Forum_ID from tblPermissions where Author_ID=@AuthorID or Group_ID in (select Group_ID from tblAuthor where Author_ID= @AuthorID)  and [Read]=0)
 ORDER BY 
      tblCategory.Cat_order ASC, tblForum.Forum_Order ASC, tblTopic.Last_entry_date DESC
ELSE
 SELECT     
  --dbo.tblCategory.Cat_ID AS Cat_ID,dbo.tblCategory.Cat_name AS Cat_name, 
  dbo.tblForum.Forum_name AS Forum_name, dbo.tblForum.Password AS Password, 
  dbo.tblForum.Forum_code AS Forum_code, dbo.tblForum.[Read] AS [Read], dbo.tblTopic.* 
 FROM dbo.tblCategory RIGHT OUTER JOIN 
   dbo.tblForum ON dbo.tblCategory.Cat_ID = dbo.tblForum.Cat_ID RIGHT OUTER JOIN 
    dbo.tblTopic ON dbo.tblForum.Forum_ID = dbo.tblTopic.Forum_ID 
 WHERE 
  (tblForum.[Read] = 1 OR (tblTopic.Forum_ID in (select Forum_ID from tblPermissions where Author_ID=@AuthorID or Group_ID in (select Group_ID from tblAuthor where Author_ID= @AuthorID)  and [Read]=1))) 
  AND (tblTopic.Last_entry_date > GetDate() - @dblActiveFrom )
  AND NOT tblForum.Forum_ID in (select Forum_ID from tblPermissions where Author_ID=@AuthorID or Group_ID in (select Group_ID from tblAuthor where Author_ID= @AuthorID)  and [Read]=0)
 ORDER BY 
      tblCategory.Cat_order ASC, tblForum.Forum_Order ASC, tblTopic.Last_entry_date DESC
GO

note: that you can add this Stored procedure without too much trouble because of the fact that the original is named wwfspActiveToipcs.
alright... test it in Query Analyzer... 

EXECUTE wwfSpActiveTopics '1','1'    (admin)
EXECUTE wwfSpActiveTopics '2','1'    (guest)
EXECUTE wwfSpActiveTopics '24','1'    (a random member)

beautiful!
now fix the ASP to pass the new variable... on line 258 of active_topics.asp, comment out the old one and add the new one: (both lines shown here)

 'strSQL = "EXECUTE " & strDbProc & "ActiveToipcs @dblActiveFrom = " & dblActiveFrom
 strSQL = "EXECUTE " & strDbProc & "ActiveTopics " & lngLoggedInUserID & "," & dblActiveFrom & "" 

AND SAVE IT AS active_topics2.asp or something... dont screw up your original!
alright... now is where it gets funky... I get an ERROR?!?

ADODB.Recordset error '800a0cb3' 
Current Recordset does not support bookmarks. This may be a limitation of the provider or of the selected cursortype. 
/forumtest/active_topics4.asp, line 315 

alright... at this point i'm confused...  but i managed to narrow it down to one thing:

code removed to avoid confusion with the mod.

whenever you put in a conditional into the SQL code, it doesn't return the recordset correctly.
I've tried BEGIN ... END with the if blocks, set nocount on ... set nocount off,  but nothing seems to send back the recordset properly.
can someone PLEASE EXPLAIN this to me!!!
glypher

Edited by glypher