Recent

Author Topic: [SOLVED} SQLite Zeos Update  (Read 1862 times)

Pe3s

  • Full Member
  • ***
  • Posts: 246
[SOLVED} SQLite Zeos Update
« on: April 20, 2022, 07:20:06 pm »
Hi I have tables id, Name, Last_name
when I load the table it does not show the id column
Code: Pascal  [Select][+][-]
  1. SELECT Name, Last_name FROM table
I am downloading the data to the form
Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button3Click(Sender: TObject);
  2. begin
  3.   LabeledEdit1.Text := ZQuery1.FieldByName('Name').AsString;
  4.   LabeledEdit2.Text := ZQuery1.FieldByName('Last_name').AsString;
  5. end;

Updates data

Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button4Click(Sender: TObject);
  2. begin
  3.   ZQuery1.SQL.Clear;
  4.   ZQuery1.SQL.Text := 'UPDATE laski SET Name=:Name,Last_name=:Last_Name  WHERE id=:id';
  5.   ZQuery1.ParamByName('Name').AsString := LabeledEdit1.Text;
  6.   ZQuery1.ParamByName('Last_name').AsString := LabeledEdit2.Text;
  7.  
  8.   ZQuery1.ExecSQL;
  9.  
  10.   ZQuery1.Sql.Text := 'SELECT  Name, Last_name FROM table';
  11.   ZQuery1.Open;
  12. end;

What do I need to fix to get the code to work ?

« Last Edit: April 29, 2022, 05:00:22 pm by Pe3s »

af0815

  • Hero Member
  • *****
  • Posts: 1021
Re: SQLite Zeos Update
« Reply #1 on: April 20, 2022, 07:43:47 pm »
Look in the discussion here https://forum.lazarus.freepascal.org/index.php/topic,59065.0.html

In you select statement is no ID.

Code: Pascal  [Select][+][-]
  1. SELECT ID, Name, Last_name FROM laski
« Last Edit: April 20, 2022, 07:47:08 pm by af0815 »
regards
Andreas

Pe3s

  • Full Member
  • ***
  • Posts: 246
Re: SQLite Zeos Update
« Reply #2 on: April 20, 2022, 08:26:16 pm »
Reading. Thanks

Pe3s

  • Full Member
  • ***
  • Posts: 246
Re: SQLite Zeos Update
« Reply #3 on: April 20, 2022, 08:49:00 pm »
If I read without id column
Code: Pascal  [Select][+][-]
  1. SELECT Name, LastName FROM table
how to read id ?
« Last Edit: April 20, 2022, 08:55:27 pm by Pe3s »

Zvoni

  • Hero Member
  • *****
  • Posts: 1335
Re: SQLite Zeos Update
« Reply #4 on: April 20, 2022, 10:19:52 pm »
If I read without id column
Code: Pascal  [Select][+][-]
  1. SELECT Name, LastName FROM table
how to read id ?
Easy!
You don‘t!
If it‘s not SELECTed, it‘s not available. Period.
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Pe3s

  • Full Member
  • ***
  • Posts: 246
Re: SQLite Zeos Update
« Reply #5 on: April 22, 2022, 07:50:46 pm »
Thank you  :)

Pe3s

  • Full Member
  • ***
  • Posts: 246
Re: [SOLVED] SQLite Zeos Update
« Reply #6 on: April 27, 2022, 07:00:09 pm »
Hi, I would like to implement a fix to update a record in SQLite + ZeosLib database, I mean is it possible to make the selection in DBGrid stay and the data will be updated?
Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button4Click(Sender: TObject);
  2. begin
  3.   ZQuery1.SQL.Clear;
  4.   ZQuery1.SQL.Text := 'UPDATE base SET Opis=:Opis, Link=:Link, Kategoria=:Kategoria, E-Mail=:E-Mail WHERE id=:id';
  5.   ZQuery1.ParamByName('Opis').AsString := LabeledEdit1.Text;
  6.   ZQuery1.ParamByName('Link').AsString := LabeledEdit2.Text;
  7.   ZQuery1.ParamByName('Kategoria').AsInteger := StrToInt(LabeledEdit3.Text);
  8.   ZQuery1.ParamByName('E-Mail').AsString := ComboBox1.Items[ComboBox1.ItemIndex];
  9.   ZQuery1.ParamByName('id').AsInteger := iId;
  10.   ZQuery1.ExecSQL;
  11.  
  12.   ZQuery1.SQL.Text := 'SELECT * FROM base';
  13.   ZQuery1.Open;
  14. end;
