Search code examples
sqldelphidelphi-5zeos

Using multiple SQL queries

I have done some searching and can't find a definitive answer to this one.

I am just getting into SQL so be gentle. Using D5, TNTUnicode, Zeos and SQLite3

I have a DBGrid with all the Account names in the tblAccounts showing. I have a DBGrid with all the Folders in the tblFolders showing.

In the OnCellClick of the Accounts grid I have an SQL query

qryFolders.Close;
qryFolders.SQL.Clear;  // Not really needed as I am assigning the Text next - but :)
qryFolders.SQL.Text:=
  'SELECT Folder FROM tblFolders WHERE UPPER(Account)="FIRSTTRADER"'
qryFolders.ExecSQL;
tblFolders.Refresh;

In my application, nothing happens, I still have the full list of Folders visible.

In the SQL-Expert desktop that line works fine and displays only the two Folders associated with that Account. In that app it keeps displaying the full list of Folders

If I step through the OnCellClick it shows the correct Text etc.

Where am I going wrong?

Thanks

Solution

  • If you want to display a Master-Detail (Account as Master, Folder as Detail), so we start from here:

    // connecting the grids
AccountsDataSource.DataSet := tblAccounts;
AccountsGrid.DataSource := AccountsDataSource;

FoldersDataSource := tblFolders;
FoldersGrid.DataSource := FoldersDataSource;

// retrieving the data
tblAccounts.Open;
tblFolders.Open;

    That should reflect, what you already have. Now lets go to the Master-Detail.

    It should be obvious that all Query and Table Components have a valid Connection set, so I will left this out.

    First be sure, the Query is not active

    qryFolders.Active := False;

    Having a Master-Detail with a Query as Detail, we have to set the MasterSource

    qryFolders.MasterSource := AccountsDataSource;

    and after that we can setup the Query with parameters to link to the fields from MasterSource. Linking to the field Account in the MasterSource is done by using :Account

    qryFolders.SQL.Text := 
  'SELECT Folders FROM tblFolders WHERE UPPER( Account ) = :Account';

    Now, we are ready to retrieve the data

    qryFolders.Open;

    Until this, we will not see any changes in the FoldersGrid, because we didn't told anyone to do so. Now let's get this to work with

    FoldersDataSource.DataSet := qryFolders;

    In your approach, you didn't Open the Query and you didn't link the Query to the Grid.

    Another option is to have a Master-Detail without a separate Query. (It seems there were some code refactoring, so i guess this is a working sample) 

    tblFolders.MasterSource := AccountsDataSource;
tblFolders.MasterFields := 'Account';
tblFolders.LinkedFields := 'Account';

    Reference: