Home | Using a Virtual Basic Script to Increment a Serial Number Stored Database – CODESOFT Reference
Print or Email this article

Using a Virtual Basic Script to Increment a Serial Number Stored Database – CODESOFT Reference

Intended Audience: Advanced User
Article # 7778

Summary

This Reference Article demonstrates how to create a Virtual Basic script to generate incremental serial numbers within a database.

Information

The below example inserts the Virtual Basic script into the label property (File/Properties/Visual Basic Scripting). The script is inserted to run after each print job (Sub OnAfterPrint in CODESOFT) and updates the field in SQL database (serial.serialnumber) with a new serial number (incremented by 100 for each label printed). It also resets the ser_counter variable in the label back to “1”.

VB Script Inserted in CODESOFT

Terminology Guide:

VARVALUE1 – the calculated value of the new serial number. “ser_counter” is the counter in the label that counts how many labels were printed.

ConnString – the variable that is used to construct the connecting string command to connect to the database.

SQL – the variable that is used to construct the actual SQL statement to update the SQL database

Dim ConnString
Dim SQL
Dim myConnDim MyCommand
DIM VARVALUE1
DIM VARVALUE2

VARVALUE1 = Document.Variables.Item("SerialNumber").Value() +  (document.Variables.Item("ser_counter").value() - 1)* 100)
VARVALUE2 = Document.Variables.Item("SKU_NO").Value()
msgbox ("Counter = " & (document.Variables.Item("ser_counter")-1))
document.Variables.Item("ser_counter").value = 1
SQL = "Update Serial set serial.serialnumber = " & VARVALUE1 & " where sku = '" & VARVALUE2 & " ' "
ConnString="DRIVER={SQL Server};SERVER=(local)\SQLEXPRESS;UID=sa,PWD=Efficient!;DATABASE=TOPCON"
Set myConn = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command" )
myConn.Open ConnString
Set myCommand.ActiveConnection = myConn
myCommand.CommandText = SQL
myCommand.Execute
myConn.Close

Example

You can download a sample file demonstrating the above by clicking on the link, below.

Sample label file

Additional information:

The CODESOFT Help/Programmer’s Guide provides a description of the different objects available under CODESOFT.

CODESOFT’s Programmers Guide is located in the Help menu.

Search for “createobject” within the Programmer’s Guide to locate the CreateObject Function for reference and guidance.

The CreateObject Function can be found using the keyword search in the Programmer’s Guide.

Author

Dave Klement

Was this article helpful?

Related Articles