Updating Serial Number In Database Works Too Well! Follow

0
Avatar
Legacy Poster

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)

1 comments

0
Avatar
Legacy Poster
Comment actions Permalink

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!

Please sign in to leave a comment.