Updating Serial Number In Database Works Too Well!

Legacy Poster

• May 07, 2015 11:48

Hi

 

The client wants the serial number to increase automatically and to start from last number each day.

 

I've written a VBScript that connects to a MS SQL table, reads the larges serial number so far, adds 1 to the number and stores it back in the database.

 

This works fine - except that the script seems to run several times on curious occasions: The number increases by 3 when the template is opened, it increases by 3 when the template is printed (even though only one label is printed) etc. Essentially I only want it to run each time a new label is printed!

 

The script is in the DataSource for the serial number in the template and is as follows:

 

'declare the variables 

Dim Connection

Dim Recordset

Dim SQL

Dim Serial

 

'create an instance of the ADO connection and recordset objects

Set Connection = CreateObject("ADODB.Connection")

Set Recordset = CreateObject("ADODB.Recordset")

 

'open the connection to the database

Connection.Open "DSN=BTDSN32;UID=user_name;Trusted_connection=yes;Database=DDS"

 

SQL = "SELECT MAX(Serial) as S FROM DDS.dbo.Palle"

 

Set RecordSet = Connection.Execute(SQL)

 

Do Until RecordSet.EOF

    Serial = RecordSet("S")

    RecordSet.MoveNext

Loop

 

Dim s 

s = CLng(Serial)

s = s + 1

 

SQL = "INSERT INTO DDS.dbo.Palle (Serial) VALUES (" & s & ")"

Connection.Execute(SQL)

 

Value = CStr(s)

0

• 

  Legacy Poster

  ★ BarTender Hero ★

  • May 10, 2015 06:27

  SOLVED

   

  Put this script into the proper event (doh!) and it does exactly what is needed.

   

  My version of BarTender is in Danish so I'm not sure what the proper name is for it, but the VisualBasic help explains it all!

  0

Please sign in to leave a comment.