Vbscript - Sql Query That References
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
-
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 rsstrConn = "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 rsstrConn = "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 -
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 -
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 -
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 -
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.
0 -
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 -
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 -
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...
- 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....
- If there are data entry errors in the QueryPrompt's input, error handling seems difficult to do / make easy for the user
- 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 -
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 -
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 -
Ping.... ?
I'm guessing at this point, that this can't be done using Bartender's built-in DB connectivity functions.
Again,
- 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
- Each row has a barely human-readable key, and since 'n' is very large, manually entering the key is not practical
- So, I want to populate a drop-down or list box with the possible keys, and allow the user to select the appropriate one
- 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;
- The ability for the User to select a key for a row in a database - i.e. from a list/drop-down box
- 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 -
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 -
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 rsstrConn = "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
0
Vous devez vous connecter pour laisser un commentaire.
Commentaires
13 commentaires