passing a parameter to vbscript for db update 追蹤

0
Avatar
Burch, Ricky

 

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.

 

My problem:

 

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:

 

Dim SQL

 

Dim ordno

 

Set objConn = CreateObject("ADODB.Connection")

 

set rs = createobject("adodb.recordset")

 

connstr="DSN=XXX;SRVR=YYY;UID=user;PWD=pass"

 

objConn.Open connstr

 

ordno = "?order_id"

 

SQL ="UPDATE order_table set wsoh_gt_19='YES' where order_id='" & ordno & "'"

 

objConn.execute SQL

 

objConn.close

 

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?

 

1 意見

1
Avatar
Burch, Ricky
評論操作 永久連結

changing the line, 'ordno = "?order_id"' to 'ordno = Format.NamedSubStrings("order_id").Value' resolves this issue. Closed.

 

登入寫評論。