Recent

Author Topic: [SOLVED] SQLIte Update and Delete record  (Read 6304 times)

Pe3s

  • Full Member
  • ***
  • Posts: 145
[SOLVED] SQLIte Update and Delete record
« on: December 11, 2021, 05:43:37 pm »
Hello, what do I have to correct in the code to make the record editing work.
Code: Pascal  [Select][+][-]
  1. rocedure TForm1.Button4Click(Sender: TObject);
  2. begin
  3.   SQLQuery1.Clear;
  4.   SQLQuery1.SQL.Text := 'UPDATE base SET Record_1= :Record_1, Record_2= :Record_2, Record_3= :Record_3 WHERE id= Form1.Caption';
  5.   SQLQuery1.Params.ParamByName('Record_1').Value := LabeledEdit1.Text;
  6.   SQLQuery1.Params.ParamByName('Record_2').Value := LabeledEdit2.Text;
  7.   SQLQuery1.Params.ParamByName('Record_3').Value := LabeledEdit3.Text;
  8.   SQLQuery1.ExecSQL;
  9. end;

How to write erasing a record?

« Last Edit: December 12, 2021, 08:00:32 pm by Pe3s »

paweld

  • Sr. Member
  • ****
  • Posts: 368
Re: SQLIte Update and Delete record
« Reply #1 on: December 11, 2021, 08:42:30 pm »
Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button4Click(Sender: TObject);
  2. begin
  3.   //update record
  4.   SQLQuery1.SQL.Text := 'UPDATE test_table SET column_1=:col1, column_2=:col2, column_3=:col3 WHERE id=:id';
  5.   SQLQuery1.ParamByName('col1').AsString := LabeledEdit1.Text;
  6.   SQLQuery1.ParamByName('col2').AsString := LabeledEdit2.Text;
  7.   SQLQuery1.ParamByName('col3').AsString := LabeledEdit3.Text;
  8.   SQLQuery1.ParamByName('id').AsString := Form1.Caption;
  9.   SQLQuery1.ExecSQL;
  10.   //delete record
  11.   SQLQuery1.SQL.Text := 'DELETE FROM test_table WHERE id=:id';
  12.   SQLQuery1.ParamByName('id').AsString := Form1.Caption;
  13.   SQLQuery1.ExecSQL;
  14. end;
Best regards
paweld

Pe3s

  • Full Member
  • ***
  • Posts: 145
Re: SQLIte Update and Delete record
« Reply #2 on: December 12, 2021, 11:57:44 am »
What could be the reason why the data is not updated and deleted?

GetMem

  • Hero Member
  • *****
  • Posts: 3741
Re: SQLIte Update and Delete record
« Reply #3 on: December 12, 2021, 12:42:52 pm »
What 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

  • Full Member
  • ***
  • Posts: 145
Re: SQLIte Update and Delete record
« Reply #4 on: December 12, 2021, 06:57:34 pm »
whether such a code needs to be improved ?
Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button3Click(Sender: TObject);
  2. begin
  3.   SQLQuery1.SQL.Text := 'DELETE FROM testbase WHERE id=:id';
  4.   SQLQuery1.ParamByName('id').AsString := Form1.Caption;
  5.   SQLQuery1.ExecSQL;
  6.   SQLTransaction1.Commit;
  7.   begin
  8.     SQLQuery1.Close;
  9.     SQLQuery1.SQL.Text := 'SELECT * FROM testbase';
  10.     SQLQuery1.ExecSQL;
  11.     SQLQuery1.Open;
  12.   end;
  13. end;
  14.  

GetMem

  • Hero Member
  • *****
  • Posts: 3741
Re: SQLIte Update and Delete record
« Reply #5 on: December 12, 2021, 07:46:20 pm »
@Pe3s

For insert, update, delete use SQLQuery.ExecSQL, for select SQLQuery.Open:
Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button3Click(Sender: TObject);
  2. begin
  3.   SQLQuery1.SQL.Text := 'DELETE FROM testbase WHERE id=:id';
  4.   SQLQuery1.ParamByName('id').AsString := Form1.Caption;
  5.   SQLQuery1.ExecSQL;
  6.   SQLTransaction1.Commit;
  7.  
  8.   SQLQuery1.Close;
  9.   SQLQuery1.SQL.Text := 'SELECT * FROM testbase';  
  10.   SQLQuery1.Open;
  11. end;

Pe3s

  • Full Member
  • ***
  • Posts: 145
Re: [SOLVED] SQLIte Update and Delete record
« Reply #6 on: December 12, 2021, 08:00:42 pm »
OK I understand, Thank you

Gizmo

  • Hero Member
  • *****
  • Posts: 816
Re: [SOLVED] SQLIte Update and Delete record
« Reply #7 on: December 21, 2021, 05:32:27 pm »
Quote
For 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.
Lazarus 2.0.12 and fpc 3.2.0 - Linux Mint 19 LTS, Windows 10 64 and Mac OSX Big Sur
Useful Page to remember : http://wiki.freepascal.org/Cross_compiling#From_Linux_x64_to_Linux_i386

Pe3s

  • Full Member
  • ***
  • Posts: 145
Re: [SOLVED] SQLIte Update and Delete record
« Reply #8 on: December 21, 2021, 06:11:29 pm »
Thank you, I remember

 

TinyPortal © 2005-2018