| Author |
 Topic Search  Topic Options
|
Awangku 
Groupie
Joined: 15 November 2001
Location: Malaysia
Status: Offline
Points: 70
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: Auto-Numbering
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?
|
 |
Gary
Senior Member
Joined: 20 March 2002
Location: United Kingdom
Status: Offline
Points: 326
|
Post Options
Thanks(0)
Quote Reply
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.
|
|
ljamal
Mod Builder Group
Joined: 16 April 2003
Status: Offline
Points: 888
|
Post Options
Thanks(0)
Quote Reply
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.
|
|
|
|
Awangku
Groupie
Joined: 15 November 2001
Location: Malaysia
Status: Offline
Points: 70
|
Post Options
Thanks(0)
Quote Reply
Posted: 30 June 2003 at 7:53pm |
 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.
|
|
Awangku
Groupie
Joined: 15 November 2001
Location: Malaysia
Status: Offline
Points: 70
|
Post Options
Thanks(0)
Quote Reply
Posted: 30 June 2003 at 7:55pm |
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.
|
|
ljamal
Mod Builder Group
Joined: 16 April 2003
Status: Offline
Points: 888
|
Post Options
Thanks(0)
Quote Reply
Posted: 30 June 2003 at 8:44pm |
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.
|
|
|
|
Bunce
Senior Member
Joined: 10 April 2002
Location: Australia
Status: Offline
Points: 846
|
Post Options
Thanks(0)
Quote Reply
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.
|
|
Awangku
Groupie
Joined: 15 November 2001
Location: Malaysia
Status: Offline
Points: 70
|
Post Options
Thanks(0)
Quote Reply
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.
Edited by Awangku
|
|
