Unable delete record inserted -

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
claudio.cancelli
Posts: 4
Joined: Mon 10 Dec 2007 16:10

Unable delete record inserted -

Post by claudio.cancelli » Mon 10 Dec 2007 16:17

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
Posts: 2098
Joined: Tue 14 Feb 2006 10:14

Post by Antaeus » Wed 12 Dec 2007 11:45

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
Posts: 4
Joined: Mon 10 Dec 2007 16:10

Post by claudio.cancelli » Wed 12 Dec 2007 17:34

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
Posts: 2098
Joined: Tue 14 Feb 2006 10:14

Post by Antaeus » Thu 13 Dec 2007 12:25

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
Posts: 4
Joined: Mon 10 Dec 2007 16:10

Post by claudio.cancelli » Thu 13 Dec 2007 16:39

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
Posts: 2098
Joined: Tue 14 Feb 2006 10:14

Post by Antaeus » Fri 14 Dec 2007 08:52

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

claudio.cancelli
Posts: 4
Joined: Mon 10 Dec 2007 16:10

Post by claudio.cancelli » Fri 14 Dec 2007 14:26

now all is ok.

thanks a lot.

Claudio

GlennDT
Posts: 2
Joined: Thu 19 Mar 2015 08:07

Re: Unable delete record inserted -

Post by GlennDT » Thu 19 Mar 2015 08:13

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
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Unable delete record inserted -

Post by azyk » Thu 19 Mar 2015 11:23

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
Posts: 2
Joined: Thu 19 Mar 2015 08:07

Re: Unable delete record inserted -

Post by GlennDT » Thu 19 Mar 2015 13:39

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
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Unable delete record inserted -

Post by azyk » Fri 20 Mar 2015 08:47

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.

Post Reply