How to increment Registration number using ado.net?

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" a开发者_如何学Pythonnd so on.

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

---

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

---

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)

---

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