Preventing/Removing duplicates

Discussion in 'ASP General' started by Drew, Nov 9, 2005.

  1. Drew

    Drew Guest

    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
     
    Drew, Nov 9, 2005
    #1
    1. Advertising

  2. Drew wrote:
    > 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.
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Nov 9, 2005
    #2
    1. Advertising

  3. Drew

    Drew Guest

    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]" <> wrote in message
    news:...
    > Drew wrote:
    >> 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.
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
     
    Drew, Nov 9, 2005
    #3
  4. Drew wrote:
    > 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#

    Bob Barrows

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Nov 9, 2005
    #4
  5. Drew

    Drew Guest

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

    Thanks a bunch,
    Drew


    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > Drew wrote:
    >> 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#
    >
    > Bob Barrows
    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
     
    Drew, Nov 9, 2005
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. William F. Robertson, Jr.

    Re: Removing duplicates from a DropdownList

    William F. Robertson, Jr., Aug 4, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    589
    brian richards
    Aug 4, 2003
  2. makthar
    Replies:
    0
    Views:
    369
    makthar
    Aug 4, 2003
  3. brian richards

    Re: Removing duplicates from a DropdownList

    brian richards, Aug 4, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    364
    brian richards
    Aug 4, 2003
  4. S. Justin Gengo

    Re: Removing duplicates from a DropdownList

    S. Justin Gengo, Aug 4, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    1,425
    S. Justin Gengo
    Aug 4, 2003
  5. brian richards

    Re: Removing duplicates from a DropdownList

    brian richards, Aug 4, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    8,758
    Marina
    Aug 4, 2003
Loading...

Share This Page