SQL insert data simultaneous I have a small label printing software that print sequential numbers. It selects the last serialnumber on sql db's and add one.

The problem I have is that I need now two users to print sequential serial numbers.

SQL Select statement:

sql_query.CommandText = "SELECT TOP 1 cast (serialnumber as nvarchar) as serialnumber,
                                indexnumber, versao
                         FROM  etiquetas
                         where partnumber Like '" &amp; partnumber &amp; "'
                         ORDER BY serialnumber DESC"


Sql Insert Statement:

 "if not exists (select serialnumber
                 from etiquetasklc.dbo.etiquetas
                 where partnumber like '" &amp; partnumber &amp; "'
                 and serialnumber like '" &amp; serialnumber &amp; "')
  begin
      INSERT INTO etiquetasklc.dbo.etiquetas(Nome_Posto, data, Lote, serialnumber, partnumber,
                                             Versao, indexnumber, Semana, QRGerado, operador)
      VALUES( '" &amp; posto &amp; "','" &amp; data &amp; "','" &amp; ordemfabrico &amp; "','" &amp; serialnumber &amp; "','" &amp; partnumber &amp; "',
              '" &amp; partnumber2 &amp; "','" &amp; indexnumber &amp; "','" &amp; weekyear &amp; "','" &amp; codigo &amp; "','" &amp; operador &amp; "')
  end "


The problem occurs when they hit the print button simultaneous.

Can anyone help me

I'm no coder and the SQL is wrote based on my limited skills
I think 2 options that can help you:


Instead of getting the last number and add 1 you can create a table with an IDENTITY column (SerialNumber). When you insert one row the identity column adds the increment you specify (in your case, 1). If 2 users hit the button at the same time SQL server is capable to manage the concurrency and it will assign different numbers.
The same solution as number 1, but instead of using IDENTITY column you extract the next serial number from a sequence object. You create this sequence object and define the "Starts With" number and "Increment" number. Same way about the users, you extract the number from the sequence and this number can only be extracted once avoiding concurrency issues. With this solution, you have to keep in mind that if by any reason your insert fails the serial number is now lost, once you extracted it the counter of the sequence was incremented even though your insert was not committed.


Here you have the official links for extra details about how to create an identity column and a sequence object:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-2017