Preventing/Removing duplicates

[Drew]

I have a permission tracking app that I am working on, and I have made the
insert page for it. I am having issues on how to prevent duplicates from
getting entered.

Currently the interface for the app has a mixture of select boxes, list
boxes and checkboxes. The form submits the page to processAIMR.asp and then
does the inserting. I am using a loop to insert a new record for each
checkbox checked or listbox entry selected.

My database table looks something like this,

UID - autoincrementing primary key
EmpID - the employee who the permission is given to
AuthEmpID - the employee who authorized the permission
AccessOption - the permission that was given (i.e. Internet Access, etc).
This is a foreign key to the AccessOptions table.

My question is concerning efficiency and how I should go about preventing or
removing the duplicates. There are a couple methods that I have thought
about using, but I am not sure which would be best.

1. Create a recordset on each run through the loop to check to see if the
AccessOption is already there for a certain EmpID. I would guess this would
be really hard on the server, but it would work.
2. Throw the recordset into an array, then check it (still kinda unsure how
to do this) on each run through the loop.
3. After the processAIMR.asp page has inserted, it redirects. Upon
redirection I could run a DELETE command that deletes all duplicates.

Which do you think would be the best route? Or maybe you have another idea?

Thanks,
Drew

 

[Bob Barrows [MVP]]

I have a permission tracking app that I am working on, and I have
made the insert page for it. I am having issues on how to prevent
duplicates from getting entered.

Currently the interface for the app has a mixture of select boxes,
list boxes and checkboxes. The form submits the page to
processAIMR.asp and then does the inserting. I am using a loop to
insert a new record for each checkbox checked or listbox entry
selected.

My database table looks something like this,

UID - autoincrementing primary key
EmpID - the employee who the permission is given to
AuthEmpID - the employee who authorized the permission
AccessOption - the permission that was given (i.e. Internet Access,
etc). This is a foreign key to the AccessOptions table.

My question is concerning efficiency and how I should go about
preventing or removing the duplicates. There are a couple methods
that I have thought about using, but I am not sure which would be
best.

1. Create a recordset on each run through the loop to check to see
if the AccessOption is already there for a certain EmpID. I would
guess this would be really hard on the server, but it would work.

Horrible idea. Avoid slow, inefficient recordset loops.

2. Throw the recordset into an array, then check it (still kinda
unsure how to do this) on each run through the loop.

Again, unnecessary.

3. After the processAIMR.asp page has inserted, it redirects. Upon
redirection I could run a DELETE command that deletes all duplicates.

No, why slow down your transaction like this?

Which do you think would be the best route? Or maybe you have
another idea?

The only sure way to prevent duplicates is to create a unique index on the
columns that identify unique rows. In this case, it looks as if you need a
unique index on EmpID and AccessOption. Once you have the index in place you
have the option of attempting to insert data and trapping the error that
results from duplicate key violation. Or you can use a query similar to the
one I proposed in this thread:

http://groups.google.com/group/micr..._frm/thread/1409ae96cd7d112d/e0191629a94f3000


For more specific help, tell us what database you are using.

 

[Drew]

I am using SQL Server 2000. I guess that I am a little confused on what is
happening on the link provided, I don't understand why we use the MSObjects
table... I also don't understand the following line of code,

cn.execute InsertTXT,lrecs,129

If you could explain just a little more in depth I would appreciate it!

Thanks,
Drew

 

[Bob Barrows [MVP]]

I am using SQL Server 2000. I guess that I am a little confused on
what is happening on the link provided, I don't understand why we use
the MSObjects table...

I don't blame you ... that was an Access-specific query. Access requires a
FROM clause, so using the builtin system table MSysObjects is a kludge.
T-SQL does not require this (see why it's important to tell us what database
you are using?) so you can do something like:

insert into yourtable(EmpID,AuthEmpID,AccessOption)
select 12, 38, 4 where not exists (
select * from yourtable where EmpID=12 and AccessOption=4)

I also don't understand the following line of
code,

cn.execute InsertTXT,lrecs,129

If you could explain just a little more in depth I would appreciate
it!

The ADO documentation can be found here :
http://msdn.microsoft.com/library/en-us/ado270/htm/mdmscadoapireference.asp

If you look up the Execute method. you will see that it accepts 3 arguments:
source - the sql statement/table name/stored procedure name
records affected - a byref argument that will contain the number of records
affected after the query is executed
options - command type and execution option

Now that I know what database you are using, I would revise my response to
suggest you use a stored procedure (you should still create the unique
index).

create procedure InsPermission (
@EmpId int,
@AuthEmpID int,
@AccessOption int) AS
SET NOCOUNT ON
IF NOT EXISTS (select * FROM yourtable where
EmpID= @EmpId and AccessOption= @AccessOption)
INSERT INTO yourtable(EmpID,AuthEmpID,AccessOption)
VALUES(@EmpID,@AuthEmpID,@AccessOption)
ELSE
RETURN -1 --or whatever non-zero number you wish to use

Then, in ASP, if you don't wish to know if a duplicate was attempted to be
inserted, simply do this:

cn.InsPermission EmpID, AuthEmpID, AccessOption

If you do wish to know if a duplicate was attempted, then use a command
object and read the return value. For more, read:
http://groups.google.com/groups?hl=en&lr=&c2coff=1&selm=OVlfw#[email protected]

Bob Barrows

 

[Drew]

Bob... I really appreciate your help with this issue... your knowledge
amazes me!

Thanks a bunch,
Drew