To do queries to TreeView nodes built from DB

[lander]

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

1. procedure TForm1.TreeView1ContextPopup(Sender: TObject; MousePos: TPoint;
2.   var Handled: Boolean);
3. begin
4.   if TreeView1.GetNodeAt(MousePos.X, MousePos.Y) = TreeView1.TopItem then
5.     Handled := True;
6. 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][+][-]

1. procedure TForm2.Button1Click(Sender: TObject);
2. var
3.   ID_UserVar, RecordDeptID: Integer;
4.   RootNode, DeptNode: TTreeNode;
5. begin
6.   ID_UserVar := 0;
7.   TreeView1Items.Clear;
8.   RootNode := nil;
9.   DeptNode := nil;
10.   Form1.ZQuery1.SQL.Text := 'select * from (SELECT student_no,surname,forename from students order by student_no asc) where ROWNUM <= 20';
11.   Form1.ZQuery1.Open;
12.   try
13.     Form1.ZQuery1.First;
14.     while not Form1.ZQuery1.Eof do
15.     begin
16.       RecordDeptID := Form1.ZQuery1.FieldByName('student_no').AsInteger;
17.       if (DeptNode = nil) or (RecordDeptID <> ID_UserVar) then
18.       begin
19.         DeptNode := TreeView1.Items.Add(RootNode, Form1.ZQuery1.FieldByName('surname').AsString);
20.         ID_1Var := RecordDeptID;
21.       end;
22.       TreeView1.Items.AddChild(DeptNode, Form1.ZQuery1.FieldByName('forename').AsString);
23.       Form1.ZQuery1.Next;
24.     end;
25.   finally
26.     Form1.ZQuery1.Close;
27.   end;
28. 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]

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]

Thank you wp, I'll study your code and get back for feedback!

[Hansvb]

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]

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]

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.

Just a copy&paste error. In MenuItem1Click, branch "if Sender = MenuItem1", replace the "Status" in BufDataset1.FieldByName('Status') by "Name":

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

1.   ...
2.   if (Sender = MenuItem1) then
3.   begin
4.     s := InputBox('Change name', 'New value', BufDataset1.FieldByName('Name').AsString);
5.     if s = BufDataset1.FieldByName('Name').AsString then
6.       exit;
7.     BufDataset1.Edit;
8.     BufDataset1.fieldByName('Name').AsString := s;   // "Status" was replaced by "Name"
9.     BufDataset1.Post;
10.     FPopupNode.Text := 'Name: ' + s;
11.   end else
12.   ...

[lander]

@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]

I 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]

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]

I modified my sample project so that the items in the popup menu are created dynamically.

[lander]

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]

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

1.  Result := ANode.Parent.GetFirstChild;

[lander]

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

1. procedure TForm1.FormCreate(Sender: TObject);
2. begin
3.   ZQuery1.Open;
4.   BufDataSet1.CopyFromDataset(ZQuery1, true);
5.   BufDataset1.Open;
6.   PopulateTree;
7. end;

Thanks to wp.
I'll keep learning from your code to make the menu working on this dynamic data.

[wp]

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]

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%.