Only one record should have been updated.

[kidjake28]

I have a table with only ONE record in it. When I try to edit it, I get the error message stated in the subject.

....EZSQLException with mesage '0 record(s) updated. Only one record should have been updated."

Here is my code:

Code: Select all

if MainDM.adoRegistration.RecordCount = 0 then
        MainDM.adoRegistration.Insert
      else
        MainDM.adoRegistration.edit;
      Anode := httpxml.Root;
      MainDM.adoRegistration.FieldByName('company').AsString := Anode.Items.ItemNamed['company'].properties[0].value;
      MainDM.adoRegistration.FieldByName('address1').AsString := Anode.Items.ItemNamed['address1'].properties[0].Value;
      MainDM.adoRegistration.FieldByName('address2').AsString := Anode.Items.ItemNamed['address2'].properties[0].Value;
      MainDM.adoRegistration.FieldByName('city').AsString := Anode.Items.ItemNamed['city'].properties[0].Value;
      MainDM.adoRegistration.FieldByName('province').AsString := Anode.Items.ItemNamed['province'].properties[0].Value;
      MainDM.adoRegistration.FieldByName('postalcode').AsString := Anode.Items.ItemNamed['postalcode'].properties[0].Value;
      MainDM.adoRegistration.FieldByName('phone').AsString := Anode.Items.ItemNamed['phone'].properties[0].Value;
      MainDM.adoRegistration.FieldByName('country').AsString := Anode.Items.ItemNamed['country'].properties[0].Value;
      MainDM.adoRegistration.FieldByName('email').AsString := Anode.Items.ItemNamed['email'].properties[0].Value;
      MainFRM.listingBoardREG.WriteString('Theme\Logo', Anode.Items.ItemNamed['parentcompany'].properties[0].Value);

      MainDM.adoRegistration.Post;
      MainDM.adoRegistration.ApplyUpdates;
      MainDM.adoRegistration.CommitUpdates;

adoRegistration is a TZTable.

[kidjake28]

I should mention that if I delete the record (so that it inserts) it works fine.

[EgonHugeist]

kidjake28,


No showstopper! Just a wanted behavior for a nice database structure.

this is a problem of PrimaryKeys or Unique checks. Am i right you have no such one?

There was a long thread about this issue in the 7.0 Beta forum. Zeos trys to determine an 100% exact row to update. It's never a good idea to update tables with either 0 or more than one updated rows. Best practice: use a primary key constraint.

Why your updatecount is 0 i cant say right now.

If you're sure what you're doing and such an update behavior is wanted just add:
1. ValidateUpdateCount=-1 or False to the ZDataSet.Properties.
2. I've made a global define to suppress this checking: WITH_VALIDATE_UPDATE_COUNT
comment it in Zeos.inc recompile and you'll be a lucky man again.

[kidjake28]

Thank you once again Egon. I appreciate the help.

So I created a Primary Index on the 'Company' field.

What do you mean by 'User primary key constraint'?

Can you give me an example?

[EgonHugeist]

kidjake28,

