Lookup Sql Field S’abonner

0
Avatar
Legacy Poster

Hi, i have a bartender document which needs to connect to an SQL database via VBscript and check a certain field whether it is empty (NULL) or not. If it is empty it should display NO and otherwise YES.

 

I have no clue how to establish a connection via vb script to this database. I can´t use the database feature within bartender to connect, because i have another database connected at the same time.

 

Perhaps anyone can help me out with this, or showing me some example script that might point me to the right direction.

 

Im running Bartender Automation 10.1 SR4

6 commentaires

0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

you can connect more than one database to a label document.  You just have to set the relation to the two databases after more than one are added.

0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

Hi Slickson, I know, i use your idea in other documents, but the database i need to connect to has no relation so i can combine them. I need a connection via VB scripting.

 

Any help would be greatly appreciated.

0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

This is part of a script that I use - maybe it can help you.

 

'------QUERY PARAMETERS------------------------------------
'SQL Server:
sqlserver = "NameOfYourSQL-Server"

'SQL Database:
sqldb = "NameOfTheSQL-DB"

'SQL DB Table:
sqltable = "NameOfTheSQL-Table"

'Search for...:
searchkey = "YourValue"

'...in this column:
searchcol = "RelevantColumn"
'----------------------------------------------------------

conpar = "Provider=SQLOLEDB;Data Source=" & sqlserver & ";Trusted_Connection=Yes;Initial Catalog=" & sqldb & ";"
sqlcall = "SELECT * FROM " & sqltable & " WHERE " & searchcol & "='" & CStr(searchkey) & "'"

Set conn = CreateObject("ADODB.Connection")
conn.Open conpar
Set recs = CreateObject("ADODB.Recordset")
recs.Open sqlcall, conn, 1, 3
0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

'------QUERY PARAMETERS------------------------------------
'SQL Server:
sqlserver = "MYSERVER"

'SQL Database:
sqldb = "Database"

'SQL DB Table:
sqltable = "dbo.Data"

'Search for...:
searchkey = product

'...in this column:
searchcol = "Product_Code"
'----------------------------------------------------------

conpar = "Provider=SQLOLEDB;Data Source=" & sqlserver & ";Trusted_Connection=Yes;Initial Catalog=" & sqldb & ";"
sqlcall = "SELECT * FROM " & sqltable & " WHERE " & searchcol & "='" & CStr(searchkey) & "'"

Set conn = CreateObject("ADODB.Connection")
conn.Open conpar
Set recs = CreateObject("ADODB.Recordset")
recs.Open sqlcall, conn, 1, 3

Hi, thanks for your reply. Mabe you can help me a little further... Okay i can make a database connection, but basically what i need working is the script checks whether named data source "product" excist in the database and displays the contents from column "Pictogram1" in my database onto my label. Then, i need to know what script i need to display on my object to fill that data onto my label.

 

The code i have now in my bartender document options is at onOpen:

0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

The way the SQL query is set up in your current script, only records are loaded from the DB that contain "product" in column "Product_Code". You can use

recs.Fields("Pictogram1")

to get the value in column "Pictogram1". To display the value in the object where the script is located, simply assign that to "Value":

Value = recs.Fields("Pictogram1")

This will get the value from the first found record - so this only really works, if the records are unique.

 

You can check whether a record is found at all using something like this:

If recs.EOF or recs.BOF Then
...

 

Keep in mind though that this is mostly amateur/noob code that I scratched together from the internet. So don't sue me if it doesn't work  :)

0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

Hi KM, sorry im not getting any further, anyway thanks for you help.

Vous devez vous connecter pour laisser un commentaire.