Script Help - Writing to database

Divyesh Patel

• 19. Juni 2018 22:55

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.  

0

• 

  Peter Thane

  ★ BarTender Hero ★

  • 20. Juni 2018 08:34

  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
• 

  John Cummens

  ★ BarTender Hero ★

  • 19. Juli 2018 21:46

  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

  0

Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.