Student roll number generation and store in database-CodePudding

I have a asp.net webforms application, where student can fill up a form and submit. The student information then gets saved to a database. There is a system to auto generate their academic roll number. To accomplish this task i created a separate table which has only one column named "LastRollNumber". The table also have an ID column. The table always have one row only. So if a student admit himself/herself then after submitting the form the application will check the table for last roll number and will add 1 and update the table. I used the following SQL to do that:

"UPDATE [dbo].[AcademicRollData] SET [LastRollNumber] = [LastRollNumber]  1 OUTPUT inserted.LastRollNumber WHERE [ID]=1;"

Many students are accessing the application. The application is working fine but some rollnumber is getting missed. There is no duplicate in roll number but sometimes it is missing. What i am doing wrong? What is the best way to accomplish this task? Please help me with your valuable answer to resolve the issue.

Thanks.

CodePudding user response：

have you tried scope_identity()

dim cmd as sqlcommand=new sqlcommand("insert into table () values();SELECT CAST(scope_identity() AS int)",connection)
dim LastRollNumber as integer=cmd.ExecuteScalar

cmd=new sqlcommand("UPDATE [dbo].[AcademicRollData] SET [LastRollNumber] = " & LastRollNumber  1 & " WHERE [ID]=" & LastRollNumber",connection)

CodePudding user response：

As per my understanding, you don't need to create a separate table for generating roll number. Recommended way is to use max() 1. Additionally, you can make column unique and not null to avoid duplicate and null values. Also lock the table or use transactional scope.

Example query to insert

INSERT INTO [dbo].[AcademicRollData]
( rollNumber, column1, otherColumn )
VALUES 
((SELECT MAX( rollNumber ) 1 FROM [dbo].[AcademicRollData] acaRollData), 
'value1', 'value')

In this case, you don't need to run an additional update query.

Please let me know if understood your purpose.