Skip to main content
InSource KnowledgeCenter

Application Server script that executes a SQL non-query stored procedure

Overview

A script that executes a SQL Stored Procedure that has both input parameters and an output parameter that returns an error code

Resolution

UDAs for this Database Object

Debug

Boolean

RecordsAffected

Integer

SQLErrorMsg

String

Trigger

Boolean

Script Configuration

Script Name:

InsertDB

Execute Type:

Execute

Expression:

Me.Trigger

Trigger Type:

OnTrue

Run async

(checked)

Declarations
dim dt AS System.Datetime;
dim SQLConnection as System.Data.SqlClient.SqlConnection;
dim SQLCommand as System.Data.SqlClient.SqlCommand;
Script
me.Trigger = False;
me.SQLErrorMsg = "";
me.RecordsAffected = 0;

Dim iRC As Integer;
Dim sDateTime As String;
Dim sQuery As String;

dt = Now();
sDateTime = (dt.ToString("MM/dd/yyyy HH:mm:ss"));


' --- Could/Should replace with UDAs here --------------
Dim iBatchID As Integer;
Dim rActual As Float;
Dim rTarget As Float;
Dim sItem As String;

iBatchID = 23;
sItem = "Weight";
rActual = 51.1;
rTarget = 50.0;

' -----------------------------------------------

SQLConnection = new System.Data.SqlClient.SqlConnection(MyContainer.ConnectStr);
SQLConnection.Open();

sQuery = "BatchRecordInsert" ;
'' Enter the stored procedure name here

SQLCommand = new System.Data.SqlClient.SqlCommand(sQuery, SQLConnection);
SQLCommand.CommandType = System.Data.CommandType.StoredProcedure;

SQLCommand.Parameters.Add("@BatchDateTime", System.Data.SqlDbType.datetime);
SQLCommand.Parameters("@BatchDateTime").Value = sDateTime ;

SQLCommand.Parameters.Add("@BatchID", System.Data.SqlDbType.int);
SQLCommand.Parameters("@BatchID").Value = iBatchID ;

SQLCommand.Parameters.Add("@Item", System.Data.SqlDbType.varchar,18) ;
SQLCommand.Parameters("@Item").Value = sItem ;

SQLCommand.Parameters.Add("@Actual", System.Data.SqlDbType.float) ;
SQLCommand.Parameters("@Actual").Value = rActual ;

SQLCommand.Parameters.Add("@Target", System.Data.SqlDbType.float);
SQLCommand.Parameters("@Target").Value = rTarget;

SQLCommand.Parameters.Add("@Err", System.Data.SqlDbType.Int) ;
SQLCommand.Parameters("@Err").Direction = System.Data.ParameterDirection.Output ;


if me.Debug then
   LogMessage( "Insert About to Execute: " + SQLCommand );
endif;

me.RecordsAffected = SQLCommand.ExecuteNonQuery();

iRC = (SQLCommand.Parameters("@Err").Value) ;

SQLConnection.Dispose();


if me.Debug then
   LogMessage( "Insert Submitted ...Records Affected: " + Text(me.RecordsAffected,"#"));
   LogMessage( " *** SQL Err Code: " + StringFromIntg( iRC, 10 ) + " -:- SQL Error Message: " + me.SQLErrorMsg );
endif;

 

J.B.T.