Deleting a record from DBGrid
lazarus / 16-Databases

----------------------------------------------------------------------
xenblaise · 2010-10-12 01:22

SQLQuery1.Active := False;
  SQLQuery1.DeleteSQL.Text := 'delete from LEADERS where
LEADER_ALIAS_NAME = :L';
  SQLQuery1.Params.ParamValues['L'] := DBGrid1.SelectedField.Text;
  SQLQuery1.Params.ParamByName('L').Value :=
DBGrid1.SelectedField.Text;
  SQLQuery1.Active := True;
  SQLQuery1.ExecSQL;
  SQLQuery1.Delete;

Parameter :L not found? What do I miss?  something wrong with my code?

----------------------------------------------------------------------
eny · 2010-10-12 03:53

Quote from: xenablaise on October 12, 2010, 03:22:50 am 
SQLQuery1.Params.ParamValues['L'] := DBGrid1.SelectedField.Text;
  SQLQuery1.Params.ParamByName('L').Value :=
DBGrid1.SelectedField.Text;
Are those two llines not doing the same thing?
And therefore the exception is thrown on the 2nd line because 'L' has
already been processed?

----------------------------------------------------------------------
Lacak2 · 2010-10-12 07:44

You mismatch two things:
1. you can use SELECT :
  SQLQuery1.Close;
  SQLQuery1.SQL.Text := 'select * from LEADERS where LEADER_ALIAS_NAME
= :L';
  SQLQuery1.Params.ParamByName('L').Value :=
DBGrid1.SelectedField.Text;
  SQLQuery1.Open;
  SQLQuery1.Delete;

2. or you can use DELETE:
  SQLQuery1.Close;
  SQLQuery1.SQL.Text := 'delete from LEADERS where LEADER_ALIAS_NAME =
:L';
  SQLQuery1.Params.ParamByName('L').Value :=
DBGrid1.SelectedField.Text;
  SQLQuery1.ExecSQL;

----------------------------------------------------------------------
xenblaise · 2010-10-12 11:51

Heres the code, dont know whats wrong with it.

//DELETING (errors)
procedure TForm1.Button4Click(Sender: TObject);
begin

{
  SQLQuery1.close;
  SQLQuery1.SQL.Text := 'delete from LEADERS where LEADER_ALIAS_NAME =
:L';
  SQLQuery1.Params.ParamByName('L').Value  :=
DBGrid1.SelectedField.Text;
  SQLQuery1.ExecSQL;
}

{
 SQLQuery1.close;
 SQLQuery1.SQL.Text := 'select * from LEADERS where LEADER_ALIAS_NAME
= :L';
 SQLQuery1.Params.ParamByName('L').Value  :=
DBGrid1.SelectedField.Text;
 SQLQuery1.Open;
 SQLQuery1.Delete;
}

{
  SQLQuery1.close;
  SQLQuery1.deleteSQL.Text := 'delete from LEADERS where
LEADER_ALIAS_NAME = :L';
  SQLQuery1.Params.ParamByName('L').Value  :=
DBGrid1.SelectedField.Text;
  SQLQuery1.ExecSQL;
 }

end;

//LOADING DBGRID1 AND DBGRID2 AUTOMATION (master-detail relationship)
procedure TForm1.FormShow(Sender: TObject);
begin

  SQLite3Connection1.Directory := '';
  SQLite3Connection1.DatabaseName := 'candid.db3';
  SQLTransaction1.DataBase := SQLite3Connection1;
  SQLTransaction1.Action := caCommit;
  SQLTransaction1.Active := True;
  SQLite3Connection1.Transaction := SQLTransaction1;
  SQLite3Connection1.Connected := True;


  SQLQuery1.DataBase := SQLite3Connection1;
  SQLQuery1.SQL.Text := 'select * from leaders';
  SQLQuery1.Transaction := SQLTransaction1;
  Datasource1.DataSet := SQLQuery1;

  DbEdit1.DataField  := 'LEADER_ALIAS_NAME';
  DbEdit2.DataField  := 'PRECINCT_NUMBER';
  DbMemo1.DataField  := 'LEADER_NOTE';

  DbEdit1.DataSource := Datasource1;
  DbEdit2.DataSource := Datasource1;
  DbMemo1.DataSource := Datasource1;

  DbGrid1.DataSource := Datasource1;
  SQLQuery1.Open;


  SQLQuery2.DataBase := SQLite3Connection1;
  SQLQuery2.DataSource := Datasource1;
  SQLQuery2.Transaction := SQLTransaction1;

  SQLQuery2.SQL.Text := 'select * from MEMBERS where
