TMSQuery.InsertSql exec :TestId = TestInsert :Description does not return data

[Olivier Olmer]

Hi,

We have are using Delphi 5 with version 3.8 and all of our projects work correctly. When we moved to the latest version 4.30 we do not get data back after performing an insert call.


To test the error we define in the database a table:
create table Test
(
TestId int identity(1,1),
Description nvarchar(50) not null
constraint PKTestId primary key(TestId)
)

create procedure TestInsert(@Description nvarchar(50))
as
begin
insert into Test(Description)
values (@Description)
return scope_identity()
end


create procedure TestSelectId(@TestId int)
as
begin
select
Test.TestId,
Test.Description,
Test.DBUserName,
Test.Dlc
from
Test
where
TestId=@TestId
end



The structure of TMSQuery is
Sql.Text = exec TestSelectId :TestId
SqlInsert = exec :TestId = TestInsert :Description

Options.QueryIdentity = False
Options.StrictUpdate = False
Options.NumberRange = True
Options.ReturnParams = True

After inserting a record the Field TestId is not set. In older versions the variable was set.

With kind regards,

Olivier Olmer

[Antaeus]

In the TMQuery.BeforeUpdateExecute event handler you should change parameter types to ptResult or to ptOutput (by default all parameters of update queries are ptInput). Here is a small sample:

Code: Select all

procedure TForm1.MSQuery1BeforeUpdateExecute(Sender: TCustomMSDataSet;
  StatementTypes: TStatementTypes; Params: TMSParams);
  var Param: TMSParam;
begin
  if Sender.State = dsInsert then begin
    Param := Params.ParamByName('TestId');
    if Param  nil then
      Param.ParamType := ptResult;
  end;
end;

If you want to reflect the actual values of the currently inserted record, you should use the TMQuery.RefreshRecord method, or add the roAfterInsert option to TMSQuery.RefreshOptions to do it automatically.

[Olivier Olmer]

We have a project build with your components and have this solution implemented in over 700 forms. In 3.8 it works fine. Do you suggest to modify all the forms to get it to work? Is there any good reason for CrLab to change the way it use to work?

With kind regards,

Olivier Olmer

[Antaeus]

We cannot get this work both with SDAC 3.80 and SDAC 4.30. Please send us a complete small sample at sdac*crlab*com to demonstrate the problem, including script to create and fill table.

What was the exact version (including build number) of your SDAC 3.80?

[Olivier Olmer]

You are right. We created a test project and found out that we are wrong. Due to the refresh after insert it worked in lots of screens. Sorry for sending the message.