NiceLabel Automation Builder中VBScript参数错误及异常捕获咨询

Fixing the "Invalid Value Type" Error #

The core issue here stems from incorrect label variable access in VBScript and a potential data type mismatch with your stored procedure.

1. Correctly Retrieve the Label Variable
   In NiceLabel Automation Builder, you can’t directly use [POC.ID] in VBScript to access label variables. Instead, use the official NiceLabel.Variables object model to fetch the value:

   adpar1Value = NiceLabel.Variables("POC.ID").Value
   

   This ensures you’re pulling the actual value of POC.ID from the active label session.

2. Align Parameter Data Type with Your Stored Procedure
   Your code uses adPar1Type=200 (which maps to adVarChar, a string type) with a size of 4. If your stored procedure’s @ID parameter expects an integer instead, this will cause a type mismatch:

   • For integer @ID: Change adPar1Type to 3 (which is adInteger). The size of 4 already matches standard integer byte length:

     Const adPar1Type=3 ' Use for numeric ID values
     

   • For string @ID: Confirm the size (4) matches the maximum length of the @ID parameter in your stored procedure. If POC.ID values are longer than 4 characters, adjust the size accordingly.

Exception Handling in NiceLabel Automation Builder #

You can handle errors at both the VBScript level and the automation flow level:

1. VBScript-Level Error Handling
   Use VBScript’s built-in error checking to catch issues during execution and clean up resources:

   On Error Resume Next ' Enable error handling
   
   ' After opening the connection:
   adoConn.Open
   If Err.Number <> 0 Then
       MsgBox "Database connection failed: " & Err.Description
       Err.Clear
       Exit Sub
   End If
   
   ' After executing the stored procedure:
   adoCmd.Execute
   If Err.Number <> 0 Then
       MsgBox "Stored procedure failed: " & Err.Description
       adoConn.Close
       Set adoConn = Nothing
       Err.Clear
       Exit Sub
   End If
   

2. Automation Flow-Level Error Handling
   In the NiceLabel Automation Builder interface:

   • Add a Catch Error action immediately after your VBScript step to trigger a response if the script throws an unhandled error.
   • Use the Error Handling trigger to define global error workflows (e.g., logging errors to a file, sending email alerts).
   • Access error details using built-in variables like {Error.Description} and {Error.Code} in subsequent actions.

Are Label Variables Retained After Printing? #

Yes, label variables like POC.ID are preserved in the automation session until the entire flow completes. As long as you access them via the NiceLabel.Variables object (as shown earlier), you’ll get the same value used during printing. Variables are only cleared if explicitly reset in your flow or script.

Corrected Script Snippet #

Here’s your script with the variable access fix and basic error handling included:

'Function to query database and update printer status
Dim adoCmd, adoConn
Dim ConString, dbName, Catalog
Dim par1, adpar1Value

On Error Resume Next ' Enable error handling

'Connect to the database
Catalog = "DataWarehouse_XXXX"
dbName = "OUR-SERVER"
ConString = "Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;" _
& "Initial Catalog=" & Catalog & ";Data Source=" & dbName & ";"

Set adoConn = CreateObject("ADODB.Connection")
adoConn.ConnectionString = ConString
adoConn.Open

If Err.Number <> 0 Then
    MsgBox "Database connection failed: " & Err.Description
    Err.Clear
    Exit Sub
End If

'Execute stored procedure
Set adoCmd = CreateObject("ADODB.Command")
adoCmd.commandtype = 4 ' Stored procedure type
adoCmd.commandtext = "CHC_Labels.usp_UpdatePrinterStatus"

'Parameter setup
Const adPar1Name="@ID"
Const adPar1Type=3 ' Use 3 for integer, 200 for string
Const adPar1Direction = 1 ' Input parameter

' Get label variable value correctly
adpar1Value = NiceLabel.Variables("POC.ID").Value

Set par1 = adoCmd.CreateParameter(adPar1Name, adPar1Type, adPar1Direction, 4, adpar1Value)
adoCmd.Parameters.Append par1
adoCmd.ActiveConnection = adoConn
adoCmd.Execute

If Err.Number <> 0 Then
    MsgBox "Stored procedure execution failed: " & Err.Description
    adoConn.Close
    Set adoConn = Nothing
    Set adoCmd = Nothing
    Err.Clear
    Exit Sub
End If

'Cleanup
adoConn.Close
Set adoConn = Nothing
Set adoCmd = Nothing

内容的提问来源于stack exchange，提问作者Ian W