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

Script Configuration

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;