To do queries to TreeView nodes built from DB lazarus / 31-LCL ---------------------------------------------------------------------- lander · 2021-10-07 15:53 I have a TreeView list automatically generated. Each node is a field got from a DB. Primary nodes are directly the rows identified by the name of a column, the second are the other columns with each value taken from the fields. The harder thing I'm encountering is to have the possibility to right click each sub-node and open various option (TPopUpMenu). I just want apply to each option a select/update etc. Is that possible? I have found this code on stackoverflow: 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.TreeView1ContextPopup(Sender: TObject; MousePos: TPoint;  var Handled: Boolean);begin  if TreeView1.GetNodeAt(MousePos.X, MousePos.Y) = TreeView1.TopItem then    Handled := True;end; But I don't know how to apply this code to each single node. Each primary node is a row, I'd want to manage the sub-nodes by queries. How I can recognize which node is selected? Because the queries cannot be random. I must specify a query for each field. Example: John Doe - Status: Subscribed - Expiry Date: 15/12/2022 - Join Date: 01/01/2021 Mark Zuck - Status: Unsubscribed - Expiry Date: 15/12/2022 - Join Date: 01/01/2021 Actually the TreeView fills from DB thanks to this code: 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 TForm2.Button1Click(Sender: TObject);var  ID_UserVar, RecordDeptID: Integer;  RootNode, DeptNode: TTreeNode;begin  ID_UserVar := 0;  TreeView1Items.Clear;  RootNode := nil;  DeptNode := nil;  Form1.ZQuery1.SQL.Text := 'select * from (SELECT student_no,surname,forename from students order by student_no asc) where ROWNUM <= 20';  Form1.ZQuery1.Open;  try    Form1.ZQuery1.First;    while not Form1.ZQuery1.Eof do    begin      RecordDeptID := Form1.ZQuery1.FieldByName('student_no').AsInteger;    if (DeptNode = nil) or (RecordDeptID <> ID_UserVar) then      begin      DeptNode := TreeView1.Items.Add(RootNode, Form1.ZQuery1.FieldByName('surname').AsString);        ID_1Var := RecordDeptID;      end;      TreeView1.Items.AddChild(DeptNode, Form1.ZQuery1.FieldByName('forename').AsString);      Form1.ZQuery1.Next;    end;  finally    Form1.ZQuery1.Close;  end;end;I'd like that right clicking on 'Status: Subcribed' for example, the system should know that I clicked on that recognizing the User of course. ---------------------------------------------------------------------- wp · 2021-10-07 22:48 At first you need something to identify each dataset record. In a simple dataset you can use the RecNo for it; in advanced datasets usually the RecNo is not reliably but you will have some kind of ID column then which usually is an integer. Whenever you add a record and field node to the tree add this ID to the Data field of the node, i.e. call TreeView.AddChildNodeObject, rather than AddChildNode; the Data value passed here is a pointer, you thus must cast the integer ID to a pointer (declare the ID as a PtrInt so that the type-cast works on 64-bit systems too). This way you know for each node the dataset record from which it was created. Look at the attached project which demonstrates this idea. A right- click on a node opens the context menu which is populated with the field values. Using the node's Data field the ID of the clicked record can be extracted. This is needed to navigate to this record. For this purpose, I set the dataset's RecNo to the ID in my example with a TBufDataset - in a more complex database you should call Dataset.Locate(ID...). And when you click on an item of the context menu, a simple editing procedure is called which allows the user to change the field value. Tree and dataset then are updated with the new value. The dbgrid is just shown to check whether the new values really appear in the dataset. ---------------------------------------------------------------------- lander · 2021-10-08 18:50 Thank you wp, I'll study your code and get back for feedback! ---------------------------------------------------------------------- Hansvb · 2021-10-09 07:54 Do you mean something like this? (See attachement) This is a treeview that is populated from a Sqllite database. Depending on the schema in which you log in, different queries will appear in the treeview. The node "Groen" (Green) is a parent and "Onderhoud" (Maintenance) is also a parent and all childs under "Onderhoud" (Maintenance) are queries. You can run this by double clicking on it. Or check a few queries and run them together. If that's what you mean then I can tell you how this is made. It was made with C# but the idea remains the same of course. I was planning to convert this to Lazarus Pascal, but it's not happening due to lack of time. ---------------------------------------------------------------------- lander · 2021-10-09 13:51 I'm trying to make it working like this: Right click or left click to a node or subnode. If that node or subnode ID has set a query to be executed, a menu appears with the query pre-written in the code. Just some variables inside the query. Instead of showing the same popup menu for each node, each node or subnode ID has a different popup menu to show. For example if I click the sub-node status, it should only show the popup menu voice 'Change to Unsubscribed' if it is subscribed of course, viceversa 'Change to Subscribed'. So I should exactly put an identity to each node- subnode to be sure after the righ click that it will show the right menu because I can't run a query to the wrong id! update students set status=X where id_student=Y Based to which subnode I click the program will know what will be X and what is Y. I'm trying wp code but I'm founding it hard to finish. All is to set an identity to each node, when I know how to identify that the rest will be easier. WP code presents some issue, in some case when I try to edit name it edit the status. So it's imprecise to recognize the correct cell. ---------------------------------------------------------------------- wp · 2021-10-09 14:25 Quote from: lander on October 09, 2021, 03:51:01 pmWP code presents some issue, in some case when I try to edit name it edit the status. So it's imprecise to recognize the correct cell. Just a copy&paste error. In MenuItem1Click, branch "if Sender = MenuItem1", replace the "Status" in BufDataset1.FieldByName('Status') by "Name": 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   ...  if (Sender = MenuItem1) then  begin    s := InputBox('Change name', 'New value', BufDataset1.FieldByName('Name').AsString);    if s = BufDataset1.FieldByName('Name').AsString then      exit;    BufDataset1.Edit;    BufDataset1.fieldByName('Name').AsString := s;   // "Status" was replaced by "Name"    BufDataset1.Post;    FPopupNode.Text := 'Name: ' + s;  end else   ... ---------------------------------------------------------------------- lander · 2021-10-09 14:33 @wp I applied your source code and elements on my project and popup is not showing but gives access violation. And yes it was the issue that caused the problem specified in my previous post. Edit: yes, your exact code/elements on my existing project make the popup to not showing up. Instead on your project it works. I don't understand what I miss. ---------------------------------------------------------------------- wp · 2021-10-09 15:45 Quote from: lander on October 09, 2021, 04:33:41 pmI don't understand what I miss. Me too. I just can guess... Did you look at the object inspector? My popup menu has a handler for the OnPopup event which must be assigned (PopupMenu1Popup), and the popup menu items share the same OnClick event handler (MenuItem1Click) (of course you can use separate handlers for each menu item as well). ---------------------------------------------------------------------- lander · 2021-10-09 15:54 I was just comparing the elements with two lazarus opened. I forgot a 'little' detail to see events infact! (my poor experience). Something is moving now but when I right click on a node I get 'Access violation'!. Edit: I understood. The point is that I have no menu item pre-inserted because I should get it from the DB. So I should set the event MenuItemClick after filling the TreeView from the db. Edit2: I have to try this: [RESOLVED] dynamically created menu and OnClick event ---------------------------------------------------------------------- wp · 2021-10-09 17:14 I modified my sample project so that the items in the popup menu are created dynamically. ---------------------------------------------------------------------- lander · 2021-10-09 17:23 Thanks for the comments in the project. I'll keep studying. Thank you. In TForm1.FindNodeOfField I had to comment {Result := ANode.GetFirstSibling} cause of error: identifier idents no member "GetFirstSibling". ---------------------------------------------------------------------- wp · 2021-10-09 17:39 Ah I should not write such demos with Laz trunk - Laz 2.0.12 or older do not have this. But you must assign a value, otherwise the application will not work properly when you click on the child node of a record. Use this instead: 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  Result := ANode.Parent.GetFirstChild; ---------------------------------------------------------------------- lander · 2021-10-09 18:27 Thank you wp I'm so happy right now. I have did some changes to fill automatically the BufDataSet from the DB. I'd put here how I did if it can be useful for someone: 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.FormCreate(Sender: TObject);begin  ZQuery1.Open;  BufDataSet1.CopyFromDataset(ZQuery1, true);  BufDataset1.Open;  PopulateTree;end; Thanks to wp. I'll keep learning from your code to make the menu working on this dynamic data. ---------------------------------------------------------------------- wp · 2021-10-09 20:26 I see here ZQuery and BufDataset. You can do the same as in my demo with the ZQuery alone, there is no need to use a BufDataset (I only like it for demos because it is simple and available in every Lazarus installation). ---------------------------------------------------------------------- lander · 2021-10-10 09:15 Yes it works with ZQuery also! How reliable is this method of selecting nodes/subnodes? Since I will execute updates, the system really know the exact field I selected? Interpretating the code seems that the application predict the mouse position so do not gives each node a unic ID to recognize what I clicked. Just to be sure 100%. ---------------------------------------------------------------------- wp · 2021-10-10 09:51 There are two things which must be available in the nodes: - the record associated with the clicked subtree (Name - Status/ExpiryDate/JoinDate) - the field associated with the clicked node I store in each node of the subtree the RecNo which is fine for BufDataset but probably not for TSQLQuery/TZQuery. Your database must contain a column, ideally an integer field, by which each record can be identified uniquely. But putting the ID value into the Data field of each node of the subtree leaves no space for how to find the field from the clicked node. I solved this by putting some field-identifying text into the node's Text property, e.g. "Expiry date: " - clicking on this node makes clear that this is for the ExpiryDate field. If you can follow this convention, my solution will work. If you cannot then you should store the associated field in the node's 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.PopulateTree;var  node: TTreeNode;  id: PtrInt;  field: TField;begin  TreeView1.Items.Clear;  ZQuery1.DisableControls;  try  ZQuery1.First;    while not ZQuery1.EoF do    begin      id := ZQuery1.FieldByName('ID').AsInteger;      node := Treeview1.Items.AddChildObject(nil, ZQuery.FieldByName('Name').AsString, pointer(id));      field := ZQuery1.FieldByName('Status');      TreeView1.Items.AddChildObject(node, < some text >, field);      field := ZQuery1.FieldByName('ExpiryDate');      Treeview1.Items.AddChildObject(node, < some text >, field);      field := ZQuery1.FieldByName('JoinDate');      Treeview1.Items.AddChildObject(node, < some text >, field);      node.Expanded := true;      ZQuery1.Next;    end;  finally    ZQuery1.EnableControls;  end;end;However, now the subnodes do no longer know to which record they belong. Therefore, in above snippet, I assumed that the "Name" node is always the top node and stored the record ID here. So, when you click on a subnode you must navigate to the top node and extract the record ID from there. The cleanest solution, however, is to create a new TTreeNode class with an extra field for the RecordID info: 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 type  TExtendedTreeNode = class(TTreeNode)  private    FRecordID: Integer;  public    property RecordID: Integer read FRecordID write FRecordID;  end;To make sure that the Tree creates nodes of this type you must provide a handler for the TOnCreateNodeClass event: 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.TreeView1CreateNodeClass(Sender: TObject; var NodeClass: TTreeNodeClass);begin  NodeClass := TExtendedTreeNode;end;Now, when you populate the tree you can set the RecordID directly for every node: 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.PopulateTree;var  topnode, node: TTreeNode;  id: PtrInt;  field: TField;begin  TreeView1.Items.Clear;  ZQuery1.DisableControls;  try    ZQuery1.First;    while not ZQuery1.EoF do    begin      id := ZQuery1.FieldByName('ID').AsInteger;       field := ZQuery1.FieldByName('Name');      topnode := Treeview1.Items.AddChildObject(nil, < some text >, field);      (topNode as TExtendedTreeNode).RecordID := id;       field := ZQuery1.FieldByName('Status');      node := TreeView1.Items.AddChildObject(topnode, < some text >, field);      (node as TExtendedTreeNode).RecordID := id;        field := ZQuery1.FieldByName('ExpiryDate');      node := Treeview1.Items.AddChildObject(topnode, < some text >, field);      (node as TExtendedTreeNode).RecordID := id;       field := ZQuery1.FieldByName('JoinDate');      node := Treeview1.Items.AddChildObject(topnode, < some text >, field);      (node as TExtendedTreeNode).RecordID := id;       topnode.Expanded := true;      ZQuery1.Next;    end;  finally    ZQuery1.EnableControls;  end;end;In this solution all nodes have the associated field in the node's Data, and the RecordID is available in the node's new RecordID property (however, you must cast the node to TExtendedTreeNode to get access to it). Please note that the code fragments shown were not tested and may contain typos and logical errors. But I hope you understood the principle. ---------------------------------------------------------------------- lander · 2021-10-10 11:47 TreeView filling works and so every node have their id. Thank you. Now topnode, node and id should be the public variable that I should use on the POPupMenu also, right? If yes I should move that in public. Because when the treeview is filled, if I select a node the system know the node unic identity number taken from db row. So the populatetree should share those variable to the popupmenu1popup because with the right click i'll check which node/subnode has been clicked. If is clicked the node 'Name' i'll only show "change name" (that open the inputbox) and i'll set query1.sql.text like 'update students set surname=:newName where student_no=3' (student_no is my 'id' variable). ---------------------------------------------------------------------- wp · 2021-10-10 13:34 Should the popupmenu be different for each node? I mean: When I right- click on the "Name" item, should the context menu only contain items related to the "Name" node, e.g. "Change Name..."? Similarly, when I right-click on the "Status" item" should the content menu only contain items related to the "Status" field, e.g. "Change Status to Subscribed" or "Change Status to Unsubscribed", depending on the current value of the Status field? etc. Or should there also be menu items affecting the entire record at every node submenu, such as "Add new record" or "Delete record"? Code: [Select] Context menu of "Name" node:   -------------------   | Change Name...   |   |------------------|   | New record       |   | Delete record    |   -------------------- Context menu of "Status" node:   ------------------------------   | Change Status to Suscribed |   |----------------------------|   | New record                 |   | Delete record              |   ------------------------------ Context menu of "Expiry" node   -------------------   | Change date...   |   |------------------|   | New record       |   | Delete record    |   ------------------- ---------------------------------------------------------------------- lander · 2021-10-10 15:25 Yes I'd prefer to make the context menu different for each node clicked. If it is clicked name it should only show menu based on that field. With your previous code I understood how to create a different handler for each item in the menu. I have to understand how to recognize the node id (id taken from db column>specified row) selected, to show a custom context menu. I'm trying to move node, topnode and id to public declarations and adding an if in the popup. -deleted- WP kindly show me how to start determining the popupmenu know which node field has been selected. I now know how to create the menu but not how to recognize the node selected (with unique id). ---------------------------------------------------------------------- lander · 2021-10-11 10:49 I re-read the entire topic. Your 2nd .zip contains the BufDataSet which then we modified with ZQuery for filling the treeview from the DB. Then with this post you added the function to set an ID to each node. After this addon, the function TForm1.FindNodeOfField still works? Because I'm trying to create a dynamic popupmenu based on the node ID taken from row. ---------------------------------------------------------------------- wp · 2021-10-11 15:57 Since now I know what the popup menu should look like, I rewrote the sample project with a fully dynamically created popupmenu. Moreover, I used a TSQLQuery for sqlite3, rather than a simple TBufDataset. From this it is probably easier for you to move to ZQuery (ATM, I don't have ZEOS my system). For association of nodes with records/field, I used yet another idea - I think this is less hacky than abusing the node's Data storing integers, and less confusing than introducing a new TreeNode type. In the new idea, I am adding a helper class, TTreeNodeData, which stores the record id and the fieldname for each node: 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 type  TTreeNodeData = class    RecordID: Integer;    FieldName: String;    constructor Create(AID: Integer; AFieldName: string);  end; The ID is a unique primary key field added to the dataset. When a tree node is created this is done by AddChildNodeObject(node, treenodedata): 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     idField := SQLQuery1.FieldByName('ID');    namefield := SQLQuery1.FieldByName('Name');    ...      id := idField.AsInteger;    node := TreeView1.Items.AddChildObject(nil, nameField.AsString, TTreeNodeData.Create(id, nameField.FieldName)); ---------------------------------------------------------------------- lander · 2021-10-12 20:23 wp your last project looks really powerful! Thank you, I'm studying it. I'll get back for feedback. ---------------------------------------------------------------------- lander · 2021-10-14 17:52 wp, the TreeView and PopUpMenu works perfectly in your project. In my project I converted it for ZQuery. It fills the TreeView. The problem born when I right click to show the PopUpMenu. Edit1: I don't believe it, today I booted the pc, without a new compile I run the exe and the popup works. It gives me access violation but the field is updated anyway. Gonna solve the access violation though. Misteries of Lazarus... Edit2: I maybe found the problem. It is on ChangeNameHandler procedure. Cause the Edit feature is not really supported by ZQuery (to confirm). Anyway I removed that to do the standard query usage to update the field but this is how I 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 if FPopupNode = nil then    exit;  if not (Sender is TMenuItem) then    exit;   // Get the field. It has been stored in the Tag of the menu item.  field := TObject(TMenuItem(Sender).Tag) as TField;  // Get current value in the Name field  oldForename := field.AsString;  // Query new name  newForename := Inputbox('Change Forename', 'New forename:', oldForename);  // Exit if Name was not changed.  if newForename = oldForename then    exit;  // Write new name to database  ZQuery1.Sql.Text:=('update students set forename=:newForename where student_no=?');  ZQuery1.Params.ParamByName(newForename);      //field.AsString := newForename;  ZQuery1.ExecQuery;  // Update Name node in treeview  FindNodeOfField(FPopupNode, 'forename').Text := newForename;   where student_no=? How I take the id? Edit3: Solved with this query: ZQuery1.Sql.Text:=('update students set forename=' + QuotedStr(newForename) + 'where student_no=' + id.ToString); The query is executed but I get an access violation with: // Update Name node in treeview   FindNodeOfField(FPopupNode, 'forename').Text := newForename; Edit4: I don't know how I solved (cause I did lot of changes to my addons/ZQuery code converting) but now everything works. As always all of this would not be possible without wp. Thanks bro. ---------------------------------------------------------------------- wp · 2021-10-14 21:19 Now I finally installed ZEOS and converted the demo project to ZEOS, it is working correctly. I cannot believe that ZEOS does not support the Edit method for its query. This is one of the most basic methods of a dataset descendant. And in fact, it works in my demo. If you prefer the corresponding SQL Update query, fine, but I am afraid this solution shadows the real issue in your code. ---------------------------------------------------------------------- lander · 2021-10-14 21:42 Dear WP I worked hard to fix my converting errors but as I updated my last post everything worked. I'm glad you were converting it for me. Anyway I'll enjoy your ZQuery convertion! Also, I am not sure ZQuery doesn't support 'Edit'. That was my supposing. This is now the handler (will confront with Your Zeos version): 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 TForm3.ChangeForenameHandler(Sender: TObject);var  newForename, oldForename: String;  field: TField;begin  if FPopupNode = nil then  exit;  if not (Sender is TMenuItem) then    exit;   // Get the field. It has been stored in the Tag of the menu item.  field := TObject(TMenuItem(Sender).Tag) as TField;  // Get current value in the Name field  oldForename := field.AsString;  // Query new name  newForename := Inputbox('Change Forename', 'New forename:', oldForename);  // Exit if Name was not changed.  if newForename = oldForename then    exit;  // Write new name to database  ZQuery1.Sql.Text:=('update students set forename=' + QuotedStr(newForename) + 'where student_no=' + id.ToString);  ZQuery1.ExecSql;   ZQuery1.Sql.Text:=('select * from students');  ZQuery1.Open;  // Update Name node in treeview  FindNodeOfField(FPopupNode, 'forename').Text := 'Forename: ' + newForename; end;