Calling a Stored Procedure via VB Code


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 :-

  1. The IDO Name – “SP!” is telling it is not an IDO it is a direct stored procedure call
  2. The IDO Method name – I am just telling it the SP name instead
  3. 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

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

 

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.