Script Help - Writing to database Follow

0
Avatar
Jamie Spain

Hello, 

I need some guidance on creating some specific functionality within bartender. I currently have a label connected to a database which uses a query prompt form at print time. 
The prompt checks if the user input data (a 13 number barcode) is found in the database. If found, it prints, if not, it doesn't. It's used as a quality check to ensure that no incorrect barcodes are entered. 

The next piece of functionality I need is a log of what barcodes have been scanned in and thus printed. To the best of my knowledge there is no way to do this within the software. Basically I need a script that takes any data entered in the query prompt and save it as a new record in a database. 

I have very limited experience with VB and don't know where to start to get this happening, Links to resources and material or tips and advice that will help me learn would be greatly appreciated. Thank you.  

2 comments

0
Avatar
Pete Thane
Comment actions Permalink

Hi Jamie,

If it the data can just be written out to a flat text file rather than a specific ODBC database then you could just use logging option from the Administer menu (depending on your version of BarTender) and record the number that has been printed. If  the data needs adding to a specific file then you maybe better finding a VB programmer to help with this although I think one of the VB commands will be the write.line command and you could check out the Help in VB and see if anything in there makes sense on how to achieve what you want.

Sorry not much help with this.

Pete

0
Avatar
John Cummens
Comment actions Permalink

Hi Jamie;

Having recently tackled this myself, I can tell you that it is entirely doable.  I am able to successfully connect to a database, add/update records, retrieve records, etc.

Here is a link to a MS doc that explains many of the concepts that would be used.

https://docs.microsoft.com/en-us/sql/ado/guide/appendixes/using-ado-with-microsoft-visual-basic?view=sql-server-2017

 

First I had to create the database and table(s), and set up an ODBC Data Source Name (DSN) for it in the Windows DSN manager.

Here is the script I was using to learn/debug the process.  It shows both updating records and querying (add/delete is just a change of SQL statement). It currently uses MsgBox for debug statements, which can be removed for production, but were great for learning.

The sample database in question ('GLOBALS') has a single table ('Globals') with two columns ('FieldName' and 'FieldValue').

 

rem start of script ---------------------------------------------------------------

Dim connection
Set connection = CreateObject("ADODB.Connection")
connection.ConnectionString = "DSN=BARTENDER;database=GLOBALS;"
connection.Open

If connection.State = 0 then
   MsgBox "Could not Connect to DB" + connection.State
End If


rem --------------------------------------------
rem This block updates records with testing data

dim sql
sql = "UPDATE Globals SET FieldValue = 'TestUpd1' WHERE FieldName = 'Test1';"
connection.Execute sql

rem end update record block
rem --------------------------------------------

rem --------------------------------------------
rem This block reads all records from the table and displays in a msg box each fieldname/fieldvalue pair

set rs=CreateObject("ADODB.recordset")
rs.Open "Select * from dbo.Globals", connection

if rs.State = 0 then
   MsgBox "Could not read records from table"
End if


if rs.BOF or rs.EOF then
   MsgBox("NO records were read")
else
   MsgBox("Read records:")
   do until rs.EOF
      MsgBox("FieldName:" + rs("FieldName"))
      MsgBox("FieldValue:" + rs("FieldValue"))
      rs.MoveNext
   loop
End If

rem end read record block
rem --------------------------------------------

connection.Close

 

rem end script ---------------------------------------------------------------

Hope this helps;

John

Please sign in to leave a comment.