Can't add into DA tables

Data Abstract
21

Hi,

have you declared FrmNbr & ToNbr parameters as output parameters of SP in DB?

22

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;

23

Hi,
I checked it returns the blank value in out parameters value

return
return1211×210 60.8 KB

24

Hi,

if output values cannot be found in this event it won’t transfer them to client-side too

25

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 :
a

If change to out parameters format, it will not return value in SMS.
exec [sp_GetMaxRangeNum] ‘YY’,‘PTAR’,2,null, null

26

Hi,

show your SP after modification when you have added output params

27

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
28

Hi,

can you retest it in Schema Modeler & Delphi project, pls ?

29

Hi,
How to test it in Schema Modeler? I can’t find any “Preview” page.

test
test706×218 20.1 KB

30

Hi,

in Schema Modeler it should detect all params (input & output) correctly
actual testing will be in delphi project

31

Hi,
I tested it before that return empty value. I sent my SP for you. Are you re-produce the case?

return
return1211×210 60.8 KB

32

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?

33

Hi,
I traced in server-side that return the value.
aa

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.

34

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;
35

Sorry that where should I paste your helper? Put in “DataAbstract4_Intf”?

36

Hi,

in any place of your project

37

Hi,
I paste to inherit “Base Form”. But it uses "outParam.ParamByName(‘ToNbr’).Value that return undeclared identifier. What’s wrong in my code?

base
base678×531 44.9 KB

38

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;
39

Hi,
Sorry, it is not raise error issue. It is a compile error issue.

bbb
bbb1007×176 38.1 KB

40

Hi,

try to put helper into shared unit:

Untitled
Untitled959×726 99.8 KB