Recent

Author Topic: Error when Deleting DBGrid Entry  (Read 5033 times)

jeroen2015

  • Newbie
  • Posts: 3
Error when Deleting DBGrid Entry
« on: March 08, 2015, 10:22:39 pm »
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.UpdateMode:=UpWhereChanged;
  SQLQuery1.ApplyUpdates;
  SQLTransaction1.Commit;
  SQLQuery1.Close;
  SQLQuery1.Open;
end;

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

ahiggins

  • Jr. Member
  • **
  • Posts: 92
Re: Error when Deleting DBGrid Entry
« Reply #1 on: March 08, 2015, 10:50:30 pm »
not to sure of the top of my head, but

i'd try changing your SQLQuery1.UpdateMode 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

  • Newbie
  • Posts: 3
Re: Error when Deleting DBGrid Entry
« Reply #2 on: March 09, 2015, 08:44:49 pm »
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

  • Jr. Member
  • **
  • Posts: 92
Re: Error when Deleting DBGrid Entry
« Reply #3 on: March 09, 2015, 09:07:40 pm »
have you tried

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

ahiggins

  • Jr. Member
  • **
  • Posts: 92

LacaK

  • Hero Member
  • *****
  • Posts: 600
Re: Error when Deleting DBGrid Entry
« Reply #5 on: March 10, 2015, 07:59:38 am »
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)

, 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 UpdateMode 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

  • Jr. Member
  • **
  • Posts: 92
Re: Error when Deleting DBGrid Entry
« Reply #6 on: March 10, 2015, 11:34:57 am »
@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 updatemode to upWhereAll get around this? or is it best to use the DeleteSQL?




 






jeroen2015

  • Newbie
  • Posts: 3
Re: Error when Deleting DBGrid Entry
« Reply #7 on: March 10, 2015, 10:41:23 pm »
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.UpdateMode:=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

  • Hero Member
  • *****
  • Posts: 600
Re: Error when Deleting DBGrid Entry
« Reply #8 on: March 11, 2015, 09:08:29 am »
delete from mytable where id = :oldid
delete from mytable where id = :OLD_id

You must use prefix: OLD_

does setting the updatemode 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

  • Jr. Member
  • **
  • Posts: 92
Re: Error when Deleting DBGrid Entry
« Reply #9 on: March 11, 2015, 04:45:27 pm »
Thank you for the advice Lacak

 

TinyPortal © 2005-2018