Recent

Author Topic: [SOLVED] Operation cannot be performed on an active dataset.  (Read 6034 times)

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
[SOLVED] Operation cannot be performed on an active dataset.
« on: September 18, 2012, 05:25:06 pm »
Firebird.

I'm trying to iterate through a view (AdresNotEnough) (that has names in it), get the primary key (indicating a person).
Then get the last modified record in a linked table with addresses (AdresLatest) (this has some addresses, none of which have the StandaardAdres field set to 1).
Finally I want to update the linked table, and set an integer field to 1 (AdresDefault) for that latest field.
This latest operation fails with
Quote
Operation cannot be performed on an active dataset.
(marked failure here below)
The .executedirect command does modify the data so that one record in the original view would disappear. Is that the problem I'm facing or is it something else?

If it's the former, I can redo the thing with 2 transaction, something like a snapshot read only transaction for the read iteration and a normal transaction for the write transaction... just wanted to make sure that is really needed.

Code: [Select]
procedure TAddressScrubber.MainProgram;
const
  AdresDefault='UPDATE ADRESSEN a SET a.STANDAARDADRES = 1 WHERE a.ADRESID = ';
  AdresLatest='select a.adresid from adressen a where a.naamid=%s order by changedate desc rows 1 to 1';
  AdresNonDefault='UPDATE ADRESSEN a SET a.STANDAARDADRES = 0 WHERE a.ADRESID <> ';
  AdresNotEnough='select naamid from vwadres_std_notenough';
var
  Conn: TIBConnection;
  DetailDefault, NameID: integer;
  Q, QDetail: TSQLQuery;
  Rec: integer;
  Tran: TSQLTransaction;
begin
  Conn:=TIBConnection.Create(nil);
  Q:=TSQLQuery.Create(nil);
  QDetail:=TSQLQuery.Create(nil);
  Tran:=TSQLTransaction.create(nil);
  try
    //todo fix static assignments
    Conn.HostName:='someserver';
    Conn.DatabaseName:='thedatabase';
    Conn.UserName:='SYSDBA';
    Conn.Password:='masterkey';
    Conn.CharSet:='UTF8';
    Conn.Open;
    if not(Conn.Connected) then
    begin
      writeln('Connection error.');
      halt(2);
    end;
    Conn.Transaction:=Tran;
    Tran.StartTransaction;
    Q.DataBase:=Conn;
    Q.SQL.Text:=AdresNotenough;
    Q.Open;
    QDetail.Database:=Conn;
    while not(q.EOF) do
    begin
      NameID:=q.Fields[0].AsInteger;
      // Get detail record that was modified last, a suitable default for the standard detail record:
      QDetail.SQL.Text:=Format(AdresLatest, [inttostr(NameID)]);
      QDetail.Open;
      writeln('NameID: '+inttostr(NameID));
      DetailDefault:=QDetail.Fields[0].AsInteger;
      writeln('DetailDefault '+inttostr(DetailDefault));
      writeln('going to run:');
      writeln(AdresDefault+inttostr(DetailDefault));
      Conn.ExecuteDirect(AdresDefault+inttostr(DetailDefault));  //<===== failure here
      q.next;
    end;
    Tran.Commit;
    QDetail.Close;
    q.close;
    conn.close;
  finally
    Q.Free;
    QDetail.Free;
    Tran.Free;
    Conn.Free;
  end;

end;

Lazarus 1.0, FPC 2.6.0, x86, Windows, also occurs with FPC trunk x86

Thanks,
BigChimp
« Last Edit: September 18, 2012, 07:17:19 pm by BigChimp »
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: [SOLVED] Operation cannot be performed on an active dataset.
« Reply #1 on: September 18, 2012, 07:18:04 pm »
Mmmm.... getting tired.
Throwing in a .close helped:
Quote
...
      QDetail.Close;
      QDetail.SQL.Text:=Format(AdresLatest, [inttostr(NameID)]);
      QDetail.Open;
...
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

Knipfty

  • Full Member
  • ***
  • Posts: 230
Re: [SOLVED] Operation cannot be performed on an active dataset.
« Reply #2 on: September 18, 2012, 07:23:09 pm »
I've found that anytime you change the SQL Text property, the dataset needs to be closed.  The IDE does this automatically and forces you to set active to true to see the result.  It doesn;t surprise me that you need to do this programically as well.
32-bit Lazarus 1.0 FPC 2.6.0, 64-bit Win 7, Advantage DB 10.10

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: [SOLVED] Operation cannot be performed on an active dataset.
« Reply #3 on: September 18, 2012, 07:43:53 pm »
Nope, that seems logical. Lesson: don't program when you're tired ;)

edit: ... and if you do, do what you tell others to do: run with the debugger first to find out where the error message is generated exactly ;)
« Last Edit: September 19, 2012, 10:39:24 am by BigChimp »
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified