I have a databse that i am developing to control what stock we have and 
the location of where it is stored. Each record has a record id, this was 
only put in the help me select out individual records for updating and 
deletion.
  I wish to know if there is a way for me to  update the record id for all 
the records if one is deleted, e.g. if i delete record 56 i want records 
57 and above to "shift" down by one number so that records 56 is not 
empty. The reason behind this is that on my record insertion page it reads 
how many records i have in my database and if i have deleted for example 
number 56 of 100, it will report i have 99 records and try and insert a 
record with id number 100, but that is already taken because the other 
records have not shifted down. Could i change my insertion page to detect 
the last record number in my database then add 1 and try to insert a 
record with that number? I am reletivelly new to this so im not sure how 
to go about implementing this.

Thanks for any help you can provide.

Mat,

If you want to get the number of records you should run a query using the
aggregate COUNT() function.

<%
strSQL =_
       "SELECT COUNT(*) as TotalProducts FROM Products"

Set objRS = objConn.Execute strSQL

If not objRS.EOF then
    Response.Write(objRS("TotalProducts"))
End If
%>

I *strongly* suggest against trying to do what you want to do.
Autonumbers/Primary Keys are for uniquely identifying each record, *not* for
numbering each record. Certainly you shouldn't be using them to work out how
many records you have. SQL is a set-based language which is designed to work
with sets of relational data. It is not a cursor based language. You need to
get your head around that in order to work with databases effectively.

When you insert a new record, the new autonumber value will be created
automatically for you. You shouldn't be creating these manually and
inserting them.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Mat Pike" <mattpike@o...>
Subject: [asp_web_howto] Database record numbers


:   I have a databse that i am developing to control what stock we have and
: the location of where it is stored. Each record has a record id, this was
: only put in the help me select out individual records for updating and
: deletion.
:   I wish to know if there is a way for me to  update the record id for all
: the records if one is deleted, e.g. if i delete record 56 i want records
: 57 and above to "shift" down by one number so that records 56 is not
: empty. The reason behind this is that on my record insertion page it reads
: how many records i have in my database and if i have deleted for example
: number 56 of 100, it will report i have 99 records and try and insert a
: record with id number 100, but that is already taken because the other
: records have not shifted down. Could i change my insertion page to detect
: the last record number in my database then add 1 and try to insert a
: record with that number? I am reletivelly new to this so im not sure how
: to go about implementing this.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Strongly recommend you do not try renumbering.  The autonumber is a
primary key assigned to each record. If you just need to get the total
count of records at any time, just do a SELECT COUNT(8) FROM [TABLE] or
a GETROWS

-----Original Message-----
From: Mat Pike [mailto:mattpike@o...]
Sent: Tuesday, April 23, 2002 8:00 PM
To: ASP Web HowTo
Subject: [asp_web_howto] Database record numbers


  I have a databse that i am developing to control what stock we have
and
the location of where it is stored. Each record has a record id, this
was
only put in the help me select out individual records for updating and
deletion.
  I wish to know if there is a way for me to  update the record id for
all
the records if one is deleted, e.g. if i delete record 56 i want records

57 and above to "shift" down by one number so that records 56 is not
empty. The reason behind this is that on my record insertion page it
reads
how many records i have in my database and if i have deleted for example

number 56 of 100, it will report i have 99 records and try and insert a
record with id number 100, but that is already taken because the other
records have not shifted down. Could i change my insertion page to
detect
the last record number in my database then add 1 and try to insert a
record with that number? I am reletivelly new to this so im not sure how

to go about implementing this.

Thanks for any help you can provide.

---

Improve your web design skills with these new books from Glasshaus.

Usable Web Menus
http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=3Dnosim/theprogramm
e
r-20
Constructing Accessible Web Sites
http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=3Dnosim/theprogramm
e
r-20
Practical JavaScript for the Usable Web
http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=3Dnosim/theprogramm
e
r-20