Vb Script Passing Parameter Into Stored Procedure

Legacy Poster

• March 19, 2014 16:18

I need to get a hand with my VB Script. I am getting the below error when I try passing a parameter to a stored procedure.

 

Function and Subs(Line 30):: Conversion failed when converting the varchar value 'Error' to data type int.

 

I ran the code in VB Edit with the parameter 'JobNumber' being hard coded, and the script executed with no problem. But when I put the script into BarTender I get an error.

 

Ultimately I need to get a value from a text box on the Data Entry Form into the parameter 'JobNumber'.

 

VB Script

============

Option Explicit
Sub GetjobNumber()

Dim JobNumber
Dim cmd
Dim sp
Dim par1
Const adParamInput = 1
Const adVarChar = 200

sp = "Z_Add_LotNumber"
JobNumber = Format.NamedSubStrings("txt_JobNumber").Value   '"IU9FU-0000"

Set cmd = CreateObject("ADODB.Command")
set par1 = CreateObject("ADODB.Parameter")

par1.Direction=adParamInput
par1.name="@fjobno"
par1.Size=200
par1.Type=adVarChar
par1.Value= JobNumber


With cmd
   .ActiveConnection = "Provider=SQLOLEDB.1;Data Source=server;Initial Catalog=database;user id ='';password=''"
	.CommandType = 4
	.CommandText = sp
	.Parameters.Append (par1) 
	.Execute
End With

Set cmd = Nothing
set par1 = Nothing

msgbox(sp & " " & JobNumber & " has been executed successfully!")

End Sub

 

 

Stored Procedure

=============

ALTER PROCEDURE [dbo].[Z_Add_LotNumber]
	  @fjobno varchar(50)
	--,@V_Date varchar(50) -- for testing
AS
DECLARE @V_Date smalldatetime;
DECLARE @V_fpartno char(25);

SET @V_Date = GETDATE();
SET @V_fpartno = (SELECT fpartno FROM joitem WHERE fjobno =  @fjobno);
	BEGIN
.
.
.
.
	END

 

 

0

• 

  Michael Toupin (mtoupin

  ★ BarTender Hero ★

  • March 19, 2014 21:41

  When is this vb script being called?  If it's not in an OnPostPrompt event than it's probably not reading the value from the prompt.

  0
• 

  Legacy Poster

  ★ BarTender Hero ★

  • March 19, 2014 22:10

  I have the VB Script in the following location, "Bartender Document Options" >>> "VB Script" tab >> Functions and Subs.

   

  For testing I have the script being called in the "OnPrintJobCancel".

   

  Where can I find the "OnPostPrompt"?  I looking around for it but I am not finding it.

  0
• 

  Legacy Poster

  ★ BarTender Hero ★

  • March 27, 2014 21:50

  For BarTender version 9.4 SR3, it can be found via he Label Design > BarCode Object > Data Source tab> Source: Visual Basic Scrip > Event Control Scripts > Event List option.

   

  Not sure of the new version 10.

  0
• 

  Legacy Poster

  ★ BarTender Hero ★

  • March 27, 2014 21:58

  Chuen,

   

  Thank you. From what I understand, BarTender can not do what I need it to do, and that is pass a parameter in to the stored procedure. I need to come up with a different solution.

  0

Please sign in to leave a comment.