0

I am inserting student values to a table using ado.net form.I want to generate registration number automatically. For Example, "R0001"

Everytime it will automatically incremened by 1.

  • "R0001" -> "R0002-> "R0003" and so on.
  • "R0010" -> "R0011" and so on.
  • "R0100" -> "R0101" and so on.

But i am unable to generate registration number automatically. Can anyone help me?

gbn
  • 422,506
  • 82
  • 585
  • 676
Sonu Kumar
  • 1
  • 2
  • Please read about http://meta.stackexchange.com/questions/7237/how-does-reputation-work – gbn Aug 05 '11 at 08:12

3 Answers3

0

I would have

  • an ID INT IDENTITY column - it's automatically incremented by SQL Server

  • add a computed, persisted column 

    ALTER TABLE dbo.YourTable
   ADD RegistrationNumber AS 'R' + RIGHT('0000' + CAST(ID AS VARCHAR(4)), 4) PERSISTED

The table will contain values 1, 2, 3, 4 for the ID column, and R0001, R0002, R0003, R0004 for the RegistrationNumber column

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
0

One option:

  • Use an IDENTITY value so you get 1, 2, 3, 4, 5 etc

Then do one of:

  • Add the R and leading zeroes in the client code
  • Use a computed column on the table

Like

CREATE TABLE Students (
    StudentID int NOT NULL IDENTITY (1,1) PRIMARY KEY,
    StudentName
    ... ,
    RegistrationNumber AS 'R' + RIGHT('0000' + CAST(StudentID AS varchar(5)), 5)

There is no elegant and clean way to generate one columns R0001, R0002 etc. And you'll get gaps too for failed INSERTs.

You can make the RegistrationNumber column unique too

gbn
  • 422,506
  • 82
  • 585
  • 676
  • CREATE TABLE Students ( StudentID int NOT NULL IDENTITY (1,1) PRIMARY KEY, Name varchar(20), Course Varchar(20), RegistrationNumber AS 'R' + RIGHT('0000' + CAST(StudentID AS varchar(5)), 5)) How to insert values? – Sonu Kumar Aug 05 '11 at 07:01
  • But how to insert values in this table? – Sonu Kumar Aug 05 '11 at 07:04
  • 1
    @Sonu Kumar: INSERT (Name, Course) only. SQL Server will manage StudentID and RegistrationNumber – gbn Aug 05 '11 at 07:32
  • @Sonu Kumar: please accept and/or upvote then (the tick and up arrow on the left of my answer). See http://meta.stackexchange.com/questions/7237/how-does-reputation-work – gbn Aug 08 '11 at 15:36
0

If you are able to otherwise sort the existing fields based upon some id/timestamp, you can simply get the latest entry, remove the r, increment, and save the value.

DECLARE @RegNum INT, @RegNumChar VARCHAR(5)
SET @RegNum = CAST(SUBSTRING(RegistrationNumber, 2, LEN(RegistrationNumber)) AS INT)
SET @RegNum = @RegNum + 1
SET @RegNumChar = 'R" + CONVERT(varchar(5), @RegNum)
Bradley Staples
  • 367
  • 1
  • 8
  • If you mean a manual identity type action, this isn't safe for concurrency. Two processes can easily get the same number. – gbn Aug 05 '11 at 07:01
  • Here's how to make such manual solutions safe for concurrency: http://stackoverflow.com/questions/1994771/in-tsql-is-an-insert-with-a-select-statement-safe-in-terms-of-concurrency/1994922#1994922 – Heinzi Aug 05 '11 at 07:12