Microsoft.AspNetCore.Mvc.Localization.LocalizedHtmlStringDecrease members post count on post deletion

Author : modifichicci
Subject : Decrease members post count on post deletion
Posted : 02 June 2007, 09:57
Message :

Using antispam mod checking for a number of posts to allow sending mail or PM, if a memeber post and delete his posts can raise the count and then begin to spam..
But snitz code doesn't update member post count if post is deleted.
So I have modified pop_delete to update count when deleting topics, replies, forum and cat.
This is the pop_delete modified in an original snitz 06 file. Changing are marked by
' #### post count update modifichicci mod #####

and

' #### post count update modifichicci mod ##### END

pop_delete mod
It works on my sql forum, but I think no problem with access also

save file as pop_delete.asp and if you haven't made changes overwrite the old file, or compare files..
Title changed by ruirib<

Reply Author: AnonJr
Replied on: 03 June 2007, 09:12
Message:

Will it not decrease the post count if they delete a post made in a forum that doesn't increase the post count?
For instance, if I make a post in the testing forum here it doesn't increase my post count. Would your MOD subtract one post if I deleted said post?<

Reply Author: modifichicci
Replied on: 03 June 2007, 10:30
Message:

Now if forum doesn't increase count, if deleting a post in that forum count doesn't decrease.
File updated. Thanks

(more testing are welcome..)<

Reply Author: modifichicci
Replied on: 03 June 2007, 11:42
Message:

I have checked also for moderate post, if not approved or suspended their deletion doesn't affect count (as it is not affected if not approved..).
File updated.
<

Reply Author: AnonJr
Replied on: 03 June 2007, 13:09
Message:

Nice work.

<

Reply Author: Etymon
Replied on: 04 June 2007, 07:01
Message:

Thanks! Good idea!

<

Reply Author: Hermes
Replied on: 06 June 2007, 09:35
Message:

Modifichicci I get this message when tried to delete individual post:

Microsoft VBScript compilation error '800a03ee'
Expected ')'
/forum/pop_delete.asp, line 634
canUpdate = cLng(getForumCount(Forum_Cat_Replica)
also when tried delete topic

<

Reply Author: modifichicci
Replied on: 06 June 2007, 12:44
Message:

there is a missing ) at the end of the line.. and in some lines after.. sorry..

and thanks..
File updated now.<

Reply Author: Hermes
Replied on: 06 June 2007, 15:16
Message:

Yes, working now.
excellent

<

Reply Author: Shaggy
Replied on: 11 December 2007, 05:14
Message:

Nice work, Mod'

Worked on this meself a couple of times over the years but never got around to finishing it off.
<

Reply Author: modifichicci
Replied on: 11 December 2007, 12:31
Message:

Yes Image, that is true, I have been confused as in pop delete is used strActivePrefix because it works both on A_TABLE and TABLE, but FORUM doesn't need of archived function.
CHange the function to this

function getForumCount(fPostCount)
Dim strSql
Dim rsgetForumCount
'## Forum_SQL
if isNull(fPostCount) then exit function
strSql = "SELECT F_COUNT_M_POSTS "
strSql = strSql & "FROM " & strTablePrefix & "FORUM "
strSql = strSql & "WHERE FORUM_ID = " & cLng(fPostCount)

Set rsgetForumCount = Server.CreateObject("ADODB.Recordset")
rsgetForumCount.Open strSql, my_Conn

if rsgetForumCount.EOF or rsgetForumCount.BOF then
getForumCount = ""
else
getForumCount = rsgetForumCount("F_COUNT_M_POSTS")
end if
rsgetForumCount.Close
Set rsgetForumCount = nothing
end function

Thanks Shaggy!!
I will update the zip on snitzbitz (mod is in approve mod till now..)<

Reply Author: modifichicci
Replied on: 11 December 2007, 13:33
Message:

the errors happens when you try to delete an archive topic, i think, so it was difficult to find

Thanks Image for advice.
<

Reply Author: philsbbs
Replied on: 15 December 2007, 07:08
Message:

thanks for this, ive installed it and it works a treat.<

Reply Author: modifichicci
Replied on: 15 December 2007, 07:25
Message:

you are welcome<

Reply Author: natty
Replied on: 19 April 2008, 20:19
Message:

...was thinking about this myself, but I'm not good with code.
Very nice; thank you ;)<

Reply Author: modifichicci
Replied on: 20 April 2008, 02:47
Message:

thanks

<

Reply Author: Andy Humm
Replied on: 20 April 2008, 08:34
Message:

Great mod and all seems to work.. However, I have noticed the following:
Member adds a post
post count =0
Moderator approves post
post count =1
Member deletes his/her post
Post Count =0
then go to members.asp
Post Count=0 as it should however, the next column 'Last Post' still shows todays date.
Should this database field be triggered to reflect the reduction in post counts.
Obviously when a member has multiple posts if a post is deleted the last post will; read his last posting..
Any solutions please... rgds andy
<

Reply Author: modifichicci
Replied on: 20 April 2008, 10:12
Message:

I think it is a little complicate to reset lastpost date, as we have to memorize previous date and when post is deleted we have to ripristinate old date.. hard with simply coding and without a db modification..<

Reply Author: ruirib
Replied on: 20 April 2008, 10:40
Message:

Originally posted by modifichicci
I think it is a little complicate to reset lastpost date, as we have to memorize previous date and when post is deleted we have to ripristinate old date.. hard with simply coding and without a db modification..

You'd just need a SQL query to be executed...
<

Reply Author: modifichicci
Replied on: 20 April 2008, 10:44
Message:

mmmm but how we can know when an user delete a post? and what of his posts? if you can give some advice on SQL query I can give a look at it..<

Reply Author: ruirib
Replied on: 20 April 2008, 11:27
Message:

Am example query good both for MySQL and SQL Server would be:

Code:


SELECT MAX(T_DATE) As T_DATE FROM
(
SELECT MAX(T_DATE) AS T_DATE FROM FORUM_TOPICS  WHERE T_AUTHOR=1
UNION
SELECT MAX(R_DATE) AS T_DATE FROM FORUM_REPLY WHERE R_AUTHOR=1
UNION
SELECT MAX(R_DATE) AS T_DATE FROM FORUM_A_REPLY WHERE R_AUTHOR=1
UNION
SELECT MAX(T_DATE) AS T_DATE FROM FORUM_A_TOPICS  WHERE T_AUTHOR=1
) As T

You'd just need change it to change it to use the forum_prefix and such and to get the member_id from a suitable forum variable. I don't know the context, so can't help much more.
Running the query will give you the last post date for a user. You can then use it to update it in the members table.<

Reply Author: modifichicci
Replied on: 20 April 2008, 11:37
Message:

and then insert T in M_LASTPOSTDATE in members table where T_AUTHOR etc etc.. right?<

Reply Author: ruirib
Replied on: 20 April 2008, 12:26
Message:

No, T is the alias given to the table resulting from the "inner" UNION. I've just changed the query to have the returning value being named T_VALUE, so insert T_VALUE in M_LASTPOSTDATE in the members table.
I could try a syntax do it all in a single query, but I'm not sure all MySQL versions would support it, or even if the syntax would be valid both for MySQL and SQL Server.<

Reply Author: modifichicci
Replied on: 20 April 2008, 13:12
Message:

it is possible to add also A_REPLY and A_TOPICS in query? Thanks for help, I will try to implement this function also..<

Reply Author: ruirib
Replied on: 20 April 2008, 13:18
Message:

Originally posted by modifichicci
it is possible to add also A_REPLY and A_TOPICS in query? Thanks for help, I will try to implement this function also..

Yeah, it's possible, just changed the SQL for that.<

Reply Author: modifichicci
Replied on: 20 April 2008, 13:23
Message:

ok thanks!
I will work on it. <

Reply Author: ruirib
Replied on: 20 April 2008, 13:33
Message:

You're welcome.<

Reply Author: modifichicci
Replied on: 20 April 2008, 15:18
Message:

I have tryed this query:

Code:

