Vbscript - Sql Query That References フォローする

0
Avatar
Legacy Poster

Seeking help with on a referencing field on label template, in which it's object name is called "Plant".   Plant object is a drop down list that contains a list of code locations, i.e. BT01, RS01..etc.  Below is the VBScript being used in an other drop down list, that wants to use (reference) the data in "Plant" object.

 

I am not able to get this to work.

 

 

******************************************************************************

 

dim objCon
dim strCon
dim rs


 

dataSource = "someserver" 'sql server"
initialCatalog = "warehouseDB" 'Database name
tableName = "MyTable" 'Table(view) of dropdown source
columnName = "Item" 'Fieldname of dropdown source
'columnName2 = "Warehouse" 'Fieldname of dropdown source
userId = " " 'SQL Authentication is used
password = " " 'SQL Authentication is used


 

'Create connection
Set objCon = CreateObject("ADODB.Connection")


 


'SQL authentication
objCon.ConnectionString = "Provider=SQLOLEDB;Data Source=" & dataSource & ";Initial Catalog=" & initialCatalog & ";Persist Security Info=False;User ID=" & userId & ";Password=" & password & ";"


 


objCon.Open


 


'generate SQL
 

'If I hardcode it, the SQL works.

 

strCon = "SELECT " & columnName & " FROM " & Tablename & " WHERE warehouse = 'BT01' GROUP BY " & columnName

 

'but if I reference using the object name it does not work.

 

'strCon = "SELECT " & columnName & " FROM " & Tablename & " WHERE warehouse = 'Format.NamedSubString("Plant")' GROUP BY " & columnName

 


 

'run query
Set rs = CreateObject("ADODB.Recordset")
Set rs = objCon.Execute(strCon)


 


'fill options
options=""
Do Until rs.EOF
 If Len(options)>0 Then options = options + vbCR
 options = options + rs(columnName).value
 rs.MoveNext
Loop


 

Value = options

13 コメント

0
Avatar
Domingo Rodriguez
モデレータ
コメントアクション Permalink

I've attached an example which should be useful to you.

 

In order to test this label, copy the Excel spread sheet to the location “C:\Seagull”. First of all we have to populate the dropdown list using the data from a data source. To see this code click “Dropdown List Options”, select “Visual Basic Script” and then the “Edit” button. The following code is used in order to populate the list:

 

Functions and Subs

dim objConn
dim strConn
dim rs

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Seagull\Drop down.xls';Extended Properties=""Excel 8.0;HDR=YES;"""

Public Function GetNames()
    Dim strNames
     Set objConn = CreateObject("ADODB.Connection")
    objConn.Open strConn
    Set rs = CreateObject("ADODB.Recordset")
    Set rs = objConn.Execute("SELECT * FROM [Sheet1$]")
     strNames = ""
    rs.MoveFirst()
    do while NOT rs.EOF
        strNames = strNames + rs.fields("Fruit") + vbCrLf
        rs.MoveNext()
    loop
    GetNames = strNames
End Function

OnFillList

Value = GetNames()

 

 

The next thing that we have to do is to populate our other text objects based upon the data that has been selected in the drop down list via a query. The following code is in the second text object and displays the price, relating to the fruit that has been selected. This is the code that populates the data for this object:

 

dim objConn
dim strConn
dim rs

 

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Seagull\Drop down.xls';Extended Properties=""Excel 8.0;HDR=YES;"""
Set objConn = CreateObject("ADODB.Connection")
objConn.Open strConn
Set rs = CreateObject("ADODB.Recordset")
Set rs = objConn.Execute("SELECT * FROM [Sheet1$] WHERE Fruit = '" & TextFruit & "'")
Value = rs.fields("Price")
 

The line that you see in red is the line that does the query. Basically the “SELECT * FROM” selects all the records where the record “Fruit” is the same as the selected item from the drop down list. The drop down list is referenced by setting the “Share/Name” in this case “TextFruit”. 

 

 

0
Avatar
Legacy Poster
コメントアクション Permalink

We have a drop down list called "PlantDropdown" (object name) that contains a list locations.   This drop down list is related to (object name) "Plant" on the label.  This drop down is working successfully. 

 

We would like to uses the data that is selected from the drop list, which we believe is being stored in the (object name) "Plant" on the label.

 

