Introduction
In this post, I have 2 examples surrounding Calling a stored procedure via VB code, example 1 is quite basic, with no return values, example 2 is a little more sophisticated
Example 1
This example continues from my earlier post Updating IDO’s via VB Code. You may want to read that post first to put into context what I am doing.
My colleague Gary Seviour has written a stored procedure which swaps out specific components in a bom. Obviously there is a few caveats to running this(the job should not of been actioned at all to start with), however the purpose of this blog entry is simply to show you how to call a stored procedure, which has not been set up as an IDO method :-
The Code
Me.IDOClient.Invoke(“SP!”, “pbti_ih_sp_JobChangeIntermediate”, r(“job”).Value, r(“SecondaryIntermediate”).Value, sMessage, r(“PrimaryIntermediate”).Value)
Explanation
Syteline’s IDOClient.Invoke is expecting :-
- The IDO Name – “SP!” is telling it is not an IDO it is a direct stored procedure call
- The IDO Method name – I am just telling it the SP name instead
- The parameters required. Where it says “r”, that is previously defined within the routine as this :- Dim r AsIWSIDORow = ThisForm.PrimaryIDOCollection.Item(n)
Example 2
This example originates from our In house shop floor data collection system. I have not yet had much of a chance to document the system, which is a shame, as it is probably one of our most successful project areas. We have issued a series of Windows 8 Acer Iconica W3 tablets to staff and use a full Syteline client on them, manipulating screens to fit.
In this example I want to stop job completion transactions being posted in our version of the job transactions form if there is insufficient materials issued to the job. The materials included within the consideration are not back flushed and we have an internal UF field on the item record (which in turn is a derived field in the jobmatl IDO which also indicates it is not important to kit. The stored procedure also send an email to the team leader as they need to ensure the transaction is not overlooked
Firstly the VB code looks like this :-
Function PBTI_CheckEnoughKitted() As Integer
‘Application.ShowMessage(“sTARTED”)
Dim job As String = ThisForm.Variables(“PBTI_VarJob”).Value
Dim Qty As String = ThisForm.Variables(“PBTI_VarQtyComp”).Value
If Len(job) < 1 Then
Application.ShowMessage(“You have not entered a job – unable to continue”, MsgBoxStyle.Critical, “Job Number Missing”)
Return -1
End If
If CInt(Qty) = 0 Then
Application.ShowMessage(“You have not entered a Quantity – unable to continue”, MsgBoxStyle.Critical, “Qty Missing”)
Return -1
End If
Dim PBTIGetkittingdetails As InvokeResponseData
Dim itmcnt As String = “”
Dim itemdetails As String = “”
‘Me.IDOClient.Invoke(
PBTIGetkittingdetails = Me.IDOClient.Invoke(“SP!”, “PBTI_IH_SP_JobKittedQuery”, job, CInt(Qty), itmcnt, itemdetails)
itmcnt = PBTIGetkittingdetails.Parameters(2).Value()
itemdetails = PBTIGetkittingdetails.Parameters(3).Value()
If CInt(itmcnt) > 0 Then
Application.ShowMessage(“Unable to complete the packing job as “ & itmcnt + ” items have not been kittied.” _
& vbCrLf _
& vbCrLf _
& “These items are :- “ _
& vbCrLf _
& vbCrLf _
& itemdetails _
, MsgBoxStyle.Critical _
, “CANCELLED !”)
Return -1
Else
Return 0
End If
Return 0
End Function
The SP it is calling currently emails me every if this condition is met, this is so I can follow up with the user as close to real time as possible and ensure they are trained on how to resolve the situation
USE <DATABASE NAME>
GO
/****** Object: StoredProcedure [dbo].[PBTI_IH_SP_JobKittedQuery] Script Date: 01/28/2015 16:49:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[PBTI_IH_SP_JobKittedQuery] (@job as dbo.jobtype = ‘xte0000672’
, @qtytocomplete as int = 1
,@itemcount as int output
,@itemlist as nvarchar(200) output ) as
declare @subject as nvarchar(200) = ‘PBTI_IH_SP_JobKittedQuery has been called’
declare @body as nvarchar(200) = getdate()
declare @profile_name as nvarchar(200) = ‘PBTI’
declare @recipients as nvarchar(200) = <EMAIL ADDREESS>
if OBJECT_ID(‘tempdb..#pbti_unkitteditemsbyjob’) is not null
drop table #pbti_unkitteditemsbyjob
create table #pbti_unkitteditemsbyjob (
item nvarchar(30)
)
insert into #pbti_unkitteditemsbyjob
select jmi.item as material
from jobmatl as jm
left join item as jmi on jm.item = jmi.item
left join job as j on j.job = jm.job
where jm.backflush = 0
and jm.job = @job
and jmi.uf_consumablematerial = 0
and jm.qty_issued < ((jm.matl_qty_conv * @qtytocomplete) + (jm.matl_qty_conv * j.qty_complete))
select @itemcount = (select count(*) from #pbti_unkitteditemsbyjob)
select @itemlist = stuff((select ‘, ‘ + item
from #pbti_unkitteditemsbyjob
for xml path (”) ),1,1,”)
if @itemcount > 1
begin
select @subject = ‘COMPLETE PACKING JOB : Processing canceled for job ‘ + @job + ‘.’
select @body = ‘<p>COMPLETE PACKING JOB : Processing canceled for job ‘ + @job + ‘ as the following items have not been kitted to it.</p>’ + replace(@itemlist,‘,’,‘</br><br>’)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile_name
,@recipients = @recipients
,@subject = @subject
,@body = @body
,@body_format = ‘html’
end
The form in question appears as follows :-
PBTI’s Custom Unposted Job Transaction form
Example 3
We have a “Bom Importer” form that takes Bill of materials supplied to us by one of our customers in an Excel file and generated items and BoMs accordingly. Using the “Me.IDOClient.Invoke” method to call the SP generated a very bizzare issue stating the transaction count was previously 2 and is now 0 so it could not commit the change. When calling the SP via SSMS, I did not have any issues, and with limited time to try to understand why the Syteline / Mongoose framework would treat execution of a stored procedure differently to a direct call I opted to use a standard VB.NET library to call the SP instead.
Firstly I needed to include the library within the script – My colleague has probably previously added this to the client deployment.
Imports System.Data
Imports System.Data.SqlClient
Then to call the SP the code is as follows. Theres a lot of other bits in here specific to our code, but you’ll get the gist of it! :-
Dim strSQLConnString As String = “”
Dim strSQLCmdString As String = “”
Dim strSQLDatabase As String = “”
Dim sqlConn As SqlConnection = Nothing
Dim sqlDa As SqlDataAdapter = Nothing
If (Application.ConfigurationName.ToLower() <> “test-ireland” And Application.ConfigurationName.ToLower() <> “unitedkingdom” And _
Application.ConfigurationName.ToLower() <> “xiniat”) Then
strSQLDatabase = <db1> & “_app”
ElseIf (Application.ConfigurationName.ToLower() = “test-ireland”) Then
strSQLDatabase = “<db3>_app”
ElseIf (Application.ConfigurationName.ToLower() = “unitedkingdom”) Then
strSQLDatabase = “<db1>_app”
ElseIf (Application.ConfigurationName.ToLower() = “xiniat”) Then
strSQLDatabase = “<db2>_app”
End If
strSQLConnString = “Data Source=10.0.1.17;Initial Catalog=” & strSQLDatabase & “;Integrated Security=SSPI”
sqlConn = New SqlConnection(strSQLConnString)
Try
sqlConn.Open()
Catch ex As Exception
SendExToDebug_MSG(ex)
sqlConn.Close()
End Try
If (sqlConn.State = ConnectionState.Open) Then
Application.ShowMessage(“Sql Open”)
‘Update the instructions
If ComponentInstructions <> “” And P_M_T_code.ToLower() = “m” Then
Dim success As String = “0”
Dim reason As String = “”
Dim cmd As New SqlCommand
ItemType = “11”
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = “pbti_ih_sp_SubstitiuteComponent”
cmd.Parameters.AddWithValue(“@newitemcode”, NewItemCode)
cmd.Parameters.AddWithValue(“@ToItemVar”, ComponentInstructions)
cmd.Parameters.AddWithValue(“@itemtype”, ItemType)
cmd.Parameters.AddWithValue(“@ToQty”, “1”)
cmd.Parameters.AddWithValue(“@success”, “0”)
cmd.Parameters.Add(“@reason”, SqlDbType.VarChar, 50).Direction = ParameterDirection.Output
cmd.Parameters(“@reason”).Value = “”
cmd.Parameters(“@success”).Direction = ParameterDirection.Output
cmd.Connection = sqlConn
cmd.ExecuteNonQuery()
success = cmd.Parameters(“@success”).Value.ToString()
reason = cmd.Parameters(“@reason”).Value.ToString()
cmd.Dispose()
Application.ShowMessage(“success: “ & success)
If success = “1” Then
LogMessage(“Bom Amendment of Item “ & NewItemCode & “, component type “ & ItemType & ” SUCCESSFULL. Updated to “ & ComponentInstructions, “I”)
Else
LogMessage(“Bom Amendment of Item “ & NewItemCode & “, component type “ & ItemType & ” FAILED Updated to “ & ComponentInstructions & ” Reason : “ & reason, “E”)
End If
ElseIf ComponentInstructions = “” And P_M_T_code.ToLower() = “m” Then
LogMessage(“Bom Amendment of Item “ & NewItemCode & “, component type “ & ItemType & ” FAILED as Instructions Item code not set – Item is manufactureed”, “I”)
End If