Preventing double posting to Access database

[Peter N Roth]

Our website accepts data posted by clients from forms
using the FP generated method="POST" action="--webbot-self--".
The data are stored in an Access database.

The tables are designed with multiple fields A, B, and C
as the primary key, to prevent duplicate records. These
fields are xmitted as hidden fields.

On rare occasions, duplicate records get posted anyway.
It's as if the record is submitted in duplicate simultaneously,
from the same client. The Jet engine eats both of them.

So what do i do? Move to SQL Server? Lock the database
in some way beyond what FP does?

 

[Kevin Spencer]

Hi Peter,

Grace and Peace to you!

This problem isn't due to the FrontPage DRW, nor is it related to the
database product you're using. The problem is due to the nature of the WWW.
When a Form is posted, it posts to an application running on the server. In
this case, the application has instructions to take the form data and insert
a new record. If the form page is refreshed after the post, the page is
re-posted (this is the way browsers behave - all of them), and the
application re-inserts the data. HTTP is stateless, which means that each
Request/Response from a browser happens "in a vaccuum" so to speak, with no
prior knowledge of any activity from before.

There are ways around this, but you won't find them built into the fairly
simple FrontPage DRW. All of them require custom programming. Some
alternatives include:

1. Using a Session variable to keep track of what data was just posted. You
would have to record ALL of the data in the form, and programmatically
disallow a re-post if all of the data is the same.
2. Doing a Response.Redirect from the Server to a different page after the
data is posted. This ensures that the page which posted the form is one page
back in the history list. If the page returned is refreshed, it does
nothing.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

 

[Peter N Roth]

Thanks Kevin.



Yes, indeed TH, and shows 1 way how two copies

of the data could arrive at the server simultaneously.

Alternatively, I'm imagining Nervous User who

clicks the Submit button twice because "nothing

happens" immediately, rather than a page refresh.



However again... Notwithstanding that

the www is stateless, Jet gets two records with

the identical primary key and accepts them both.

How is that possible (I mean, I believe my eyes,

but I don't believe my eyes)? DBs aint s'posed

to do that - they're supposed to return an error

rather than posting the data. Ding! the idea lightbulb

goes off - I bet I have allowed duplicate keys...

Yep!



Well, folks, i'm glad we had this little chat...

;o)

--
Grace + Peace,
Peter N Roth
Engineering Objects International
http://engineeringobjects.com

Hi Peter,

Grace and Peace to you!

This problem isn't due to the FrontPage DRW, nor is it related to the
database product you're using. The problem is due to the nature of the

WWW.

 

[Kevin Spencer]

Hi Peter,

You notice I didn't say anything about Primary Keys in my message. The
problem has nothing to do with Priimary Keys at all, but duplicate data.
There is no way to know whether a record is a duplicate of another before it
is inserted, other than to compare ALL of the values posted against each
other. Autonumber fields are added DURING an INSERT, and are therefore
irrelevant. If your table includes a Primary Key which you are allowing a
user to enter, you are in danger of primary key violation. However, I don't
think that's the case, as your complaint is that the database is ALLOWING
duplicate INSERTS, and if it didn't allow duplicate values in any single
column the INSERT would fail.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

 

[Peter N Roth]

Hi Peter,

You notice I didn't say anything about Primary Keys in my message. The
problem has nothing to do with Priimary Keys at all, but duplicate data.
There is no way to know whether a record is a duplicate of another before it
is inserted, other than to compare ALL of the values posted against each
other. Autonumber fields are added DURING an INSERT, and are therefore
irrelevant. If your table includes a Primary Key which you are allowing a
user to enter, you are in danger of primary key violation. However, I don't
think that's the case, as your complaint is that the database is ALLOWING
duplicate INSERTS, and if it didn't allow duplicate values in any single
column the INSERT would fail.

the tough part of solving problems is stating what the problem IS. Stating
the problem correctly facilitates solution ~ but problems hide themselves
because we dont understand what's going wrong.

I misunderstood what was happening, and misdescribed the problem.

It is exactly as you say: a primary key violation. The DB was built with
autonumbers, but the key is a 3-field key which should have been built
as a 3-field combo. The combo provides uniqueness, whereas each of
the 3 individual keys may have duplicates.

Thx again!