assigning student numbers

lional

Well-known member
Joined
Nov 29, 2018
Messages
55
Programming Experience
Beginner
Hi all
I need a bit of advice
I am writing a student application for a college (bearing in mind this is my first application), and I am not sure of the best route to follow with regard to assigning student numbers.
Is auto-incrementing best or randomly assigning numbers.

The additional problem that I face is that there is data that needs to be pulled in and I will need to match the already assigned student numbers.
I am not sure what is the easiest or best method. I normally would have chosen auto-incrementing but the existing student numbers may cause problems.

Any advice will be greatly appreciated
 
What format and range are the existing student numbers in? Format will determine whether just an auto-incrementing value is required or you will need to combine it with something else, e.g. you might generate the number 1 but the actual student number might be S00001. Range will determine what seed to use for your auto-incrementing ID, e.g. if the larget student number is currently 1234 then you can create an identity column in SQL Server and set the seed to 1235, so that will be the first number generated.
 
Hi
The student number currently is STU000001. My concern is importing the current data which is not incremented mainly caused be records being deleted. I can do a formula in SQL Server to generate the number STU combination but I would need to keep the current student numbers consistent with the student.
My way of thinking was to do an identity column and then import the data, then change the next identity number (if that is possible) to match the next student number, then create a formula on the database to generate student numbers going forward.
It seems long winded but importing the data with student numbers that are not flowing sequentially adds to the complexity
 
That sounds about right. You'd have an identity column for the numbers and, optionally, another column with an expression to generate the alphanumeric string, or you might just do that in app code. You can use IDENTITY_INSERT in SQL Server to allow you to insert data into identity columns. Not sure of the equivalent in other databases. Not sure whether you can set the seed after inserting the data or even whether you would need to but you can test that out.
 
Hi
I got the data imported but the problem I face is I need a way to generate the numbers.
I was thinking of computing the number in SQL to generate a number preceded by STU. I would use the primary key.
The problem I face is that it back calculates all the numbers and changes all student numbers, so I will need to do it programmatically.

The only way I know how to do this is to get the latest student id which is an integer (identity) and then increment the number and generate the student number. My fear is that if two users do this split seconds apart isn't there a problem with the student numbers because they will effectively be assigned the same number.

Hope this makes sense
 
You don't need to do anything programmatically. As I said earlier, you would use two columns. One would be for the auto-generated numeric key. The other would be for the student number and you can set an expression in the column properties that will build a string with a prefix followed by the value from the PK column, padded if required. Your student numbers will then be generated automatically from your PK values.
 
Hi
Thanks for your response.
I tried that but then it alters all the student numbers in the table to their current pk values, unless there is a setting that I am missing. I put a formula under computed column specification, with Is Persistent set to yes,
I need the student numbers to stay like they were because all the other data is referenced to the student number.
What I did was import the data, change the identity value of the pk to the highest student number (the flow of student numbers had gaps so it was less than the pk value), then generate the student numbers programmatically.
I would prefer to do it in SQL Server if I could find a way to stop in altering all the existing student numbers to PK values.
I really appreciate all your assistance
 
I tried that but then it alters all the student numbers in the table to their current pk values, unless there is a setting that I am missing.

You were supposed to insert the existing data and set the PK explicitly based on the existing student numbers. That's why I mentioned INSERT_IDENTITY: because turning it on would allow you to insert values directly into an identity column. Once the existing data is in there, you can allow the identity column to generate the new PKs automatically.
 
Back
Top Bottom