Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Access DB + ASP + auto-numbering = Q?

Reply
Thread Tools

Access DB + ASP + auto-numbering = Q?

 
 
Steven Burn
Guest
Posts: n/a
 
      04-03-2004
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 \

--
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)



 
Reply With Quote
 
 
 
 
Maarten
Guest
Posts: n/a
 
      04-03-2004
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


 
Reply With Quote
 
 
 
 
Steven Burn
Guest
Posts: n/a
 
      04-03-2004
hehe, I'd not thought of marking them when deleted \

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 <(E-Mail Removed)> wrote in message
news:NJubc.59143$(E-Mail Removed)-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
>
>



 
Reply With Quote
 
Maarten
Guest
Posts: n/a
 
      04-03-2004

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:%(E-Mail Removed)...
> 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 \
>
> --
> 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)
>
>
>



 
Reply With Quote
 
Steven Burn
Guest
Posts: n/a
 
      04-03-2004
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 <(E-Mail Removed)> wrote in message
news:nTubc.59150$(E-Mail Removed)-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:%(E-Mail Removed)...
> > 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 \
> >
> > --
> > 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)
> >
> >
> >

>
>



 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      04-03-2004
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"


 
Reply With Quote
 
Steven Burn
Guest
Posts: n/a
 
      04-03-2004
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 <(E-Mail Removed)> wrote in message
news:#Uq7#(E-Mail Removed)...
> 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"
>
>



 
Reply With Quote
 
Aaron Bertrand [MVP]
Guest
Posts: n/a
 
      04-03-2004
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:(E-Mail Removed)...
> Cheers for the reply Bob..... I've actually decided to go with his first
> suggestion (marking unused/deleted one's)



 
Reply With Quote
 
Egbert Nierop \(MVP for IIS\)
Guest
Posts: n/a
 
      04-03-2004
"Aaron Bertrand [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Very annoying error: Access to the path is denied. ASP.NET is not authorized to access the requested resource. Consider granting access rights to the resource to the ASP.NET request identity Jay ASP .Net 2 08-20-2007 07:38 PM
403 Forbidden: You were denied access because: Access denied by access control list Southern Kiwi NZ Computing 6 03-19-2006 05:19 AM
Access Denied to access db with asp.net Ros@ ASP .Net 3 02-22-2006 04:51 PM
Faster access to MDB? MS Access MDB and ASP =?Utf-8?B?UGhpbA==?= ASP .Net 8 01-27-2005 07:08 PM
How do I let people access the internet via an access point but not allow them access to my network yar Wireless Networking 4 09-21-2004 03:48 AM



Advertisments