My code works but it reloads the whole database from scratch which selects the row from the beginning. How can I fix the code?

af0815

  • Hero Member
  • *****
  • Posts: 1021
Re: SQLite Zeos Update
« Reply #7 on: April 27, 2022, 07:11:41 pm »
If you deal with ExecSQL and Open the code is not fixable. if you write "ZQuery1.SQL.Text := 'SELECT * FROM base';" nobody can see your coloums. (And for me it is a nogo)

One way is to use the Query correct. Look for Query.Edit, Query.post, Query.delete,.... 

BTW: Read https://wiki.freepascal.org/SqlDBHowto and https://wiki.freepascal.org/Working_With_TSQLQuery
« Last Edit: April 27, 2022, 07:13:34 pm by af0815 »
regards
Andreas

Pe3s

  • Full Member
  • ***
  • Posts: 246
Re: SQLite Zeos Update
« Reply #8 on: April 27, 2022, 08:01:01 pm »
Is this record update code correct?
Code: Pascal  [Select][+][-]
  1.   ZQuery1.Edit;
  2.   ZQuery1.FieldByName('Opis').AsString := LabeledEdit1.Text;
  3.   ZQuery1.FieldByName('Kategoria').AsString := LabeledEdit2.Text;
  4.   ZQuery1.FieldByName('E-Mail').AsString := LabeledEdit3.Text;
  5.   ZQuery1.Post;
  6.   ZQuery1.ApplyUpdates;
  7.   MessageDlg('Informacja','Zapisano zmiany!', mtinformation, [mbOK], 0);

af0815

  • Hero Member
  • *****
  • Posts: 1021
Re: SQLite Zeos Update
« Reply #9 on: April 27, 2022, 09:11:40 pm »
It looks correct, if the ID ( i think that is the primary key) is enclosed in the select statement and it is a simple table.
regards
Andreas

Zvoni

  • Hero Member
  • *****
  • Posts: 1335
Re: [SOLVED] SQLite Zeos Update
« Reply #10 on: April 28, 2022, 08:52:52 am »
My code works but it reloads the whole database from scratch which selects the row from the beginning. How can I fix the code?
Andreas' answer not withstanding:
To autoselect the row you changed after the reload:
Remember the ID before you update, do the Update, do the Reload, Locate the ID in the Grid/DataSet, Done
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

miab3

  • Full Member
  • ***
  • Posts: 144
Re: SQLite Zeos Update
« Reply #11 on: April 28, 2022, 09:53:20 am »
Is this record update code correct?
Code: Pascal  [Select][+][-]
  1.   ZQuery1.Edit;
  2.   ZQuery1.FieldByName('Opis').AsString := LabeledEdit1.Text;
  3.   ZQuery1.FieldByName('Kategoria').AsString := LabeledEdit2.Text;
  4.   ZQuery1.FieldByName('E-Mail').AsString := LabeledEdit3.Text;
  5.   ZQuery1.Post;
  6.   ZQuery1.ApplyUpdates;
  7.   MessageDlg('Informacja','Zapisano zmiany!', mtinformation, [mbOK], 0);

Yes, on condition that:
ZQuery1.SQL.Text := 'SELECT * FROM base';

Michał

Zvoni

  • Hero Member
  • *****
  • Posts: 1335
Re: SQLite Zeos Update
« Reply #12 on: April 28, 2022, 10:23:07 am »
Yes, on condition that:
ZQuery1.SQL.Text := 'SELECT * FROM base';

Michał
BLEEHHH!!
Don't do "SELECT * FROM".
Just..... don't
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Pe3s

  • Full Member
  • ***
  • Posts: 246
Re: SQLite Zeos Update
« Reply #13 on: April 28, 2022, 12:35:55 pm »
I see that opinions are divided.  Then how can I solve the problem?  I can ask more experienced colleagues for an example.

Zvoni

  • Hero Member
  • *****
  • Posts: 1335
Re: SQLite Zeos Update
« Reply #14 on: April 28, 2022, 01:07:00 pm »
I see that opinions are divided.  Then how can I solve the problem?  I can ask more experienced colleagues for an example.
Explicitly state the Columns you want
Code: SQL  [Select][+][-]
  1. SELECT ID, Opis, Link, Kategoria, E-Mail FROM base
  2.  
Sideeffect: You even get to determine the "order" of the Columns
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

 

TinyPortal © 2005-2018