0

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 '" & partnumber & "'
                         ORDER BY serialnumber DESC"

Sql Insert Statement:

 "if not exists (select serialnumber
                 from etiquetasklc.dbo.etiquetas
                 where partnumber like '" & partnumber & "'
                 and serialnumber like '" & serialnumber & "')
  begin
      INSERT INTO etiquetasklc.dbo.etiquetas(Nome_Posto, data, Lote, serialnumber, partnumber,
                                             Versao, indexnumber, Semana, QRGerado, operador)
      VALUES( '" & posto & "','" & data & "','" & ordemfabrico & "','" & serialnumber & "','" & partnumber & "',
              '" & partnumber2 & "','" & indexnumber & "','" & weekyear & "','" & codigo & "','" & operador & "')
  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

  • 2
    That's a bad way to generate sequential numbers, as you've found out. Consider using a sequence instead. – mustaccio Jan 11 at 17:33
0

A lot of the support code for this recommended answer depends on how the serial numbers are formatted. For this case, I will assume a simple incrementing number, but adding in other information into the serial number is just a matter of concatenating the extra information onto the start or the end of the incrementing number.

If you are using SQL Server 2012 or later (and, really, you should be if you can), then implementing a SEQUENCE is your best bet. A sequence in SQL Server is an object that will return the next number in sequence as defined by your CREATE SEQUENCE statement. Say, for example, that your serial number's variable portion is a nine digit number starting at 100000000, allowing for about 900 million sequence numbers. To create the sequence, your code would be:

CREATE SEQUENCE dbo.SerialNumber  
    START WITH 100000000
    INCREMENT BY 1;

Now, when you want a new sequence / serial number, you can retrieve it with a command like:

DECLARE @NextSerialNumber bigint    -- Default data type for a sequence
SET @NextSerialNumber = NEXT VALUE FOR SerialNumber

If you need two people to generate separate sequential sequences (so, each sequence should have the same numbers), you can create two different sequences to draw from. If the two people should be creating serial numbers for the same sequential sequence (say, the same part twice, or a sequence that needs to be non-identical between two different tables, so that table A gets sequence entries 1, 2, and 4, while table B gets sequence entries 3, 5, and 6), use one sequence only.

  • thks, this will help – Nuno Roque Jan 14 at 10:04
  • i have the following issue also: the struture is serialnumber + partnumber. so it happens to have 00001partnumber01 and 00001partnumber02. do i have to create a seuqence table for each partnumber? – Nuno Roque Jan 14 at 10:05
0

I think 2 options that can help you:

  1. 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.

  2. 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

  • thks, this will help – Nuno Roque Jan 14 at 10:03

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.