[SOLVED} SQLite Zeos Update lazarus / 16-Databases ---------------------------------------------------------------------- Pe3s · 2022-04-20 17:20 Hi I have tables id, Name, Last_name when I load the table it does not show the id column 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 SELECT Name, Last_name FROM tableI am downloading the data to the form 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  LabeledEdit1.Text := ZQuery1.FieldByName('Name').AsString;  LabeledEdit2.Text := ZQuery1.FieldByName('Last_name').AsString;end; Updates data 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  ZQuery1.SQL.Clear;  ZQuery1.SQL.Text := 'UPDATE laski SET Name=:Name,Last_name=:Last_Name  WHERE id=:id';  ZQuery1.ParamByName('Name').AsString := LabeledEdit1.Text;  ZQuery1.ParamByName('Last_name').AsString := LabeledEdit2.Text;   ZQuery1.ExecSQL;   ZQuery1.Sql.Text := 'SELECT  Name, Last_name FROM table';  ZQuery1.Open;end; What do I need to fix to get the code to work ? ---------------------------------------------------------------------- af0815 · 2022-04-20 17:43 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT ID, Name, Last_name FROM laski ---------------------------------------------------------------------- Pe3s · 2022-04-20 18:26 Reading. Thanks ---------------------------------------------------------------------- Pe3s · 2022-04-20 18:49 If I read without id column 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 SELECT Name, LastName FROM tablehow to read id ? ---------------------------------------------------------------------- Zvoni · 2022-04-20 20:19 Quote from: Pe3s on April 20, 2022, 08:49:00 pmIf I read without id column 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 SELECT Name, LastName FROM tablehow to read id ? Easy! You don‘t! If it‘s not SELECTed, it‘s not available. Period. ---------------------------------------------------------------------- Pe3s · 2022-04-22 17:50 Thank you ---------------------------------------------------------------------- Pe3s · 2022-04-27 17:00 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][+][-]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  ZQuery1.SQL.Clear;  ZQuery1.SQL.Text := 'UPDATE base SET Opis=:Opis, Link=:Link, Kategoria=:Kategoria, E-Mail=:E-Mail WHERE id=:id';  ZQuery1.ParamByName('Opis').AsString := LabeledEdit1.Text;  ZQuery1.ParamByName('Link').AsString := LabeledEdit2.Text;  ZQuery1.ParamByName('Kategoria').AsInteger := StrToInt(LabeledEdit3.Text);  ZQuery1.ParamByName('E-Mail').AsString := ComboBox1.Items[ComboBox1.ItemIndex];  ZQuery1.ParamByName('id').AsInteger := iId;  ZQuery1.ExecSQL;   ZQuery1.SQL.Text := 'SELECT * FROM base';  ZQuery1.Open;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 · 2022-04-27 17:11 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 · 2022-04-27 18:01 Is this record update code correct? 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   ZQuery1.Edit;  ZQuery1.FieldByName('Opis').AsString := LabeledEdit1.Text;  ZQuery1.FieldByName('Kategoria').AsString := LabeledEdit2.Text;  ZQuery1.FieldByName('E-Mail').AsString := LabeledEdit3.Text;  ZQuery1.Post;  ZQuery1.ApplyUpdates;  MessageDlg('Informacja','Zapisano zmiany!', mtinformation, [mbOK], 0); ---------------------------------------------------------------------- af0815 · 2022-04-27 19:11 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 · 2022-04-28 06:52 Quote from: Pe3s on April 27, 2022, 07:00:09 pmMy 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 · 2022-04-28 07:53 Quote from: Pe3s on April 27, 2022, 08:01:01 pmIs this record update code correct? 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   ZQuery1.Edit;  ZQuery1.FieldByName('Opis').AsString := LabeledEdit1.Text;  ZQuery1.FieldByName('Kategoria').AsString := LabeledEdit2.Text;  ZQuery1.FieldByName('E-Mail').AsString := LabeledEdit3.Text;  ZQuery1.Post;  ZQuery1.ApplyUpdates;  MessageDlg('Informacja','Zapisano zmiany!', mtinformation, [mbOK], 0); Yes, on condition that: ZQuery1.SQL.Text := 'SELECT * FROM base'; Michał ---------------------------------------------------------------------- Zvoni · 2022-04-28 08:23 Quote from: miab3 on April 28, 2022, 09:53:20 amYes, on condition that: ZQuery1.SQL.Text := 'SELECT * FROM base'; Michał BLEEHHH!! Don't do "SELECT * FROM". Just..... don't ---------------------------------------------------------------------- Pe3s · 2022-04-28 10:35 I see that opinions are divided.  Then how can I solve the problem?  I can ask more experienced colleagues for an example. ---------------------------------------------------------------------- Zvoni · 2022-04-28 11:07 Quote from: Pe3s on April 28, 2022, 12:35:55 pmI 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT ID, Opis, Link, Kategoria, E-Mail FROM base Sideeffect: You even get to determine the "order" of the Columns ---------------------------------------------------------------------- Pe3s · 2022-04-28 12:26 @zvoni The select code will reload the database but will not select the edited line ---------------------------------------------------------------------- Zvoni · 2022-04-28 12:51 Quote from: Pe3s on April 28, 2022, 02:26:18 pm@zvoni The select code will reload the database but will not select the edited line And you didn't read the posts. I'm talking about your general SQL-Statement. Don't use SELECT * FROM Everything else like in your Post 8 should still work And in Post 10 i told you how to autoselect the changed Row, even if the Grid gets reloaded ---------------------------------------------------------------------- Pe3s · 2022-04-28 14:35 @Zvoni It seems easy to remember the id, update the database, reload the database and restore the remembered id. Only with the implementation is worse, lack of knowledge.But there are willingness to learn. ---------------------------------------------------------------------- Zvoni · 2022-04-29 07:58 Aircode 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 Var MyID:Integer;//LaterZQuery1.SQL.Text:='SELECT ID, Opis, Link, Kategoria, E-Mail FROM base';//Load Result into DBGrid // LaterMyID:=ZQuery1.FieldByName('ID').AsInteger;ZQuery1.Edit;  ZQuery1.FieldByName('Opis').AsString := LabeledEdit1.Text;  ZQuery1.FieldByName('Kategoria').AsString := LabeledEdit2.Text;  ZQuery1.FieldByName('E-Mail').AsString := LabeledEdit3.Text;  ZQuery1.Post;  ZQuery1.ApplyUpdates;  MessageDlg('Informacja','Zapisano zmiany!', mtinformation, [mbOK], 0);//Now if you reloadDBGrid1.DataSource.Dataset.Refresh;DBGrid1.DataS ource.Dataset.Locate('ID',MyID,[loCaseInsensitive]); ---------------------------------------------------------------------- miab3 · 2022-04-29 11:37 In the case of ZEOS, it is enough to: 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 ZQuery1.SQL.Text:='SELECT ID, Opis, Link, Kategoria, E-Mail FROM base'; // or 'select * from base'ZQuery1.Open;...ZQuery1.Edit;ZQuery1.FieldByName('Opis').AsString := LabeledEdit1.Text;ZQuery1.FieldByName('Kategoria').AsString := LabeledEdit2.Text;ZQuery1.FieldByName('E-Mail').AsString := LabeledEdit3.Text;ZQuery1.Post;MessageDlg('Informacja','Zapisano zmiany!', mtinformation, [mbOK], 0); Michał ---------------------------------------------------------------------- Zvoni · 2022-04-29 11:42 Quote from: miab3 on April 29, 2022, 01:37:23 pmCode: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i  // or 'select * from base' DON'T! Period! ---------------------------------------------------------------------- miab3 · 2022-04-29 11:52 What now?: 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 ZQuery1.SQL.Text:='SELECT ID, Opis, Link, Kategoria, E-Mail FROM base'; // or 'select * from base'ZQuery1.Open;...ZQuery1.Edit;ZQuery1.FieldByName('Opis').AsString := LabeledEdit1.Text;ZQuery1.FieldByName('Kategoria').AsString := LabeledEdit2.Text;ZQuery1.FieldByName('E-Mail').AsString := LabeledEdit3.Text;ZQuery1.Post;MessageDlg('Informacja','Zapisano zmiany!', mtinformation, [mbOK], 0);...ZQuery1.Edit;ZQuery1.FieldByName('tel').AsString := LabeledEdit4.Text;ZQuery1.Post;???Michał ---------------------------------------------------------------------- Zvoni · 2022-04-29 12:15 Quote from: miab3 on April 29, 2022, 01:52:17 pmWhat now?: 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  // or 'select * from base' Michał Don't advise/teach people to use "SELECT * FROM" in Production-Code It's a ticking time-bomb ---------------------------------------------------------------------- Pe3s · 2022-04-29 15:00 Thank you very much for your help. Have a nice day. Greetings