Updating An External Db (Not Set Up Under Db Connections) Follow

0
Avatar
Legacy Poster

Using BarTender 10, I have 3 databases, 2 of which I can include in the label as they have a relationship; however the third I cannot. I need to pull and update information from the third DB and have resorted to using VBScript. I have it working, but there are some issues. The issue is the database is being updated several times or is updated even if the event isn't triggered. In an attempt to simplify troubleshooting, I have created a blank label with a single line text object on it. This object should display the data value and when printing is completed, update the DB value by 1 (serialization). To get the value to display I used VBScript found under the Text Properties > Transforms tab then under VBScript "Using VB Scripting" > OnProcessData. I had the update DB code in here originally but it would update 13 times when run, so I moved the update DB portion to Text Properties > Data Source > Event Controls Scripts (Edit with Scripting Assistant) > OnPrintEnd.

 

The issue I am noticing is, when going in to the properties of the text object, and viewing the OnPrintEnd, if you click on OK after, it appears to execute the update. Is there a reason for this?

 

Also, if I have done something wrong or if there is a better way of going about what I and trying to achieve, please let me know. Your help/insight is appreciated!

 

See attached sample (download and rename to ZIP), extracting the files and structure to your C drive.

 

3 comments

0
Avatar
Legacy Poster
Comment actions Permalink

UPDATE:

 

I moved the update code from the single line text object to the BarTender Document Options (View > View Options > VB Scripting > OnPrintJobEnd). I have also added a variable to determine if printing was cancelled or processed and increase it only when a job has completed successfully. This seems to have trapped the issue.

0
Avatar
Legacy Poster
Comment actions Permalink

I need to do something similar -- except that I'll be updating a SQL Server database so I'll need to change the connection information.  How do I set that up?

 

Thanks,

Valerie

0
Avatar
Domingo Rodriguez
Moderator
Comment actions Permalink

Although I've never tried to connect to an SQL Server database using VBScript, find below some code on how to achieve this:

<%
'declare the variables
Dim Connection
Dim ConnString
Dim Recordset
Dim SQL

'define the connection string, specify database driver
ConnString="DRIVER={SQL Server};SERVER=yourServername;UID=yourUsername;" & _
"PWD=yourPassword;DATABASE=yourDatabasename"

'declare the SQL statement that will query the database
SQL = "SELECT * FROM TABLE_NAME"

'create an instance of the ADO connection and recordset objects
Set Connection = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")

'Open the connection to the database
Connection.Open ConnString

'Open the recordset object executing the SQL statement and return records
Recordset.Open SQL,Connection

'first of all determine whether there are any records
If Recordset.EOF Then
Response.Write("No records returned.")
Else
'if there are records then loop through the fields
Do While NOT Recordset.Eof
Response.write Recordset("FIRST_FIELD_NAME")
Response.write Recordset("SECOND_FIELD_NAME")
Response.write Recordset("THIRD_FIELD_NAME")
Response.write "<br>"
Recordset.MoveNext
Loop
End If

'close the connection and recordset objects to free up resources
Recordset.Close
Set Recordset=nothing
Connection.Close
Set Connection=nothing
%>

Please sign in to leave a comment.