Retrieve Value From Mssql Database Based On Commander Field
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.
-
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 -
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 = nothing0 -
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 -
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 -
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 -
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.0
Please sign in to leave a comment.
Comments
6 comments