Error when Deleting DBGrid Entry
lazarus / 16-Databases

----------------------------------------------------------------------
jeroen2015 · 2015-03-08 21:22

Firebird v2.5.3 and Lazarus v.1.2.6 and FPC v.2.6.4 on Win7X64

I have a single table in Firebird that I am accessing with a
TIBConnection, TSQLTranaction, TSQLQuery, TDataSource, TDBGrid, and
TDBNavigation. Three buttons have the code as below to Update, Save
and Delete the table. If I use the Delete button to delete an entry,
all is fine. After that when I use the save button to save the new
database, I get a message "Project myproject raised exception class
'EDatabaseError' with message: SQLQuery1: no delete query specified
and failed to generate one. (No fields for inclusion in where
statement found)." The same is the case when I use the TDBNavigation
Delete button: all is fine until I use the save button, which
generates the same error message. Does anyone have any idea?

Thanks, Jeroen

procedure TForm1.Button1Click(Sender: TObject);
begin
  SQLQuery1.ApplyUpdates;
  SQLQuery1.Refresh;
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
  SQLQuery1.Edit;
  SQLQuery1.Upda]"]>Blockedde:=UpWhereChanged;
  SQLQuery1.ApplyUpdates;
  SQLTransaction1.Commit;
  SQLQuery1.Close;
  SQLQuery1.Open;
end;

procedure TForm1.Button3Click(Sender: TObject);
begin
  DBGrid1.DataSource.DataSet.Delete;
end;

----------------------------------------------------------------------
ahiggins · 2015-03-08 21:50

not to sure of the top of my head, but

i'd try changing your SQLQuery1.Upda]"]>Blockedde to "upWhereAll"
first

read http://wiki.freepascal.org/Working_With_TSQLQuery


i'm sure there is a section in there regarding updating, inserting and
deleting (8.2)

failing that i'm sure one of the DB gurus on here will be able to
help.

good luck

----------------------------------------------------------------------
jeroen2015 · 2015-03-09 19:44

Have tried various methods, but keep having the same problem. Deleting
a record from the DBGrid is no issue, but saving the changes after
deleting keeps coming up with the same error message. Would anybody
have an idea? Laz keeps coming back with "no delete query specified".
I cannot see what delete query is required.

Thanks, Jeroen

----------------------------------------------------------------------
ahiggins · 2015-03-09 20:07

have you tried

Code: [Select]procedure TForm1.Button3Click(Sender: TObject);
begin
  SQLQuery1.Upda]"]>Blockedde:=UpWhereChanged;
  DBGrid1.DataSource.DataSet.Delete;
  SQLQuery1.ApplyUpdates;
  SQLTransaction1.CommitRetaining;
end;

----------------------------------------------------------------------
ahiggins · 2015-03-09 20:12

check out http://www.freepascal.org/docs-
html/fcl/sqldb/tsqlquery.deletesql.html

----------------------------------------------------------------------
LacaK · 2015-03-10 06:59

Quote from: jeroen2015 on March 09, 2015, 08:44:49 pm Deleting a
record from the DBGrid is no issue
It only seems so.
In fact Delete only locally marks record as deleted. Nothing has
happen in underlaying database!
Only ApplyUpdates propagates all local cached changes to database.
(deletes, insert, edits)

Quote from: jeroen2015 on March 09, 2015, 08:44:49 pm, but saving the
changes after deleting keeps coming up with the same error message.
Error message indicates, that:
- your table does not has primary key defined (so sqlDB can not
construct condition in WHERE clause)
- your Upda]"]>Blockedde requires primary key
- if sqlDb can not construct DELETE statement on the fly, you must
supply it explicitly see DeleteSQL property (as ahiggins wrote)

----------------------------------------------------------------------
ahiggins · 2015-03-10 10:34

@jeroen2015 do you have a unique field in your table like an autoinc?

@Lacak

could jeroen2015 use something like

delete from mytable where id = :oldid

in the DeleteSQL property? (not sure of the syntax)
does setting the upda]"]>Blockedde to upWhereAll get around this? or
is it best to use the DeleteSQL?

----------------------------------------------------------------------
jeroen2015 · 2015-03-10 21:41

I have added a primary key (not autoincrement) to an int column of the
table and this code below did actually (as mentioned above) allow to
delete the active record in the grid

procedure TForm1.Button3Click(Sender: TObject);
begin
  SQLQuery1.Upda]"]>Blockedde:=UpWhereChanged;
  DBGrid1.DataSource.DataSet.Delete;
  SQLQuery1.ApplyUpdates;
  SQLTransaction1.CommitRetaining;
end;

Seems that a primary key is required, even if there is just a single
table in the database. I did notice though that there is very few
examples on the DeleteSQL, i.e. how exactly to complete a delete SQL
statement

Thanks for all the help, it is much appreciated
Jeroen

----------------------------------------------------------------------
LacaK · 2015-03-11 08:08

Quote from: ahiggins on March 10, 2015, 11:34:57 amdelete from mytable
where id = :oldid
delete from mytable where id = :OLD_id

You must use prefix: OLD_

Quote from: ahiggins on March 10, 2015, 11:34:57 amdoes setting the
upda]"]>Blockedde to upWhereAll get around this? or is it best to use
the DeleteSQL?
IMO yes, using upWhereAll does skip requirement of primary key as all
fields are inculed in where clause.
(in this case you do not require supply DeleteSQL)

----------------------------------------------------------------------
ahiggins · 2015-03-11 15:45

Thank you for the advice Lacak