|
 |
asp_databases thread: Created Unintentional Duplicate Records
Message #1 by sg48@y... on Tue, 29 May 2001 18:33:27
|
|
I am trying to write a add a unique record into an Access2000 database
using ASP 3 and Personal Web Server. First I have an update instruction-
then a block of code that sets up the recordset then an update
instruction. The problem is that sometime (not consistently) two duplicate
entries are created even though only one was intended. What am I doing
wrong? - Steve
Message #2 by "Dallas Martin" <dmartin@z...> on Tue, 29 May 2001 16:45:20 -0400
|
|
What is unqiue about your record: One field, two fields,...,all fields?
Do you want a One to Many, One to One, or Many to Many relationship?
In other words what do you mean by unique?
Does your db design allow multiple instances of the same data?
For additional information, visit http://sqlcourse.com/ This site offers a
free online course
in SQL.
Dallas
----- Original Message -----
From: <sg48@y...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, May 29, 2001 6:33 PM
Subject: [asp_databases] Created Unintentional Duplicate Records
> I am trying to write a add a unique record into an Access2000 database
> using ASP 3 and Personal Web Server. First I have an update instruction-
> then a block of code that sets up the recordset then an update
> instruction. The problem is that sometime (not consistently) two duplicate
> entries are created even though only one was intended. What am I doing
> wrong? - Steve
Message #3 by sg48@y... on Tue, 29 May 2001 22:43:35
|
|
I should have said "First I have an 'addnew' instruction...."
> I am trying to write a add a unique record into an Access2000 database
> using ASP 3 and Personal Web Server. First I have an update instruction-
> then a block of code that sets up the recordset then an update
> instruction. The problem is that sometime (not consistently) two
duplicate
> entries are created even though only one was intended. What am I doing
Message #4 by sg48@y... on Wed, 30 May 2001 04:37:09
|
|
What I meant about unique is the following:
I have a form for a user to enter some personal data and submit it for
entry into an Acess 2000 data base. The data will go into one table that
has data on people that want to volunteer for a political campaign. The
problem that I am having is that when I write the record, two identical
new records show up
in the data base instead of just one. I have intentionally configured the
data base to allow duplicates so that I don't get error messages. I have
tried writing the data using a SQL statement but had the same problem.
What is strange is that it doesn't happen consistently. I hope that
explains it better.
The code that does the addition of the record is below ( I deleted some of
the fields
Dim rsVolunteer
Set rsVolunteer = Server.CreateObject("ADODB.Recordset")
rsVolunteer.Open "tblvolunteer", objConn, adOpenForwardOnly,
adLockOptimistic, adCmdTable
rsVolunteer.AddNew
rsVolunteer("title") = Request.Form("title")
rsVolunteer("FirstName") = Request.Form("FirstName")
rsVolunteer("LastName") = Request.Form("LastName")
rsVolunteer("streetaddress") = Request.Form("Address")
rsVolunteer.Update ' update the database
rsVolunteer.close
Set rsVolunteer = nothing
objConn.Close
set objConn= nothing
> What is unqiue about your record: One field, two fields,...,all fields?
> Do you want a One to Many, One to One, or Many to Many relationship?
> In other words what do you mean by unique?
>
> Does your db design allow multiple instances of the same data?
>
> For additional information, visit http://sqlcourse.com/ This site
offers a
> free online course
> in SQL.
>
> Dallas
>
>
> ----- Original Message -----
> From: <sg48@y...>
> To: "ASP Databases" <asp_databases@p...>
> Sent: Tuesday, May 29, 2001 6:33 PM
> Subject: [asp_databases] Created Unintentional Duplicate Records
>
>
> > I am trying to write a add a unique record into an Access2000 database
> > using ASP 3 and Personal Web Server. First I have an update
instruction-
> > then a block of code that sets up the recordset then an update
> > instruction. The problem is that sometime (not consistently) two
duplicate
> > entries are created even though only one was intended. What am I doing
> > wrong? - Steve
Message #5 by "Dallas Martin" <dmartin@z...> on Wed, 30 May 2001 07:44:08 -0400
|
|
There is at least two reasons that two identical records are entered into
your database:
The user is submitting the data twice. This may be cause by slow response
time
on the user's connection. The scenario goes like this: The user clicks the
submit button,
the application doesn't provide any feedback to indicate that the form has
been submitted,
the user clicks the submit button again. Voila, two records are now in your
database.
Another problem is the "back" navigation browser button. Sometimes a user
will navigate
back to a previous page and re-submit the data. This can happen if the user
"refreshes"
a previously submitted page.
You should change your application design. First, you shouldn't allow
duplicates in your
table. You should issue a SELECT statement against the table before adding a
record.
This statement should be constructed to select the minimum number of fields
that are
consistent with a duplicate record. For example, I would hope that you have
an autonumber
field in your volunteers table.
Then you would SELECT volunteer_id FROM volunteers WHERE upper(title)='" &
ucase(title) & "'
AND upper(FirstName)='" & ucase(firstname) & "' AND upper(LastName)='" &
ucase(lastname) & "'
AND upper(address)='" & ucase(address) & "'"
If this SELECT returns a volunteer_id then don't allow a new record to be
created.
You should write a JavaScript function to indicate that the form is being
submitted. Attach the function
to the onClick event of the submit button.
(place in the <head> section of your page>
<script language="JavaScript1.2">
<!--
function checkdata()
{
if (!confirm("Save this information?"))
{
return false;
}
return true;
}
// -->
</script>
<input type="submit" value="Submit" onClick="checkdata();">
Dallas Martin
----- Original Message -----
From: <sg48@y...>
To: "ASP Databases" <asp_databases@p...>
Sent: Wednesday, May 30, 2001 4:37 AM
Subject: [asp_databases] Re: Created Unintentional Duplicate Records
> What I meant about unique is the following:
> I have a form for a user to enter some personal data and submit it for
> entry into an Acess 2000 data base. The data will go into one table that
> has data on people that want to volunteer for a political campaign. The
> problem that I am having is that when I write the record, two identical
> new records show up
> in the data base instead of just one. I have intentionally configured the
> data base to allow duplicates so that I don't get error messages. I have
> tried writing the data using a SQL statement but had the same problem.
> What is strange is that it doesn't happen consistently. I hope that
> explains it better.
>
> The code that does the addition of the record is below ( I deleted some of
> the fields
>
> Dim rsVolunteer
> Set rsVolunteer = Server.CreateObject("ADODB.Recordset")
> rsVolunteer.Open "tblvolunteer", objConn, adOpenForwardOnly,
> adLockOptimistic, adCmdTable
> rsVolunteer.AddNew
>
> rsVolunteer("title") = Request.Form("title")
> rsVolunteer("FirstName") = Request.Form("FirstName")
> rsVolunteer("LastName") = Request.Form("LastName")
> rsVolunteer("streetaddress") = Request.Form("Address")
>
> rsVolunteer.Update ' update the database
> rsVolunteer.close
> Set rsVolunteer = nothing
> objConn.Close
> set objConn= nothing
>
>
> > What is unqiue about your record: One field, two fields,...,all fields?
> > Do you want a One to Many, One to One, or Many to Many relationship?
> > In other words what do you mean by unique?
> >
> > Does your db design allow multiple instances of the same data?
> >
> > For additional information, visit http://sqlcourse.com/ This site
> offers a
> > free online course
> > in SQL.
> >
> > Dallas
> >
> >
> > ----- Original Message -----
> > From: <sg48@y...>
> > To: "ASP Databases" <asp_databases@p...>
> > Sent: Tuesday, May 29, 2001 6:33 PM
> > Subject: [asp_databases] Created Unintentional Duplicate Records
> >
> >
> > > I am trying to write a add a unique record into an Access2000 database
> > > using ASP 3 and Personal Web Server. First I have an update
> instruction-
> > > then a block of code that sets up the recordset then an update
> > > instruction. The problem is that sometime (not consistently) two
> duplicate
> > > entries are created even though only one was intended. What am I doing
> > > wrong? - Steve
>
Message #6 by sg48@y... on Wed, 30 May 2001 16:05:15
|
|
Dallas,
I think you figured it out. In this case the user is only me - during debugging. I think the
problem is the refresh. I didn't realize that this might cause a problem. Can you explain how it
does? Anyway, I like your suggestion about the select statement and will umplement it. Your
advice was very helpful. Thanks for your help. - Steve Greenbaum
> There is at least two reasons that two identical records are entered into
> your database:
> The user is submitting the data twice. This may be cause by slow response
> time
> on the user's connection. The scenario goes like this: The user clicks
the
> submit button,
> the application doesn't provide any feedback to indicate that the form
has
> been submitted,
> the user clicks the submit button again. Voila, two records are now in
your
> database.
>
> Another problem is the "back" navigation browser button. Sometimes a user
> will navigate
> back to a previous page and re-submit the data. This can happen if the
user
> "refreshes"
> a previously submitted page.
>
> You should change your application design. First, you shouldn't allow
> duplicates in your
> table. You should issue a SELECT statement against the table before
adding a
> record.
> This statement should be constructed to select the minimum number of
fields
> that are
> consistent with a duplicate record. For example, I would hope that you
have
> an autonumber
> field in your volunteers table.
>
> Then you would SELECT volunteer_id FROM volunteers WHERE upper(title)='"
&
> ucase(title) & "'
> AND upper(FirstName)='" & ucase(firstname) & "' AND upper(LastName)='" &
> ucase(lastname) & "'
> AND upper(address)='" & ucase(address) & "'"
>
> If this SELECT returns a volunteer_id then don't allow a new record to be
> created.
>
> You should write a JavaScript function to indicate that the form is being
> submitted. Attach the function
> to the onClick event of the submit button.
>
> (place in the <head> section of your page>
>
> <script language="JavaScript1.2">
> <!--
> function checkdata()
> {
> if (!confirm("Save this information?"))
> {
> return false;
> }
> return true;
> }
> // -->
> </script>
>
> <input type="submit" value="Submit" onClick="checkdata();">
>
> Dallas Martin
>
>
> ----- Original Message -----
> From: <sg48@y...>
> To: "ASP Databases" <asp_databases@p...>
> Sent: Wednesday, May 30, 2001 4:37 AM
> Subject: [asp_databases] Re: Created Unintentional Duplicate Records
>
>
> > What I meant about unique is the following:
> > I have a form for a user to enter some personal data and submit it for
> > entry into an Acess 2000 data base. The data will go into one table
that
> > has data on people that want to volunteer for a political campaign. The
> > problem that I am having is that when I write the record, two
identical
> > new records show up
> > in the data base instead of just one. I have intentionally configured
the
> > data base to allow duplicates so that I don't get error messages. I
have
> > tried writing the data using a SQL statement but had the same problem.
> > What is strange is that it doesn't happen consistently. I hope that
> > explains it better.
> >
> > The code that does the addition of the record is below ( I deleted some
of
> > the fields
> >
> > Dim rsVolunteer
> > Set rsVolunteer = Server.CreateObject("ADODB.Recordset")
> > rsVolunteer.Open "tblvolunteer", objConn, adOpenForwardOnly,
> > adLockOptimistic, adCmdTable
> > rsVolunteer.AddNew
> >
> > rsVolunteer("title") = Request.Form("title")
> > rsVolunteer("FirstName") = Request.Form("FirstName")
> > rsVolunteer("LastName") = Request.Form("LastName")
> > rsVolunteer("streetaddress") = Request.Form("Address")
> >
> > rsVolunteer.Update ' update the database
> > rsVolunteer.close
> > Set rsVolunteer = nothing
> > objConn.Close
> > set objConn= nothing
> >
> >
> > > What is unqiue about your record: One field, two fields,...,all
fields?
> > > Do you want a One to Many, One to One, or Many to Many relationship?
> > > In other words what do you mean by unique?
> > >
> > > Does your db design allow multiple instances of the same data?
> > >
> > > For additional information, visit http://sqlcourse.com/ This site
> > offers a
> > > free online course
> > > in SQL.
> > >
> > > Dallas
> > >
> > >
> > > ----- Original Message -----
> > > From: <sg48@y...>
> > > To: "ASP Databases" <asp_databases@p...>
> > > Sent: Tuesday, May 29, 2001 6:33 PM
> > > Subject: [asp_databases] Created Unintentional Duplicate Records
> > >
> > >
> > > > I am trying to write a add a unique record into an Access2000
database
> > > > using ASP 3 and Personal Web Server. First I have an update
> > instruction-
> > > > then a block of code that sets up the recordset then an update
> > > > instruction. The problem is that sometime (not consistently) two
> > duplicate
> > > > entries are created even though only one was intended. What am I
doing
> > > > wrong? - Steve
> >
|
|
|
View Cart
