[SOLVED} SQLite Zeos Update

[Pe3s]

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 ?

[af0815]

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

[Pe3s]

Reading. Thanks

[Pe3s]

If I read without id column

Code: Pascal  [Select][+][-]

1. SELECT Name, LastName FROM table

how to read id ?

[Zvoni]

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.

[Pe3s]

Thank you 

[Pe3s]

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]

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

[Pe3s]

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]

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.

[Zvoni]

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

[miab3]

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]

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

Michał

BLEEHHH!!
Don't do "SELECT * FROM".
Just..... don't

[Pe3s]

I see that opinions are divided.  Then how can I solve the problem?  I can ask more experienced colleagues for an example.

[Zvoni]

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