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?

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.

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.

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.

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

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.

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

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

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

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

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

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

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.

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.

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