Lazarus

Programming => Databases => Topic started by: guest48180 on May 29, 2018, 02:18:43 pm

Title: [SOLVED] How to Query Two Tables for Two Grids on One Form?
Post by: guest48180 on May 29, 2018, 02:18:43 pm
I have a database with a few tables on it. One of my forms has two DBGrids. How can I assign a query to one table on DBGrid1 and another query from another table on DBGrid2?

I want to query a table for names and present them in DBGrid1. As the user scrolls through the names, another query will keep DBGrid2 updated with info for the selected name from DBGrid1.

I have a main form and a datamodule, and I can't figure out how to wire this up. I was thinking add another SQLQuery to the datamodule, but then that takes me right back to the problem of not being able to assign a query to a grid. How is this done?
Title: Re: How to Query Two Tables for Two Grids on One Form?
Post by: GetMem on May 29, 2018, 02:48:47 pm
Link the query to the grid this way: TDBGrid-->TDataSource-->TSQLQuery

You have an AfterScroll event(first query), pass a parameter to the second query, like this:
Code: Pascal  [Select]
  1. procedure TForm1.SQLQuery1AfterScroll(DataSet: TDataSet);
  2. begin
  3.   SQLQuery2.Close;
  4.   SQLQuery2.ParamByName('ID').AsInteger := SQLQuery1.FieldByName('THE_ID').AsInteger;
  5.   SQLQuery2.Open;
  6. end;
Title: Re: How to Query Two Tables for Two Grids on One Form?
Post by: guest48180 on May 29, 2018, 03:01:04 pm
Quote
Link the query to the grid this way: TDBGrid-->TDataSource-->TSQLQuery

I thought the same thing. Remember now: my data components are inside a datamodule. And I can access them from this form that has the grids. On a first run, the db and tables are created on the fly. So I know I can access them here.

But when I try
Code: Pascal  [Select]
  1. DBGrid1.DataSource1.
I don't get any of the data components listed to use there. I was sure this was the way, but it isn't letting me do it.

What I need to be able to do is something like this:
Quote
DBGrid2.d_mod_1.DataModule1.SQLQuery2.SQL.Text:='select * from recipe_ingredients where name_id = "1"';
But it won't let me do that.
Title: Re: How to Query Two Tables for Two Grids on One Form?
Post by: GetMem on May 29, 2018, 04:21:51 pm
Take a look at the attached screenshot.
Title: Re: How to Query Two Tables for Two Grids on One Form?
Post by: guest48180 on May 29, 2018, 05:01:42 pm
I have all of that. But I still don't know how to run two separate queries, one for each grid, and point those queries to the grid they need to work on. That's my problem. I have everything wired up right. I think.

I just need to be able to direct SQLQuery1.SQL.Text to DBGrid1. And, in the SQLQueryAfterScroll event, direct SQLQuery2,SQL.Text to DBGrid2. Usually the obvious is staring me right in the face. But I can't see it if it is. What am I missing?
Title: Re: How to Query Two Tables for Two Grids on One Form?
Post by: guest48180 on May 29, 2018, 05:35:39 pm
It has to be the DataModules messing this up. That's all I can see. Time to start over  :D

Thanks for your help GetMem.
Title: Re: How to Query Two Tables for Two Grids on One Form?
Post by: guest48180 on May 29, 2018, 07:07:35 pm
Solved!

2 DataSources
2 SQLQueries
1 SQLTransaction
1SQLite3Connection

Wire DataSource1 to SQLQuery 1 and DataSource2 to SQLQuery 2.
Then wire DBGrid1 to Datasource1 and DBGrid2 to DataSource2.

Works like a charm!
Title: Re: [SOLVED] How to Query Two Tables for Two Grids on One Form?
Post by: taazz on May 30, 2018, 12:27:52 am
That's a master detail relationship or its my mind playing tricks on me? Wasn't there a mechanism in place to handle those automatically (http://wiki.freepascal.org/MasterDetail )?I see no problems here only bad design.
Title: Re: [SOLVED] How to Query Two Tables for Two Grids on One Form?
Post by: guest48180 on May 30, 2018, 08:25:00 am
I see no bad design, except in the beginning of this. As for your link to Master/detail...that's exactly what I have now. And I did it by figuring it out, not by having the benefit of a tutorial.