Lazarus

Programming => Databases => Topic started by: talorigomat on April 22, 2014, 04:54:59 pm

Title: Dataset "%s" is not in an edit or insert state
Post by: talorigomat on April 22, 2014, 04:54:59 pm
I keep getting the following error when I try to insert a record in a table in a Firebird 2.5 database - Dataset Error: Operation not allowed, dataset"%s" is not in an edit or insert state state

I'm using the following procedure below to set up the SQL statements in the TSQLQuery components.  The workWith is a global variable in the form which is set when the form is created.

procedure TOrganisationFrm.SetupDatasets;
begin
  TopQry.Active := False;
  WorkingQry.Active := False;

  WorkingQry.deletesql.Text := 'execute procedure deleteOrg (:OrgId)';
  case workWith of
    'Directorate':
    begin
      With WorkingQry do
      begin
      SQL.Text := 'select ORGID, ORG_NME AS Name, LEVEL_NUM, PARENT, ';
      SQL.Text := SQL.Text + 'INUSE from organisation WHERE Parent IS NULL ';
      SQL.Text := SQL.Text + 'ORDER BY Name;';

      InsertSQL.Text := 'execute procedure insertorg (:OrgName, 1, NULL)';

      Active  := True;
      end;
    end;

    'Division':
    begin
      With WorkingQry do
      begin
        SQL.Text := 'select ORGID, ORG_NME AS Name, LEVEL_NUM, ';
        SQL.Text := SQL.Text + 'PARENT, INUSE from organisation WHERE ';
        SQL.Text := SQL.Text + 'LEVEL_NUM=2 AND PARENT = :ORGID ';
        SQL.Text := SQL.Text + 'ORDER BY Name;';

        InsertSQL.Text := 'execute procedure insertorg (:OrgName, 2, :OrgParent)';

        DataSource := TopDsr;
      end;

      With TopQry do
      begin
        SQL.Text :='select ORGID, ORG_NME AS Name, LEVEL_NUM, PARENT, ';
        SQL.Text := SQL.Text + 'INUSE from organisation WHERE LEVEL_NUM = 1 ';
        SQL.Text := SQL.Text + 'ORDER BY Name;';
      end;
      TopQry.Active := True;
      WorkingQry.Active := True;
    end;
  end;
end;

I am using the following procedure to try to insert a new record.

procedure TOrganisationFrm.Button1Click(Sender: TObject);
begin
  WorkingQry.Append ;
  WorkingQry.FieldByName('Name').AsString := AddEdt.Text;    // Error message gets triggered here
  WorkingQry.Post;
  GovDM.GovTrnMain.Commit;
end;

From what I've read issuing using the statement "WorkingQry.Append;" should have place the dataset in insert mode.  I have also tried WorkingQry.Insert and get the same error.
Title: Re: Dataset "%s" is not in an edit or insert state
Post by: eara on April 22, 2014, 05:14:10 pm
1. Are you closing somewhere the queries before changing sql?
*** 2. is datasource Cleared in case Parent IS NULL ? ( otherwise can have old value and behave as detail of TopQuery, which may be also closed)
3. is there an Abort on beforeInsert aborting the state transition ?
4. in what state the workingQry fires the error? Is it a detail of Top query or not ?
Title: Re: Dataset "%s" is not in an edit or insert state
Post by: talorigomat on April 22, 2014, 05:46:58 pm
Hi Eara

1. Yes the datasets are closed, changed and then made active  in the SetupDatasets procedure.
2. On the forms creation the datasource is clear.  I use the form's OnShow event handler to call the SetupDatasets procedure which then sets the TSQLQuery components various SQL properties and set up the datasources
3. There is nothing assigned to the BeforeInsert Event
4. It remains in an unmodified state after issuing the .Append statement.  If we are working with a Directorate then workingQry is not a detail of TopQuery.  If we are working with a Division then WorkingQuery becomes a detail of TopQuery.
Title: Re: Dataset "%s" is not in an edit or insert state
Post by: eara on April 22, 2014, 08:30:18 pm
Title: Re: Dataset "%s" is not in an edit or insert state
Post by: talorigomat on April 25, 2014, 08:18:24 am
Made some changes but still getting the error.  I've attached the project to have a look at.  Also if I try to make to change the name of an entry in the DBgrid when I'm working with Specialty of Division I get a column not found error.  It works fine for directorate.
Title: Re: Dataset "%s" is not in an edit or insert state
Post by: mangakissa on April 25, 2014, 04:28:07 pm
This is your problem:
Code: [Select]
procedure TOrganisationFrm.WorkingDsrStateChange(Sender: TObject);
begin
  if WorkingDsr.State = dsEdit then
    begin
      SaveBtn.visible := True;
      CancelBtn.visible := True;
    end;
  if WorkingDsr.State = dsInsert then   <---- problem
    begin
      WorkingQry.Cancel;
    end;
end;
At the moment the dataset is set in append mode, the datasource set data dataset back to browsemode.

Another thing. I dont know if I'm right but you're insertquery has an alias field. As far as I know this should be real fieldname.
INSERT INTO Organisation (Org_Nme As Name,  Level_Num, Parent) VALUES   (:Org_Nme, :Level_Num, :Parent);

Also WorkingQry.ExecSQL is only working for WorkingQry.SQL and not WorkingQry.InsertSQL.
WorkingQry.Post can create a insertQuery for you, but also can use your insertQuery as far as your fieldnames are correct.
Code: [Select]
procedure TOrganisationFrm.AddBnClick(Sender: TObject);
begin
  try
    WorkingQry.Append;
    WorkingQry.FieldByName('Name').AsString := AddEdt.Text;
    WorkingQry.Post;
    WorkingQry.ApplyUpdates;
    GovDm.GovTrnMain.Commit;
  except
    on e: EIbDatabaseError  do
      case E.GDSErrorcode of
        335544665 :  //unique_key_violation
        begin
          GovDm.GovTrnMain.Rollback;
          GovDm.GovTrnMain.Action := caCommit;  <-- is not working in SQLdb and isn't nessesary
          Showmessage('A ' + workwith + ' with that name already exists.  ' +
                   'You may need to show inactive records to see it');
        end;
      end;
  end;
end;
Title: [Solved]: Dataset "%s" is not in an edit or insert state
Post by: talorigomat on April 30, 2014, 07:33:38 am
Thanks for spotting this mangakissa.  I had forgotten that I had placed the .cancel statement in whilst experimenting with something else.
TinyPortal © 2005-2018