When
asked the question, "How do I prevent previously submitted form data
from being reinserted into the database when the user presses the
browser's Refresh button?" my instinctive response is, "Why on Earth
would the user be pressing the Refresh button?" However, if you have
been in application development for any length of time, you will know
that end users have no limit to their creative approaches to application
navigation. And they will indeed press that Refresh button even though
there is no logical reason to do so. So you need to program defensively
to handle the problem of a Refresh request.
To duplicate this problem, I set
up a simple ASP.NET page that collects first name and last name data
and, upon the click of a submit button, inserts it into the Employees
table in SQL Server 2000's Northwind database. The default behavior of
such a page, without any special programming, is to submit the form data
upon the button click, insert the data into the database, and bring the
user back to the page with the first name and last name textboxes still
populated. Pressing the form's submit button again will reinsert the
same data into the database, as will pressing the browser's Refresh
button. One would reasonably expect that pressing the submit button
again would reinsert the same data into the database; however, this is
not the behavior one would expect with the Refresh button.
Listing 1 - Simple Web Form Used for All Tests
<html>
<head>
<title>Preventing Duplicate Record Insertion on Page Refresh</title>
</head>
<body>
<form runat="server">
<p>First Name <asp:TextBox id="firstName" runat="server" />
<p>Last Name <asp:TextBox id="lastName" runat="server" />
<p><asp:Button id="Button1" onclick="Button1_Click" runat="server" Text="Add Employee" />
<p><asp:Label id="Message" runat="server" />
</form>
</body>
</html>
The purpose of this article is
to discover how to prevent the data from being reinserted into the
database when the browser's Refresh button is pressed.
| |
| Ideas That Did Not Work | |
[ Back To Top ]
Before we look at solutions that do work, let's look at ideas that did not work.
Delving into troubleshooting
mode, the first idea I had was, "Well, blank out your form elements and
you should be good to go'. But wait, not so fast. Due to the nature of
web pages and posted data, the form values are still held by the
browser. A refresh of the page will post the data again and the data
will wind up in your database an additional time. This has nothing to
do with ASP.NET in particular; it is a built-in browser behavior. The
same thing can happen with PHP, ASP, and any other type of web page.
My next idea was to use a hidden
field on the form that would be set to an initial value on the first
page load. Before the database insertion is performed, that hidden
field would be evaluated and if it contains the expected initial value
then the insert would be permitted. Following the insert the hidden
field would then be set to a different value. However, this again does
not work because with the refresh the form's previously posted data is
resent, and the hidden field still contains that initial value.
Pulling the next trick out of
the bag, I changed the above approach to use a Session variable instead
of a hidden form field. And lo and behold this approach worked. It
actually worked so well that once one record was successfully inserted
into the database, no more could be added, even intentionally, since
once that Session variable was set there was no way to know when it was
okay to reset it to the initial value. This is not a feasible solution
because a web application would likely need to write more than one
record to the database.
The last approach I tried
without success was disallowing the caching of the page on the user’s
browser. This is accomplished by adding the following to the Page.Load
handler:
Response.Cache.SetExpires(DateTime.Now.AddDays(-1))
Response.Cache.SetCacheability(HttpCacheability.NoCache)
Response.Cache.SetValidUntilExpires(false)
The
intent here was to force the user's browser to call for a fresh version
of the page each time. However, this had no effect on the posted data
in the header, and the Refresh button still had the effect of reposting
the form data.
Now that we have considered a few ideas, and found that they do not work, let's look at some solutions that do work.
| |
| Solutions That Work by Clearing the Header | |
[ Back To Top ]
A simple solution is to
Response.Redirect back to the same page after the INSERT command is
called. This will call up the page without transmitting any post
headers to it. Using Request.Url.ToString() as the first parameter of
Response.Redirect will cause both the URL and the page's querystring to
be included in the redirect. The use of false as the second parameter
will suppress the automatic Response.End that may otherwise generate a
ThreadAbortedException. A disadvantage of this approach is that any
ViewState that had been built up will be lost.
Listing 2 – Server-Side Code Using Response.Redirect
Sub Button1_Click(sender As Object, e As EventArgs)
If AddEmployee(firstName.Text, lastName.Text) = 0
Message.Text = "Success"
Response.Redirect(Request.Url.ToString(), false) ' will include the querystring
Else
Message.Text = "Failure"
End If
firstName.Text = ""
lastName.Text = ""
End Sub
Function AddEmployee(firstName As String, lastName As String) As Integer
Dim connectionString As String
connectionString = "server='(local)'; trusted_connection=true; database='Northwind'"
Dim dbConnection As New SqlConnection(connectionString)
Dim insertString As String
insertString = "INSERT INTO Employees (FirstName, LastName) VALUES (@FirstName, @LastName)"
Dim dbCommand As New SqlCommand
dbCommand.CommandText = insertString
dbCommand.Connection = dbConnection
dbCommand.Parameters.Add(New SqlParameter("@FirstName",SqlDbType.NVarchar,10))
dbCommand.Parameters("@FirstName").Value = firstName
dbCommand.Parameters.Add(New SqlParameter("@LastName",SqlDbType.NVarchar,20))
dbCommand.Parameters("@LastName").Value = lastName
Dim rowsAffected As Integer = 0
Dim commandResult As Integer = 1
Try
dbConnection.Open
rowsAffected = dbCommand.ExecuteNonQuery
If rowsAffected > 0 Then commandResult = 0
Catch ex AS SqlException
commandResult = ex.Number
Finally
dbConnection.Close
End Try
Return commandResult
End Function
As a side note, I thought that
Server.Transfer would be a more lightweight alternative to
Response.Redirect; however this method does not clear the post data from
the header and hence does not solve the refresh problem.
A related approach would be for
the form to submit to an intermediate processing page and then
Response.Redirect back to the calling page, similar to the classic ASP
approach to form processing. This has the same effect as simply using
the Response.Redirect in the Button_Click event so it has the same
disadvantages, with the added disadvantage of creating another page for
the website developer to manage.
| |
| Solutions That Work by Detecting the Browser's Refresh | |
[ Back To Top ]
The next batch of solutions works by determining
whether the user has refreshed the page in the browser instead of
pressing the form's submit button. All of these solutions depend on the
ability of the website to use Session variables successfully. If the
website uses cookie-based Sessions, but the user's browser does not
permit the use of cookies, these solutions would all fail.
Additionally, should the Session expire these solutions would also
fail.
A simple way to implement
refresh trapping is by the use of a date/time stamp held in a ViewState
variable and a date/time stamp held in the user's Session. When the
page is first loaded, a Session variable is populated with the current
date/time. On the page's PreRender event, a ViewState variable is set
to the value of the Session variable. These two values are compared to
each other immediately before the database INSERT command is run. If
they are equal, then the command is permitted to execute and the Session
variable is updated with the current date/time, otherwise the command
is bypassed. Should the user refresh the page, the ViewState variable
will be repopulated from the post header, so then the ViewState and
Session variables will no longer hold the same values, and the INSERT
command will not run. Note that ViewState needs to be enabled on the
page for this to work; if ViewState is not enabled then a hidden form
field may be used instead.
Listing 3 – Server-Side Code Using Session and ViewState Variables
Sub Page_Load (sender As Object, e As EventArgs)
If Not Page.IsPostBack
Session("update") = Server.URLEncode(System.DateTime.Now.ToString())
End If
End Sub
Sub Page_PreRender (sender As Object, e As EventArgs)
ViewState("update") = Session("update")
End Sub
Sub Button1_Click(sender As Object, e As EventArgs)
If Session("update").ToString() = ViewState("update").ToString() Then
If AddEmployee(firstName.Text, lastName.Text) = 0
Message.Text = "Success"
Session("update") = Server.URLEncode(System.DateTime.Now.ToString())
Else
Message.Text = "Failure"
End If
Else
Message.Text = "Failure - Session"
End If
firstName.Text = ""
lastName.Text = ""
End Sub
A similar approach can be seen in Dino Esposito's article, titled Build Your ASP.NET Pages on a Richer Bedrock.
This article makes some suggestions on creating a custom base Page
class that includes, among other things, trapping the browser refresh.
His methodology is a lot more complex than what I describe above, and
involves the use of a hidden form field and a Session variable. The
advantage it has is that the code extends the Page class, so the only
code that is needed is a simple check of the Boolean property named
IsPageRefresh before the INSERT command is executed. An additional
advantage is that the page refresh can be trapped for a variety of
scenarios, not just for the prevention of duplicate records which we are
concerned with here.
| |
| Solutions That Work By Trapping at the Database Level | |
[ Back To Top ]
Should the user somehow manage to circumvent the
two solutions described above, the last line of defense is at the
database. There are two methods that can be employed to prevent a
duplicate record from being inserted into the database. For each
method, I've moved the SQL code into a stored procedure, since there are
now more processing steps involved and these are easier to illustrate
in a separate stored procedure. Note however that a stored procedure is
not strictly required in order for these methods to work.
The first method is to check
whether the record exists in the database table before inserting it. If
the record does exist then the user will receive an error message. The
stored procedure in Listing 4 first does a lookup in the
Employees table to see if the supplied FirstName and LastName
combination already exists in the table. If it does then a -1 result
will be returned to the calling code. If it does not, then an INSERT is
attempted and the @@ERROR value is returned to the calling code. The
code in Listing 5 checks the stored procedure's RETURN value and displays a corresponding message to the user.
Listing 4 – Stored Procedure spAddEmployee_UsingExists
CREATE PROCEDURE spAddEmployee_UsingExists
(
@FirstName varchar(50),
@LastName varchar(50)
)
AS
DECLARE @Result int
BEGIN TRANSACTION
IF EXISTS
(
SELECT
NULL
FROM
Employees WITH (UPDLOCK)
WHERE
FirstName = @FirstName AND
LastName = @LastName
)
BEGIN
SELECT @Result = -1
END
ELSE
BEGIN
INSERT INTO
Employees
(
FirstName,
LastName
)
VALUES
(
@FirstName,
@LastName
)
SELECT @Result = @@ERROR
END
IF @Result <> 0
BEGIN
ROLLBACK
END
ELSE
BEGIN
COMMIT
END
RETURN @Result
Listing 5 – Server-Side Code Using Exists
Sub Button1_Click(sender As Object, e As EventArgs)
Dim addResult As Integer = 0
addResult = AddEmployee(firstName.Text, lastName.Text)
Select addResult
Case Is = 0
Message.Text = "Success"
Case Is = -1
Message.Text = "Failure - record already exists"
Case Else
Message.Text = "Failure"
End Select
firstName.Text = ""
lastName.Text = ""
End Sub
Function AddEmployee(firstName As String, lastName As String) As Integer
Dim connectionString As String
connectionString = "server='(local)'; trusted_connection=true; database='Northwind'"
Dim dbConnection As New SqlConnection(connectionString)
Dim dbCommand As New SqlCommand
dbCommand.CommandText = "spAddEmployee_UsingExists"
dbCommand.CommandType = CommandType.StoredProcedure
dbCommand.Connection = dbConnection
dbCommand.Parameters.Add(New SqlParameter("@FirstName",SqlDbType.NVarchar,10))
dbCommand.Parameters("@FirstName").Value = firstName
dbCommand.Parameters.Add(New SqlParameter("@LastName",SqlDbType.NVarchar,20))
dbCommand.Parameters("@LastName").Value = lastName
dbCommand.Parameters.Add(New SqlParameter("@Result",SqlDbType.Int))
dbCommand.Parameters("@Result").Direction = ParameterDirection.ReturnValue
Dim commandResult As Integer = 1
Try
dbConnection.Open
dbCommand.ExecuteNonQuery
commandResult = CType(dbCommand.Parameters("@Result").Value,Integer)
Catch ex AS SqlException
commandResult = ex.Number
Finally
dbConnection.Close
End Try
Return commandResult
End Function
The second method is to make use of the database
table's ability to enforce a unique constraint. To add a unique
constraint on the Last Name and First Name columns of the Employee
table, run this SQL command in Query Analyzer:
CREATE UNIQUE INDEX [LastFirstUnique] ON Employees ([LastName], [FirstName])
With this method, there is no
preliminary check to see if the record already exists; just let the
database return an error when it tries to insert a duplicate record and
check for this exception. This method of course requires that the
database allows for unique constraints. With SQL Server, when the
constraint is violated, error code 2601 is raised and returned to the
calling code. Note that the stored procedure has been stripped of its
initial EXISTS check. The disadvantage of this approach is that relying
on exceptions for programming logic is considered to be a bad
practice.
To remove the unique constraint on the Employee table created above, run this SQL command in Query Analyzer:
DROP INDEX [dbo].[Employees].[LastNameFirstNameUnique]
Listing 6 – Stored Procedure spAddEmployee_UsingSQLException
CREATE PROCEDURE spAddEmployee_UsingSQLException
(
@FirstName varchar(50),
@LastName varchar(50)
)
AS
INSERT INTO
Employees
(
FirstName,
LastName
)
VALUES
(
@FirstName,
@LastName
)
Listing 7 – Server-Side Code Using SQLException
Sub Button1_Click(sender As Object, e As EventArgs)
Dim addResult As Integer = 0
addResult = AddEmployee(firstName.Text, lastName.Text)
Select addResult
Case Is = 0
Message.Text = "Success"
Case Is = 2601
Message.Text = "Failure - record already exists"
Case Else
Message.Text = "Failure: " & addResult.ToString()
End Select
firstName.Text = ""
lastName.Text = ""
End Sub
Function AddEmployee(firstName As String, lastName As String) As Integer
Dim connectionString As String
connectionString = "server='(local)'; trusted_connection=true; database='Northwind'"
Dim dbConnection As New SqlConnection(connectionString)
Dim dbCommand As New SqlCommand
dbCommand.CommandText = "spAddEmployee_UsingSQLException"
dbCommand.CommandType = CommandType.StoredProcedure
dbCommand.Connection = dbConnection
dbCommand.Parameters.Add(New SqlParameter("@FirstName",SqlDbType.NVarchar,10))
dbCommand.Parameters("@FirstName").Value = firstName
dbCommand.Parameters.Add(New SqlParameter("@LastName",SqlDbType.NVarchar,20))
dbCommand.Parameters("@LastName").Value = lastName
Dim commandResult As Integer = 1
Try
dbConnection.Open
dbCommand.ExecuteNonQuery
commandResult = 0
Catch ex AS SqlException
commandResult = ex.Number
Finally
dbConnection.Close
End Try
Return commandResult
End Function
Final My recommendation to use session variable for full proof:-
1. How to its work
2. How to write a code 1. How Its Work:- first we comes on our page this will check the session variable is exists on our page if its not exists then only this will insert the record after insertion we will set session variable 2. How to write a code:- initially while we come on the insertion screen we check the session is exists then we will remove from it. and on the insertion we first check that session is exists on not, if its not exists (null) then we only execute insert statement and reset the session variable to any string for Example check my code
protected void btnNewAdd_Click(object sender, EventArgs e)
{
mvDepartment.SetActiveView(vAdd);
Session.Remove("Inserted");
}
protected void btnInsert_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
if (Session["Inserted"] == null)
{
//Write Insert code here
Session["Inserted"] = "Inserted";
}
}
}
Another Way to Execute the code if you don't have additional layer(add button)
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindData();
Session["Inserted"] = "Inserted";
}
}
protected void btnInsert_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
if (Session["Inserted"] != null)
{
//Write Insert Code here
Session.Remove("Inserted");
}
BindData();
}
}
|
Monday, August 20, 2012
Avoid Inserting Duplicate Record on Page Refresh
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment