Print Page | Close Window

Auto-Numbering

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=3863
Printed Date: 28 March 2026 at 12:37pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com


Topic: Auto-Numbering
Posted By: Awangku
Subject: Auto-Numbering
Date Posted: 26 June 2003 at 7:35pm

I'm trying to create an auto-numbering column (let's name it ID) in an SQL2000 table using the IDENTITY function. (Maybe they should make this a built-in function just like MS Access. Bummer! )

Everytime I click the Save button in my form, the data will be saved in the table and the ID column will be increased by 1. And so on. The problem is... I have no idea how to write the code that will do just that.

Am I doing the right thing here, or is there an alternative to this?




Replies:
Posted By: Gary
Date Posted: 27 June 2003 at 2:56am

Lets say you have an column, Ref, which contains the number. What you need to do is get the highest (Max) number and add one to it so as to get the next number....

strSQL = "SELECT MAX(Ref) AS LastRef FROM [myTable]"
SET rsSQL = con.execute(strSQL)
NextRef = rsSQL(LastRef) + 1

Not sure if that is what you are after.



Posted By: ljamal
Date Posted: 27 June 2003 at 8:05am
If the database field is set to be an IDENTITY column with the autoincrement of 1, it will automatically increment that field by 1 when a record is inserted.

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming


Posted By: Awangku
Date Posted: 30 June 2003 at 7:53pm

Originally posted by ljamal ljamal wrote:

If the database field is set to be an IDENTITY column with the autoincrement of 1, it will automatically increment that field by 1 when a record is inserted.

Yup.. that's what it says in the online book. Somehow I can't get it to work. So I tried another way. Thanks anyway.



Posted By: Awangku
Date Posted: 30 June 2003 at 7:55pm
Originally posted by Gary Gary wrote:

Lets say you have an column, Ref, which contains the number. What you need to do is get the highest (Max) number and add one to it so as to get the next number....

strSQL = "SELECT MAX(Ref) AS LastRef FROM [myTable]"
SET rsSQL = con.execute(strSQL)
NextRef = rsSQL(LastRef) + 1

Not sure if that is what you are after.

Yup.. that's exactly what I'm after. I counted the number of rows, though.. since the table was initially blank. Thanks Gary.



Posted By: ljamal
Date Posted: 30 June 2003 at 8:44pm
Originally posted by Awangku Awangku wrote:

Yup.. that's what it says in the online book. Somehow I can't get it to work. So I tried another way. Thanks anyway.


That's the way it should work, if it's not working that way then something is wrong with your SQL Server.

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming


Posted By: Bunce
Date Posted: 30 June 2003 at 9:54pm

You should not be using the method listed above (MAX) to create an 'auto-number' for your purposes.

What if another record was added in between you getting the maximum number and creating another?

Always use the functionality that the database provides.

IDENTITY will work for you, but you must have an incorrect setting somewhere.  Show us the relevant code you are using alloing with the database settings for that field.

Cheers,
Andrew



-------------
There have been many, many posts made throughout the world...
This was one of them.


Posted By: Awangku
Date Posted: 02 July 2003 at 6:46pm

Well.. to be honest, identity doesn't work for me mainly because I don't know how to use it. I set a column as identity and when I tried to insert a new record into the table, I got an error message instead. Nothing wrong with my SQL server. It's just that I have no idea how to use it. Anyone here kind enough to teach me?

As for inserting new records into my table, well.. the database is set so that the ONLY way to modify it is through my asp interface. Heck, once this whole thing goes live, I can't even touch it. And I'm the developer!

So to get that (misleading) 'auto-number' thing, I just counted the number of rows currently in the table, add 1 and use that number when I add a new row.

 



Posted By: Bunce
Date Posted: 02 July 2003 at 9:36pm

What error message are you getting?
What code are you using to add a record to the database?

Remember you should not include the name of the Identity field in you code when adding the new record.

Cheers,
Andrew



-------------
There have been many, many posts made throughout the world...
This was one of them.


Posted By: Awangku
Date Posted: 03 July 2003 at 6:58pm

I got the "HTTP 500 - Internal server error" message. And these are the codes I use to add new records in my table.

Set rsProcess = Server.CreateObject("ADODB.Recordset")
rsProcess.CursorType = 2
rsProcess.LockType = 3
rsProcess.Open strSQL, strCon

rsProcess.AddNew
rsProcess.Fields("Test1").value = strTest1
rsProcess.Fields("Test2").value = strTest2
rsProcess.Fields("Test3").value = strTest3
rsProcess.Update
rsProcess.Requery
rsProcess.MoveLast

rsProcess.Close
Set rsProcess = Nothing

I set the first column in my table as Identity, without modifying its settings.



Posted By: Bunce
Date Posted: 03 July 2003 at 10:20pm

Firstly, you don't need to open a recordset if you're not going to be displaying it on the page.

Secondly, if you're Identity column is [Test1], then you shouldn't be addding anything to that column as the database will do it for you. It therefore, should not be in your code.

So, code to create a new record:

dim strSQL as string
strSQL = "INSERT INTO [MyTable] ([Field1],[Field2],[Field3]) VALUES ('Value1','Value2','Value3')
myConn.execute(strSQL)

Note, field1, field2 and field3 are not identity fields.  The identity field is called something else, that you don't need to mention in your code. Value1, value2 and value3 are surrounded by quotes when strings, otherwise they should be left off for numerical fields.

myConn is a connection that you have already established in your code.

Cheers,
Andrew



-------------
There have been many, many posts made throughout the world...
This was one of them.


Posted By: Awangku
Date Posted: 04 July 2003 at 1:29am

I'll give that a try. Thank you. You've been a great help.

One question, though.

Let's say that I've got that identity thing up and running. What would happen if I delete some of the records already in the table? Would there be gaps in the identity numbers? (I'm still in the development phase, so I can do whatever I like to the database.)



Posted By: Bunce
Date Posted: 04 July 2003 at 7:56am

There will (and should) be gaps in the autonumber if that happens.

Try to think of Autonumber (primary key) fields' purpose, purely to uniquely identify records and to be used in relationships between tables. 

They should rarely (if ever) be seen by anyone except the database developer.

For simplicity, take for example a table of customers and a table of Purchases. 

Customer (Ralph) with an ID (Autonumber) of 10 has been part of a dozen or so purchases.  Therefore Ralph's Customer ID (10) is in the Purchases table 10 times, alongside the relevant product.

Now Ralph leaves. His record gets deleted from the customers table.  A new customer (Bill) comes along and gets Ralphs old ID of 10, which is now free... Whoops! Ralph has automatically made 10 puchases since he is linked to those 10 puchases in the Purchases table.

This is why the autonumber or identity field numbers are not recycled.

And if everyone understands that they should not be used or shown publicly for identifcation then there wouldn't be any problems.

Hope this helps,
Andrew



-------------
There have been many, many posts made throughout the world...
This was one of them.


Posted By: ljamal
Date Posted: 04 July 2003 at 8:52am
With a good DB design, if a record is deleting all references to that should be deleted as well so that there are no orphaned records in other tables.

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming


Posted By: Bunce
Date Posted: 04 July 2003 at 9:43am

That goes without saying. 

Was the easiest way to provide an example.

In the above situation, the customers record shouldn't be deleted in the first place.



-------------
There have been many, many posts made throughout the world...
This was one of them.


Posted By: Awangku
Date Posted: 06 July 2003 at 7:01pm

Yup.. that helps a lot. It also means I can't use identity in all of my tables since there'll be quite a number of deleting and adding new records to the tables once the whole project goes live.

Thanks Andrew. You've been a great help.

 



Posted By: Bunce
Date Posted: 06 July 2003 at 10:14pm
Originally posted by Awangku Awangku wrote:

Yup.. that helps a lot. It also means I can't use identity in all of my tables since there'll be quite a number of deleting and adding new records to the tables once the whole project goes live.

It shouldn't matter.  There's nothing wrong with having gaps in the autonumber primary key.

If you need a field which doesn't have gaps then you can create this yourself through code.  But you need to first ask the question - 'Why do you need a field withough gaps?'

If its just to keep a count of the number of records you can do this on the fly when you output your data..no need to actually store it in the database itself.

Cheers,
Andrew



-------------
There have been many, many posts made throughout the world...
This was one of them.


Posted By: Awangku
Date Posted: 07 July 2003 at 5:25am
Again.. you are absolutely right. I could've used some generated alphanumeric codes instead of running numbers as a way of tagging records. I guess I choose that one since it's the easiest.



Print Page | Close Window

Forum Software by Web Wiz Forums® version 12.08 - https://www.webwizforums.com
Copyright ©2001-2026 Web Wiz Ltd. - https://www.webwiz.net