Jump to content


Photo
- - - - -

Vbscript To Populate A Dropdown List


  • Please log in to reply
24 replies to this topic

#1 Tralabel

Tralabel

    Newbie

  • Members
  • Pip
  • 2 posts
  • LocationIreland

Posted 24 August 2011 - 02:51 AM

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?

#2 Shotaro I - Seagull Support

Shotaro I - Seagull Support

    Member

  • Moderators
  • 503 posts
  • LocationJapan

Posted 30 August 2011 - 05:43 PM

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.

'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

Hope that helps!

Edited by Shotaro I -Seagull Support, 17 November 2011 - 05:32 PM.
oops - commented out a line which has no meaning.


#3 Tralabel

Tralabel

    Newbie

  • Members
  • Pip
  • 2 posts
  • LocationIreland

Posted 01 September 2011 - 01:11 AM

Hi Shotaro

Thanks for the suggested code. I will let you know if resolves my issue.

#4 jkirkwood

jkirkwood

    Newbie

  • Members
  • Pip
  • 8 posts

Posted 25 September 2011 - 10:29 AM

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.

Thanks

#5 Shotaro I - Seagull Support

Shotaro I - Seagull Support

    Member

  • Moderators
  • 503 posts
  • LocationJapan

Posted 25 September 2011 - 04:17 PM

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.

#6 jkirkwood

jkirkwood

    Newbie

  • Members
  • Pip
  • 8 posts

Posted 26 September 2011 - 06:22 AM

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:

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

Any help is appreciated.

Thanks
Jkirkwood

#7 Shotaro I - Seagull Support

Shotaro I - Seagull Support

    Member

  • Moderators
  • 503 posts
  • LocationJapan

Posted 26 September 2011 - 04:34 PM

You need to create connection object by
Set objCon = CreateObject("ADODB.Connection")
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
Accessing Data with ADO:Retrieving a Record Set

#8 jkirkwood

jkirkwood

    Newbie

  • Members
  • Pip
  • 8 posts

Posted 26 September 2011 - 05:45 PM

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,
Jkirkwood

#9 GreenBeanGuy

GreenBeanGuy

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 14 November 2011 - 02:48 PM

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=options

#10 Gene H

Gene H

    Director of Technical Services

  • Administrators
  • 124 posts

Posted 17 November 2011 - 02:27 PM

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:
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


#11 GreenBeanGuy

GreenBeanGuy

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 21 November 2011 - 08:24 AM

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..

#12 Gene H

Gene H

    Director of Technical Services

  • Administrators
  • 124 posts

Posted 21 November 2011 - 01:09 PM

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:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin;Password=;
And

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;Jet OLEDB:Database Password=MyDbPassword;
You can find these and more connection string options here: www.connectionstrings.com.

Can you try a different connection string to see if that works?

#13 GreenBeanGuy

GreenBeanGuy

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 21 November 2011 - 10:49 PM

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 help

#14 Gene H

Gene H

    Director of Technical Services

  • Administrators
  • 124 posts

Posted 22 November 2011 - 09:15 AM

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:

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
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.

#15 vramos

vramos

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 05 July 2012 - 03:54 PM

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

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.

'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

Hope that helps!



#16 Shotaro I - Seagull Support

Shotaro I - Seagull Support

    Member

  • Moderators
  • 503 posts
  • LocationJapan

Posted 05 July 2012 - 08:16 PM

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


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)
Microsoft OLE DB Provider for Oracle
'in case of Microsoft OLE DB Provider for Oracle'
objCon.ConnectionString = "Provider=MSDAORA;Data Source=" & dataSource & ";User ID=" & userId & ";Password=" & password & ";"

or below (Use Oracle OLEDB)
www.simongibson.com/intranet/adooledb/#oracle
'in case of Oracle OLE DB Provider for Oracle'
objCon.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & dataSource & ";User ID=" & userId & ";Password=" & password & ";"


#17 vramos

vramos

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 06 July 2012 - 08:48 AM

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

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)
Microsoft OLE DB Provider for Oracle

'in case of Microsoft OLE DB Provider for Oracle'
objCon.ConnectionString = "Provider=MSDAORA;Data Source=" & dataSource & ";User ID=" & userId & ";Password=" & password & ";"

or below (Use Oracle OLEDB)
www.simongibson.com/intranet/adooledb/#oracle
'in case of Oracle OLE DB Provider for Oracle'
objCon.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & dataSource & ";User ID=" & userId & ";Password=" & password & ";"



#18 Shotaro I - Seagull Support

Shotaro I - Seagull Support

    Member

  • Moderators
  • 503 posts
  • LocationJapan

Posted 08 July 2012 - 05:30 PM

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:


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.

'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


#19 vramos

vramos

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 09 July 2012 - 04:36 PM

Thanks again. I tried your suggestion. Upon testing, I got the error: "NETCMN:invalid driver designator". My current driver is: Oracle in DEFAULT_ORANT9.

#20 Shotaro I - Seagull Support

Shotaro I - Seagull Support

    Member

  • Moderators
  • 503 posts
  • LocationJapan

Posted 09 July 2012 - 09:03 PM

Thanks again. I tried your suggestion. Upon testing, I got the error: "NETCMN:invalid driver designator". My current driver is: Oracle in DEFAULT_ORANT9.

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 this movie...




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users