[SOLVED] Two DBGrids showing the same content lazarus / 16-Databases ---------------------------------------------------------------------- Gizmo · 2017-09-26 16:17 I don't know if this is an obvious question or not. I'm hoping someone can set me straight. I'm making great progress converting my program from StringGrids to SQLIte and DBGrids. However, as some of you know, I've hit a few hurdles on the way that you have helped me with. My latest one is this : My program has two forms. I have a DBGrid instance in each form. When the user presses a button in one form, one table of the SQLite DB is populated with data and then displayed in DBGrid1 in Form1. When the user presses another button in another form, another different table of the same SQLite DB is populated with data as well (different data) and then displayed in DBGrid2 in Form2. However, if the user then goes from Form2 back to Form1, the data that was shown in DBGrid2 is ALSO shown in DBGrid1. In other words, the initial display of data from button press one has been replaced with the display of data from button 2. I'm guessing this is something to do with the whole initial setup of the initial database components (SQLQuery1, SQLTransaction1, DataSource1 and SQLIte3Connection1) which I have copied fairly blindly I admit without fully understanding how they all interlink. Could one of you tell me where I am going wrong? Two example routines are below which give you the gist I hope. The problem is then replicated throughout most of my other routines. 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 // MainForm has one DBGrid in itprocedure TfrmSQLiteDBases.UpdateGridFILES(Sender: TObject);  begin    try    SQLQuery1.Close;    SQLQuery1.SQL.Text := 'SELECT * FROM TBL_FILES';    SQLite3Connection1.Connected := True;  SQLTransaction1.Active := True;    SQLQuery1.Open;     // Allow the DBGrid to view the results of our query    DataSource1.DataSet := SQLQuery1;    MainForm.RecursiveDisplayGrid1.DataSource := DataSource1;    MainForm.RecursiveDisplayGrid1.AutoFillColumns := true;    except    on E: EDatabaseError do    begin      MessageDlg('Error','A database error has occurred. Technical error message: ' + E.Message,mtError,[mbOK],0);    end;  end;end; // frmDisplayGrid1 is the second form that shows different data. // After execution of this, the results also appear in MainForm.RecursiveDisplayGrid1procedure TfrmSQLiteDBases.UpdateGridCOPYTAB(Sender: TObject);  begin    try    SQLQuery1.Close;    SQLQuery1.SQL.Text := 'SELECT * FROM TBL_COPY';  SQLite3Connection1.Connected := True;    SQLTransaction1.Active := True;    SQLQuery1.Open;     // Allow the DBGrid to view the results of our query    DataSource1.DataSet := SQLQuery1;    frmDisplayGrid1.RecursiveDisplayGrid_COPY.DataSource := DataSource1;  frmDisplayGrid1.RecursiveDisplayGrid_COPY.AutoFillColumns := true;  except    on E: EDatabaseError do    begin      MessageDlg('Error','A database error has occurred. Technical error message: ' + E.Message,mtError,[mbOK],0);    end;  end;end; ---------------------------------------------------------------------- GAN · 2017-09-26 19:24 Hi, the problem may be that you are using the same DataSource1in both grids and querys. ---------------------------------------------------------------------- taazz · 2017-09-26 20:19 a bit of background info on the controls. sqlquery: it communicates with the data server executes commands and retrieves responses that then keeps in the client's memory. when you  deactivate the control (sqlquery1.Active := false or sqlquery1.close, same thing) the control frees the data retrieved from the server and returns the memory to the application for farther use. TField : holds the definition of a field, its size, type etc, this information is used by the TQuery control to decode the data send from the server and find and manage the value of a field. TDatasource: sits between the dataset (tSQLquery, tzquery, some other dataset/framework) this enables you to change the underline database completely including the framework from SQLDB to zeos data objects to IBX or anything you like with out touching the GUI part of your application it usually reside on the same form/frame as the GUI it serves. TConnection : The communication layer between dataset and data server, tcp/ip, disk, mail shots, compression, json, IPC etc, everything is usually encoded/decoded here and then given to the dataset as complete records ready to be processed. Data aware controls dbgrid, dbedits etc. Those use a simple interface to communicate with the dataset (through the tdatasource), get the values, get informed when the data have changed for any reason, set values etc. Keep in mind that dataset allows only a single record to be active at any given time it can buffer multiple records internally but it only allows access to a single record. That should clear any misunderstandings you might have if not then ask more questions if yes then answer me this. Is the observed behaviour expected or not and why? ---------------------------------------------------------------------- Gizmo · 2017-09-27 09:30 Thanks Taaz for the insight, which is perhaps the most detailed I've read so far. But, alas, I am still confused.  QuoteThat should clear any misunderstandings you might have if not then ask more questions Sorry! I'm guessing then the two key elements here are the DataSource, which you say should be one per form, and I guess the SQLQuery? So I tried creating a secondary Datasource that I linked to the same SQLite3Connection and a secondary SQLQuery instance that I linked to the Datasource. I then I adjusted my code for all the routines that involved my second database table (TBL_COPY) to use the second SQLQuery and Datasource. The result then was an empty grid in the second form :-( So I guess I'm still not grasping how this all interlinks. Sorry. In my head, theres a database over there with data in it - several tables full of rows of data. To get that data, you use SELECT statements and to put data in it, you use INSERT statements, which are forumlated by SQLQuery So then there is the SQQuery which directs what values are retrieved from what parts of the database and what values are put in it, and where. Then there is an SQLite3Connection, which is a mechanical system for joining the database line to the program - the means in which data is sent to and retrieved from the database. Then there is the SQLTransaction, which I still don't get. Then there is the DBGrid which is used to render the result from SQLQuery.Open. So whatever it gets back from the database, it displays in the appropriate grid. So if you have two separate queries and two separate grids, data from the same database can be directed to each I thought (following the replies from you two). But in my test, that has not happened. So either I'm on the right lines and just missed a final step somewhere or I am on the wrong lines! :-( So then I tried creating a second SQLTransaction. Set the SQLIte3Connection as database. But it generated an error : "cannot start a transaction within a transaction" at one of my other routines (an INSERT procedure, with an SQLQuery.ExecSQL line generates it). So I tried setting the SQLTransaction1.Active to false, and SQLTransaction2.Active := True; but same error :-( I'm getting very disheartened. I thought implementing SQLIte was supposed to be quite straight forward. The full code, if it helps people see the wider context of what I am doing, is here : https://github.com/tedsmith/quickhash/blob/v3Branch/dbases_sqlite.pas  The two procedures below show what I have just tried (which is not committed to GIT). 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 // MainForm has one DBGrid in itprocedure TfrmSQLiteDBases.UpdateGridFILES(Sender: TObject);  begin    try    SQLQuery1.Close;    SQLQuery1.SQL.Text := 'SELECT * FROM TBL_FILES';    SQLite3Connection1.Connected := True;  SQLTransaction1.Active := True;    SQLQuery1.Open;     // Allow the DBGrid to view the results of our query    DataSource1.DataSet := SQLQuery1;    MainForm.RecursiveDisplayGrid1.DataSource := DataSource1;    MainForm.RecursiveDisplayGrid1.AutoFillColumns := true;    except    on E: EDatabaseError do    begin      MessageDlg('Error','A database error has occurred. Technical error message: ' + E.Message,mtError,[mbOK],0);    end;  end;end; // frmDisplayGrid1 is the second form that shows different data.// After execution of this, the results also appear in MainForm.RecursiveDisplayGrid1procedure TfrmSQLiteDBases.UpdateGridCOPYTAB(Sender: TObject);  begin    try    SQLQuery2.Close;    SQLQuery2.SQL.Text := 'SELECT * FROM TBL_COPY';  SQLite3Connection1.Connected := True;    SQLTransaction1.Active := True;  / And tried with a second transaction object, called SQLTransaction2, same problem    SQLQuery2.Open;     // Allow the DBGrid to view the results of our query    DataSource2.DataSet := SQLQuery2;    frmDisplayGrid1.RecursiveDisplayGrid_COPY.DataSource := DataSource2;    frmDisplayGrid1.RecursiveDisplayGrid_COPY.AutoFillColumns := true;    except    on E: EDatabaseError do    begin      MessageDlg('Error','A database error has occurred. Technical error message: ' + E.Message,mtError,[mbOK],0);    end;  end;end; ---------------------------------------------------------------------- Gizmo · 2017-09-27 09:48 Taaz...I wish you'd managed to get this diagram in the FPC\Lazarus wiki! It helps a lot. (http://forum.lazarus.freepascal.org/index.php?topic=19431.0) . EXCEPT....SQL3LiteConnection has the property 'Transaction', which you can (and must?) specify to an SQLTransaction object. BUT, you can only choose one SQLTransaction per connection. But your diagram shows two SQLTransactions for one connection? SQLTransaction1 and SQLTransaction2 can both be set to SQLite3Connection, but SQL3LiteConnection can only have one SQLTransaction. So I am still confused. (NOTE: Others users struggling...be advised there is a good (but still quite confusing) explanation here http://wiki.freepascal.org/SQLdb_Programming_Reference ) Basically, I have one database with 5 tables in it. My program has 5 tabs in it. Each tab is responsible for putting data in and reading from the corresponding table of the database. So Tab2 = Table2. All I want to achieve is for the data in Tab2 or Tab3 to show the data from the table of Table2 or Table3. As you can see in the screenshot of my database form, I now have two SQLQueries, 2 Transactions, and two datasources with one SQLite3Connection. I'd like all the Components1 for one tab form of my program to deal with TableX, and all the Components2 for the second tab form of my program to deal with TableXX and so on. And what I really need to find is a really simple (like an explanation for a child) explanation of how these 4 elements all bind together. And how you can use them to query ONE database with SEVERAL tables and have the results of those table queries output to different forms.  This must be what most people do with them, surely? ---------------------------------------------------------------------- taazz · 2017-09-27 15:50 Quote from: Gizmo on September 27, 2017, 11:48:31 amTaaz...I wish you'd managed to get this diagram in the FPC\Lazarus wiki! It helps a lot. (http://forum.lazarus.freepascal.org/index.php?topic=19431.0) . EXCEPT....SQL3LiteConnection has the property 'Transaction', which you can (and must?) specify to an SQLTransaction object. BUT, you can only choose one SQLTransaction per connection. But your diagram shows two SQLTransactions for one connection? SQLTransaction1 and SQLTransaction2 can both be set to SQLite3Connection, but SQL3LiteConnection can only have one SQLTransaction. So I am still confused. This is the default transactions it is used when the SQLQuery is not assigned its own transaction only. Quote from: Gizmo on September 27, 2017, 11:48:31 am (NOTE: Others users struggling...be advised there is a good (but still quite confusing) explanation here http://wiki.freepascal.org/SQLdb_Programming_Reference ) Basically, I have one database with 5 tables in it. My program has 5 tabs in it. Each tab is responsible for putting data in and reading from the corresponding table of the database. So Tab2 = Table2. All I want to achieve is for the data in Tab2 or Tab3 to show the data from the table of Table2 or Table3. As you can see in the screenshot of my database form, I now have two SQLQueries, 2 Transactions, and two datasources with one SQLite3Connection. I'd like all the Components1 for one tab form of my program to deal with TableX, and all the Components2 for the second tab form of my program to deal with TableXX and so on. And what I really need to find is a really simple (like an explanation for a child) explanation of how these 4 elements all bind together. And how you can use them to query ONE database with SEVERAL tables and have the results of those table queries output to different forms.  This must be what most people do with them, surely? I think I simplified as much as possible but here goes again. 1) connection talks to the database and pushes packets back and forth as needed. 2) query decodes the packets to records and field values manages the data in memory and raises events that allow data controls to refresh their view when things change 3) transaction used by a query to control when the data changes are pushed to the server and persisted on disk and how (commit, rollaback commitretaining etc) 4)datasource is the bridge between your datacontrol and your query. So instead of hunting down every single data control and change its dataset property to the new dataset you only change the datasource.dataset property and all controls see the new data at once. Maybe a proxy is a better analogy. Now I have no idea what you do wrong and since I do not have access to a sample to take a part find attached a small sample that can show multiple grids on a form it is not, by any means, a complete demonstration it has its problems and I tried to comment on them in the code but it is a working sample that gives us a base line to expand. I can't add more details because they are server based for example some servers do not support multiple active transactions on a single connection some do not support nested transactions add isolation levels and what is considered an active transaction might change from server to server and you have a very fluid environment that can't be generalized easily. I have small experience with sqlite my self so there is a moderate time investment in testing each reply before posting. ---------------------------------------------------------------------- taazz · 2017-09-27 17:41 Quote from: Gizmo on September 27, 2017, 11:30:30 amThe full code, if it helps people see the wider context of what I am doing, is here : https://github.com/tedsmith/quickhash/blob/v3Branch/dbases_sqlite.pas  erm sorry missed this one I'll download the project and try it, probably tomorrow. ---------------------------------------------------------------------- Gizmo · 2017-09-27 22:12 Taaz...many many thanks for the demo! I've had a quick look before bed (which was fatal!) but I'll look closer when I have more time. It certainly helps! I need to take some time to see how you have linked those components together. It's certainly more concise than my system. If you do get time to check out what I've done, I'd very much welcome your guidance. You'll no doubt spot lots of areas to not only fix my issue but probably offer advice on best practice. As I say, I am new to the whole DB interaction so following online guidance fairly blindly. ---------------------------------------------------------------------- taazz · 2017-09-28 02:07 Quote from: Gizmo on September 28, 2017, 12:12:40 amTaaz...many many thanks for the demo! I've had a quick look before bed (which was fatal!) but I'll look closer when I have more time. It certainly helps! I need to take some time to see how you have linked those components together. It's certainly more concise than my system. If you do get time to check out what I've done, I'd very much welcome your guidance. You'll no doubt spot lots of areas to not only fix my issue but probably offer advice on best practice. As I say, I am new to the whole DB interaction so following online guidance fairly blindly. fatal? you killed a squirel with your screams or something ---------------------------------------------------------------------- Gizmo · 2017-10-02 19:03 Ha...I meant because once you start looking at something, before you know it, and hour has passed! So I've spent some time looking at your example, and excellent as it is, I am still unclear how the same dataconnection is enabling two different grid displays to be active at the same time. In my project, there are (now) two seemingly independent grids. Grid1 and Grid2. Grid1 is populated by the result of SQLQuery1. Grid2 is populated by the result of SQLQuery2. Both SQLQuerys use SQLTransaction1. Both SQLQuerys use SQLiteDatabase1. Both work fine in that they both display their respective content from each query, which is pulling data from two different tables of the same database. Therefore, why, when I return to DBGrid1 after executing the SQLQuery2 and seeing the result of SQLQuery2 in DBGrid2, do I find the content of DBGrid2 in DBGrid1!? I must be missing something hugely obvious but not obvious to me - you have clearly made it work very well, but I don't see where. It is making no sense to my somewhat limited logic. In my head, the result of SQLQurey1 that is shown in DBGrid1 should stay there and not change again until SQLQuery1 is executed again. I can't see why or how the execution of SQLQuery2 in DBGrid2 is making DBGrid1 change ---------------------------------------------------------------------- taazz · 2017-10-02 20:08 Quote from: Gizmo on October 02, 2017, 09:03:06 pmHa...I meant because once you start looking at something, before you know it, and hour has passed! So I've spent some time looking at your example, and excellent as it is, I am still unclear how the same dataconnection is enabling two different grid displays to be active at the same time. I just downloaded everything from your repository (https://github.com/tedsmith/quickhash/tree/v3Branch). I can see the one dbgrid in the main form only one sqlquery in the frmsqlitebases form. assuming that everything compiles correctly what I have to do to see the problem? Keep in mind I'm on windows if that plays any role. ---------------------------------------------------------------------- Gizmo · 2017-10-02 21:25 Thanks taaz...you'll need to add\install the HashLib4Pascal package, the TZTimeControl package (both in the git repo), and the lazdbexport package via the Lazarus package IDE to compile it. You'll also need the SQLite3.dll for windows, from http://sqlite.org/2017/sqlite-dll- win32-x86-3200100.zip and copy the dll to the project folder. Then go to files tab. Choose a folder with 5 or 6 small files in. The grid will populate with data about those files. Then go to the Copy tab. Choose a folder with a few files in let hand tree view then choose a destination folder from the tree view on the right and hit go. You'll be shown a new grid with data about those files. Now go back to the files tab and the same data will be in that grid which formerly held data about the files you looked initially. Bear in mind that when I said above "I have two data sources and two sql queries " etc, the additions are not in that git repository. That said, even with the additions on my local working folder the behaviour is the same. I mention it just so you don't go looking for two instances and thinking "he said he has two but he only has one". The point is that the version in git behaves the same way as my local non git committed version. I was just trying something out after studying your example and decided to just try and separate it but the problem persist Appreciate you helping me with this. ---------------------------------------------------------------------- rvk · 2017-10-03 08:43 I can't compile it. (tried it last week but it didn't work then either) First... I only have a project1_linux.lpi to open in Lazarus (no project1.lpi). Ok, I'm on Windows but will try that one. Then I get: Quoteproject1.lpr(10,10) Fatal: Cannot find lazdbexport used by project1. Check if package lazdbexport is in the dependencies of the Project Inspector. Yes, I did install lazdbexport. But you might want to add lazdbexport as dependency in your project manager so it's mandatory to install it and the path will be included automatically. Then I got Quotedbases_sqlite.pas(718,2) Fatal: Cannot open include file "dbases_sqlite.lrs"on the line 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 {$I dbases_sqlite.lrs}I can't get past that because I don't have that file. If I comment out that line the compile works but running the program gives this immediately in a black screen. But that was to be expected if I comment random things out Quote[FORMS.PP] ExceptionOccurred   Sender=EClassNotFound   Exception=Class "TStringGrid" not found   Stack trace:   $0045AE77   $00459017   $004595B2   $004541CB   $0045A8EA   $004DAF96   $004DADC9   $004DA118   $0041D8E3   $0041D7CC   $0041FC78   $004268F6 TApplication.HandleException Class "TStringGrid" not found   Stack trace:   $0045AE77   $00459017   $004595B2   $004541CB   $0045A8EA   $004DAF96   $004DADC9   $004DA118   $0041D8E3   $0041D7CC   $0041FC78   $004268F6 ---------------------------------------------------------------------- taazz · 2017-10-03 08:50 I have also added in the required packages the lclbase package and when ever I comment out the {$I xxxxx.lrs} I also add the {$R *.lfm} right below the implementation delcaraion, after any uses clause it might have. In some cases I had to remove the lrs to overcome the error tstringgrid class not found. ---------------------------------------------------------------------- rvk · 2017-10-03 09:11 Yes, that worked. I removed ALL the .lrs includes and replaced them with the {$R under implementation. Why are there {$I xxxxx.lrs} lines? Did a very old version of Lazarus once work that way? I also noticed all the forms are not included in the Project Manager. So I can't switch to them easily with Ctrl+F12. ---------------------------------------------------------------------- Gizmo · 2017-10-03 09:23 Gents Sorry for the inconvenience. I've added the dbases_sqlite.lrs file to the v3branch repository. So if you git pull, it should work hopefully. (It works OK on my system) Re the LPI file...You should be able to open the project by double clicking the "project1.lpr" file, and then an LPI file will be saved when you save the project in your environment. I seem to think I excluded LPI files because aren't they customised on a per user basis, whereas LPR file is a global project file? The lazdbexport package, along with several others, is added as a dependancy already. See screenshot. Or am I mis-understanding? Re the forms and F12. If I have the main unit file tab open and I press F12, I get the main form. If I then hyperclick the appropriate other units, like uDisplayGrid etc, and press F12, I get that form as well. I thought that is how Lazarus works but are you saying there's a way to make them all available with a single press of F12? QuoteWhy are there {$I xxxxx.lrs} lines? I don't know...the project was originally started in 2011 when I had even less of an idea of what I was doing than now! So I really don't know, but it was started with a version of Lazarus from 2011. ---------------------------------------------------------------------- taazz · 2017-10-03 09:44 Quote from: rvk on October 03, 2017, 11:11:36 amYes, that worked. I removed ALL the .lrs includes and replaced them with the {$R under implementation. yes this is one of the clean ups that needs to be done. But I'm going to leave it so  gizmo can do it as a learning experience. Quote from: rvk on October 03, 2017, 11:11:36 amWhy are there {$I xxxxx.lrs} lines? Did a very old version of Lazarus once work that way? Yes there was a time where resource files where not supported in linux/macos and other *nix OS, only windows has native support for them, lazarus used its own resource management as you might notice the files are loaded on the units initialization section from the exe not when the form is instantiated. Then the FPC team build their own resource compiler that gave them the ability to use them in all supported OSes and the lrs file was phased out, some old application might still use them mostly as a consequence of a "if it is not broken don't fix it" attitude. Quote from: rvk on October 03, 2017, 11:11:36 amI also noticed all the forms are not included in the Project Manager. So I can't switch to them easily with Ctrl+F12. yeah that is a bit of a problem but it only has a hand full of forms so not a big deal. ---------------------------------------------------------------------- rvk · 2017-10-03 10:06 Your RecursiveDisplayGrid1 is connected to frmSQLiteDBases.DataSource1. In TfrmSQLiteDBases.UpdateGridFILES() you update SQLQuery1 to hold TBL_FILES. That is all correct. But... then in TfrmSQLiteDBases.UpdateGridCOPYTAB() you do this: 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 TfrmSQLiteDBases.UpdateGridCOPYTAB(Sender: TObject);  begin    try    SQLQuery1.Close;    SQLQuery1.SQL.Text := 'SELECT * FROM TBL_COPY';  SQLite3Connection1.Connected := True;    SQLTransaction1.Active := True;    SQLQuery1.Open;     // Allow the DBGrid to view the results of our query    DataSource1.DataSet := SQLQuery1;So you are closing SQLQuery1, assigning a new query and opening it again (which is still connected to DataSource1). In this post: http://forum.lazarus.freepascal.org/index.php/topic,38416.msg260854.ht ml#msg260854 you are working with DataSource2 for UpdateGridCOPYTAB(). Where is that code??? That is the reason your GridFILES suddenly hold the copy files. You are still using one datasource and one sqlquery. ---------------------------------------------------------------------- Gizmo · 2017-10-03 10:19 RVK Thanks for looking. Reply 11 : Quote Bear in mind that when I said above "I have two data sources and two sql queries " etc, the additions are not in that git repository. That said, even with the additions on my local working folder the behaviour is still the same. I mention it just so you don't go looking for two instances and thinking "he said he has two but he only has one". The point is that the version in git behaves the same way as my local non git committed version. I was just trying something out after studying your example and decided to just try and separate it but the problem persist So even if I had a secondary SQLQuery and DataSource and use those for the second display grid (UpdateGridCOPYTAB) I still have the same problem. Because I also assumed that was the problem initially, which is why I tried adding them. But when I realised they didn't work (because it seems to wipe out the data in the first DBGrid), I didn't bother pushing it up to the Git branch. So what I'm saying is if you do : 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 TfrmSQLiteDBases.UpdateGridCOPYTAB(Sender: TObject);  begin    try    SQLQuery2.Close;    SQLQuery2.SQL.Text := 'SELECT * FROM TBL_COPY';  SQLite3Connection1.Connected := True;    SQLTransaction1.Active := True;    SQLQuery2.Open;     // Allow the DBGrid to view the results of our query    DataSource2.DataSet := SQLQuery2;    frmDisplayGrid1.RecursiveDisplayGrid_COPY.DataSource := DataSource2;  frmDisplayGrid1.RecursiveDisplayGrid_COPY.AutoFillColumns := true;  except    on E: EDatabaseError do    begin      MessageDlg('Error','A database error has occurred. Technical error message: ' + E.Message,mtError,[mbOK],0);    end;  end;end;       it then wipes out the data from the first grid in FileS tab. What I need is for the results shown in Grid1 to remain there if the user goes off to other tabs and computes other values that show in other Grids. So DBGrid1 storing DataA and DBGrid2 storing DataB and so on.  Quote    QuoteYes, that worked. I removed ALL the .lrs includes and replaced them with the {$R under implementation. yes this is one of the clean ups that needs to be done. But I'm going to leave it so  gizmo can do it as a learning experience. Done :-) ---------------------------------------------------------------------- rvk · 2017-10-03 10:49 Quote from: Gizmo on October 03, 2017, 12:19:16 pmSo even if I had a secondary SQLQuery and DataSource and use those for the second display grid (UpdateGridCOPYTAB) I still have the same problem. Because I also assumed that was the problem initially, which is why I tried adding them. But when I realised they didn't work (because it seems to wipe out the data in the first DBGrid), The problem is not the same anymore. You first problem was that the content was the same. Now the problem is that when you go back to FileS, your grid is empty (like you say). That's probably due to the fact you close SQLQuery1 somewhere or close/end the transaction (which automatically closes the query). The reason for that is the following: You have this in TMainForm.Button8CopyAndHashClick. 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 frmSQLiteDBases.EmptyDBTable('TBL_COPY', frmDisplayGrid1.RecursiveDisplayGrid_COPY);But in TfrmSQLiteDBases.EmptyDBTable() you use SQLQuery1 to empty out TBL_COPY. So you close the query for the FileS grid. You should create a dynamic SQLQuery in EmptyDBTable() which you use. Don't reuse SQLQuery1 or SQLQuery2. The EmptyDBTable could look like this (although I don't like using SQLQuery1 to take the database but you can change that): 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 TfrmSQLiteDBases.EmptyDBTable(TableName : string; DBGridName : TDBGrid);var  MyQuery: TSQLQuery;begin  MyQuery := TSQLQuery.Create(nil);  try    try      MyQuery.DataBase := SQLQuery1.Database;      MyQuery.Transaction := SQLQuery1.Transaction;      MyQuery.SQL.Text := 'DELETE FROM ' + TableName;      if SQLite3Connection1.Connected then      begin      SQLTransaction1.Active := True;        MyQuery.ExecSQL;        SQLTransaction1.CommitRetaining; // Retain transaction is important here      end;    except      on E: EDatabaseError do      begin      MessageDlg('Error','A database error has occurred. Technical error message: ' + E.Message,mtError,[mbOK],0);      end;    end;  finally  MyQuery.Free;  end;end;  Then another problem. You have this EVERYWHERE in dbases_sqlite.pas: 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 TfrmSQLiteDBases.SortByHash(DataSource : TDBGrid);begin try  SQLQuery1.Close;  SQLQuery1.SQL.Text := 'SELECT Id, Filename, FilePath, HashValue, FileSize ' + So you CLOSE SQLQuery in almost every function. I'm sure you call one of those functions that close SQLQuery1 when you do the copy-action. ---------------------------------------------------------------------- rvk · 2017-10-03 11:41 Ok, I traced it a little further and got it working. Besides the fact you need to use SQLQuery2 and DataSource2 in UpdateGridCOPYTAB() you also needed to use a separate TSQLQuery in EmptyTable like I already showed you. After that... you have a call to frmSQLiteDBases.WriteCOPYValuesToDatabase in your ProcessDir(). In there you have the SQLQuery1.Close which I mentioned. It should be SQLQuery2. Even if you fix that it still doesn't work. You have this: 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 frmSQLiteDB ases.SQLTransaction1.Commit;frmSQLiteDBases.UpdateGridCOPYTAB(nil);But if you commit a transaction, ALL tables connected to that transaction will be closed automatically. You need to do a CommitRetaining. In that case the connected tables stay open. So 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 frmSQLiteDB ases.SQLTransaction1.CommitRetaining;frmSQLiteDBases.UpdateGridCOPYTAB (nil);but also in CommitCount(); you should use CommitRetaining. and everywhere you have Commit; you need to change it to CommitRetaining. There is an option for TSQLQuery in trunk, sqoKeepOpenOnCommit but I've never used that. I'm also not sure from what version it is available. You should still go through all the functions in dbases_sqlite and fix the SQLQuery1.Close because there are a lot that are wrong in that place. For some functions you pass a DBGrid. Like 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 SortBySourceFilename(DataSource : TDBGrid);Besides the fact it should be DBGrid: TDBGrid or DataSource: TDatasource you don't use that paramter. But if you use DBGrid.Datasource.Dataset instead of SQLQuery1, you can make the function a lot cleaner. Actually the whole dbases_sqlite shouldn't contain any SQLQuery1 text. You create all the TSQLQuerys and TDatasources you need and connect it designtime to the correct DBGrid. After that you can pass the TDBGrid to all those functions and you can use the TDBGrid.Datasource.Dataset to open/close etc. (I hope I explain this clearly.) ---------------------------------------------------------------------- Gizmo · 2017-10-03 12:15 Thankyou RVK! It seems CommitRetaining was the key!! I have made most of your suggestions, but I did them one at a time. I'd already adjusted the DeleteTable procedure (because it's better anyway regardless of whether it fixed the problem) but found I still expereinced the same behaviour. But as soon as I replaced all the commit lines to commitretaining as you suggested, it worked as intended. I'll have to read up more on that - all the demo's show using commit so I'd never heard of commitretaining. And I realise the code is poorly written. I'm scrambling around in new territory really so still finding my feet. I will try to improve it as you also suggest. The only thing that has confused me is this : if I remove all the SQLQuery1.Close statements, none of my right click options work. For example, sort by filename, sort by path etc. The grid just stays as it is displayed originally. Yet if I put them back in, they all work again. So I'm confused by that. But I guess I need to read up more on it or its an issue for another thread. As you say, the original question is now answered...thank you. SOLVED! ---------------------------------------------------------------------- rvk · 2017-10-03 12:23 Quote from: Gizmo on October 03, 2017, 02:15:33 pmI'll have to read up more on that - all the demo's show using commit so I'd never heard of commitretaining. In most demos the dataset can be closed after committing to the database. So a commit is sufficient. In your case the dataset needs to be kept open. And because a commit also closes the dataset you need CommitRetaining (or sqoKeepOpenOnCommit option). https://www.freepascal.org/docs- html/3.0.0/fcl/sqldb/tsqltransaction.commitretaining.html QuoteCommitRetaining commits an active transaction. The changes will be irreversably written to the database. After this, the transaction is still active. To commit data and deactivate the transaction, execute Commit instead. Glad it's solved and you can explore the wonders of databases further ---------------------------------------------------------------------- rvk · 2017-10-03 12:31 Quote from: Gizmo on October 03, 2017, 02:15:33 pmThe only thing that has confused me is this : if I remove all the SQLQuery1.Close statements, none of my right click options work. For example, sort by filename, sort by path etc. The grid just stays as it is displayed originally. Yet if I put them back in, they all work again. So I'm confused by that. But I guess I need to read up more on it or its an issue for another thread. You have this in the popup menu: 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 TMainForm.MenuItem_SortByFilenameClick(Sender: TObject);begin  RecursiveDisplayGrid1.Clear;  frmSQLiteDBases.SortByFilename(RecursiveDisplayGrid1);end;So you already pass the RecursiveDisplayGrid1 TDBGrid to the SortByFilename() function. But if I look at your SortByFilename() it DOESN'T use that parameter at all. First of all you need to fix the parameter name. It's not Datasource but DBGrid. Then you can use something like this: So you don't use SQLQuery but you use the dataset that's connected to the passed DBGrid. 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 // Used by the FILES tab to sort entries by filename alphabeticallyprocedure TfrmSQLiteDBases.SortByFileName(DBGrid : TDBGrid);begin  try    DBGrid.DataSource.Dataset.Close; // <--- see what I did here, you don't use SQLQuery1 but the query connected to the grid    TSQLQuery(DBGrid.DataSource.Dataset).SQL.Text := 'SELECT Id, Filename, FilePath, HashValue, FileSize ' +                          'FROM TBL_FILES ORDER BY FileName';    SQLite3Connection1.Connected := True;    SQLTransaction1.Active := True;    DBGrid.DataSource.Dataset.Open;     // There is NO need to connect the query again to the DBGrid because it's already connected     // Allow the DBGrid to view the results of our query    // DataSource1.DataSet := SQLQuery1;    // MainForm.RecursiveDisplayGrid1.DataSource := DataSource1;    // MainForm.RecursiveDisplayGrid1.AutoFillColumns := true;       DBGrid.AutoFillColumns := true; // maybe this one     except      on E: EDatabaseError do      begin        MessageDlg('Error','A database error has occurred. Technical error message: ' + E.Message,mtError,[mbOK],0);      end;    end;end; You can change all those function NOT to use SQLQuery1 but the connected DBGrid.Datasource.Dataset. ---------------------------------------------------------------------- Gizmo · 2017-10-03 16:25 RVK Great stuff...thanks once again for all your input and effort. Don't think I'd have ever resolved that without you and Taaz. I've made the suggested changes - I'm starting to understand better how it all links now I think. All of my routines now mostly just use DBGrid as the property and it all works much better now, and is easier to follow. I've committed my latest changes to Git in case you are interested. ---------------------------------------------------------------------- rvk · 2017-10-04 10:37 I've checked out the new version real quick. I have some remarks (which you may consider or not). These are just some observations and suggestions. *) In project options > Compiler options > "Config and Target" the "Win32 gui app" isn't checked. This will result ALWAYS in a black screen behind the gui. If you check it, that black screen doesn't appear on Windows. *) The lazdbexport is still not in dependencies. Maybe you didn't update the complete github-project yet. *) The .pas files are also still not in the project manager (but again, maybe nog updated yet). *) While adding the .pas files, I also noticed that you didn't use a consistent naming convention for the unit-names. For example you did use frmXXXX.pas for frmaboutunit.pas but used uprogress.pas for the progress form and just diskspecification.pas for the frmTechspecs. If you are going to clean your project it might be useful to name them all frmxxx.pas and TfrmXXX as class. A clear naming convention is very useful. *) Your project is named project1_linux.lpi but your executable is quickhash.exe. It might be more clear to name your project quickhash.lpi too (just do a Project > "Save project as" for this) After that you can remove project1.* and project_linux.* *) The Copy Results form is always in the middle of my two screens (so half on both screens). You have Position of form frmDisplayGrid1 set at poDesktopCenter. I can tel you... NEVER EVER USE poDesktopCenter !! On a two display system this is very annoying. Use poScreenCenter or some other value. *) Make all the DBGrids have dgRowSelect in the options. It's more intuitive to have the complete row selected instead of just one cell. *) The grid for RecursiveDisplayGrid1 has a very large width for ID. You might want to use a procedure which resizes all the columns to a more appropriate width. Now sometimes FilePath doesn't even fit while ID is very wide. You did use AutoFillColumns := true; but maybe dgAutoSizeColumns is a better choice (see screenshot and code below). *) I would change the order of fields for RecursiveDisplayGrid1. FilePath isn't really useful here because path is already at the top there. When you move filesize and hashvalue before filename it's more clear too. See attached screenshot. 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 SQLQuery1.SQL.Text := 'SELECT id, filesize, hashvalue, filename FROM TBL_FILES';(that should be changed inUpdateGridFILES() as well as SortByFileName etc then too) *) To get dgAutoSizeColumns you can't use AutoFillColumns anymore so it's a trade off. 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 // FILES tab update grid routineprocedure TfrmSQLiteDBases.UpdateGridFILES(Sender: TObject);begin  try    SQLQuery1.Close;    SQLQuery1.SQL.Text := 'SELECT id, filesize, hashvalue, filename FROM TBL_FILES';    SQLite3Connection1.Connected := True;    SQLTransaction1.Active := True;     MainForm.RecursiveDisplayGrid1.Options := MainForm.RecursiveDisplayGrid1.Options + [dgAutoSizeColumns];     SQLQuery1.Open;     // Allow the DBGrid to view the results of our query    // DataSource1.DataSet := SQLQuery1;                        // <--- no need, done in designtime    // MainForm.RecursiveDisplayGrid1.DataSource := DataSource1;// <--- no need, done in designtime    // MainForm.RecursiveDisplayGrid1.AutoFillColumns := true;  // <--- no need, done in designtime   except    on E: EDatabaseError do    begin      MessageDlg('Error','A database error has occurred. Technical error message: ' + E.Message,mtError,[mbOK],0);    end;  end;end; *) There is no need to set DataSource1.DataSet and MainForm.RecursiveDisplayGrid1.DataSource anymore because the are already set in designtime and they should never change during the program. You can remove those line from UpdateGridCOPYTAB too. *) Rename SQLQuery1, SQLQuery2, DataSource1 and DataSource2 to something more useful so you are clear as to what grid they belong. For example sqlFiles, dsFiles, sqlCopy and dsCopy are more clear. You get something like this: 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 // FILES tab update grid routineprocedure TfrmSQLiteDBases.UpdateGridFILES(Sender: TObject);begin  try    sqlFiles.Close;    sqlFiles.SQL.Text := 'SELECT id, filesize, hashvalue, filename FROM TBL_FILES';    SQLite3Connection1.Connected := True;    SQLTransaction1.Active := True;    MainForm.RecursiveDisplayGrid1.Options := MainForm.RecursiveDisplayGrid1.Options + [dgAutoSizeColumns];    sqlFiles.Open;  except    on E: EDatabaseError do    begin      MessageDlg('Error','A database error has occurred. Technical error message: ' + E.Message,mtError,[mbOK],0);    end;  end;end;I don't think you even need the SQLite3Connection1.Connected and SQLTransaction1.Active lines because they should already be open but they can't hurt. That's it for now ---------------------------------------------------------------------- Gizmo · 2017-10-07 07:49 Sorry for the delay in replying RVK. It took me a few evenings to progress on a few areas and I attempted to incorporate most of your suggestions. The only one that caused me a problem and resulted me having to resrot to a backup from the day before was changing the names of the various units and forms. I agree with you; it would be an improvement, but Lazarus kept reporting that I was trying to use "an illegal unit name" (or "an illegal form name", or something...it was a few days ago now). So I gave up on that one. But I think most of your other suggestions have been incorporated. As for the apparant missing pas files and the lazdbexport not being in the dependancies...that still confuses me. They are there in the git repository and when I load it on my system, they are all listed in the Project Inspector. Anyway, I very much appreciate your time and expertise in helping me fix the initial problem and for your guidance on project refinement and improvement. I don't work with fellow programmers and very much work solo as a hobby so its useful to have people like you and Taaz and others like Molly in the forums who generously donate their expertise. ---------------------------------------------------------------------- taazz · 2017-10-07 08:35 Quote from: Gizmo on October 07, 2017, 09:49:14 amSorry for the delay in replying RVK. It took me a few evenings to progress on a few areas and I attempted to incorporate most of your suggestions. The only one that caused me a problem and resulted me having to resrot to a backup from the day before was changing the names of the various units and forms. I agree with you; it would be an improvement, but Lazarus kept reporting that I was trying to use "an illegal unit name" (or "an illegal form name", or something...it was a few days ago now). So I gave up on that one. But I think most of your other suggestions have been incorporated. That sounds like you renamed them from outside the IDE ee explorer and you did not change the unit name inside the code. the file name and then unit name must be the same, or better yet open the unit in the IDE and select file\save as and when you are asked to delete the old unit say yes. Quote from: Gizmo on October 07, 2017, 09:49:14 amAs for the apparant missing pas files and the lazdbexport not being in the dependancies...that still confuses me. They are there in the git repository and when I load it on my system, they are all listed in the Project Inspector. that sounds like you two are looking at different applications. You are opening the application1.lpi and rvk opens the one with the "linux" in its name. ---------------------------------------------------------------------- rvk · 2017-10-07 08:49 Quote from: taazz on October 07, 2017, 10:35:06 amthat sounds like you two are looking at different applications. You are opening the application1.lpi and rvk opens the one with the "linux" in its name. Wait, there is an application1.lpi I only found one .lpi file in https://github.com/tedsmith/quickhash/tree/v3Branch and that was project1_linux.lpi. Unless application1.lpi isn't uploaded to the branche. That's why all those files need to be cleaned up because there also was a project1.lpr and project1.lrs but no project1.lpi. But there was a project1_linux.lpi but no project1_linux.lpr or project1_linux.lrs. So it was really confusing. I thought you always need to open the .lpi. That's why I suggested to save the project again as quickhash and Lazarus will create a .lpi, a .lpr etc with that name and you can delete all the project1*.* ones. Edit: I see that there now is a quickhash.lpi and quickhash.lpr from an hour ago so I guess those are the correct ones. The project1*.* still need to be deleted to prevent confusion. ---------------------------------------------------------------------- rvk · 2017-10-07 09:01 Not sure if the one on the branche is the really last version but for me it doesn't even run. You have this in your project: 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   Application.CreateForm(TMainForm, MainForm);  Application.CreateForm(TfrmDisplayGrid1, frmDisplayGrid1);But in the FormCreate of the mainform you try to set the Width of frmDisplayGrid what isn't created yet (because TMainForm is created first). So you need to change the order in which the forms are created.