Hi,
have you declared FrmNbr
& ToNbr
parameters as output parameters of SP in DB?
Hi,
have you declared FrmNbr
& ToNbr
parameters as output parameters of SP in DB?
Hi,
I haven’t declare any output parameters because existing project need to do this and works fine. I revised SP. But how to capture the out parameters value.
edt_frmnbr.Text := outParam.Items[0].Value;
edt_tonbr.Text := outParam.Items[1].Value;
Hi,
if output values cannot be found in this event it won’t transfer them to client-side too
Hi,
I tried to run in SMS that need to pass more parameters and it will not show the information.
Existing project :
exec [sp_GetMaxRangeNum] ‘YY’,‘PTAR’,2
Result :
If change to out parameters format, it will not return value in SMS.
exec [sp_GetMaxRangeNum] ‘YY’,‘PTAR’,2,null, null
Hi,
show your SP after modification when you have added output params
Hi,
Here is revised SP as output parameters.
ALTER PROCEDURE [dbo].[sp_GetMaxRangeNum] (@Company varchar(10), @TxnType varchar(10), @IncNbr int, @FrmNbr varchar(20) output, @ToNbr varchar(20) output)
AS
BEGIN
SET NOCOUNT ON;
Declare @MaxNbr int
Declare @ReturnNbr varchar(20)
Declare @ReturnPrefix varchar(10)
if @IncNbr > 0
begin
set @MaxNbr = (select UQ_TXNNbr + 1 from TxnNumber where UQ_Comp = @Company and UQ_TXNType = @TxnType)
set @ReturnPrefix = (select UQ_TXNPrefix from TxnNumber where UQ_Comp = @Company and UQ_TXNType = @TxnType)
update TxnNumber set UQ_TXNNbr = UQ_TXNNbr + @IncNbr where UQ_Comp = @Company and UQ_TXNType = @TxnType
select @FrmNbr = @ReturnPrefix + right(replicate('0',4) + cast(@MaxNbr as varchar(4)),4),
@ToNbr = @ReturnPrefix + right(replicate('0',4) + cast((@MaxNbr + (@IncNbr - 1)) as varchar(4)),4)
end
END
Hi,
can you retest it in Schema Modeler & Delphi project, pls ?
Hi,
in Schema Modeler it should detect all params (input & output) correctly
actual testing will be in delphi project
Hi,
update your SP as
ALTER PROCEDURE [dbo].[sp_GetMaxRangeNum] (@Company varchar(10), @TxnType varchar(10), @IncNbr int, @FrmNbr varchar(20) output, @ToNbr varchar(20) output)
AS
BEGIN
SET NOCOUNT ON;
SET @FrmNbr = 'XXXXXX';
SET @ToNbr = 'YYYYYYY';
END
will these values be shown on client-side?
Hi,
I traced in server-side that return the value.
Then I retest in client-side that is OK now. It’s so strange. Other question is how to using the return parameters name instead of using Item[1].value.
Hi,
create a helper like
type
DataParameterArray_helper = class helper for DataParameterArray
function ParamByName(aName: String): DataParameter;
end;
function DataParameterArray_helper.ParamByName(aName: String): DataParameter;
var
i: Integer;
begin
Result := nil;
for i := 0 to Self.Count -1 do
if AnsiSameText(aName, Self.Items[i].Name) then begin
Result := Self.Items[i];
Break;
end;
end;
Sorry that where should I paste your helper? Put in “DataAbstract4_Intf”?
Hi,
in any place of your project
Hi,
I paste to inherit “Base Form”. But it uses "outParam.ParamByName(‘ToNbr’).Value that return undeclared identifier. What’s wrong in my code?
Hi,
you can modify it and raise error if parameter isn’t found like
function DataParameterArray_helper.ParamByName(aName: String): DataParameter;
var
i: Integer;
begin
Result := nil;
for i := 0 to Self.Count -1 do
if AnsiSameText(aName, Self.Items[i].Name) then begin
Result := Self.Items[i];
Break;
end;
if Result = nil then raise Exception.Create('Parameter '+aName+' is not found!');
end;