1

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

Improve this question
7
  • What is the connection (if any) between qryFolders and tblFolders? qryFolders presumably gets a new dataset (although I'm not sure about your query), but you refresh tblFolders. Commented Mar 21, 2013 at 16:18
  • 3
    ExecSQL is a method to execute insert/update or other SQL statements that return no cursors. ExecSQL over a select statement is useless, since you usually want to get the returning cursor. Commented Mar 21, 2013 at 16:23
  • 1
    Better explain what are you trying to accomplish ;) Commented Mar 21, 2013 at 16:25
  • qryFolders and tblFolders have the same DataSource. I am trying to accomplish what I described in the "In the SQL-Expert desktop" original post, when I click the Account in the dbgrid, I want only the Folders associated with that Account to be displayed. The SQL line works in the SQL-Expert desktop so why not in my app? IF ExecSQL returns no cursor, it would be helpful for you to give an example of what WILL return a cursor. Constructive comments help. :) Commented Mar 21, 2013 at 17:29
  • ExecSQL, as @jachguate says, is for DML/DDL statements or SQL statements that return no results (like INSERT or UPDATE). To retrieve a cursor (rows of data) from a SELECT, just use qryFolders.Open; instead. Commented Mar 21, 2013 at 18:09

1 Answer 1

Reset to default
4

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:

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Wow, Sir Rufo, thanks for the great detail. I REALLY appreciate that. I had played with the Master Detail thing some time back but gave up in frustration. With this outline I am sure I will be well on the road. Thanks so much for taking the time.
You are welcome. Appreciating on SO works with upvoting and if an answer satisfy your question you should accept it

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.