Vbscript To Populate A Dropdown List
I successfully established a database connection to a SQL database. I then created a textbox field linked to part number data from the database. After that I placed a dropdown list on the prompt design page with the intention that the dropdown list would be populated with the database part numbers for the end user to select when printing. I get stuck at this point and need your help. It appears that I need to enter some vbscript in the List Items menu so the database part numbers populate the dropdown box. Can you provide me with an example of vbscript that I could use to populate the dropdown box?
0
-
Shotaro Ito
★ BarTender Hero ★
Hi Tralabel - still need code?
To populate dropdown options from database,
In Drop down Properties > List items > Select VB Script source > Edit VB Script, write VB Script into OnFillList event.
You cannot use BarTender's database connection to get dropdown options. You need to create connection in VBScript by yourself.
[code]
'Use SQL Server table as dropdown source. Place in OnFillList VBScript of Dropdown list on User prompt.
dataSource = "MYPC\MYDB" 'Specify sql server by "COMPUTERNAME\SERVERNAME"
initialCatalog = "TESTDB" 'Specify database name
tableName = "PRODUCTS" 'Specify table(view) of dropdown source
columnName = "PRODUCTCODE" 'Specify fieldname of dropdown source
userId = "foo" ' Specify when SQL Authentication is used
password = "bar" 'Specify when SQL Authentication is used
'Create connection
Set objCon = CreateObject("ADODB.Connection")
'setup connection string for Excel (Commented out as it's not used. specify fileName.)
'objCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & fileName & "';Extended Properties='Excel 8.0;HDR=YES;Mode=Read'"
'in case of Windows authentication
'objCon.ConnectionString = "Provider=SQLOLEDB;Data Source=" & dataSource & ";Initial Catalog=" & initialCatalog & ";Integrated Security=SSPI;Persist Security Info=False;"
'in case of SQL authentication
objCon.ConnectionString = "Provider=SQLOLEDB;Data Source=" & dataSource & ";Initial Catalog=" & initialCatalog & ";Persist Security Info=True;User ID=" & userId & ";Password=" & password & ";"
objCon.Open
'generate SQL
strCon = "SELECT " & columnName & " FROM " & Tablename & " ORDER 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
'Close connection
objCon.Close
Set objCon = Nothing
'MsgBox(options)'for test
'set options
Value=options
[/code]
Hope that helps!0 -
Hi Shotaro
Thanks for the suggested code. I will let you know if resolves my issue.0 -
I was looking to do the same thing here. i pasted the code into the OnFillList VBScript area and when I go to print the label with prompting on, it says that my dropdown box is not linked to a database. Any help is appreciated.
Thanks0 -
Shotaro Ito
★ BarTender Hero ★
If you got the error #2750 The prompt object "Dropdown List Control 1" is not linked to a data source. Proceed with unlinked prompt?
Then, you need to associate a text / barcode in label design view to the dropdown box.
To get a text value from dropdown,
In Label design view, Open a text > datasource tab > More options > Prompt tab > select the dropdown, or
In Prompt design view, Open the dropdown > Linked substring tab, specify datasource.0 -
So I copied the code above and set my Product Name text field on my label to look at the drop down box value. I now get a script error in the VBScript code:
"Error Message #6900"
"OnFillList(Line 14) : Object required: 'objCon'"
Here is the code In my VBScript:
[code]
datasource = "\\camcoserver\scadadata\ScadaDb_Data.mdb"
intitalCatalog = "scadaDB_data.mdb"
tableName = "Product"
columnName = "Name"
userId = "Admin"
password = ""
'Create connection
objCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = \\camcoserver\scadadata\scadadb_data.mbd"
objCon.Open
'Generate the SQL string
strCon = "SELECT " & columnName & " FROM " & tableName & " ORDER BY " & columName
'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
'Close connection
objCon.Close
Set objCon = Nothing
Value = options
[/code]
Any help is appreciated.
Thanks
Jkirkwood0 -
Shotaro Ito
★ BarTender Hero ★
You need to create connection object by
[code]Set objCon = CreateObject("ADODB.Connection")[/code]
at the beginning of the script.
Line9. It looks your connection string end up with ".mbd", isn't actual file name is ".mdb"?
Line14. SQL statement's part end with < " ORDER BY " & columName > which would be columnName.
to testing script, you can save above text as "test.vbs" and double click the vbs file to run.
(in that case, add a line [ MsgBox(Value) ] at the end of script to show result.)
doing that you can forget about BarTender and concentrate on script.
You can get VBScript mdb connection sample here
[url="http://msdn.microsoft.com/en-us/library/ms524771(v=vs.90).aspx "]Accessing Data with ADO:Retrieving a Record Set[/url]0 -
Shotaro,
Thanks for looking at the code and helping out. After so long the lines start running together. I really appreciate the help.
Thanks again,
Jkirkwood0 -
I am also a new to Bartender .. I am also trying to Configure a label to have a drop down list to populate the rest of the label pulling from a SQL database..
So far I have created a sql data base connection that populates 4 fields on a label.. ( all data is pulling correctly)
Read through the code above and have got to the following code ..But receiving a Error #6900 at the end of line
----------------
objCon.ConnectionString = "Provider=SQLOLEDB;Data Source=D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\GLData.mdb & ";Initial Catalog=" & GLData08 & ";Persist Security Info=True;User ID=" & xxxx & ";Password=" & xxxx & "
---------------
I've been over the code and can not seem to find whats going on.. any thoughts
dataSource = "GLPB05"
initialCatalog = "GLData08"
tableName = "SHP_Loadsheetlabel"
columnName = "routecode"
userId = "xxxx"
password = "xxxx"
Set objCon = CreateObject("ADODB.Connection")
objCon.ConnectionString = "Provider=SQLOLEDB;Data Source=D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\GLData.mdb & ";Initial Catalog=" & GLData08 & ";Persist Security Info=True;User ID=" & xxxx & ";Password=" & xxxx & "
objCon.Open
'generate SQL
strCon = "SELECT " & columnName & " FROM "& SHP_Loadsheetlabel & " ORDER BY "& routecode
'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
'Close connection
objCon.Close
Set objCon = Nothing
'MsgBox(options)'for test
'set options
Value=options0 -
Gene Henson
★ BarTender Hero ★
GreenBeanGuy,
It looks like you may have just had some extra quotes and &'s in the connection string (unless those were added during posting)
See if this Connection String works:
[code]
objCon.ConnectionString = "Provider=SQLOLEDB;Data Source=D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\GLData.mdb ;Initial Catalog=" & GLData08 & ";Persist Security Info=True;User ID=" & xxxx & ";Password=" & xxxx
[/code]0 -
Thank you for the code . Silly thing to miss.. However I cp/pasted your line in and re-ran the test and now I am receiving Invalid authorization specification .. On line 13 which is right above the strCon statement..
thanks for your time and advice..0 -
Gene Henson
★ BarTender Hero ★
I think the problem here may actually be the connection string. I tried using your connection string in and I got the same error outside of BarTender. Are you actually connecting to an Access database? Your post mentioned a SQL server, but the file you connecting to is an mdb file. When I’ve created connection to Access Databases in the past I’ve used both of these two formats:
[code]
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin;Password=;
[/code]
And
[code]
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;Jet OLEDB:Database Password=MyDbPassword;
[/code]
You can find these and more connection string options here: [url="www.connectionstrings.com"]www.connectionstrings.com[/url].
Can you try a different connection string to see if that works?0 -
Must have fat fingered the b the file has a ext of .mdf for a sql database .. knowing that now.. what code changes would you recommend. I have been out to the connection strings and tried a couple , but seemed to have any different affect..
Thanks for your help0 -
Gene Henson
★ BarTender Hero ★
If you are connecting to a SQL server I don’t think you want to connect to the mdf file. I think you will want to specify the actual server address.
Something like this:
[code]Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;[/code]
MyServerAddress would be the same address that you use to connect to the SQL server from any other application (like using BarTender's integrated database connectivity).
Let me know if using that works for you.0 -
Hi Shotaro,
I would like to do the same but using an Oracle database as the datasource. Can you please help me as well? If possible, can you please also provide an example of how to connect to it. Thanks.
vramos
[quote name='Shotaro I -Seagull Support' timestamp='1314754997' post='474']
Hi Tralabel - still need code?
To populate dropdown options from database,
In Drop down Properties > List items > Select VB Script source > Edit VB Script, write VB Script into OnFillList event.
You cannot use BarTender's database connection to get dropdown options. You need to create connection in VBScript by yourself.
[code]
'Use SQL Server table as dropdown source. Place in OnFillList VBScript of Dropdown list on User prompt.
dataSource = "MYPC\MYDB" 'Specify sql server by "COMPUTERNAME\SERVERNAME"
initialCatalog = "TESTDB" 'Specify database name
tableName = "PRODUCTS" 'Specify table(view) of dropdown source
columnName = "PRODUCTCODE" 'Specify fieldname of dropdown source
userId = "foo" ' Specify when SQL Authentication is used
password = "bar" 'Specify when SQL Authentication is used
'Create connection
Set objCon = CreateObject("ADODB.Connection")
'setup connection string for Excel (Commented out as it's not used. specify fileName.)
'objCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & fileName & "';Extended Properties='Excel 8.0;HDR=YES;Mode=Read'"
'in case of Windows authentication
'objCon.ConnectionString = "Provider=SQLOLEDB;Data Source=" & dataSource & ";Initial Catalog=" & initialCatalog & ";Integrated Security=SSPI;Persist Security Info=False;"
'in case of SQL authentication
objCon.ConnectionString = "Provider=SQLOLEDB;Data Source=" & dataSource & ";Initial Catalog=" & initialCatalog & ";Persist Security Info=True;User ID=" & userId & ";Password=" & password & ";"
objCon.Open
'generate SQL
strCon = "SELECT " & columnName & " FROM " & Tablename & " ORDER 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
'Close connection
objCon.Close
Set objCon = Nothing
'MsgBox(options)'for test
'set options
Value=options
[/code]
Hope that helps!
[/quote]0 -
Shotaro Ito
★ BarTender Hero ★
[quote name='vramos' timestamp='1341532480' post='2768']
Hi Shotaro,
I would like to do the same but using an Oracle database as the datasource. Can you please help me as well? If possible, can you please also provide an example of how to connect to it. Thanks.
vramos
[/quote]
Hi vramos,
I haven't got Oracle setup, so I cannot test right now - however it looks connect to Oracle via VB Script is fairly common, so you won't have much trouble finding samples from web.
Try either pattern below (Use MS OLEDB)
[url="http://msdn.microsoft.com/en-us/library/windows/desktop/ms675851(v=vs.85).aspx"]Microsoft OLE DB Provider for Oracle[/url]
[code]
'in case of Microsoft OLE DB Provider for Oracle'
objCon.ConnectionString = "Provider=MSDAORA;Data Source=" & dataSource & ";User ID=" & userId & ";Password=" & password & ";"
[/code]
or below (Use Oracle OLEDB)
www.simongibson.com/intranet/adooledb/#oracle
[code]
'in case of Oracle OLE DB Provider for Oracle'
objCon.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & dataSource & ";User ID=" & userId & ";Password=" & password & ";"
[/code]0 -
Thanks Shotaro. I found a connection strin that seems to work using the Oracle ODBC driver, however, I can't test it as I'm not sure if my script is correct and am getting the error "OnFillList(Line 15): : Object required:'objCon'". I combined your script with my connection string. Apologies as I have very little experience with VBScript. Any help you can provide is greatly appreciated. Here's the code I'm using:
Dim strCon
strCon = "Driver={Oracle in OraClient11g_home1}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=mysrv)(PORT=1700))" & _
"(CONNECT_DATA=(SERVICE_NAME=mysn))); uid=read;pwd=read;"
'generate SQL
strCon = "SELECT " & columnName & " FROM " & Tablename & " ORDER 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
'Close connection
objCon.Close
Set objCon = Nothing
'MsgBox(options)'for test
'set options
Value=options
[quote name='Shotaro I -Seagull Support' timestamp='1341548197' post='2770']
Hi vramos,
I haven't got Oracle setup, so I cannot test right now - however it looks connect to Oracle via VB Script is fairly common, so you won't have much trouble finding samples from web.
Try either pattern below (Use MS OLEDB)
[url="http://msdn.microsoft.com/en-us/library/windows/desktop/ms675851(v=vs.85).aspx"]Microsoft OLE DB Provider for Oracle[/url]
[code]
'in case of Microsoft OLE DB Provider for Oracle'
objCon.ConnectionString = "Provider=MSDAORA;Data Source=" & dataSource & ";User ID=" & userId & ";Password=" & password & ";"
[/code]
or below (Use Oracle OLEDB)
www.simongibson.com/intranet/adooledb/#oracle
[code]
'in case of Oracle OLE DB Provider for Oracle'
objCon.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & dataSource & ";User ID=" & userId & ";Password=" & password & ";"
[/code]
[/quote]0 -
Shotaro Ito
★ BarTender Hero ★
[quote name='vramos' timestamp='1341593321' post='2776']
Thanks Shotaro. I found a connection strin that seems to work using the Oracle ODBC driver, however, I can't test it as I'm not sure if my script is correct and am getting the error "OnFillList(Line 15): : Object required:'objCon'". I combined your script with my connection string. Apologies as I have very little experience with VBScript. Any help you can provide is greatly appreciated. Here's the code I'm using:
[/quote]
Hi, Try the pattern below -
Basically you need to modify from line 2(datasource) to 6(password).
it worked with Oracle 10g XE.
If your still have problems, please ask your database administrator or someone - who setup or maintain the database.
Hope that helps.
[code]
'Use Oracle table as dropdown source. Place in OnFillList VBScript of Dropdown list on User prompt.'
dataSource = "XE" 'Specify oracle database name (in case of remote server, "COMPUTERNAME:1521/XE")'
tableName = "COUNTRIES" 'Specify table(view) of dropdown source'
columnName = "COUNTRY_NAME" 'Specify fieldname of dropdown source'
userId = "HR" ' Specify oracle username'
password = "****" 'Specify oracle password'
'Create connection
Set objCon = CreateObject("ADODB.Connection")
'in case of Microsoft OLE DB Provider for Oracle'
objCon.ConnectionString = "Provider=MSDAORA;Data Source=" & dataSource & ";User ID=" & userId & ";Password=" & password & ";"
objCon.Open
'generate SQL'
strCon = "SELECT " & columnName & " FROM " & Tablename & " ORDER 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
'Close connection'
objCon.Close
Set objCon = Nothing
'MsgBox(options)'for test'
'set options'
Value=options[/code]0 -
Thanks again. I tried your suggestion. Upon testing, I got the error: "NETCMN:invalid driver designator". My current driver is: Oracle in DEFAULT_ORANT9. 0 -
Shotaro Ito
★ BarTender Hero ★
[quote name='vramos' timestamp='1341880616' post='2791']
Thanks again. I tried your suggestion. Upon testing, I got the error: "NETCMN:invalid driver designator". My current driver is: Oracle in DEFAULT_ORANT9.
[/quote]
The error means connection string's format could be wrong.
If you can connect to the oracle database from BarTender's database connection setup, you should be able to connect to the database via Microsoft OLEDB driver, so try connecting from BarTender first.
To connect, the procedure would be similar to [url="http://www.seagullscientific.com/aspx/training-video-(reading-data-from-database).aspx"]this movie...[/url]0 -
I managed to get the dropdown working just fine. Is it possible to use the user's choice as the query prompt? I'm pulling part information based on which part the user enters, but I would definitely prefer having the part number chosen from the dropdown rather than having them type it in and risk typing the wrong number. 0 -
[quote name='JasonGriffith' timestamp='1345232111' post='3098']
I managed to get the dropdown working just fine. Is it possible to use the user's choice as the query prompt? I'm pulling part information based on which part the user enters, but I would definitely prefer having the part number chosen from the dropdown rather than having them type it in and risk typing the wrong number.
[/quote]
The problem with doing that is that the print time prompt occurs after the database query does, so it wouldn't have an opportunity to populate the query before the print occurs.
Realistically the only way to do that is to actually create the database connection inside the VB script again, then manually create and execute an SQL query in the script to use that field as the data, connecting the resulting data to each field using substrings.
Have you considered using 'Select Records at Print-Time' from the print dialog? That way they can see all the data and select the field or fields that they'd like to use.0 -
Hi
Can I get some help on this please, I keep getting an error #6900
coloumnName = "state" Set objCon = CreateObject("ADODB.Connection") objCon.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\hffile\supply chain\Labels Database\Shipping labels\Shipping_Labels_Database.accdb;" objCon.Open strCon = "SELECT " & coloumnName &" FROM [Order Headers]" Set rs = CreateObject("ADODB.Connection") Set rs = objCon.Execute(strCon) options="" Do Until rs.EOF If Len(options)>0 Then options = options + vbCR options = options + rs(coloumnName).value rs.MoveNext Loop objCon.Close Set objCon = Nothing 'set options value=options 'MsgBox(options)0 -
Gene Henson
★ BarTender Hero ★
Datha,
Can you please post the full error that you're receiving?
0 -
Hi,
Im manage to link the dropdownlist from OnFilllist, but my problem is how to get the value selected from the list. please help.
0
Please sign in to leave a comment.
Comments
24 comments