MOD:Topic Count (MSSQL version)

• forum_posts.asp
• member_profile.asp
• new_post.asp
• language_file_inc.asp

2-There is one database change to be made

• Adding a field in tblAuthor (No_Of_Topics)

I'll start with Step 2 first...since this is the easiest one

• Open the table (tblAuthor) in design view
• Add a field named (No_Of_Topics [Data Type=int, Length=4, Allow Null=Yes, Default Value=(0), Identity=No)
• Click on Save Table icon & exit

Now to the other part

• Open the file
• Add this at any place in the file (Const strTxtTopicsPerDay = "topics per day")
• Save & exit

new_post.asp

• Open the file
• Under 'Dimension variables define this variable (Dim intTopics)
• Under '*** Update Author Number of Posts *** Replace the SQL query with this one ('Initalise the strSQL variable with an SQL statement to query the database to get the number of posts the user has made
   strSQL = "SELECT " & strDbTable & "Author.No_of_posts, " & strDbTable & "Group.Special_rank, " & strDbTable & "Author.No_Of_Topics " & _
   "FROM " & strDbTable & "Author " & strDBNoLock & ", " & strDbTable & "Group " & strDBNoLock & " " & _
   "WHERE " & strDbTable & "Author.Group_ID=" & strDbTable & "Group.Group_ID " & _
    "AND " & strDbTable & "Author.Author_ID=" & lngLoggedInUserID & ";")
• Just couple of lines after that find this code ['Read in the no of posts the user has made and username
    lngNumOfPosts = CLng(rsCommon("No_of_posts"))] and add after it ['Read in the no of topics the user has made and username
    intTopics = Clng(rsCommon("No_Of_Topics"))]
• Just after that find the code ['Inrement the number of posts by 1
    lngNumOfPosts = lngNumOfPosts + 1] and add after it [if strMode = "new" OR strMode = "poll" then intTopics = intTopics + 1]
• Just after that find the SQL UPDATE query ['Initalise the SQL string with an SQL update command to update the number of posts the user has made
    strSQL = "UPDATE " & strDbTable & "Author " & strRowLock & " " & _
    "SET " & strDbTable & "Author.No_of_posts = " & lngNumOfPosts  " " & _
    "WHERE " & strDbTable & "Author.Author_ID = " & lngLoggedInUserID & ";"] and replace it with ['Initalise the SQL string with an SQL update command to update the number of posts the user has made
    strSQL = "UPDATE " & strDbTable & "Author " & strRowLock & " " & _
    "SET " & strDbTable & "Author.No_of_posts = " & lngNumOfPosts & ", " & strDbTable & "Author.No_Of_Topics = " & intTopics & " " & _
    "WHERE " & strDbTable & "Author.Author_ID = " & lngLoggedInUserID & ";"]
• Save & Exit

member_profile.asp

• Open the file
• In Dimension Variable list define this variable (Dim intNoOfTopics)
• Find this code [strMemberTitle = rsCommon("Avatar_title")] under ('Read in the new user's profile from the recordset) and add after it [if isNumeric(rsCommon("No_Of_Topics")) then intNoOfTopics = Clng(rsCommon("No_Of_Topics")) else intNoOfTopics= 0]
• Find this code [<tr>
       <td><% = strTxtPosts %>:</td>
       <td><% = lngNumOfPosts %> <% If lngNumOfPosts > 0 AND DateDiff("d", dtmJoined, Now()) > 0 Then Response.Write(" [" & FormatNumber(lngNumOfPosts / DateDiff("d", dtmJoined, Now()), 2) & " " & strTxtPostsPerDay) & "]" %></td>
      </tr>] and add after it [<tr>
       <td><% = strTxtTopics %>:</td>
       <td><% = intNoOfTopics %> <% If intNoOfTopics > 0 AND DateDiff("d", dtmJoined, Now()) > 0 Then Response.Write(" [" & FormatNumber(intNoOfTopics / DateDiff("d", dtmJoined, Now()), 2) & " " & strTxtTopicsPerDay) & "]" %></td>
      </tr>]
• Save & Exit

forum_posts.asp

• Open the file
• In Dimension Variables define this variable [Dim intTopics]
• Find this query ['Intilise SQL query to get all the posts
  'Use a LEFT JOIN for the Guest name as there may not be a Guest name and so we want to include null values
  strSQL = "" & _
  "SELECT " & strDbTable & "Thread.Thread_ID, " & strDbTable & "Thread.Message, " & strDbTable & "Thread.Message_date, " & strDbTable & "Thread.Show_signature, " & strDbTable & "Thread.IP_addr, " & strDbTable & "Thread.Hide, " & strDbTable & "Author.Author_ID, " & strDbTable & "Author.Username, " & strDbTable & "Author.Homepage, " & strDbTable & "Author.Location, " & strDbTable & "Author.No_of_posts, " & strDbTable & "Author.Join_date, " & strDbTable & "Author.Signature, " & strDbTable & "Author.Active, " & strDbTable & "Author.Avatar, " & strDbTable & "Author.Avatar_title, " & strDbTable & "Group.Name, " & strDbTable & "Group.Stars, " & strDbTable & "Group.Custom_stars, " & strDbTable & "GuestName.Name " & _] and replace it with this one ['Intilise SQL query to get all the posts
  'Use a LEFT JOIN for the Guest name as there may not be a Guest name and so we want to include null values
  strSQL = "" & _
  "SELECT " & strDbTable & "Thread.Thread_ID, " & strDbTable & "Thread.Message, " & strDbTable & "Thread.Message_date, " & strDbTable & "Thread.Show_signature, " & strDbTable & "Thread.IP_addr, " & strDbTable & "Thread.Hide, " & strDbTable & "Author.Author_ID, " & strDbTable & "Author.Username, " & strDbTable & "Author.Homepage, " & strDbTable & "Author.Location, " & strDbTable & "Author.No_of_posts, " & strDbTable & "Author.Join_date, " & strDbTable & "Author.Signature, " & strDbTable & "Author.Active, " & strDbTable & "Author.Avatar, " & strDbTable & "Author.Avatar_title, " & strDbTa