RefreshRecord with TMSStoredProc

[Dmitrij Koval']

i'm trying to call TMSStoredProc.RefreshRecord but it raises exception "Invalid Parameter"

procedure is defined as follows

Code: Select all

create procedure TestProc
@ID int = null
as
begin
declare @result table (id int, name varchar(20), ...);
// fill @result table
select * from @result where id = isnull(@ID, id);
end;

can anybody tell me how to refresh just one row in this case ?

[Antaeus]

It looks like the SQL generator of SDAC can generate no correct statement to refresh the current record because of the specific way you request data from server.
To solve this problem you should fill the RefreshSQL property of your TMSStoredProc manually. This must be an SQL statement (a SELECT query, a call to a stored procedure, etc) that retrieves values to refresh the current record.

[Dmitrij Koval']

i played a bit with SQLRefresh and found that SQLRefresh does not "likes" :RETURN_VALUE parameter. the problem was solved when i set SQLRefresh to {CALL TestProc;1(:id)} instead of {:RETURN_VALUE = CALL TestProc;1(:id)}

[hmelihkara]

Hi,
I try to call sqlrefresh without returnvalue but still getting the same error. "invalid parameter".
Here is my stored procedure:

CREATE PROCEDURE dbo.select_kurtanimi
@CASINOID INTEGER
AS
BEGIN
SELECT * FROM kurtanimi WHERE casinoid=@CASINOID
ORDER BY siralama ASC
END

and here is the code that i used to refresh the record:

procedure MSRefresh(const MS:TCustomMSDataSet);
var KayitYeri:TBookMarkStr;
begin
if MS.Active then try
MS.DisableControls;
KayitYeri:=MS.Bookmark;
MS.RefreshQuick(false);
MS.Bookmark:=KayitYeri;
finally
MS.EnableControls;
end;
end;

The code runs without problem with the query components but i need to run this with stored procedures. Thanks for helps...

SDAC Ver: ...13 (latest)

[Antaeus]

The query containing the RETURN_VALUE parameter is generated by the RefreshQuick method. Try to add the BeforeUpdateExecute event handler like this to your TMSStoredProc component:

Code: Select all

procedure TForm1.MSStoredProc1BeforeUpdateExecute(Sender: TCustomMSDataSet;
  StatementTypes: TStatementTypes; Params: TMSParams);
var
  Param: TMSParam;
begin
  Param := Params.FindParam('RETURN_VALUE');
  if Param  nil then
    Param.ParamType := ptInputOutput;
end;

[hmelihkara]

Code works perfect, thanks...