Access DB + ASP + auto-numbering = Q?

Discussion in 'ASP General' started by Steven Burn, Apr 3, 2004.

  1. Steven Burn

    Steven Burn Guest

    I'm curious, I've written a very simple PIS (personal info store) that
    allows one to store whatever they wish..... the problem is, when one deletes
    an entry, the auto-numbering isn't corrected to account for said
    deletion..... for example;

    1. entry 1
    2. entry 2
    3. entry 3

    delete entry 2, and #2 is no longer available (i.e. 3 should become 2, 4,
    become 3 etc etc).....

    Anyone know of a way to tell Access to re-number them when an entry is
    deleted?

    Apologies if this makes absolutely no sense.... I've been awake for hours
    and am absolutely shattered :eek:\

    --
    Regards

    Steven Burn
    Ur I.T. Mate Group
    www.it-mate.co.uk

    Keeping it FREE!

    Disclaimer:
    I know I'm probably wrong, I just like taking part ;o)
     
    Steven Burn, Apr 3, 2004
    #1
    1. Advertising

  2. Steven Burn

    Maarten Guest

    A auto num is to indentify a record in a unique way. Whats matter is this 1
    or 10 or 23678

    To solve your problem: mark the deleted record(s) and reused when adding a
    new one

    fieldIs = "1892627" the autonum
    fieldActive = "Y"
    fieldNr = "4"
    FieldMemo = whatever

    after delete

    fieldIs = "1892627" the autonum
    fieldActive = "N"
    fieldNr = "4"
    FieldMemo = whatever

    SELECT TOP 1 FROM MyTable WHERE fieldActive="N" ORDER by fieldNr
     
    Maarten, Apr 3, 2004
    #2
    1. Advertising

  3. Steven Burn

    Steven Burn Guest

    hehe, I'd not thought of marking them when deleted :eek:\

    Cheers ;o)

    --
    Regards

    Steven Burn
    Ur I.T. Mate Group
    www.it-mate.co.uk

    Keeping it FREE!

    Disclaimer:
    I know I'm probably wrong, I just like taking part ;o)


    Maarten <> wrote in message
    news:NJubc.59143$-ops.be...
    > A auto num is to indentify a record in a unique way. Whats matter is this

    1
    > or 10 or 23678
    >
    > To solve your problem: mark the deleted record(s) and reused when adding a
    > new one
    >
    > fieldIs = "1892627" the autonum
    > fieldActive = "Y"
    > fieldNr = "4"
    > FieldMemo = whatever
    >
    > after delete
    >
    > fieldIs = "1892627" the autonum
    > fieldActive = "N"
    > fieldNr = "4"
    > FieldMemo = whatever
    >
    > SELECT TOP 1 FROM MyTable WHERE fieldActive="N" ORDER by fieldNr
    >
    >
     
    Steven Burn, Apr 3, 2004
    #3
  4. Steven Burn

    Maarten Guest

    Second solution:
    you don't use an Auto Num

    make a new number
    RS.Open "SELECT max(fieldNr) FROM myTable"
    newNr = RS("fieldNr")+1



    fieldNr = "1"
    FieldMemo = whatever

    fieldNr = "2"
    FieldMemo = whatever

    fieldNr = "3"
    FieldMemo = whatever


    You delete the second record: then


    RS.OPEN "SELECT * FROM MyTable ORDER BY fieldNr"

    ct=0

    DO WHILE NOT RS.eof
    ct=ct+1
    RS("fieldNr") = ct
    RS.MoveNext
    Loop

    RS.Close






    "Steven Burn" <nobody@PVT_it-mate.co.uk> schreef in bericht
    news:%...
    > I'm curious, I've written a very simple PIS (personal info store) that
    > allows one to store whatever they wish..... the problem is, when one

    deletes
    > an entry, the auto-numbering isn't corrected to account for said
    > deletion..... for example;
    >
    > 1. entry 1
    > 2. entry 2
    > 3. entry 3
    >
    > delete entry 2, and #2 is no longer available (i.e. 3 should become 2, 4,
    > become 3 etc etc).....
    >
    > Anyone know of a way to tell Access to re-number them when an entry is
    > deleted?
    >
    > Apologies if this makes absolutely no sense.... I've been awake for hours
    > and am absolutely shattered :eek:\
    >
    > --
    > Regards
    >
    > Steven Burn
    > Ur I.T. Mate Group
    > www.it-mate.co.uk
    >
    > Keeping it FREE!
    >
    > Disclaimer:
    > I know I'm probably wrong, I just like taking part ;o)
    >
    >
    >
     
    Maarten, Apr 3, 2004
    #4
  5. Steven Burn

    Steven Burn Guest

    Maarten,
    Thats pretty much the way I've done it before....... just
    figured I'd use AN this time to save a little time (and to keep it as simple
    as possible as the PIS is for a friend, not for myself).

    cheers for the suggestion though ;o)

    --
    Regards

    Steven Burn
    Ur I.T. Mate Group
    www.it-mate.co.uk

    Keeping it FREE!

    Disclaimer:
    I know I'm probably wrong, I just like taking part ;o)


    Maarten <> wrote in message
    news:nTubc.59150$-ops.be...
    >
    > Second solution:
    > you don't use an Auto Num
    >
    > make a new number
    > RS.Open "SELECT max(fieldNr) FROM myTable"
    > newNr = RS("fieldNr")+1
    >
    >
    >
    > fieldNr = "1"
    > FieldMemo = whatever
    >
    > fieldNr = "2"
    > FieldMemo = whatever
    >
    > fieldNr = "3"
    > FieldMemo = whatever
    >
    >
    > You delete the second record: then
    >
    >
    > RS.OPEN "SELECT * FROM MyTable ORDER BY fieldNr"
    >
    > ct=0
    >
    > DO WHILE NOT RS.eof
    > ct=ct+1
    > RS("fieldNr") = ct
    > RS.MoveNext
    > Loop
    >
    > RS.Close
    >
    >
    >
    >
    >
    >
    > "Steven Burn" <nobody@PVT_it-mate.co.uk> schreef in bericht
    > news:%...
    > > I'm curious, I've written a very simple PIS (personal info store) that
    > > allows one to store whatever they wish..... the problem is, when one

    > deletes
    > > an entry, the auto-numbering isn't corrected to account for said
    > > deletion..... for example;
    > >
    > > 1. entry 1
    > > 2. entry 2
    > > 3. entry 3
    > >
    > > delete entry 2, and #2 is no longer available (i.e. 3 should become 2,

    4,
    > > become 3 etc etc).....
    > >
    > > Anyone know of a way to tell Access to re-number them when an entry is
    > > deleted?
    > >
    > > Apologies if this makes absolutely no sense.... I've been awake for

    hours
    > > and am absolutely shattered :eek:\
    > >
    > > --
    > > Regards
    > >
    > > Steven Burn
    > > Ur I.T. Mate Group
    > > www.it-mate.co.uk
    > >
    > > Keeping it FREE!
    > >
    > > Disclaimer:
    > > I know I'm probably wrong, I just like taking part ;o)
    > >
    > >
    > >

    >
    >
     
    Steven Burn, Apr 3, 2004
    #5
  6. Steven Burn

    Bob Barrows Guest

    Maarten wrote:
    > Second solution:
    > you don't use an Auto Num
    >
    > make a new number
    > RS.Open "SELECT max(fieldNr) FROM myTable"
    > newNr = RS("fieldNr")+1
    >

    1. This will not solve the gap problem. ID's will still not be re-used if
    rows are deleted from the "middle" of the table.

    2. This will not be reliable in a multi-user situation. Two users creating
    records simultaneously will get the same ID.

    There are ways to generate unique ID's without using select max(), but the
    specifics depend on the database. In general, you use a separate table
    containing a row which contains the last-used number. To get a new number,
    lock the row (pessimistic locking), read the number into a variable,
    increment it, update the row so it contains the new number, and unlock the
    row. Your variable contains the new number.

    This will still not address the gap problem. If gaps are a problem for some
    reason, Then you need to make the ID's re-usable, either by using the "soft"
    delete method suggested in Maarten's first reply, or by extending the
    suggested method in this reply. The extension involves adding another column
    to the generator table to identify rows containing hard-deleted ID's. When a
    record is deleted from your data table, add the ID of that record to the
    generator table, using the new column to flag it as an ID to re-use. The
    idea is to first search this table for a re-usable ID. If re-usable ID's
    exist, lock the table, read one of the ID's into a variable, delete that
    record, and unlock the table. If no re-usable ID's exist, use the method in
    the previous paragraph to generate a new ID.

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows, Apr 3, 2004
    #6
  7. Steven Burn

    Steven Burn Guest

    Cheers for the reply Bob..... I've actually decided to go with his first
    suggestion (marking unused/deleted one's)

    --
    Regards

    Steven Burn
    Ur I.T. Mate Group
    www.it-mate.co.uk

    Keeping it FREE!

    Disclaimer:
    I know I'm probably wrong, I just like taking part ;o)


    Bob Barrows <> wrote in message
    news:#Uq7#...
    > Maarten wrote:
    > > Second solution:
    > > you don't use an Auto Num
    > >
    > > make a new number
    > > RS.Open "SELECT max(fieldNr) FROM myTable"
    > > newNr = RS("fieldNr")+1
    > >

    > 1. This will not solve the gap problem. ID's will still not be re-used if
    > rows are deleted from the "middle" of the table.
    >
    > 2. This will not be reliable in a multi-user situation. Two users creating
    > records simultaneously will get the same ID.
    >
    > There are ways to generate unique ID's without using select max(), but the
    > specifics depend on the database. In general, you use a separate table
    > containing a row which contains the last-used number. To get a new number,
    > lock the row (pessimistic locking), read the number into a variable,
    > increment it, update the row so it contains the new number, and unlock the
    > row. Your variable contains the new number.
    >
    > This will still not address the gap problem. If gaps are a problem for

    some
    > reason, Then you need to make the ID's re-usable, either by using the

    "soft"
    > delete method suggested in Maarten's first reply, or by extending the
    > suggested method in this reply. The extension involves adding another

    column
    > to the generator table to identify rows containing hard-deleted ID's. When

    a
    > record is deleted from your data table, add the ID of that record to the
    > generator table, using the new column to flag it as an ID to re-use. The
    > idea is to first search this table for a re-usable ID. If re-usable ID's
    > exist, lock the table, read one of the ID's into a variable, delete that
    > record, and unlock the table. If no re-usable ID's exist, use the method

    in
    > the previous paragraph to generate a new ID.
    >
    > Bob Barrows
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    >
     
    Steven Burn, Apr 3, 2004
    #7
  8. Now, can you explain why you care about gaps? If you want a seamless range
    of numbers for display purposes, then clearly the ID number doesn't matter,
    and you can generate a "ranking" during a SELECT.

    --
    Aaron Bertrand
    SQL Server MVP
    http://www.aspfaq.com/


    "Steven Burn" <nobody@PVT_it-mate.co.uk> wrote in message
    news:...
    > Cheers for the reply Bob..... I've actually decided to go with his first
    > suggestion (marking unused/deleted one's)
     
    Aaron Bertrand [MVP], Apr 3, 2004
    #8
  9. "Aaron Bertrand [MVP]" <> wrote in message
    news:...
    > Now, can you explain why you care about gaps? If you want a seamless

    range
    > of numbers for display purposes, then clearly the ID number doesn't

    matter,
    > and you can generate a "ranking" during a SELECT.
    >


    That's right and according to the rules of DB design, a primary key should
    never change.
     
    Egbert Nierop \(MVP for IIS\), Apr 3, 2004
    #9
    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. buran

    numbering rows in datagrid

    buran, Jul 7, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    471
    Saravana
    Jul 7, 2003
  2. buran

    numbering rows in datagrid

    buran, Oct 15, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    524
    Cowboy \(Gregory A. Beamer\)
    Oct 15, 2003
  3. =?Utf-8?B?Q2hyaXM=?=

    form field auto numbering with unique value

    =?Utf-8?B?Q2hyaXM=?=, Dec 31, 2004, in forum: ASP .Net
    Replies:
    3
    Views:
    445
    John Saunders
    Jan 1, 2005
  4. linkswanted
    Replies:
    1
    Views:
    935
  5. RS

    ASP.NET page numbering

    RS, Oct 13, 2008, in forum: ASP .Net
    Replies:
    3
    Views:
    1,195
    Hillbilly
    Oct 14, 2008
Loading...

Share This Page