Unable delete record inserted -

[claudio.cancelli]

I have inserted a record, but if I immediately try to delete it, I have the message: "Update fail - 0 records found".
If I refresh the dataset, I can delete the record without problem.
What's the problem?

Thanks

Claudio Cancelli

[Antaeus]

It looks like the primary key value is not returned after inserting a record. There are two alternative solutions:

1) set the TMyQuery.RefreshOptions.roAfterInsert to True;

2)

1. provide a query to TMSQuery.InsertSQL so that it returns primary key values. For example:

   Code: Select all

   INSERT INTO Emp
     (ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
   VALUES
     (:ENAME, :JOB, :MGR, :HIREDATE, :SAL, :COMM, :DEPTNO)
   SET :EMPNO = SCOPE_IDENTITY()
   

2. set the TMSQuery.ReturnParams option to True;
3. add a handler like this one to the BeforeUpdateExecute event:

   Code: Select all

   procedure TForm.MSQueryBeforeUpdateExecute(
     Sender: TCustomMSDataSet; StatementTypes: TStatementTypes;
     Params: TMSParams);
   begin
     Params.ParamByName('EMPNO').ParamType := ptInputOutput;
   end;

[claudio.cancelli]

Now I have tried the two solutions with problem.
The second way:

SQL:
SELECT anno, numero, idriga, data, an_conto, datainizio, datafine, utente, creazione, nota, terminato
FROM hhDichiarazioni
where anno=:anno
order by anno, numero

INSERT INTO hhDichiarazioni
(anno, numero, data, an_conto, datainizio, datafine, utente, creazione, nota, Terminato)
VALUES
(:anno, :numero, :data, :an_conto, :datainizio, :datafine, :utente, :creazione, :nota, :Terminato)
SET :idriga = SCOPE_IDENTITY()

procedure TFMain.DichiarazioniBeforeUpdateExecute(Sender: TCustomMSDataSet;
StatementTypes: TStatementTypes; Params: TMSParams);
begin
Params.ParamByName('IDRIGA').ParamType := ptInputOutput;
end;

TMSQuery.ReturnParams option to True;

Now, in this way, I see the correct value of idriga (primary key value) but if I try to delete the record I obtain "PARAMETER IDRIGA NOT FOUND"

Thanks Claudio Cancelli

[Antaeus]

You should check the StatementTypes parameter in the DichiarazioniBeforeUpdateExecute event handler:

Code: Select all

  if stUpdate in StatementTypes then
    Params.ParamByName('IDRIGA').ParamType := ptInputOutput; 

[claudio.cancelli]

If I write:

if stUpdate in StatementTypes then
Params.ParamByName('IDRIGA').ParamType := ptInputOutput;

Now I don't see the value of IDRIGA after post and if I try to delete the record, I obtain "UPDATE FAILED - FOUND 0 RECORDS"

[Antaeus]

It was my misprint. You should use stInsert instead of stUpdate in the condition.

[claudio.cancelli]

now all is ok.

thanks a lot.

Claudio

[GlennDT]

Hi,

I am having the exact problem with an SQL Server-database that has tables with auto-increment fields (primary key). I can't seem to figure out how to fix this, I tried all methods described in this thread, but none of them seem to work?

Any ideas what I am doing wrong or what is the exact way to achieve this functionality?

I tried posting to support, but got no answer so I am trying this way.

thanks, regards,
Glenn

[azyk]

We can't reproduce the described problem. Below is a sample, in which an INSERT SQL query with a SCOPE_IDENTITY() T-SQL function is used. To retrieve the Identity field of an inserted record:
- the TMSQuery.Options.ReturnParams property is set to True;
- in the TMSQuery.BeforeUpdateExecute event, the ptOutput parameter type is set explicitly for the Identity field;

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin
  ...
  MSQuery.Options.ReturnParams := True;
  MSQuery.SQL.Text := 'SELECT * FROM emp';
  MSQuery.SQLInsert.Text :=
    ' INSERT INTO emp ' +
    '   (ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) ' +
    ' VALUES ' +
    '   (:ENAME, :JOB, :MGR, :HIREDATE, :SAL, :COMM, :DEPTNO) ' +
    ' SET :EMPNO = SCOPE_IDENTITY() ';
  MSQuery.Open;
end;

procedure TForm1.MSQueryBeforeUpdateExecute(Sender: TCustomMSDataSet;
  StatementTypes: TStatementTypes; Params: TMSParams);
begin
  if stInsert in StatementTypes then
    Params.ParamByName('EMPNO').ParamType := ptOutput;
end;

If this code sample doesn't help solve the problem, please compose your own simple sample to reproduce the problem and send it to andreyz*devart*com .

[GlennDT]

Hi Andrey,

I have sent you the sample project, could you please check?
In that project I have checked everything (properties, AfterUpdateExecute, …) stated in the post, but still get the error trying to update.

Can you keep me informed please?

Regards,
Glenn

[azyk]

I have received your sample. In the sample, the ptInputOutput value for the parameter type is set in the AfterUpdateExecute event handler, but it should be set in BeforeUpdateExecute for correct functioning. Please edit this in your project. Let us know if the error still persists.