[SOLVED] SQLIte Update and Delete record

[Pe3s]

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?

[paweld]

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;

[Pe3s]

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

[GetMem]

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]

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]

@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]

OK I understand, Thank you

[Gizmo]

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.

[Pe3s]

Thank you, I remember