To do queries to TreeView nodes built from DB

[wp]

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][+][-]

1. procedure TForm1.PopulateTree;
2. var
3.   node: TTreeNode;
4.   id: PtrInt;
5.   field: TField;
6. begin
7.   TreeView1.Items.Clear;
8.   ZQuery1.DisableControls;
9.   try
10.     ZQuery1.First;
11.     while not ZQuery1.EoF do
12.     begin
13.       id := ZQuery1.FieldByName('ID').AsInteger;
14.       node := Treeview1.Items.AddChildObject(nil, ZQuery.FieldByName('Name').AsString, pointer(id));
15.       field := ZQuery1.FieldByName('Status');
16.       TreeView1.Items.AddChildObject(node, < some text >, field);
17.       field := ZQuery1.FieldByName('ExpiryDate');
18.       Treeview1.Items.AddChildObject(node, < some text >, field);
19.       field := ZQuery1.FieldByName('JoinDate');
20.       Treeview1.Items.AddChildObject(node, < some text >, field);
21.       node.Expanded := true;
22.       ZQuery1.Next;
23.     end;
24.   finally
25.     ZQuery1.EnableControls;
26.   end;
27. 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][+][-]

1. type
2.   TExtendedTreeNode = class(TTreeNode)
3.   private
4.     FRecordID: Integer;
5.   public
6.     property RecordID: Integer read FRecordID write FRecordID;
7.   end;

To make sure that the Tree creates nodes of this type you must provide a handler for the TOnCreateNodeClass event:

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

1. procedure TForm1.TreeView1CreateNodeClass(Sender: TObject; var NodeClass: TTreeNodeClass);
2. begin
3.   NodeClass := TExtendedTreeNode;
4. end;

Now, when you populate the tree you can set the RecordID directly for every node:

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

1. procedure TForm1.PopulateTree;
2. var
3.   topnode, node: TTreeNode;
4.   id: PtrInt;
5.   field: TField;
6. begin
7.   TreeView1.Items.Clear;
8.   ZQuery1.DisableControls;
9.   try
10.     ZQuery1.First;
11.     while not ZQuery1.EoF do
12.     begin
13.       id := ZQuery1.FieldByName('ID').AsInteger;
14.  
15.       field := ZQuery1.FieldByName('Name');
16.       topnode := Treeview1.Items.AddChildObject(nil, < some text >, field);
17.       (topNode as TExtendedTreeNode).RecordID := id;
18.  
19.       field := ZQuery1.FieldByName('Status');
20.       node := TreeView1.Items.AddChildObject(topnode, < some text >, field);
21.       (node as TExtendedTreeNode).RecordID := id;
22.  
23.  
24.       field := ZQuery1.FieldByName('ExpiryDate');
25.       node := Treeview1.Items.AddChildObject(topnode, < some text >, field);
26.       (node as TExtendedTreeNode).RecordID := id;
27.  
28.       field := ZQuery1.FieldByName('JoinDate');
29.       node := Treeview1.Items.AddChildObject(topnode, < some text >, field);
30.       (node as TExtendedTreeNode).RecordID := id;
31.  
32.       topnode.Expanded := true;
33.       ZQuery1.Next;
34.     end;
35.   finally
36.     ZQuery1.EnableControls;
37.   end;
38. 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]

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]

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]

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]

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]

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][+][-]

1. type
2.   TTreeNodeData = class
3.     RecordID: Integer;
4.     FieldName: String;
5.     constructor Create(AID: Integer; AFieldName: string);
6.   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][+][-]

1.     idField := SQLQuery1.FieldByName('ID');
2.     namefield := SQLQuery1.FieldByName('Name');
3.     ...
4.       id := idField.AsInteger;
5.       node := TreeView1.Items.AddChildObject(nil, nameField.AsString, TTreeNodeData.Create(id, nameField.FieldName));
6.  

[lander]

wp your last project looks really powerful!
Thank you, I'm studying it. I'll get back for feedback. 

[lander]

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][+][-]

1. if FPopupNode = nil then
2.     exit;
3.   if not (Sender is TMenuItem) then
4.     exit;
5.  
6.   // Get the field. It has been stored in the Tag of the menu item.
7.   field := TObject(TMenuItem(Sender).Tag) as TField;
8.   // Get current value in the Name field
9.   oldForename := field.AsString;
10.   // Query new name
11.   newForename := Inputbox('Change Forename', 'New forename:', oldForename);
12.   // Exit if Name was not changed.
13.   if newForename = oldForename then
14.     exit;
15.   // Write new name to database
16.   ZQuery1.Sql.Text:=('update students set forename=:newForename where student_no=?');
17.   ZQuery1.Params.ParamByName(newForename);    
18.   //field.AsString := newForename;
19.   ZQuery1.ExecQuery;
20.   // Update Name node in treeview
21.   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]

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]

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][+][-]

1. procedure TForm3.ChangeForenameHandler(Sender: TObject);
2. var
3.   newForename, oldForename: String;
4.   field: TField;
5. begin
6.   if FPopupNode = nil then
7.     exit;
8.   if not (Sender is TMenuItem) then
9.     exit;
10.  
11.   // Get the field. It has been stored in the Tag of the menu item.
12.   field := TObject(TMenuItem(Sender).Tag) as TField;
13.   // Get current value in the Name field
14.   oldForename := field.AsString;
15.   // Query new name
16.   newForename := Inputbox('Change Forename', 'New forename:', oldForename);
17.   // Exit if Name was not changed.
18.   if newForename = oldForename then
19.     exit;
20.   // Write new name to database
21.   ZQuery1.Sql.Text:=('update students set forename=' + QuotedStr(newForename) + 'where student_no=' + id.ToString);
22.   ZQuery1.ExecSql;
23.  
24.   ZQuery1.Sql.Text:=('select * from students');
25.   ZQuery1.Open;
26.   // Update Name node in treeview
27.   FindNodeOfField(FPopupNode, 'forename').Text := 'Forename: ' + newForename;
28.  
29. end;