mod w/ Last Post Topic

I made the following changes to default.asp to accomplish placing a link to the last Subject:

Dim strLastEntrySubject     'Holds the subject of the last entry

---------

In the section " 'Initalise the strSQL variable with an SQL statement to query the database for the date of the last entry and the author for the thread " I added:

Topic.Subject to the SELECT

AND an INNER JOIN (INNER JOIN "  & strDbTable & "Topic ON " & strDbTable & "Topic.Topic_ID = " & strDbTable & "Thread.Topic_ID "
) to the FROM clause

---------------

I added ("      strLastEntrySubject = rsCommon("Subject") ")  to the "      'If there are threads for topic then read in the date and author of the last entry" section

------------------

AND  ( <a href=""forum_posts.asp?TID=" & lngLastEntryTopicID & "&get=last#" & lngLastEntryMeassgeID & """ target=""_self"">" & strLastEntrySubject & "</a>" ) to the "  'Write the HTML of the forum descriptions and hyperlinks to the forums" section.

--------

These changes to support MS-Access work well except for forums that have no topics yet.  Since INNER JOIN is supported but not LEFT JOIN, the Subject in the query is populated even for null topics.

Example of mod:

LePaul,

I will email you the complete default.asp.

Here are the lines I changed:

---------------- create holder for subject "topic name"

' MOD: to display Topic Name in Last Post column
Dim strLastEntrySubject     'Holds the subject of the last entry

---------------- Add the Subject column to the SELECT statement in the Access query

'       strSQL = "SELECT Top 1 " & strDbTable & "Author.Username, " & strDbTable & "Author.Author_ID, " & strDbTable & "Thread.Topic_ID, " & strDbTable & "Thread.Thread_ID, " & strDbTable & "Thread.Message_date "
' MOD: to display Topic Name in Last Post column
       strSQL = "SELECT Top 1 " & strDbTable & "Author.Username, " & strDbTable & "Author.Author_ID, " & strDbTable & "Thread.Topic_ID, " & strDbTable & "Thread.Thread_ID, " & strDbTable & "Thread.Message_date, " & strDbTable & "Topic.Subject "

---------------- Add an INNER JOIN to the Access query to include the Subject from the Topic table

'       strSQL = strSQL & "FROM " & strDbTable & "Author "
' MOD: to display Topic Name in Last Post column
       strSQL = strSQL & "FROM " & strDbTable & "Author, " & strDbTable & "Thread   INNER JOIN "  & strDbTable & "Topic ON " & strDbTable & "Topic.Topic_ID = " & strDbTable & "Thread.Topic_ID "

---------------- populate the strLastEntrySubject from the query results

' MOD: to display Topic Name in Last Post column
       strLastEntrySubjec t = rsCommon("Subject")

---------------- Add the link to the last entry using the Subject as the hypertext

'      Response.Write(""" background=""" & strTableBgImage & """ width=""29%"" class=""smText"" align=""right""  nowrap=""nowrap"">" & DateFormat(dtmLastEntryDate, saryDateTimeData) & "&nbsp;" &  strTxtAt & "&nbsp;" & TimeFormat(dtmLastEntryDate, saryDateTimeData) & "" & _
' MOD: to display Topic Name in Last Post column      
      Response.Write(""" background=""" & strTableBgImage & """ width=""29%"" class=""smText"" align=""right""  nowrap=""nowrap""><a href=""forum_posts.asp?TID=" & lngLastEntryTopicID & "&get=last#" & lngLastEntryMeassgeID & """ target=""_self"">" & strLastEntrySubject & "</a>" & vbCrLf & "        <br />" & DateFormat(dtmLastEntryDate, saryDateTimeData) & "&nbsp;" &  strTxtAt & "&nbsp;" & TimeFormat(dtmLastEntryDate, saryDateTimeData) & "" & _

James,

I am using Access so the mod has only been tested using Access. 

I believe you would need to change the SQL in the LastForumPostEntry stored procedure, adding the Topic.Subject reference in the SELECt and the JOIN in the FROM clause to make this mod work for SQL Server.  The rest of the changes to default.asp should be the same whether using Access or SQL Server.

To summarize the changes:

1) add a variable to hold the Subject (I called that strLastEntrySubject)

2) modify the SQL Query (in default.asp if Access, in LastForumPostEntry if SQL Server) to return Subject from the Topic table (obtained by adding a JOIN to the FROM clause)

3) populate strLastEntrySubject from the query results

4) use strLastEntrySubject as the hyperlink text to provide a link to the last post in the respective forum (lngLastEntryTopicID).

-- if SQL Server supports LEFT JOIN I would use it instead of INNER JOIN to prevent null forums (a forum without any posted topics) from having an incorrectly displayed Subject.