SQLDataReader call not returning any records (works if using SQLCommand.ExecuteNonQuery)

mtlmonk

Hello,

I've got a SQL Stored Procedure that is called using one input parameter (sales order no.) and two output parameters (one text and one decimal field).

If I call the SQLCommand.ExecuteNonQuery and retrive the 2 output parameters, it works. However when I translate this to a SQLCommand.ExecuteReader call, the SQLDataReader returned has no records, no fields. It will not return any records, so not entering the loop.

Here's the code:

SQLCommand := SQLCommand.SqlCommand();
SQLCommand.CommandType := SQLCommand.CommandType.StoredProcedure;

// Create SQLConnection Object
SQLConnection := SQLConnection.SqlConnection('Server='+'serverxyz'+';'
+ 'Database='+'dbxyz'+';'
+ 'Integrated Security=True');

// Open SQL Connection
SQLConnection.Open;

SQLCommand.CommandText('CallSPFromNAV'); // Pass Procedure name in CommandText Parameter
SQLCommand.Connection := SQLConnection;

// Supply First Parameter: @CustomerNo
// Initailize SQL Parameter Object
SqlParamCycleCode := SqlParamCycleCode.SqlParameter();
// Parameter for Procedure
SqlParamCycleCode.ParameterName := '@CustomerNo';
// Datatype for parameter
SqlParamCycleCode.DbType := SQLDbType.String;
// Set direction to Input for input parameters
SqlParamCycleCode.Direction := SqlParamCycleCode.Direction.Input;
// Value that supplied to parameter
SqlParamCycleCode.Value := 'customerxyz';
// Provide size to passed parameter
SqlParamCycleCode.Size := 250;
// Add SQL Parameter Object to SQLCommand object
SQLCommand.Parameters.Add(SqlParamCycleCode);

// Supply 2nd Parameter: @ResourceNo
SqlParamCycleCode := SqlParamCycleCode.SqlParameter();
SqlParamCycleCode.ParameterName := '@ResourceNo';
SqlParamCycleCode.DbType := SQLDbType.String;
// Set direction to Output for output parameters
SqlParamCycleCode.Direction := SqlParamCycleCode.Direction.Output;
// No need to provide value in case on output parameter [But when direction is both type InputOutput then pass value]
SqlParamCycleCode.Value := '';
// When you want use the result from the SP then make sure to set the Size otherwise you will only get few characters in response
SqlParamCycleCode.Size := 250;
SQLCommand.Parameters.Add(SqlParamCycleCode);

// Supply 3rd Parameter: @Amount
SqlParamCycleCode := SqlParamCycleCode.SqlParameter();
SqlParamCycleCode.ParameterName := '@Amount';
SqlParamCycleCode.DbType := SQLDbType.Decimal;
// Set direction to Output for output parameters
SqlParamCycleCode.Direction := SqlParamCycleCode.Direction.Output;
// No need to provide value in case on output parameter [But when direction is both type InputOutput then pass value]
SqlParamCycleCode.Value := '';
// When you want use the result from the SP then make sure to set the Size otherwise you will only get few characters in response
SqlParamCycleCode.Size := 250;
SQLCommand.Parameters.Add(SqlParamCycleCode);


// To execute the procedure
{**********************************THIS DOES NOT WORK****************************************
SQLDataReader := SQLCommand.ExecuteReader;
WHILE(SQLDataReader.Read) DO
BEGIN
message('enter read');
//Get Resource No.
ResourceCode := FORMAT(SQLDataReader.GetString(0));
//Get Amount
Amount := SQLDataReader.GetDecimal(1);
END;
*****************************THIS DOES NOT WORK}


{ ***************************THIS WORKS (I made sure to only do a Select top 1*************************************************
SQLCommand.ExecuteNonQuery; //This only retrieves one record because I put Select top 1 in the SP
resourcecode := format(SQLCommand.Parameters.Item('@ResourceNo').Value);
evaluate(amount, format(SQLCommand.Parameters.Item('@Amount').Value));
***************************THIS WORKS*************************************************}
MESSAGE('Returned Resource: %1, %2',ResourceCode, FORMAT(Amount));

// Close SQL Connection
SQLConnection.Close;
// Clear SQLCommand Object
CLEAR(SQLCommand);
// Clear SQLConnection Object
CLEAR(SQLConnection);

********************************************
Here's the Stored Procedure:
*******************************************

ALTER PROCEDURE [dbo].[CallSPFromNAV]
-- Add the parameters for the stored procedure here
@CustomerNo varchar(20),
@ResourceNo varchar(20) output,
@Amount decimal output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
--SELECT top 1 @ResourceNo = [Resource_no], @Amount = [companyxyz$Sales Header].[Prepayment _] <---- this line is for ExecuteNonQuery call
SELECT @ResourceNo = [Resource_no], @Amount = [companyxyz$Sales Header].[Prepayment _]
from [companyxyz$Sales Header]
where [Sell-to Customer No_] = @CustomerNo

return
END