| Author |
 Topic  |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
 Posted - 20 August 2002 : 20:30:01
|
There is a bug in the archiving function, admin_forums.asp. This bug results mainly from the fact that three fields from the TOPICS table may be NULL: T_LAST_EDITBY, T_LAST_POST_REPLY_ID, T_UREPLIES.
In the 3.4 code a test was being made for NULL values in T_LAST_EDIT_BY, but in the wrong place and there was no test for NULL values in T_LAST_POST_REPLY_ID and T_UREPLIES. I've fixed the original code to test both values and use the test results. I've tested the fix and was able to archive topics, something I hadn't been able to do before.
It's too complicated to show each individual change. Instead I'll post the whole block. To fix the problem, change this code, starting at line 544, admin_forums.asp:
if rsTcheck.eof then
err.clear
if isnull(drs("T_LAST_EDITBY")) then
intT_LAST_EDITBY = "NULL"
else
intT_LAST_EDITBY = drs("T_LAST_EDITBY")
end if
strsqlvalues = "" & drs("CAT_ID") & ", " & drs("FORUM_ID") & ", " & drs("TOPIC_ID") & ", " & 0
strsqlvalues = strsqlvalues & ", '" & chkstring(drs("T_SUBJECT"),"archive") & "', '" & chkstring(drs("T_MESSAGE"),"archive")
strsqlvalues = strsqlvalues & "', " & drs("T_AUTHOR") & ", " & drs("T_REPLIES") & ", " & drs("T_UREPLIES") & ", " & drs("T_VIEW_COUNT")
strsqlvalues = strsqlvalues & ", '" & drs("T_LAST_POST") & "', '" & drs("T_DATE") & "', " & drs("T_LAST_POSTER")
strsqlvalues = strsqlvalues & ", '" & drs("T_IP") & "', " & drs("T_LAST_POST_AUTHOR") & ", " & drs("T_LAST_POST_REPLY_ID") & ", '" & drs("T_LAST_EDIT")
strsqlvalues = strsqlvalues & "', " & intT_LAST_EDITBY & ", " & drs("T_STICKY") & ", " & drs("T_SIG") & " "
strsql = "INSERT INTO " & strArchiveTablePrefix & "TOPICS (CAT_ID, FORUM_ID, TOPIC_ID, T_STATUS, T_SUBJECT, T_MESSAGE, T_AUTHOR, T_REPLIES, T_UREPLIES, T_VIEW_COUNT, T_LAST_POST, T_DATE, T_LAST_POSTER, T_IP, T_LAST_POST_AUTHOR, T_LAST_POST_REPLY_ID, T_LAST_EDIT, T_LAST_EDITBY, T_STICKY, T_SIG)"
strsql = strsql & " VALUES (" & strsqlvalues & ")"
'Response.Write strSql
'Response.End
my_conn.execute(strsql),,adCmdText + adExecuteNoRecords
msg = " <center><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """ color=""" & strForumFontColor & """>All topics older than " & strToDate(fdateolderthan) & " were archived</font></center><br />" & vbNewLine
else
strsql = "UPDATE " & strArchiveTablePrefix & "TOPICS SET " &_
"T_STATUS = " & 0 &_
", T_SUBJECT = '" & chkstring(drs("T_SUBJECT"),"archive") & "'" &_
", T_MESSAGE = '" & chkstring(drs("T_MESSAGE"),"archive") & "'" &_
", T_REPLIES = T_REPLIES + " & drs("T_REPLIES") &_
", T_UREPLIES = T_UREPLIES + " & drs("T_UREPLIES") &_
", T_VIEW_COUNT = T_VIEW_COUNT + " & drs("T_VIEW_COUNT") &_
", T_LAST_POST = '" & drs("T_LAST_POST") & "'" &_
", T_LAST_POST_AUTHOR = " & drs("T_LAST_POST_AUTHOR") &_
", T_LAST_POST_REPLY_ID = " & drs("T_LAST_POST_REPLY_ID") & _
", T_LAST_EDIT = '" & drs("T_LAST_EDIT") & "'" & _
", T_LAST_EDITBY = " & drs("T_LAST_EDITBY") & _
", T_STICKY = " & drs("T_STICKY") & _
", T_SIG = " & drs("T_SIG") & _
" WHERE TOPIC_ID = " & drs("TOPIC_ID")
response.write(" <font face=""" & strDefaultFontFace & """ size=""" & strFooterFontSize & """ color=""" & strForumFontColor & """>." & vbNewLine)
by this one:
if isnull(drs("T_LAST_EDITBY")) then
intT_LAST_EDITBY = "NULL"
else
intT_LAST_EDITBY = drs("T_LAST_EDITBY")
end if
if isnull(drs("T_LAST_POST_REPLY_ID")) then
intT_LAST_POST_ID = "NULL"
else
intT_LAST_POST_ID= drs("T_LAST_POST_REPLY_ID")
end if
if isnull(drs("T_UREPLIES")) then
intT_UREPLIES = "NULL"
else
intT_UREPLIES= drs("T_UREPLIES")
end if
if rsTcheck.eof then
err.clear
strsqlvalues = "" & drs("CAT_ID") & ", " & drs("FORUM_ID") & ", " & drs("TOPIC_ID") & ", " & 0
strsqlvalues = strsqlvalues & ", '" & chkstring(drs("T_SUBJECT"),"archive") & "', '" & chkstring(drs("T_MESSAGE"),"archive")
strsqlvalues = strsqlvalues & "', " & drs("T_AUTHOR") & ", " & drs("T_REPLIES") & ", " & intT_UREPLIES & ", " & drs("T_VIEW_COUNT")
strsqlvalues = strsqlvalues & ", '" & drs("T_LAST_POST") & "', '" & drs("T_DATE") & "', " & drs("T_LAST_POSTER")
strsqlvalues = strsqlvalues & ", '" & drs("T_IP") & "', " & drs("T_LAST_POST_AUTHOR") & ", " & intT_LAST_POST_ID & ", '" & drs("T_LAST_EDIT")
strsqlvalues = strsqlvalues & "', " & intT_LAST_EDITBY & ", " & drs("T_STICKY") & ", " & drs("T_SIG") & " "
strsql = "INSERT INTO " & strArchiveTablePrefix & "TOPICS (CAT_ID, FORUM_ID, TOPIC_ID, T_STATUS, T_SUBJECT, T_MESSAGE, T_AUTHOR, T_REPLIES, T_UREPLIES, T_VIEW_COUNT, T_LAST_POST, T_DATE, T_LAST_POSTER, T_IP, T_LAST_POST_AUTHOR, T_LAST_POST_REPLY_ID, T_LAST_EDIT, T_LAST_EDITBY, T_STICKY, T_SIG)"
strsql = strsql & " VALUES (" & strsqlvalues & ")"
'Response.Write strSql
'Response.End
my_conn.execute(strsql),,adCmdText + adExecuteNoRecords
msg = " <center><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """ color=""" & strForumFontColor & """>All topics older than " & strToDate(fdateolderthan) & " were archived</font></center><br />" & vbNewLine
else
strsql = "UPDATE " & strArchiveTablePrefix & "TOPICS SET " &_
"T_STATUS = " & 0 &_
", T_SUBJECT = '" & chkstring(drs("T_SUBJECT"),"archive") & "'" &_
", T_MESSAGE = '" & chkstring(drs("T_MESSAGE"),"archive") & "'" &_
", T_REPLIES = T_REPLIES + " & drs("T_REPLIES") &_
", T_UREPLIES = T_UREPLIES + " & intT_UREPLIES &_
", T_VIEW_COUNT = T_VIEW_COUNT + " & drs("T_VIEW_COUNT") &_
", T_LAST_POST = '" & drs("T_LAST_POST") & "'" &_
", T_LAST_POST_AUTHOR = " & drs("T_LAST_POST_AUTHOR") &_
", T_LAST_POST_REPLY_ID = " & intT_LAST_POST_ID & _
", T_LAST_EDIT = '" & drs("T_LAST_EDIT") & "'" & _
", T_LAST_EDITBY = " & intT_LAST_EDITBY & _
", T_STICKY = " & drs("T_STICKY") & _
", T_SIG = " & drs("T_SIG") & _
" WHERE TOPIC_ID = " & drs("TOPIC_ID")
response.write(" <font face=""" & strDefaultFontFace & """ size=""" & strFooterFontSize & """ color=""" & strForumFontColor & """>." & vbNewLine)
|
Snitz 3.4 Readme | Like the support? Support Snitz too |
Edited by - ruirib on 20 August 2002 20:32:59 |
|
|
RichardKinser
Snitz Forums Admin
USA
16655 Posts |
Posted - 20 August 2002 : 20:32:15
|
if setup.asp runs correctly, then T_LAST_POST_REPLY_ID should not be NULL
this is in setup.asp:
'## Update T_LAST_POST_REPLY_ID for existing Topics in TOPICS table
SpecialSql8(Access) = "UPDATE " & strTablePrefix & "TOPICS SET T_LAST_POST_REPLY_ID = 0 WHERE (T_LAST_POST_REPLY_ID IS NULL)"
SpecialSql8(SQL6) = "UPDATE " & strTablePrefix & "TOPICS SET T_LAST_POST_REPLY_ID = 0 WHERE (T_LAST_POST_REPLY_ID IS NULL)"
SpecialSql8(SQL7) = "UPDATE " & strTablePrefix & "TOPICS SET T_LAST_POST_REPLY_ID = 0 WHERE (T_LAST_POST_REPLY_ID IS NULL)"
SpecialSql8(MySql) = "UPDATE " & strTablePrefix & "TOPICS SET T_LAST_POST_REPLY_ID = 0 WHERE (T_LAST_POST_REPLY_ID IS NULL)"
strOkMessage = "T_LAST_POST_REPLY_ID field value updated in the TOPICS table"
call SpecialUpdates(SpecialSql8, strOkMessage)
(I fixed the typo on line #2590 of setup.asp, it does not affect the setup of the forum though). |
 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 20 August 2002 : 20:35:09
|
I don't know Richard, I had problems with mine, and had no errors during setup.
I think that field is not updated for all the pre-existing topics. I had to create a script to do it, although this test forum hadn't been updated by the script yet.
<edit> Seen your script, but still I was forced to include the field as well, otherwise archiving would fail.</edit>. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
Edited by - ruirib on 20 August 2002 20:37:29 |
|
|
|
RichardKinser
Snitz Forums Admin
USA
16655 Posts |
Posted - 20 August 2002 : 20:36:18
|
| that code I posted above is supposed to set the field for all existing topics. It was something I added at the last minute, so it was not in rc2, but was in the final. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
RichardKinser
Snitz Forums Admin
USA
16655 Posts |
Posted - 20 August 2002 : 20:58:22
|
| can you check your setup.asp file to see if that section is in there? I did replace the .zip file on sourceforge right before I posted the note that it was available for download. Sourceforge might have still had the older file when you downloaded it. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 20 August 2002 : 21:21:15
|
Well I know what happened now. As I kept having problems while archiving, some topics were getting repeatedly archived (due to errors on these NULL values). So I decided to DTS the forum data from my Access DB to my SQL Server DB, to test it with a clean DB, to compare with the DB before archiving.
So, that T_LAST_POST_ID field has NULL values due to the data being imported through DTS, after the database upgrade. As I think this is a situation that may happen with our users (because of Access to SQL Server upgrades, for example) I'd say it's better to leave it this way. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
Edited by - ruirib on 20 August 2002 21:26:35 |
|
|
|
RichardKinser
Snitz Forums Admin
USA
16655 Posts |
Posted - 21 August 2002 : 01:42:22
|
fixed in v3.4.01
that section now looks like this:
if isnull(drs("T_LAST_EDITBY")) then
intT_LAST_EDITBY = "NULL"
else
intT_LAST_EDITBY = drs("T_LAST_EDITBY")
end if
if isnull(drs("T_LAST_POST_REPLY_ID")) then
intT_LAST_POST_REPLY_ID = "NULL"
else
intT_LAST_POST_REPLY_ID = drs("T_LAST_POST_REPLY_ID")
end if
if isnull(drs("T_UREPLIES")) then
intT_UREPLIES = "NULL"
intT_UREPLIEScnt = 0
else
intT_UREPLIES = drs("T_UREPLIES")
intT_UREPLIEScnt = drs("T_UREPLIES")
end if
if rsTcheck.eof then
err.clear
strsqlvalues = "" & drs("CAT_ID") & ", " & drs("FORUM_ID") & ", " & drs("TOPIC_ID") & ", " & 0
strsqlvalues = strsqlvalues & ", '" & chkstring(drs("T_SUBJECT"),"archive") & "', '" & chkstring(drs("T_MESSAGE"),"archive")
strsqlvalues = strsqlvalues & "', " & drs("T_AUTHOR") & ", " & drs("T_REPLIES") & ", " & intT_UREPLIES & ", " & drs("T_VIEW_COUNT")
strsqlvalues = strsqlvalues & ", '" & drs("T_LAST_POST") & "', '" & drs("T_DATE") & "', " & drs("T_LAST_POSTER")
strsqlvalues = strsqlvalues & ", '" & drs("T_IP") & "', " & drs("T_LAST_POST_AUTHOR") & ", " & intT_LAST_POST_REPLY_ID & ", '" & drs("T_LAST_EDIT")
strsqlvalues = strsqlvalues & "', " & intT_LAST_EDITBY & ", " & drs("T_STICKY") & ", " & drs("T_SIG") & " "
strsql = "INSERT INTO " & strArchiveTablePrefix & "TOPICS (CAT_ID, FORUM_ID, TOPIC_ID, T_STATUS, T_SUBJECT, T_MESSAGE, T_AUTHOR, T_REPLIES, T_UREPLIES, T_VIEW_COUNT, T_LAST_POST, T_DATE, T_LAST_POSTER, T_IP, T_LAST_POST_AUTHOR, T_LAST_POST_REPLY_ID, T_LAST_EDIT, T_LAST_EDITBY, T_STICKY, T_SIG)"
strsql = strsql & " VALUES (" & strsqlvalues & ")"
'Response.Write strSql
'Response.End
my_conn.execute(strsql),,adCmdText + adExecuteNoRecords
msg = " <center><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """ color=""" & strForumFontColor & """>All topics older than " & strToDate(fdateolderthan) & " were archived</font></center><br />" & vbNewLine
else
strsql = "UPDATE " & strArchiveTablePrefix & "TOPICS SET " &_
"T_STATUS = " & 0 &_
", T_SUBJECT = '" & chkstring(drs("T_SUBJECT"),"archive") & "'" &_
", T_MESSAGE = '" & chkstring(drs("T_MESSAGE"),"archive") & "'" &_
", T_REPLIES = T_REPLIES + " & drs("T_REPLIES") &_
", T_UREPLIES = T_UREPLIES + " & intT_UREPLIEScnt &_
", T_VIEW_COUNT = T_VIEW_COUNT + " & drs("T_VIEW_COUNT") &_
", T_LAST_POST = '" & drs("T_LAST_POST") & "'" &_
", T_LAST_POST_AUTHOR = " & drs("T_LAST_POST_AUTHOR") &_
", T_LAST_POST_REPLY_ID = " & intT_LAST_POST_REPLY_ID & _
", T_LAST_EDIT = '" & drs("T_LAST_EDIT") & "'" & _
", T_LAST_EDITBY = " & intT_LAST_EDITBY & _
", T_STICKY = " & drs("T_STICKY") & _
", T_SIG = " & drs("T_SIG") & _
" WHERE TOPIC_ID = " & drs("TOPIC_ID")
response.write(" <font face=""" & strDefaultFontFace & """ size=""" & strFooterFontSize & """ color=""" & strForumFontColor & """>." & vbNewLine)
|
|
|
| |
Topic |
|
|
|
Forum Locked
Topic Locked
