Retrieve Value From Mssql Database Based On Commander Field Follow

0
Avatar
Legacy Poster

I have the following scenario:

 

Printing labels using Commander. One of the fields sent to Commander is the item number. I have a field on my label that should display the item description. The item description is held in a MSSQL database. I need to take the item number from the Commander input and use that value in my query to the MSSQL database.

 

My problem is that I am unsure how to reference the item number field from the commander database in a query to my MSSQL database. Here's what I have so far:

 


	Set oCon = CreateObject("ADODB.Connection")
	Set oRS = CreateObject("ADODB.RecordSet")

	oCon.Open "Provider=SQLOLEDB.1;Password=xxxxx;Persist Security Info=true;User ID=xxxxx;Initial Catalog=MyDB;Data Source=MyServer"
	oRS.open "SELECT Description FROM dbo.items where itemnumber = LBITEM", oCon, 3, 4

	value = oRs.Fields("Description")

	set oRS = nothing
	set oCon = nothing

 

LBITEM is the name of the item number field from the Commander database.  This gives me an Invalid column name 'LBLOT' error, however.

 

Can someone assist?

 

Thanks.

6 comments

0
Avatar
Ian Cummings
Moderator
Comment actions Permalink

Just to clarify...

 

Are you saying that a unique field value for a product item in the trigger file is to be used as the input for a query to a fixed database that will thus give you a related description field in that database?

 

Assuming the above is true, then I suggest that in the database connection setup of the BarTender document, you add a connection to both a template data file (which will be the trigger file at print time) and the fixed database joining on the related LBITEM field.  At print time the join of one data record to the fixed database will return a record set to BarTender of one label to print.

 

There are many users out there that take this approach.

0
Avatar
Legacy Poster
Comment actions Permalink

Something very similar to what you described, but I did simplify my example a bit. Due to the nature of the not-so-simple task, a simple join won't work for me.

 

I did figure out what I needed to know, and this is what I came up with (based on the simplified example):

 

Dim ItmNum
Dim SQLStmt

ItmNum = Field("db680.LBITM") 
SQLStmt = "SELECT Description FROM dbo.items where itemNumber = '" & ItmNum & "'"

Set oCon = CreateObject("ADODB.Connection")
Set oRS = CreateObject("ADODB.RecordSet")
oCon.Open "Provider=SQLOLEDB.1;Password=xxxxx;Persist Security Info=true;User ID=xxxxx;Initial Catalog=MyDB;Data Source=MYDBSERVER"
oRS.open SQLStmt, oCon, 3, 4

If oRs.EOF = false then
  value = oRs.Fields("Description")
Else
  value = "No Value"
End If

set oRS = nothing
set oCon = nothing

0
Avatar
Legacy Poster
Comment actions Permalink

I am looking for a solution very similar to this one. We use Bartender Automation and Macola/Progression from Exact software(ERP software) with the WMS modules. Unfortunately I don't know sqat about vb scripting.

 

I beleive I need to "on process data" run a script using the order#  to query SQL for the ship date and return the value to my label design for printing. The order# is keyed into a WMS screen to kicking off the printing of labels and is also a field on the label design.

I'm pretty confident I can find the proper table in SQL where the ship date would be.

 

Any help would be greatly appreciated. 

 

Thanks

Matt

0
Avatar
Domingo Rodriguez
Moderator
Comment actions Permalink

Matt,

Are you also using Commander to integrate your ERP System with BarTender? If yes, does the proposed solution from "Ian C" not help you to perform a query prompt on your database?
0
Avatar
Legacy Poster
Comment actions Permalink

No, I am not using commander. As I understand it WMS, one label at a time, opens the label feeds data to the label via Share/Names and prints the label.

I do have a version of the label where this field is set to pull the correct field from SQL but I can't figure out how to query the order#. I f I print it manually now it prints all the data in the table.

0
Avatar
Domingo Rodriguez
Moderator
Comment actions Permalink

Find information on how to create a query prompt:
 

An example on how to use the .NET SDK to assign a query prompt value programmatically:

 

Setting Query Prompts
A query prompt, defined as part of the attached database, is used to select which records are used when printing to label. The LabelFormat class contains a list of QueryPrompts.

 

The following example demonstrates the use of QueryPrompts in the BarTender Print SDK.

 

In C#:

 

Engine btEngine = new Engine();

 

// Start a BarTender Engine process

btEngine.Start();

 

// Open a label format

LabelFormatDocument btFormat = btEngine.Documents.Open(@"c:\MyLabel.btw");

 

// Set the QueryPrompt

btFormat.DatabaseConnections.QueryPrompts["Nutrition Calorie Data"].Value = "40 Calories";

 

// Print the label format

Result result = btFormat.Print();

 

In VB:

 

Dim btEngine As New Engine()

 

' Start a BarTender Engine process

btEngine.Start()

 

' Open a label format

Dim btFormat As LabelFormatDocument = btEngine.Documents.Open("c:\MyLabel.btw")

 

' Set the QueryPrompt

btFormat.DatabaseConnections.QueryPrompts("Nutrition Calorie Data").Value = "40 Calories"

 

' Print the label format

Dim result As Result = btFormat.Print()

In the above example, a BarTender Engine instance is started in a typical manner. A format with a database connection is then opened. Next, the nutrition calorie data prompt value is set to "40 Calories". Finally, the label format is printed. 
 

Please sign in to leave a comment.