passing a parameter to vbscript for db update Folgen
I am trying to create a label in BarTender 10.1 SR4, but only want the user to be able to print this label 1 time for the desired parameter. I’m using an odbc connection to find an order number in a table. Connection sql:
SELECT order_id, validate_data FROM "ORDER_TABLE" where "ORDER_ID" like '%?order_id%' and "VALIDATE_DATA"<>'YES'
As shown, the user would input an order number as a parameter (?order_id), and would only return data if the field ‘validate_data’ is not = ‘YES’.
The above works correctly and I can print a label as desired.
After printing, I would like to update the field ‘validate_data’ to a value of ‘YES’, so that label cannot be printed again without intervention from an approved party (IT).
I am a novice at VB Scripting, but the below works, at least to a point. I have tried to enter a VB Scripting event, under ‘OnPrintJobEnd’. The script:
Set objConn = CreateObject("ADODB.Connection")
set rs = createobject("adodb.recordset")
ordno = "?order_id"
SQL ="UPDATE order_table set wsoh_gt_19='YES' where order_id='" & ordno & "'"
Of note, the above does not change the value of the field ‘validate_data’. It appears the value of the parameter is not being passed to the script. If I change line ‘ordno = "?order_id"’ above to an actual value, (i.e., ordno = ‘SO004956328’) and testing the script under the Script Assistant in BarTender, it updates the field. Likewise, if I change the SQL statement to include the same hard-coded value, it will update the data base field. Likewise, if I print the label with the hard-coded values, it also updates the field.
How do I pass the parameter into this VB script to update the correct data base record/view, or, is there a better solution?