Lazarus
Programming => Databases => Topic started 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.
-
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 ?
-
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.
-
- (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 it
- go a walk, drink a beer, and look it again...
-
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.
-
This is your problem:
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.
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;
-
Thanks for spotting this mangakissa. I had forgotten that I had placed the .cancel statement in whilst experimenting with something else.