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.