had wrong typo? Nope this times I wrote: (((:

use a primary key constraint

first google match: http://www.w3schools.com/sql/sql_primarykey.asp
have a look. But i'm not sure if SQLite understands the constraint syntax. Anyway having a not null unique index should resolve your issue.

[kidjake28]

Sorry my mistake. You are absolutely right...I misread your reply.

However I did as you say and still get the same problem.

http://www.tiikoni.com/tis/view/?id=2c9139c
http://www.tiikoni.com/tis/view/?id=34dd27b

[EgonHugeist]

kidjake28,

Man i was terribly wrong because i didn't read you code example completely.

please have a look to: http://zeosbugs.firmos.at/view.php?id=206

Here i see an starting discussion (;

CommitUpdates trys to update the rows again. Nothing has changed -> UpdateCount = 0

what do you think was my veeeery old patch right?

[kidjake28]

The problem is that I'm getting the error on the POST?

I don't even make it to the applyupdates or commitupdates.

Should I not be posting????

[kidjake28]

According to this example, I believe I'm doing the same thing?

Code: Select all

: 
With DataSet do Begin 
bEverythingOK := True; 
CachedUpdates := True; 
DataSet.First; 
While (not DataSet.EOF) and (bEverythingOK) do Begin 
DataSet.Edit; 
: 
// process record 
: 
DataSet.Post; 
DataSet.Next; 
: 
bEverythingOK := AFunctionForValidation; 
End; 
If bEverythingOK Then Begin 
ApplyUpdates; 
CommitUpdates; 
End 
Else 
CancelUpdates; 
CachedUpdates := False; 
End; 
:

[kidjake28]

Actually I didn't have cacheupdates set to TRUE. I'm still getting the error but it's on the applyupdates and not on the post.

[EgonHugeist]

kidjake28,

wasn't @home.

Actually I didn't have cacheupdates set to TRUE. I'm still getting the error but it's on the applyupdates and not on the post.

That's the point: you're trying to update the column twice.
If CachedUpdates = False than Post does successfully update/insert the row. Cached updates can't find a edited row anymore and the exception is raised.

Your attached code sequence of your firstpost works perfectly if Cached updates = True. (I've made a testcase to validate my suggestions) Btw. Add TZConnection.Commit to write the transaction.

If CachedUpdates = False than check the Transaction mode and Autocommit of the TZConnection. Be sure Zeos can close the transaction.

example with no chached update:

Code: Select all

Connection.Autocommit := True;
Connection.TransactIsolationLevel =: tiNon.

TZDataSet.Cached updates := False;
TZDataSet.Open;
TZDataSet.Edit or Insert;
.. add some data ...
TZDataSet.Post.

and another more complex example of our testsuites with cached updates(to show you some more options too):

Code: Select all

{**
  Runs a test for in extendet clientdatset rules
  All detail-queries should be updated in a single transaction ?.
  But now the MasterTable should be updated first for an valid ForegnKey.
  Then all DetailTables should have been updated.
  Very tricky and has to deal with MetaData informations.
}
procedure TZTestMasterDetailCaseMBCs.TestClientDatasetWithForeignKey_doUpdateMasterFirst;
var
  SQLMonitor: TZSQLMonitor;
  //CommitCount, I: Integer;
begin
  SQLMonitor := TZSQLMonitor.Create(nil);
  SQLMonitor.Active := True;
  MasterQuery.SQL.Text := 'SELECT * FROM department ORDER BY dep_id';
  MasterQuery.Options := MasterQuery.Options + [doDontSortOnPost];
  MasterQuery.Open;

  CheckStringFieldType(MasterQuery.FieldByName('dep_name').DataType, Connection.DbcConnection.GetConSettings);
  CheckStringFieldType(MasterQuery.FieldByName('dep_shname').DataType, Connection.DbcConnection.GetConSettings);
  CheckStringFieldType(MasterQuery.FieldByName('dep_address').DataType, Connection.DbcConnection.GetConSettings);

  DetailQuery.SQL.Text := 'SELECT * FROM people';
  DetailQuery.MasterSource := MasterDataSource;
  DetailQuery.MasterFields := 'dep_id';
  DetailQuery.LinkedFields := 'p_dep_id';
  DetailQuery.Options := DetailQuery.Options + [doUpdateMasterFirst, doDontSortOnPost];
  DetailQuery.Open;
  //CommitCount := 0;
  try
    MasterQuery.Append;
    MasterQuery.FieldByName('dep_id').AsInteger := TestRowID;
    MasterQuery.FieldByName('dep_name').AsString := GetDBTestString('?????', Connection.DbcConnection.GetConSettings);
    MasterQuery.FieldByName('dep_shname').AsString := 'abc';
    MasterQuery.FieldByName('dep_address').AsString := GetDBTestString('A adress of ?????', Connection.DbcConnection.GetConSettings);

    CheckEquals(True, (MasterQuery.State = dsInsert), 'MasterQuery Insert-State');

    DetailQuery.Append;
    DetailQuery.FieldByName('p_id').AsInteger := TestRowID;
    DetailQuery.FieldByName('p_dep_id').AsInteger := TestRowID;

    DetailQuery.FieldByName('p_begin_work').AsDateTime := now;
    DetailQuery.FieldByName('p_end_work').AsDateTime := now;
    DetailQuery.FieldByName('p_picture').AsString := '';
    DetailQuery.FieldByName('p_resume').AsString := '';
    DetailQuery.FieldByName('p_redundant').AsInteger := 5;
    CheckEquals(True, (DetailQuery.State = dsInsert), 'MasterQuery Insert-State');

    MasterQuery.Post;

    CheckEquals(True, (MasterQuery.State = dsBrowse), 'MasterQuery Browse-State');
    CheckEquals(True, (DetailQuery.State = dsBrowse), 'DetailQuery Browse-State');

    {fix it
    for i := 0 to SQLMonitor.TraceCount -1 do
      if SQLMonitor.TraceList[i].Category = lcTransaction then
        if Pos('COMMIT', UpperCase(SQLMonitor.TraceList[i].Message)) > 0 then
          Inc(CommitCount);
    CheckEquals(1, CommitCount, 'CommitCount'); }
  finally
    MasterQuery.SQL.Text := 'delete from people where p_id = '+IntToStr(TestRowID);
    MasterQuery.ExecSQL;
    MasterQuery.SQL.Text := 'delete from department where dep_id = '+IntToStr(TestRowID);
    MasterQuery.ExecSQL;
    SQLMonitor.Free;
  end;
end;

[kidjake28]

Thanks for the reply Egon,

I am doing what you say to do and I'm still getting the same error on the post. I don't understand, I've gone over your example 1000 times. I don't see any difference to my code yet I get the error on the post?

I'm also puzzled why the INSERT works with the code below and not the EDIT?

Code: Select all

      MainDM.ListingBoardDB.AutoCommit := true;
      MainDM.ListingBoardDB.TransactIsolationLevel:=tiNone;
      MainDM.adoRegistration.CachedUpdates := false;
      MainDM.adoRegistration.Open;

      if MainDM.adoRegistration.RecordCount = 0 then
        MainDM.adoRegistration.Insert
      else
        MainDM.adoRegistration.edit;
      Anode := httpxml.Root;
MainDM.adoRegistration.FieldByName('company').AsString := Anode.Items.ItemNamed['company'].Properties[0].Value;
      MainDM.adoRegistration.FieldByName('address1').AsString := Anode.Items.ItemNamed['address1'].Properties[0].Value;
      MainDM.adoRegistration.FieldByName('address2').AsString := Anode.Items.ItemNamed['address2'].Properties[0].Value;
      MainDM.adoRegistration.FieldByName('city').AsString := Anode.Items.ItemNamed['city'].Properties[0].Value;
      MainDM.adoRegistration.FieldByName('province').AsString := Anode.Items.ItemNamed['province'].Properties[0].Value;
      MainDM.adoRegistration.FieldByName('postalcode').AsString := Anode.Items.ItemNamed['postalcode'].Properties[0]
        .Value;
      MainDM.adoRegistration.FieldByName('phone').AsString := Anode.Items.ItemNamed['phone'].Properties[0].Value;
      MainDM.adoRegistration.FieldByName('country').AsString := Anode.Items.ItemNamed['country'].Properties[0].Value;
      MainDM.adoRegistration.FieldByName('email').AsString := Anode.Items.ItemNamed['email'].Properties[0].Value;
      MainFRM.listingBoardREG.WriteString('Theme\Logo', Anode.Items.ItemNamed['parentcompany'].Properties[0].Value);

      MainDM.adoRegistration.Post;

[EgonHugeist]

kidjake28,

can you write a litte demo application, please? I'm still not able to reproduce your issues. Than i can fix your code or Zeos if necessary. What do you think?

[kidjake28]

Sorry I was busy for a bit...I will try and get you the code.

[Mar_01]

0 RECORD UPDATED. ONLY ONE RECORD SHOULD HAVE BEEN UPDATED

After including and trying to edit or delete the same item, the error occurs. Follow the code. and also the sample application I used.
Delph XE 10.4 i/ Firebird 3.0.
I do not know what to do.
https://1drv.ms/f/s!AqUco637FO1GhY0kWpC ... A?e=E5lHmr
******************************************************************************************************
Select * from vendas where serie='CFe'
and numserie_ecf=1 and impresso='NAO'

Svenda.DataSet.Insert;
Svenda.DataSet.FieldByName('total_nota').AsCurrency:=subtotal;
Svenda.DataSet.FieldByName('total_itens').AsCurrency:=subtotal;
Svenda.DataSet.FieldByName('SERIE').AsString:='CFe';
Svenda.DataSet.FieldByName('NUMNOTA').AsString:='';//numcop.Caption;
Svenda.DataSet.FieldByName('CODCLI').AsString:='9999';
Svenda.DataSet.FieldByName('CLIENTE').AsString:='AO CONSUMIDOR';
Svenda.DataSet.FieldByName('DATAEMISSAO').AsDateTime:=now;
Svenda.DataSet.FieldByName('DATASAIDA').AsDateTime:=now;
Svenda.DataSet.FieldByName('total_nota').AsCurrency:=Svenda.DataSet.FieldByName('total_nota').AsCurrency+subtotal;
Svenda.DataSet.FieldByName('total_itens').AsCurrency:=Svenda.DataSet.FieldByName('total_itens').AsCurrency+subtotal;
Unit2.DataModule2.ZQuery1.ApplyUpdates;
Unit2.DataModule2.ZQuery1.CommitUpdates;
// Svenda.DataSet.Post;

*******************************************************************************************************************************
Svenda.DataSet.Edit;
Svenda.DataSet.FieldByName('total_nota').AsCurrency:=Svenda.DataSet.FieldByName('total_nota').AsCurrency+subtotal;
Svenda.DataSet.FieldByName('total_itens').AsCurrency:=Svenda.DataSet.FieldByName('total_itens').AsCurrency+subtotal;
Svenda.DataSet.Post;
**********************************************************************************************************************************
UPDATE vendas SET
CODCLI = :CODCLI,
COD_VENDEDOR = :COD_VENDEDOR,
COD_TRANSP = :COD_TRANSP,
CODFORMAPAG = :CODFORMAPAG,
NUMNOTA = :NUMNOTA,
NUMPED = :NUMPED,
SERIE = :SERIE,
CFOP = :CFOP,
DATAEMISSAO = :DATAEMISSAO,
DATASAIDA = :DATASAIDA,
TOTAL_FRETE = :TOTAL_FRETE,
CONHEC_FRETE = :CONHEC_FRETE,
TOTAL_NOTA = :TOTAL_NOTA,
TOTAL_ITENS = :TOTAL_ITENS,
TOTAL_DESC = :TOTAL_DESC
WHERE
vendas.CODIGO = :OLD_CODIGO
*****************************************************************************************************************
DELETE FROM vendas
WHERE
vendas.CODIGO = :OLD_CODIGO