One way to do this would be to create a table containing the next free number. In the simplest form the table column would already contain the year so the value could be like
20180019
Now each time a new row is added the logic would be following
- Start a transaction
- Update the free number row, add 1 to it
- Select the value from the table
- Insert the new row using the selected value
- Commit the transaction.
However I woudn't advice to use such column in the target table. What happens if you need more than 9999 rows in the table per year? You would need to add different kinds of checks and possibly change the database schema.
Instead I would recommend using a
Surrogate key - Wikipedia[
^]
As a side note, because you concatenate values from UI directly to the SQL statement your code is vulnerable to SQL injections. See
SQL injection - Wikipedia[
^] The preferred way is to use parameters, have a look at
CreateParameter Method (ADO) | Microsoft Docs[
^]