Question 87491 in ASP.NET Question posted in
The official documentation can be found here.

Student roll number generation and store in database – Asp.net

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.

Tags:

3

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

    In SQL Server you can use identity column that auto-increments with a seed that you specify at 1. You can also use a sequence in Oracle and SQL Server.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search