Environment
.NET 4.5, BarTender 2016R2
Symptoms
After creating a new label file which uses an ODBC data connection to a MS SQL View, attempting to print with a custom SQL statement using the .NET SDK will result in a “field not found” message (see repro below) being generated for all text fields in the view. Number and data fields appear to work properly.
This issue can be fixed by editing the label file and changing the data connection SQL statement to “custom” by checking the box “Specify a custom SQL statement” in the SQL Statement pane of the Database Connection Setup dialog and specifying a valid SQL statement for the view. After the label file is saved, the data connection “custom” SQL statement can then be unchecked/removed. After saving the label file again the label can be printed with no errors using the .NET SDK.
Repro Scenario
Create a new blank template and add some blank text objects to it.
Using the Database Setup dialog, create a new ODBC data connection using an existing ODBD data source and select a SQL view for the table.
Test the new data connection and then exit the Database Setup dialog.
Add a mix of the new text and number fields database fields to the label’s text objects
Save the label file.
Try to print or preview the label in the BarTender Designer to confirm it works.
Save the label file and exit BarTender Designer
Print the label file using the .NET SDK
Result
The label fails to print and the following message is displayed in a dialog twice for each text based database field, number and date files will work.
An object on the template was set to read in the field "TestDB.TestView.Name". However, this field was not found. Proceed with unknown fields as blank strings?
To fix or workaround this issue
Edit the failing template and change the data connection to use a custom SQL statement that’s simply a SELECT * FROM TestView, although a list of fields instead of a wildcard work fine as well.
Rebind all the now missing text database fields back to their respective text objects on the label.
• Note that number and date bindings were unaffected by the change.
Save the label file.
Try to print or preview the label in the BarTender Designer to confirm it works.
Save the label file and exit BarTender Designer
Print the label file using the .NET SDK
Result
The label file prints properly.
What’s even odder
Edit the same template and change the data connection back so it no longer uses a custom SQL statement
• Note: I noticed that all the text fields disappeared in the grayed out SQL statement that was generated after I unchecked the box. This is a bit suspicious.
Save the label file and exit BarTender Designer
Print the label file using the .NET SDK
Result
The label file prints properly.
Sample .NET SDK code used to print the label files
var btFormat = btEngine.Documents.Open(“test.btw”);
btFormat.PrintSetup.UseDatabase = true;
btFormat.PrintSetup.IdenticalCopiesOfLabel = 1;
if (btFormat.DatabaseConnections.Count > 0 && btFormat.DatabaseConnections[0] != null)
{
var dbConnection = btFormat.DatabaseConnections[0];
dbConnection.SQLStatement = sqlQuery;
}
btFormat.Print();
FWIW: I’ve found the below suggested code pattern isn’t working in 2016R2, but a new public property in the DatabaseConnection class called SQLStatement now exist and works fine for setting the SQLStatement. I hope this is just a documentation issue.
var newODBC = new ODBC(dbConnection.Name);
newODBC.SQLStatement = sqlQuery;
btFormat.DatabaseConnections.SetDatabaseConnection(newODBC);