Deleting a record from DBGrid

[xenblaise]

  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]

  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]

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]

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]

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]

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]

 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]

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]

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]

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.