Dataset "%s" is not in an edit or insert state
lazarus / 16-Databases

----------------------------------------------------------------------
talorigomat · 2014-04-22 14:54

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.

----------------------------------------------------------------------
eara · 2014-04-22 15:14

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 ?

----------------------------------------------------------------------
talorigomat · 2014-04-22 15:46

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.

----------------------------------------------------------------------
eara · 2014-04-22 18:30

(Plz) mention also VERSION/Platform of LAZARUS you are using etc,etc
mayby is something buggy there...simplyfy it .... try the basics : to
open a query from IDE (set up db, hostname, etc,etc, write In SQL
property the text in Object Inspector, and then press Active)... mayby
is something else.... Upload the .pas to check itgo a walk, drink a
beer, and look it again...

----------------------------------------------------------------------
talorigomat · 2014-04-25 06:18

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.

----------------------------------------------------------------------
mangakissa · 2014-04-25 14:28

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;

----------------------------------------------------------------------
talorigomat · 2014-04-30 05:33

Thanks for spotting this mangakissa.  I had forgotten that I had
placed the .cancel statement in whilst experimenting with something
else.