ID automation (increment)

[Rhayezelle]

hi guys can you please help me with my code.. I'm trying to generate a new ID for every student in my system that will be registered..


for example ( if the last studentID in my database is 3, then i would come up to this new id "0004" in another table for maxID...

but when the student count begins at 25, the result would be like this.. "00025".. I'm trying to implement 4 characters only in generating ID... how can i make it as this "0025"

here's my code...

SELECT format('0') & ('0') & ('0') & (Max([StudentID])+1) AS NewID
FROM Tbl_StudentLevel;

please teach me how to have a better solution for this problem....

 

[svilla]

Do you really want to restrict your database to just 9,999 students? Seems sort of like an arbitrary limitation.

Database systems have an auto increment feature so as you add a new record the next number in the series is used as the ID for the new record. This way you don’t have to go through hoops to get the next number.

If you are really want to implement the approach you have laid out I would simplify the SQL statment to retrieve the MAX([StudentID]) and the format it using the string.format method. You can then take that number and plug it back in with the SQL INSERT statement.

The problem with the method you outlined that if two records are being inserted simultaneously then there is the possibility of generating the same ID number.

 

[cjard]

Mmmh, I'd think it better to use your database's built in auto-increment feature. In Oracle this is a sequence, in Access and SQL Server this is autoIncrement

Store it as a number: 1,2,3,4..

When you format it for display or print purposes use:

Dim id as Integer = 25 'get it from the db, here just a fixed constant to demonstrate
Dim displayid as String = id.ToString("0000")

 

[kulrom]

Mmmh, I'd think it better to use your database's built in auto-increment feature. In Oracle this is a sequence, in Access and SQL Server this is autoIncrement

Store it as a number: 1,2,3,4..

When you format it for display or print purposes use:

Dim id as Integer = 25 'get it from the db, here just a fixed constant to demonstrate
Dim displayid as String = id.ToString("0000")

Actually In SQL Server it is called IDENTITY (Property)

 

[cjard]

Thanks for the correction

 

[kulrom]

You are welcome