How do we reference the data with the object name "Plant", which is related to the drop down list with the object name "PlantDropdown" in the SQL query listed below.

 

Is using the Format.NamedSubString("Plant") the proper method or should we being referencing another object name?

 

strCon = "SELECT " & columnName & " FROM " & Tablename & " WHERE warehouse = '"Format.NamedSubString("Plant")"' GROUP BY " & columnName

 

 

0
Avatar
Domingo Rodriguez
モデレータ
コメントアクション Permalink

You cannot reference an object name, you will need to reference the named data source instead. See attached a screenshot example. You will just need to give a name to the data source.

0
Avatar
Legacy Poster
コメントアクション Permalink

I need a similar solution and know next to nothing about vbscript. BarTender is excuted via our WMS software. I have a BarTender label design wich requires the ship date. The Label references BarTender tables to pull data via Named Sub Strings(share names), unfortunately no Named Sub-String for ship date. Current I have to hard code the date each time I print. Can a script be written which will query our SQL server for the ship date using the Named Sub-String for PO as a reference and return the value to the label for printing?

 

Thanks

Matt

0
Avatar
Domingo Rodriguez
モデレータ
コメントアクション Permalink

BarTender supports defining a query prompt via its database connection setup dialog. By entering the value of the query prompt, you will just be printing those database records which match the query prompt value. You can also use "VBScript" on the query prompt input field (and read a named sub-string value as your query prompt value). Do you think such a solution would be useful to you?

 

The below training video also explains how to perform a query prompt on your database. You will also need to open the properties for the query prompt you create, and then under "More Options" write some VBScripting.

http://www.bartenderbarcodesoftware.com/label-software/training-video-(selecting-records-from-a-database-at-print-time).aspx

0
Avatar
Legacy Poster
コメントアクション Permalink

I think what you're describing is exactly what I want to do - but I'm trying to figure out what you mean by "use 'VBScript' on the query prompt input field". 

 

Here's what I have, and what I'm trying to do.

- I have an excel table with several columns that add up to a unique key: call them A and B.

- I want to print a form that contains values from the row associated with that unique key 

 

I know I can use the Data Entry form to capture values for the key's columns (A&B). I can then - for each and every field on the form - write a VBScript query to query the table for the particular value for that field (e.g. "SELECT <field> FROM <db> WHERE A=x and B=y"). But, since there are dozens of fields on the form, this is a major pain, and practically a real maintenance nightmare. Plus, it doesn't take any advantage of Bartender's built-in database connectivity. 

 

I think what I really want to do is to be able to programatically set the SQL statement for the database connection at run time - based on the values returned from the Data Entry prompt. Then, the fields on the label can be simply tied to the datasource, which has a single row selected.

 

Is this possible, and if not, is there another approach you'd suggest? I'd also strongly prefer to avoid writing a standalone app to do the database manipulation and drive BarTender - that's another maintenance / support problem.

 

Thanks!

0
Avatar
Domingo Rodriguez
モデレータ
コメントアクション Permalink

Did you check the training video from my last post where it explains how to create a query prompt in BarTender? Would your problem not be solved by setting up 2 query prompts, one for A, one for B, or is there no way you can enter direct values for A and B at print time?

 

Just to be totally sure: are A and B two separate and different database fields?

0
Avatar
Legacy Poster
コメントアクション Permalink