strUpLastPost = Reply_Autore
strSql = "SELECT MAX(T_VALUE) As T_VALUE FROM "
strSql = strSql & "( SELECT MAX(T_DATE) AS T_VALUE FROM " & strTablePrefix & "TOPICS  WHERE T_AUTHOR = " & strUpLastPost
strSql = strSql & " UNION "
strSql = strSql & "SELECT MAX(R_DATE) AS T_VALUE FROM " & strTablePrefix & "REPLY WHERE R_AUTHOR = " & strUpLastPost
strSql = strSql & " UNION "
strSql = strSql & " SELECT MAX(R_DATE) AS T_VALUE FROM " & strTablePrefix & "A_REPLY WHERE R_AUTHOR = " & strUpLastPost
strSql = strSql & " UNION "
strSql = strSql & " SELECT MAX(T_DATE) AS T_VALUE FROM " & strTablePrefix & "A_TOPICS  WHERE T_AUTHOR = " & strUpLastPost
strSql = strSql & ") As T "
response.write strSql
set rs = my_Conn.Execute (strSql)
DataUltimoPost = rs("T_VALUE")
rs.close
set rs = nothing
				
				
strSql = "UPDATE " & strMemberTablePrefix & "MEMBERS "
strSql = strSql & " SET M_LASTPOSTDATE = '" & DataUltimoPost & "'" 
strSql = strSql & " WHERE MEMBER_ID = " & strUpLastPost
       
my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords

and I get this strSql and the error below..

SELECT MAX(T_VALUE) As T_VALUE FROM ( SELECT MAX(T_DATE) AS T_VALUE FROM FORUM_TOPICS WHERE T_AUTHOR = 1 UNION SELECT MAX(R_DATE) AS T_VALUE FROM FORUM_REPLY WHERE R_AUTHOR = 1 UNION SELECT MAX(R_DATE) AS T_VALUE FROM FORUM_A_REPLY WHERE R_AUTHOR = 1 SELECT MAX(T_DATE) AS T_VALUE FROM FORUM_A_TOPICS WHERE T_AUTHOR = 1) As T

error '80040e14'
some advices?<

Reply Author: ruirib
Replied on: 20 April 2008, 15:37
Message:

You're missing a " UNION " between the 3rd and 4th queries. I've added it in red.<

Reply Author: modifichicci
Replied on: 20 April 2008, 16:04
Message:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[MySQL][ODBC 3.51 Driver][mysqld-4.0.27-standard-log]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT MAX(T_DATE) AS T_DATE FROM FORUM_TOPICS WHERE T_AUTHOR

I have changed T_VALUE to T_DATE, as in original Ruirib query, but the same error with T_VALUE instead of T_DATE

<

Reply Author: ruirib
Replied on: 20 April 2008, 17:07
Message:

Without knowing the actual SQL being generated, it's hard to help.
The code, as you posted it earlier, with the UNION added in red, works in MySQL 4.x and 5.x.<

Reply Author: ruirib
Replied on: 20 April 2008, 17:17
Message:

Also, you need to use the exact syntax I used, or you will have issues. Please do not change field names.<

Reply Author: ruirib
Replied on: 20 April 2008, 17:36
Message:

This file works perfectly for replies only. Haven't changed the topics code.<

Reply Author: Andy Humm
Replied on: 20 April 2008, 18:34
Message:

I've just tried ruirib's suggested file and received the folowing error when deleting a members reply to a post:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Circular reference caused by alias 'T_VALUE' in query definition's SELECT list.

/forum/pop_delete.asp, line 127
<

Reply Author: ruirib
Replied on: 20 April 2008, 18:38
Message:

The code does not work for Access.<

Reply Author: Andy Humm
Replied on: 20 April 2008, 18:43
Message:

okay reverted back to original file.. thanks andy<

Reply Author: ruirib
Replied on: 20 April 2008, 18:44
Message:

This file should work for all DBs and replies only. Haven't changed the topics code.<

Reply Author: Andy Humm
Replied on: 21 April 2008, 04:28
Message:

Thank you, will you so kindly be adding the topic code?<

Reply Author: ruirib
Replied on: 21 April 2008, 04:36
Message:

Originally posted by Andy Humm
Thank you, will you so kindly be adding the topic code?

Well, this is modifichicci's mod, I'm just helping out. With the examples given, I think he will be able to provide the code for the topics. If not, I may find some time later in the week to get acquainted with the code, which I really am not at the moment.<

Reply Author: Andy Humm
Replied on: 21 April 2008, 05:05
Message:

Roger, that's copied! Thank you for your valuable input!<

Reply Author: ruirib
Replied on: 21 April 2008, 05:06
Message:

You're welcome.<

Reply Author: modifichicci
Replied on: 21 April 2008, 12:46
Message:

Thank for help, I am sure I need it a lot!!!
With your code I receive the same error

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[MySQL][ODBC 3.51 Driver][mysqld-4.0.27-standard-log]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT MAX(T_DATE) AS T_VALUE FROM FORUM_TOPICS WHERE T_AUTHOR

