Error when accessing forums with inactive messages

Dec 2, 2011 at 9:55 AM

Hi,

I noticed some problems with showing forums and latest topics which have inactive messages in them.
The MessageUserId is null and the app does not like that. It looks like that is caused by the SP having left joins on Message with isActive = 1 in the join. This could leed to a null Message record being joined and endign up with MessageUserId being null if i am correct

For testing i altered it into an inner join and the app leaves out the inactive messages. I do still have a problem with Tags begin shown to that topics/messages and when i click on that tag it shows an empty page

Is there a reason for the left join on Message in several forum related SP's? The code does not seem to like the MessageUserId to be null, but maybe i missed something and this may be by design

Could you shed some light on this?

Coordinator
Dec 2, 2011 at 10:07 AM

Hi,

I don't think its a problem or I don't understand it correctly...

MessageUserId can be null in if the topic hasn't any messages posted or if all of the message posted are hidden (active=0) by a moderator.

Latest topics really inside the application means "Latest activity" (that's the menu displayed). Latest activity: Topic posted recently or Topic that has messages that were posted recently... 

 

Kind Regards,

Jorge

Dec 2, 2011 at 11:49 AM
Edited Dec 2, 2011 at 11:56 AM

But when i deactivate some messages and then visit the forum i get an exception. This is caused by the DataRow containtin null for MessageUserId. I do not understand why this is happing with my app
I hope this makes any sense. Excuse me for my blurryness



This is what i've got:

Selection of Messages

 

Selection used by SPTopicsGetByForum

SELECT
        ROW_NUMBER()
            OVER
                (ORDER BY TopicOrder desc
                 ,TopicViews desc)
            AS RowNumber
        ,T.TopicId
        ,T.TopicTitle
        ,T.TopicShortName
        ,T.TopicDescription
        ,T.TopicCreationDate
        ,T.TopicViews
        ,T.TopicReplies
        ,T.UserId
        ,T.TopicTags
        ,T.TopicIsClose
        ,T.TopicOrder
        ,T.LastMessageId
        ,T.UserName
        ,M.MessageCreationDate
        ,M.UserId AS MessageUserId
        ,MU.UserName AS MessageUserName
    FROM
        TopicsComplete T
        LEFT JOIN Messages M ON M.TopicId = T.TopicId AND M.MessageId = T.LastMessageId AND M.Active = 1
        LEFT JOIN Users MU ON MU.UserId = M.UserId
where T.ForumId = 2

Result (shortend to have only the relevant cols)

RowNumber TopicId LastMessageId UserNameMessageCreation MessageUserId
MessageUserName
1 1 4 User1 30-11-2011 9:24 27 User7
2 2 1 User2 22-11-2011 21:41 14 User8
3 3 4 User3


4 4 1 User4


5 6 2 User5 2-12-2011 11:39 3 User6
6 7
User6


 

This looks ok, but if i look at TopicId 4 and pull up the Messages i get this

TopicId MessageId MessageBody MessageCreation MessageLastEdit UserId Active MessageLastEditUser
4 1 Message text 24-11-2011 20:38 1-12-2011 22:47 16 FALSE 2

This 'false' is legit because i disabled that message, but isnt that the reason why MessageUserId is null?
Although the UserId in Message is filled, it does not get seleted becasue the Message record is left out by the fact that it is inactive

The code then come to this function when i browse to that forum

        protected virtual List<Topic> ParseTopicsForFullList(DbCommand comm)
        {
            var list = new List<Topic>();
            DataTable dt = this.GetTable(comm);
            foreach (DataRow dr in dt.Rows)
            {
                Topic t = ParseBasicTopicDataRow(dr);
                t.User = new User(dr.Get<int>("UserId"), dr.Get<string>("UserName"));
                if (!dr.IsNull("LastMessageId"))
                {
                    t.LastMessage = new Message(dr.Get<int>("LastMessageId"), dr.GetDate("MessageCreationDate"));
                    t.LastMessage.User = new User(dr.Get<int>("MessageUserId"), dr.GetString("MessageUserName"));
                }
                list.Add(t);
            }
            return list;
        }
UserId, LastMessageId are filled but because of the left join with Message causes the MessageUserId (M.UserId) to be null when the Message is Inactive
The Get<T> function does not allow DBNull and throws an exception


COuld it be that my database was not updated correctly?
This could be due to the fact that the LastMessage is an inactive one

Maybe the Topic's LastMessage determination does not look at active state?

Coordinator
Dec 2, 2011 at 11:54 AM

Hey, That's a great error report!

I will try to reproduce it based on your explanation.

 

Thanks!!

Coordinator
Dec 2, 2011 at 12:35 PM

I was able to reproduce it.

We will release the version 7.0.2 containing this bug fix, within the next couple of hours.

To thank you for your excellent bug report, I uploaded the files that changed to avoid you to reinstall Nearforums on your server, to apply it: 

 

Thanks!!!

Dec 2, 2011 at 12:43 PM
Edited Dec 2, 2011 at 12:45 PM

Thanks for the fast reaction!
6 dll's changed, I am keen to see what you have changed in code

 

Thanks again for the fix

Coordinator
Dec 2, 2011 at 1:05 PM

The fix is just 1 line of code in the dataaccess project, but the 6 dlls were changed with the new assembly version... ;)