MEMBER_LEADERS_NAME = :LEADER_ALIAS_NAME';
  Datasource2.DataSet := SQLQuery2;

  DbGrid2.DataSource := Datasource2;
  SQLQuery2.Open;

end;

----------------------------------------------------------------------
Lacak2 · 2010-10-13 03:22

SQLQuery1 and SQLQuery2 are used in master-detail relationship. So you
can not use SQLQuery1 for "deleting", when master-detail is active.

What errors ? What do you want to do ?
Delete master row and all related details rows ?

----------------------------------------------------------------------
xenblaise · 2010-10-13 05:12

This solves the problem,
But
the problem is I dont want to use < SQLQuery1.SQL.Text > since its
more data to be loaded during at the second call when other record has
been deleted.


SQLQuery2.close;
  s:= SQLQuery1.SQL.Text;
  SQLQuery1.close;
  SQLQuery1.SQL.Text := 'delete from LEADERS where LEADER_ALIAS_NAME =
:L';

  SQLQuery1.Params.CreateParam(ftString,'L',ptUnknown);
  SQLQuery1.Params.ParamByName('L').AsString  := DbEdit1.Text;
  SQLQuery1.ExecSQL;


  SQLTransaction1.Commit;
  SQLTransaction1.Action := caCommit;

  SQLQuery1.SQL.Text := s;
  SQLQuery1.open;

  SQLQuery2.open;

----------------------------------------------------------------------
xenblaise · 2010-10-13 06:52

SQLQuery3.SQL.Text := 'delete from LEADERS where LEADER_ALIAS_NAME =
:LEADER_ALIAS_NAME';
  SQLQuery3.ExecSQL;
  SQLTransaction1.Commit;
  SQLQuery1.open;
  SQLQuery2.open;

That also solves the problem.  I did make another SQLQuery to handle
the deletion.

BUT the problem is I dont want to use < SQLQuery1.SQL.Text > since HAS
more data to be loaded during at the second call with < SQLQuery.Open
>

How could I use  < SQLQuery3.deleteSQL.Text > to delete record
smoothly and not loading back with the select sql statement.

----------------------------------------------------------------------
Lacak2 · 2010-10-13 07:27

if you are posioned in SQLQuery1 (LEADERS table) on record, which you
want to delete, then try simply:

SQLQuery1.Delete;
SQLQuery1.ApplyUpdates;
SQLQuery1.Commit;

----------------------------------------------------------------------
xenblaise · 2010-10-13 08:27

This totally solved the problem.

//FormLoad/Show
SQLQuery1
....
...
SQLQuery2
...
...

  SQLQuery3.DataBase := SQLite3Connection1;
  SQLQuery3.DataSource := Datasource1;
  SQLQuery3.Transaction := SQLTransaction1;


//Button Delete (didn't use deleteSQL [bug] maybe).
begin
  SQLQuery3.SQL.Text := 'delete from LEADERS where LEADER_ALIAS_NAME =
:LEADER_ALIAS_NAME';
  SQLQuery3.ExecSQL;
  SQLQuery1.Delete;
  SQLQuery1.ApplyUpdates;
  SQLTransaction1.CommitRetaining;
end;

//Button Update
begin
   SQLQuery1.Edit;
   SQLQuery1.Post;
   SQLQuery1.ApplyUpdates;
   SQLTransaction1.CommitRetaining;
end;

Ok, since I was the one who solved my own problem,  can anyone add a
button event for adding a new record.

sample;
when newButton was pressed then what event shall I code for SQLQuery1?
After inputting a few data, then press save, what shall I code also?

//Button NewSave
begin

if newsavebutton.caption='new' then
begin
?
end

else

begin
?
end;

end;



Thanks

----------------------------------------------------------------------
xenblaise · 2010-10-14 07:32

Lacak2?
You are right.
I should use only

SQLQuery1.Delete;
SQLQuery1.ApplyUpdates;
SQLQuery1.Commit;

Thanks, problem all solved, except printing table as html file, got to
open another question.


Thanks.