on deleting reply. ( and on deleting topic also, if can help..)

The file is your file
I cannot understand what is the issue.. I am blockhead I am afraid..<

Reply Author: ruirib
Replied on: 21 April 2008, 16:07
Message:

Can it be that MySQL 4.0 does not support the syntax? I can only access 4.1 and 5.x versions... If your server allows remote access and you don't mind sending me the access data, I may try the query from SQLYog.<

Reply Author: modifichicci
Replied on: 21 April 2008, 16:53
Message:

I have to ask to the admin of the site, on my side there are no problem, in the meantime i will try an installation on a mysql5 server to see if it works. Thanks for your availability<

Reply Author: ruirib
Replied on: 21 April 2008, 17:06
Message:

Ok, will wait for your input. I've tested the query in my local 5.0.19 version and a 4.1 server to which I have access and it runs without issues.<

Reply Author: modifichicci
Replied on: 22 April 2008, 15:04
Message:

Running well in Mysql 5. Not on MySql 4.0.27..<

Reply Author: ruirib
Replied on: 22 April 2008, 15:51
Message:

Yeah, that figures. MyBadSQL!<

Reply Author: modifichicci
Replied on: 23 April 2008, 14:47
Message:

Well, to bypass the problem I have tryed this solution:

instead of ruirib's query, i have put
strUpLastPost = Reply_Autore
DataUltimoPost = getultimopost(strUpLastPost)

and created the function

Code:

function getultimopost(strUpLastPost)
strSql = "SELECT MAX(T_DATE) AS T_VALUE FROM " & strTablePrefix & "TOPICS  WHERE T_AUTHOR = " & strUpLastPost
set rs = my_Conn.Execute (strSql)
if not(rs.eof or rs.bof) then
DataUltimoPostT = rs("T_VALUE")
else
DataUltimoPostT = ""
end if
rs.close
set rs = nothing
strSql = "SELECT MAX(R_DATE) AS T_VALUE FROM " & strTablePrefix & "REPLY  WHERE R_AUTHOR = " & strUpLastPost
set rs = my_Conn.Execute (strSql)
if not(rs.eof or rs.bof) then
DataUltimoPostR = rs("T_VALUE")
else
DataUltimoPostR = ""
end if
rs.close
set rs = nothing
strSql = "SELECT MAX(R_DATE) AS T_VALUE FROM " & strTablePrefix & "A_REPLY  WHERE R_AUTHOR = " & strUpLastPost
set rs = my_Conn.Execute (strSql)
if not(rs.eof or rs.bof) then
DataUltimoPostRA = rs("T_VALUE")
else
DataUltimoPostRA = ""
end if
rs.close
set rs = nothing
strSql = "SELECT MAX(T_DATE) AS T_VALUE FROM " & strTablePrefix & "A_TOPICS  WHERE T_AUTHOR = " & strUpLastPost
set rs = my_Conn.Execute (strSql)
if not(rs.eof or rs.bof) then
DataUltimoPostTA = rs("T_VALUE")
else
DataUltimoPostTA = ""
end if
rs.close
set rs = nothing
if DataUltimoPostT > DataUltimoPostR then
	DUpost = DataUltimoPostT
else
	DUpost = DataUltimoPostR
end if

if DataUltimoPostTA > DataUltimoPostRA then
	DUpostA = DataUltimoPostTA
else
	DUpostA = DataUltimoPostRA
end if
if DUpost > DUpostA then
 	getultimopost = DUpost
 else
 	getultimopost = DUpostA
end if

end function

no error now.. but.. it worked once with admin and then it get a null value for last post date.. I think there is a logical error in my routine, but i cannot find it and maybe there is a better way to find the max value of four, but I am a surgeon and this is a pure hobby, so if someone can give me some advices he is welcome..

<

Reply Author: ruirib
Replied on: 23 April 2008, 15:26
Message:

You will need to test for Null value, since if the user has no posts, max(t_Date) will return a null value. So, test for NULL and if the date is null, set the value for the last post date to ''.<

Reply Author: modifichicci
Replied on: 23 April 2008, 15:35
Message:

that isn't done by bof and eof?<

Reply Author: ruirib
Replied on: 23 April 2008, 15:39
Message:

No, because there will be a record, with a single column, but that column will have a null value.<

Reply Author: modifichicci
Replied on: 23 April 2008, 16:35
Message:

