VBScript - SQL Stored Procedure

Label printing integration (NiceWatch middleware and ActiveX programming interface)

Moderators: Georges, milos, NiceLabel Support Team

Post Reply
groupit
Newcomer
Posts: 1
Joined: Thu May 09, 2019 3:43 pm

VBScript - SQL Stored Procedure

Post by groupit » Thu May 09, 2019 4:21 pm

Hi, I am currently trying to Execute a script within NiceLabel Automation Builder.

As we only have Easy and not Pro, I am trying to write back to the SQL database using a script before the label is printed.



The script I am using is as follows;

'Function to querry database and return xml string
Dim Part ' as StockCode Filter
Dim adoCmd ' As ADODB.Command
Dim adoConn ' As ADODB.adoConnection
Dim outStrm ' As ADODB.Stream
Dim txtResults ' String for results
Dim ConString ' String for adoConnection
Dim sQuery ' Query String
Dim fso ' File System Object
Dim ts ' Text Stream
Dim dbName ' The DNS name or IP address of your DB Server
Dim Catalog ' The specific database to adoConnect to
Dim TableName ' The table we will retrieve data from
Dim UID ' The User Name to login to your database (Not used)
Dim PWD ' The Password to login to your database (Not used)


'Connect to the database
'*******************************************************

Catalog = "DataWarehouse"
'Set our server name
dbName = "GRP-SYSPRO"
ConString = "Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;" _
& "Initial Catalog=" & Catalog & ";Data Source=" & dbName & ";"
'msgbox (sadoConn)
Set adoConn = CreateObject("ADODB.Connection")
adoConn.ConnectionString = ConString
' adoConn.CursorLocation = 3
adoConn.Open


'Execute a stored procedure
'********************************************
'Create SQL Command Object

Set adoCmd = CreateObject("ADODB.Command" )
adoCmd.commandtype = 4 'To run store procedure
adoCmd.commandtext = "CHC_Labels.usp_UpdatePrinterStatus" 'stored procedure name

'Create Parameter object
dim par1, adpar1Value
Const adPar1Name="@ID"
Const adPar1Type=3 'For integer see microsoft docs DataTypeEnum
Const adPar1Direction = 1 'For input see microsoft for ParameterDirectionEnum
'Const adPar1Size = 200 ' specifies the maximum length for the parameter value in characters or bytes.
adpar1Value= [POC.ID]'Convert string to Integer'[POC.ID],

' Format Set parameter = command.CreateParameter (Name, Type, Direction, Size, Value)
set par1 =adoCmd.CreateParameter (adPar1Name,adPar1Type, adPar1Direction,4,adpar1Value)

'Add the parameter to the Command object
adoCmd.Parameters.Append par1


adoCmd.ActiveConnection = adoConn
adoCmd.Execute



'Cleanup Database objects
'*******************************************************

adoConn.Close

set adoConn = Nothing

set adoCmd = Nothing

set outStrm = Nothing


When the POC.ID variable is used I get an “Application uses a value of the wrong type for the current operation error message.”

But when I hardcode a number in instead of using the variable it works.

I have tried CInt to make sure the variable is a integer but I get a type mismatch error

User avatar
Saso
NiceLabel
NiceLabel
Posts: 2963
Joined: Mon Sep 04, 2006 8:09 am
Contact:

Re: VBScript - SQL Stored Procedure

Post by Saso » Thu May 16, 2019 2:04 pm

Well, if CInt cannot convert value into integer, your value is not numerical or is too large.
Can you see what value you have in [POC.ID]?

If it has no value, CInt() will also fail with 'Type mismatch'.
Saso Fleiser
Senior Technical Product Specialist

Post Reply