0

How to reset the ID back to 001 every year, the auto generated ID is 001-19 which 19 is the current year. Following is the code:

Dim idnumber As Integer

con.Open()

Dim command As SqlClient.SqlCommand = con.CreateCommand()
command.CommandText = "select MAX([CCLA File Number]) as id from tblLegalAidCaseFile"

objdatareader = command.ExecuteReader()

While objdatareader.Read
    idnumber = Val(objdatareader.Item("id").ToString()) + 1
End While

Me.CCLA_File_NumberTextBox.Text = Format(idnumber, "000") + "-" + Format(Now, "yy")

Thank You

Sri9911
  • 1,187
  • 16
  • 32
Jay DC
  • 13
  • 1
  • Given that this site provides a preview of your question, there's really no good excuse for posting something with nerfed formatting. Don't submit until you have formatted correctly. If you can't take the time to make your question easy to read, there's less chance that we'll take the time to read it. – jmcilhinney Sep 26 '19 at 05:21
  • Also, a title and some code isn't really an acceptable question. The title should be a succinct summary and then the body should contain a detailed description, including the code and an explanation how the behaviour of that code differs from your expectation. – jmcilhinney Sep 26 '19 at 05:22
  • You are using SQL Server which is a mult user database. So you want each case to have a unique number. So only way of getting a unique number is to use a stored procedure : https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-2017 – jdweng Sep 26 '19 at 05:24

1 Answers1

0

Reset Identity Field a few links

Reset identity seed after deleting records in SQL Server

https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql?view=sql-server-2017

If you want to reseed so that your first record is ID 1 then the reseed command must reseed to 0, so that the next record is ID 1.

Use SampleCode
DBCC CHECKIDENT (KeyWords, RESEED, 13)

The database name is SampleCode. The table name is KeyWords The value of the last valid ID is 13. After running this code the next value is for ID is 14.

Mary
  • 14,926
  • 3
  • 18
  • 27