* * *

Author Topic: [SOLVED] How to Query Two Tables for Two Grids on One Form?  (Read 981 times)

Landslyde

  • Full Member
  • ***
  • Posts: 158
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?
« Last Edit: May 29, 2018, 07:07:55 pm by Landslyde »
Linux Mint 19
Lazarus 1.8.4
FPC 3.0.4

GetMem

  • Hero Member
  • *****
  • Posts: 3108
Re: How to Query Two Tables for Two Grids on One Form?
« Reply #1 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;

Landslyde

  • Full Member
  • ***
  • Posts: 158
Re: How to Query Two Tables for Two Grids on One Form?
« Reply #2 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.
« Last Edit: May 29, 2018, 04:45:33 pm by Landslyde »
Linux Mint 19
Lazarus 1.8.4
FPC 3.0.4

GetMem

  • Hero Member
  • *****
  • Posts: 3108
Re: How to Query Two Tables for Two Grids on One Form?
« Reply #3 on: May 29, 2018, 04:21:51 pm »
Take a look at the attached screenshot.

Landslyde

  • Full Member
  • ***
  • Posts: 158
Re: How to Query Two Tables for Two Grids on One Form?
« Reply #4 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?
Linux Mint 19
Lazarus 1.8.4
FPC 3.0.4

Landslyde

  • Full Member
  • ***
  • Posts: 158
Re: How to Query Two Tables for Two Grids on One Form?
« Reply #5 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.
« Last Edit: May 29, 2018, 06:28:46 pm by Landslyde »
Linux Mint 19
Lazarus 1.8.4
FPC 3.0.4

Landslyde

  • Full Member
  • ***
  • Posts: 158
Re: How to Query Two Tables for Two Grids on One Form?
« Reply #6 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!
« Last Edit: May 30, 2018, 09:45:37 am by Landslyde »
Linux Mint 19
Lazarus 1.8.4
FPC 3.0.4

taazz

  • Hero Member
  • *****
  • Posts: 5243
Re: [SOLVED] How to Query Two Tables for Two Grids on One Form?
« Reply #7 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.
« Last Edit: May 30, 2018, 12:29:34 am by taazz »
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

Landslyde

  • Full Member
  • ***
  • Posts: 158
Re: [SOLVED] How to Query Two Tables for Two Grids on One Form?
« Reply #8 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.
« Last Edit: May 30, 2018, 08:42:48 am by Landslyde »
Linux Mint 19
Lazarus 1.8.4
FPC 3.0.4

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus