Unable delete record inserted -
-
- Posts: 4
- Joined: Mon 10 Dec 2007 16:10
Unable delete record inserted -
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
If I refresh the dataset, I can delete the record without problem.
What's the problem?
Thanks
Claudio Cancelli
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) set the TMyQuery.RefreshOptions.roAfterInsert to True;
2)
- 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()
- set the TMSQuery.ReturnParams option to True;
- 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;
-
- Posts: 4
- Joined: Mon 10 Dec 2007 16:10
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
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
You should check the StatementTypes parameter in the DichiarazioniBeforeUpdateExecute event handler:
Code: Select all
if stUpdate in StatementTypes then
Params.ParamByName('IDRIGA').ParamType := ptInputOutput;
-
- Posts: 4
- Joined: Mon 10 Dec 2007 16:10
Re: Unable delete record inserted -
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
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
Re: Unable delete record inserted -
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;
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 .
- 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;
Re: Unable delete record inserted -
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
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
Re: Unable delete record inserted -
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.