redundant posts | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

redundant posts

Hi all, Is there a simple way using SQL to prevent redundant posts? I have a script that accepts post from users, it works and all. But if the hosting server experiences any sort of lag, users normally hit the "post" button repeatively which results in redundant posts. Trying to prevent this. Thanks in advance,
a8le. http://www.noteful.com – read and share anonymous online secrets and confessions.
DO you mean duplicate heading posts, I believe you can manage that with a script to run every day to find and delete them from database. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
What app are you running? If you want to prevent a time lag, perhaps your database needs tuning, such as adding a missing index, or some other sort of performance tweak. Without more information, it is hard to make a suggestion at this time. ——————————–
Brad M. McGehee, SQL Server MVP
http://www.sqlbrad.com
Assuming you are using stored procedures to do the INSERTs, you can check in your code<br /><pre id="code"><font face="courier" size="2" id="code"><br />IF NOT EXISTS( SELECT * FROM yourTable WHERE &lt;condition&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ /><br />BEGIN<br /> — do the issnert<br />END<br /></font id="code"></pre id="code"><br /><br />This will prevent any duplicate posts, even if the user clicks the submit buttom multiple times.<br /><br />***********************<br />Dinakar Nethi<br />SQL Server MVP<br />***********************<br /<a target="_blank" href=http://weblogs.sqlteam.com/dinakar/>http://weblogs.sqlteam.com/dinakar/</a>
You can also try to manage from the front end application where app takes first click grayed out your post button once click is completed. MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

Hi all, Thank you for all the good ideas. The website is programmed using ASP.net v1.1. And before coming here I tried several times to make the button disappear after it was clicked using button.visible = false. I also tried using javascript to display a progress bar but I couldn’t get it to work. And after thinking about it… preventing it by editing my stored procedure was the best option. So ndinakar, here is my stored proc, can you (or anyone) help me with the syntax/code…
CREATE PROCEDURE dbo.Blog_Comments_Add
@BlogID bigint,
@Comment varchar(3000),
@Date datetime
AS
INSERT INTO Blog_Comments (
[BlogID],
[Comment],
[Date]
)
VALUES (
@BlogID,
@Comment,
@Date
)
select SCOPE_IDENTITY()
GO So I guess the logic would be…
if (newIncomingComment <> latestComment of Blog) then
add the new comment
else
don’t add Is this a good way of think on my problem? Is the logic right? Oh, I asked this because what if it was the first comment? There won’t be anything to compare to… Thanks again,
a8le http://www.noteful.com – read and share anonymous online secrets and confessions.
*bump* http://www.noteful.com – read and share anonymous online secrets and confessions.
someone please… ? http://www.noteful.com – read and share anonymous online secrets and confessions.
CREATE PROCEDURE dbo.Blog_Comments_Add
@BlogID bigint,
@Comment varchar(3000),@Date datetime
AS
INSERT INTO Blog_Comments ([BlogID],[Comment],[Date])
select @BlogID,@Comment,@Date from Blog_Comments
where not exists (select 1 from Blog_Comments where Comment = @Comment)
select SCOPE_IDENTITY() — make sure you have the index on "Comment " column…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

I would do something like this: CREATE PROCEDURE dbo.Blog_Comments_Add
@BlogID bigint,
@Comment varchar(3000),
@Date datetimeASINSERT
As
BEGIN
SET NOCOUNT ON IF NOT EXISTS ( SELECT * FROM Blog_Comments WHERE Comment = @Comment)
INTO Blog_Comments ([BlogID],[Comment],[Date])
VALUES (@BlogID,@Comment,@Date)
select SCOPE_IDENTITY()
SET NOCOUNT ON
END
This way even if your button is pressed N number of times, the value will only be inserted once. ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/
Got it working using…<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />CREATE PROCEDURE dbo.Blog_Comments_Add<br />@BlogID bigint,<br />@Comment varchar(3000),<br />@Date datetime<br />AS<br />Begin<br />IF EXISTS ( SELECT *<br />FROM Blog_Comments<br />WHERE [CommentID] = (select max(CommentID) from Blog_Comments) <br />AND [Comment] = @Comment )<br /><br />begin <br />SELECT @Comment = @@ERROR<br />RETURN @Comment<br />end<br />ELSE <br />Begin<br />INSERT INTO Blog_Comments (<br />[BlogID],<br />[Comment],<br />[Date]<br />VALUES (<br />@BlogID,<br />@Comment,<br />@Date<br />End <br />End<br />select SCOPE_IDENTITY()<br />GO</font id="code"></pre id="code"><br /><br />Thank you so much for all the advice and insight. BTW, is my proc "tuned-up-able?" <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br />Best Regards,<br />a8le<br /><br /<a target="_blank" href=http://www.noteful.com>http://www.noteful.com</a> – read and share anonymous online secrets and confessions.
The SELECT SCOPE_IDENTITY() should follow the INSERT in the same Begin/End loop, not outside the loop.
Also, your @@ERROR value will most likely be a 0 in your IF EXISTS block, since EXISTS () will return a TRUE or FALSE which means the expression got evaluated successfully and @@ERROR will not get incremented. So, there’s nothing much you need to do in the IF EXISTS() block. ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/
i made the suggestted change. again, thank you for all the help and advice. -a8le http://www.noteful.com – read and share anonymous online secrets and confessions.
]]>