I need basic help with DB component interaction

[yes]

Hej,

I need some basic help with the interaction of the visual DB components, maybe a hint for what to reread.

Situation:
I want to display data in a DBGrid. When selected I want to display dependent information in a second DBGrid. It is 1:n, e.g. a user and a list of associated notes (by a foreign key in the notes table). I use SQLite. The basic part works due to the nice tutorials in the wiki.

Idea:
SQLiteConnection, SQLTransaction, SqlQuery1, DataSource1 for DBGrid1. Event OnDataChange in DataSource1 fires; then I execute SqlQuery2 for DataSource2 for DBGrid2 for the dependent data. Of course no second connection or transaction is used.

Problem:
The event is called but the dependent data is loaded only once (at first call). The data for the first call is displayed correctly in the DBGrid2 but no changes are shown for subsequent calls.

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

1. procedure TForm1.DataSource1DataChange(Sender: TObject; Field: TField);
2. begin
3.   SqlNotiz.Sql.Text:='select * from notes n where n.id_user = :user';
4.   SqlNotiz.Params.ParamByName('user').AsInteger:=DataSource1.DataSet.Fields[0].AsInteger;
5.   SqlNotiz.Open;
6.   Label1.Caption:='Changed to '+DataSource1.DataSet.Fields[0].AsString;
7. end;
8.  

It's obvious that I miss something basic like refreshing the sql statement or properly closing it. I would be thankful for any hints (also about hints how to do it differently if the whole approach is done in other ways normally or better).

[GAN]

Assuming SqlNotiz is your datset2, you may try this:

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

1. procedure TForm1.DataSource1DataChange(Sender: TObject; Field: TField);
2. var
3.   iUser:Interger;
4. begin
5.   iUser:=DataSource1.DataSet.Fields[0].AsInteger;
6.   SqlNotiz.Close;
7.   SqlNotiz.Sql.Text:='select * from notes n where n.id_user = :iuser';
8.   SqlNotiz.Params.ParamByName('user').AsInteger:=iUser;
9.   SqlNotiz.Open;
10.   Label1.Caption:='Changed to '+IntToStr(iUser);
11. end;

And yes, you need to close first, that's why the first time it works fine and then it doesn't.

[cdbc]

Hi
Did a quick search on /gockle/ "master-detail in sqlite and freepascal" and these 2 are the first hits:
https://wiki.freepascal.org/TSqlite3_Master_Detail_Example
https://wiki.freepascal.org/MasterDetail
If you're more into videos, here's one about Delphi, but that's practically the same: https://www.youtube.com/watch?v=UmZYVB8n8bc
HTH
Regards Benny

[yes]

Thank you for your help.

Yes, I forgot to change the localized name for the post in that case. It works now.

Of course I could have sworn that I tried closing it...

[yes]

Hi
Did a quick search on /gockle/ "master-detail in sqlite and freepascal" and these 2 are the first hits:
https://wiki.freepascal.org/TSqlite3_Master_Detail_Example
https://wiki.freepascal.org/MasterDetail

Thank you, that looks like the intended way to go, and helped me to grasp a bit more of the ideas behind the components.

[marcov]

Moderator note:  (I adjusted the small font so that it is a bit more readable, SEO spammers often use such tricks to hide their URLs)