Devart Forums

Posted: **Mon 08 Oct 2007 12:03**

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

Posted: **Tue 09 Oct 2007 09:49**

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.

Posted: **Wed 10 Oct 2007 11:37**

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

Posted: **Thu 11 Oct 2007 08:55**

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?

Posted: **Mon 15 Oct 2007 11:09**

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.

Devart Forums

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

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

Change of behaviour

You are right