Strange problem with SDAC 3.70.1.27

[Sergio Bertolotti]

Hi all,
I have this strange problem with last version of SDAC for VCL.
I use Delphi 7.0 prof. with the last service pack and MS SQL 2005 Express Edition with sp 1 in italian.

I create this simple table in my local DB :

CREATE TABLE [dbo].[sergio] (
[CONTA] int IDENTITY(1, 1) NOT NULL,
[DEMO] varchar(30) COLLATE Latin1_General_CI_AS,
CONSTRAINT [sergio_pk] PRIMARY KEY CLUSTERED ([CONTA]),
UNIQUE ([CONTA])
)
ON [PRIMARY]

After it I create a simple project in Delphi with :

1) TMSConnection.
2) TMSQuery
3) TDataSource
4) TDBGrid

My SQL's properties of TMSQuery are :

SQL : select * from sergio order by conta.

SQLInsert :
INSERT INTO sergio
(DEMO)
VALUES
(:DEMO)
SET :CONTA = SCOPE_IDENTITY()

SQLUpdate :
UPDATE sergio
SET
DEMO = :DEMO
WHERE
CONTA = :Old_CONTA

SQLRefresh :
SELECT sergio.DEMO FROM sergio
WHERE sergio.CONTA = :CONTA

SQLDelete :
DELETE FROM sergio
WHERE
CONTA = :Old_CONTA

all others properties of TMSQuery are at default value.

I insert on my form 3 buttons and 1 TEdit control.
When I press button - APPEND - I execute an append on TMSQuery with DEMO equal to Edit1.Text.
After that I press button POST - and this execute a POST on my TMSQuery.
At this point I see in my DBgrid a new record with a value of field DEMO that I inserted, but field CONTA is blank.
If i press a button that execute this statement :

ShowMessage('Value of CONTA: ' + IntToStr(MSQuery1.FieldByName('CONTA').AsInteger));

the value that it show is 0.

At this point if I execute a REFRESH statement on my TMSQuery, I see a new record inserted and CONTA contains the correct value.

If I use standard ADO components that is included in Delphi 7, and I execute step by step this statement the value of CONTA is inserted correctly without refresh TADODataSet component.

How can I obtain the same results with the last version SDAC ?

Best regards.
Sergio Bertolotti

[Jackson]

Use TCustomDADataSet.BeforeUpdateExecute event to set parameter type to ptInputOutput for your insert statement.
For example:

Code: Select all

procedure TForm1.MSQuery1BeforeUpdateExecute(Sender: TCustomMSDataSet;
  StatementTypes: TStatementTypes; Params: TMSParams);
begin
  if stInsert in StatementTypes then
    Params.ParamByName('CONTA').ParamType := ptInputOutput;
end;

Don't forget to set property TCustomDADataSet.Options.ReturnParams to True.

[Sergio Bertolotti]

Hi EvgeniyM,

your suggestion resolve my little problem.

Many thank's for your interesting.

Best regards.
Sergio Bertolotti