Thanks Domingo. I did check the video - but in general, the QueryPrompt pop-up approach has deficiencies I'm trying to avoid, to keep the interface usable. Some of the problems with the QueryPrompt approach that I see are that...

  1. The values of 'A' and 'B' are not very human readable (e.g. 'SDF2974' or 'SFJ5246' - it's going to be very hard for operators to key in the correct codes all the time. I could require the operator to scan them from barcodes, but that's ugly - I'd like to use pre-populated drop-down lists. I have those working fine on the DataForm....
  2. If there are data entry errors in the QueryPrompt's input, error handling seems difficult to do / make easy for the user
  3. It's kind of annoying to have pop-up after pop-up.
So - I want to use values collected on the DataEntry form (i.e from drop-down lists) to set the database connection's SQL string. You implied that I could "read a named sub-string value as your query prompt value". If that implies that either (a) I can pre-populate the QueryPrompt input so that the user only has to accept the value, or (b) I could avoid seeing/displaying the QueryPrompt at all, that would be great!! Or, if you can think of another way (other than VBScripting a db lookup for each label field, or writing a separate app to drive the print format), that'd also be great!
 
Thanks!
0
Avatar
Domingo Rodriguez
モデレータ
コメントアクション Permalink

I've attached some samples which may be useful.

 

PromptDatabase and PromptDatabaseAdvance will shot how you get display database content in a drop down list in BarTender's data entry form (you will need to connect the BarTender document to an Access database and make some adjustments first).

 

LabelDataStore shows how you can prompt for a query prompt value in the data entry form and then use this value to query the database and also update the rest of the BarTender objects on the label to just display those records which resulted from the query prompt.

 

The query prompt dialog in BT is displayed before the data entry form. Therefore, you cannot use enter query prompt values (via VBScript) for a value which has been selected in the data entry form.

0
Avatar
Legacy Poster
コメントアクション Permalink

Hi,

 

Thanks for the examples - I'm a bit confused by Label data store, though. It looks to me like this example doesn't use a query prompt at all?. Instead, it looks like it's meant to demonstrate how to use SQL to add a record to an excel file every time you print the label. Am I missing something, or is this the wrong example?

 

Thanks!

0
Avatar
Legacy Poster
コメントアクション Permalink

Ping.... ?

 

I'm guessing at this point, that this can't be done using Bartender's built-in DB connectivity functions.

 

Again, 

  1. I've got a DB with 'n' rows (where n is very large), from which I want to populate a label with content of one row
  2. Each row has a barely human-readable key, and since 'n' is very large, manually entering the key is not practical
  3. So, I want to populate a drop-down or list box with the possible keys, and allow the user to select the appropriate one
  4. Once selected, the label should be populated with data from the row matching the selected key

The 'Prompt' dialog doesn't solve my problem, because it only seems to allow for manual text entry.

 

I know how to populate drop-down/listboxes with the available keys, but I can't seem to figure out how to use them to subsequently select the row Bartender's database connection will use to populate the label.

 

None of the examples seem to combine what seem to be the two essential functions/capabilities;

  1. The ability for the User to select a key for a row in a database - i.e. from a list/drop-down box
  2. The ability to have that User-selected key used to select the active row in the database, which is then used for the label fields

Unless I'm still missing something (like, I still haven't figured out how the Label Data Store example is relevant), .... it can't be done?

 

Thanks!

0
Avatar
Domingo Rodriguez
モデレータ
コメントアクション Permalink

Please take a more detailled look at my very first example "Drop Down from spreadsheet.btw". This does exactly what you want to do, but using BT's data entry form, not using BT's database connection setup wizard. You would first need to select BT's data entry form (press the F8 key), double click on the "Drop Down List", select the "List Items" node on the Left and now the "Edit VBScript" button to see what code has been used to populate all records from an existing database field.

 

Then, go back to BarTender's label design (F7). The first text object will display the item you've selected from the Drop-Down list and has a variable name of "TextFruit". The second text object uses VBScript to make a SELECT statement to the same database. This SELECT statement will only return those records which match with the value you've previously selected from the drop-down list.

0
Avatar
Legacy Poster
コメントアクション Permalink

Good Morning,

 

I'm trying to edit a similar script.

I've this excel file

 

Fruit               Price

item 1            8053369239606

item 2            8053369239606

item 3            8053369239606

 

I would like to use the script in this post for an EAN13 print output solution. Not Text (that works).

I've tryied to copy (ctrl+c, ctrl+v) the "Text 2 object"  vba code but it doesn't work.

 

Refering to Rominigo R post, This is the code that I'm trying to use:

 

dim objConn
dim strConn
dim rs

 

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Seagull\Drop down.xls';Extended Properties=""Excel 8.0;HDR=YES;"""
Set objConn = CreateObject("ADODB.Connection")
objConn.Open strConn
Set rs = CreateObject("ADODB.Recordset")
Set rs = objConn.Execute("SELECT * FROM [Sheet1$] WHERE Fruit = '" & TextFruit & "'")
Value = rs.fields("Price") - this line give me error - 

 

I repeat, it works for text object output print solution; not for ean13 object.
 

This is an example for the label that i would like to print.

 

     ITEM1

 

||||||||||||||||||||||||||

8053369239606

 

 

Thanks

 

 

BARTENDER Enterprise Automation 10.1 SR4

ログインしてコメントを残してください。