[SOLVED] SQLIte Update and Delete record
lazarus / 16-Databases

----------------------------------------------------------------------
Pe3s · 2021-12-11 16:43

Hello, what do I have to correct in the code to make the record
editing work.
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i rocedure
TForm1.Button4Click(Sender: TObject);begin  SQLQuery1.Clear; 
SQLQuery1.SQL.Text := 'UPDATE base SET Record_1= :Record_1, Record_2=
:Record_2, Record_3= :Record_3 WHERE id= Form1.Caption'; 
SQLQuery1.Params.ParamByName('Record_1').Value := LabeledEdit1.Text; 
SQLQuery1.Params.ParamByName('Record_2').Value := LabeledEdit2.Text; 
SQLQuery1.Params.ParamByName('Record_3').Value := LabeledEdit3.Text; 
SQLQuery1.ExecSQL;end;
How to write erasing a record?

----------------------------------------------------------------------
paweld · 2021-12-11 19:42

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button4Click(Sender: TObject);begin  //update record 
SQLQuery1.SQL.Text := 'UPDATE test_table SET column_1=:col1,
column_2=:col2, column_3=:col3 WHERE id=:id'; 
SQLQuery1.ParamByName('col1').AsString := LabeledEdit1.Text; 
SQLQuery1.ParamByName('col2').AsString := LabeledEdit2.Text; 
SQLQuery1.ParamByName('col3').AsString := LabeledEdit3.Text; 
SQLQuery1.ParamByName('id').AsString := Form1.Caption; 
SQLQuery1.ExecSQL;  //delete record  SQLQuery1.SQL.Text := 'DELETE
FROM test_table WHERE id=:id';  SQLQuery1.ParamByName('id').AsString
:= Form1.Caption;  SQLQuery1.ExecSQL;end;

----------------------------------------------------------------------
Pe3s · 2021-12-12 10:57

What could be the reason why the data is not updated and deleted?

----------------------------------------------------------------------
balazsszekely · 2021-12-12 11:42

Quote from: Pe3s on December 12, 2021, 11:57:44 amWhat could be the
reason why the data is not updated and deleted?
Because you only did a logical transaction. In order to physically
apply the changes to the database, you have call
SQLTransaction1.CommitRetaining or at least set
SQLQuery1->Options->sqoAutoCommit

----------------------------------------------------------------------
Pe3s · 2021-12-12 17:57

whether such a code needs to be improved ?
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button3Click(Sender: TObject);begin  SQLQuery1.SQL.Text :=
'DELETE FROM testbase WHERE id=:id'; 
SQLQuery1.ParamByName('id').AsString := Form1.Caption; 
SQLQuery1.ExecSQL;  SQLTransaction1.Commit;  begin   
SQLQuery1.Close;    SQLQuery1.SQL.Text := 'SELECT * FROM testbase';  
SQLQuery1.ExecSQL;    SQLQuery1.Open;  end;end;

----------------------------------------------------------------------
balazsszekely · 2021-12-12 18:46

@Pe3s

For insert, update, delete use SQLQuery.ExecSQL, for select
SQLQuery.Open:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button3Click(Sender: TObject);begin  SQLQuery1.SQL.Text :=
'DELETE FROM testbase WHERE id=:id'; 
SQLQuery1.ParamByName('id').AsString := Form1.Caption; 
SQLQuery1.ExecSQL;  SQLTransaction1.Commit;   SQLQuery1.Close; 
SQLQuery1.SQL.Text := 'SELECT * FROM testbase';    
SQLQuery1.Open;end;

----------------------------------------------------------------------
Pe3s · 2021-12-12 19:00

OK I understand, Thank you

----------------------------------------------------------------------
Gizmo · 2021-12-21 16:32

QuoteFor insert, update, delete use SQLQuery.ExecSQL, for select
SQLQuery.Open
Gizmo hurries off and does a quick Ctrl+F throughout his entire SQL
function stack to check he has followed this great ground rule.
Fortunately, he has. But a good thing to remember.

----------------------------------------------------------------------
Pe3s · 2021-12-21 17:11

Thank you, I remember