I have checked the dates
with
if DataUltimoPostR > DataUltimoPostRA then
response.write "DataUltimoPostR" & DataUltimoPostR
else
response.write "Dati non corrispondenti"
end if
response.write "DataUltimoPostT" & DataUltimoPostT
response.write "DataUltimoPostRA" & DataUltimoPostRA
response.write "DataUltimoPostTA" & DataUltimoPostTA

and dates are selected correctly, and when there is no reply or topics from the author the value is null. But the if statement
if DataUltimoPostR > DataUltimoPostRA then
with DataUltimoPostR = 20080423223022
and DataUltimoPostRA = "" isn't verify and I get always "Dati non corrispondenti"
near the goal but so far..

<

Reply Author: ruirib
Replied on: 23 April 2008, 18:19
Message:

A null date is not comparable, you need to test it with IsNull(DataUltimoPostRA) or whatever variable you want to test for Null.<

Reply Author: modifichicci
Replied on: 25 April 2008, 05:30
Message:

I think I have solved:
here is the new pop_delete.asp

pop_delete.asp
I have changed the function to:

Code:


function getultimopost(strUpLastPost)
strControllo = "00000000000000"
strSql = "SELECT MAX(T_DATE) AS T_VALUE FROM " & strTablePrefix & "TOPICS  WHERE T_AUTHOR = " & strUpLastPost
set rs = my_Conn.Execute (strSql)
if not(rs.eof or rs.bof) then
DataUltimoPostT = trim(rs("T_VALUE"))
	if IsNull(DataUltimoPostT) then DataUltimoPostT = strControllo
else
DataUltimoPostT = strControllo
end if
rs.close
set rs = nothing
strSql = "SELECT MAX(R_DATE) AS T_VALUE FROM " & strTablePrefix & "REPLY  WHERE R_AUTHOR = " & strUpLastPost
set rs = my_Conn.Execute (strSql)
if not(rs.eof or rs.bof) then
DataUltimoPostR = trim(rs("T_VALUE"))
	if IsNull(DataUltimoPostR) then DataUltimoPostR = strControllo
else
DataUltimoPostR = strControllo
end if
rs.close
set rs = nothing
strSql = "SELECT MAX(R_DATE) AS T_VALUE FROM " & strTablePrefix & "A_REPLY  WHERE R_AUTHOR = " & strUpLastPost
set rs = my_Conn.Execute (strSql)
if not(rs.eof or rs.bof) then
DataUltimoPostRA = trim(rs("T_VALUE"))
	if IsNull(DataUltimoPostRA) then DataUltimoPostRA = strControllo
else
DataUltimoPostRA = strControllo
end if
rs.close
set rs = nothing
strSql = "SELECT MAX(T_DATE) AS T_VALUE FROM " & strTablePrefix & "A_TOPICS  WHERE T_AUTHOR = " & strUpLastPost
set rs = my_Conn.Execute (strSql)
if not(rs.eof or rs.bof) then
DataUltimoPostTA = trim(rs("T_VALUE"))
	if IsNull(DataUltimoPostTA) then DataUltimoPostTA = strControllo
else
DataUltimoPostTA = strControllo
end if
rs.close
set rs = nothing
if StrComp(DataUltimoPostT, DataUltimoPostR) = 1 then 
	DUpost = DataUltimoPostT
else
	DUpost = DataUltimoPostR
end if

if StrComp(DataUltimoPostTA, DataUltimoPostRA) = 1 then
	DUpostA = DataUltimoPostTA
else
	DUpostA = DataUltimoPostRA
end if
if StrComp(DUpost, DUpostA) = 1 then 
 	getultimopost = DUpost
 else
 	getultimopost = DUpostA
end if
if getultimopost = strControllo then getultimopost = ""
end function

and changed the Ruirib routine to

Code:


strUpLastPost = Reply_Autore ' or Topic etc in different section
DataUltimoPost = getultimopost(strUpLastPost)
strSql = "UPDATE " & strMemberTablePrefix & "MEMBERS "
strSql = strSql & " SET M_LASTPOSTDATE = '" & DataUltimoPost & "'" 
strSql = strSql & " WHERE MEMBER_ID = " & strUpLastPost
 my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords

If someone knows how to optimize the function, he is welcome.<

Reply Author: natty
Replied on: 27 April 2008, 22:43
Message:

very nice<

Decrease members post count on post deletion

Topic

Replies ...