Board index » delphi » Help needed programming databases with Delphi

Help needed programming databases with Delphi

Hi all,

    I have what seems like a trivial task to me, but I'm finding it
reasonably ellusive. I have 3 tables which can be summarized as
illustrated below :

  +---------+--------+     +-------------+------------+
  | Player  | Player |     | Player ID   | Team ID    |
  | Name    | ID     |     +-------------+------------+
  +---------+--------+             CrossIdxTable
        PLayerTable              

  +---------+--------+
  | Team ID | Team   |
  |         | Name   |
  +---------+--------+
       TeamTable

I want to create a form which
(1) has a combo-box containing a list of the teams, and
(2) has a grid containing a list of players on the team being displayed
    in the combo-box.

I've tried using lookup fields to solve the first problem, but that just
seems to give me a list in the drop-down which won't do anything.

The second part I thought would be easy to solve, using a master/detail
relationship between TeamTable and CroxxIdxTable, and then another
between CrossIdxTable and PlayerTable. Unfortunately the second
master/detail relationship doesn't appear to work.

Any clues on this task would be greatly appreciated.

Regards,
    Andrew

 

Re:Help needed programming databases with Delphi


In article <7673ps$...@newsserver.trl.OZ.AU>, adavi...@newsserver.trl.oz.au

Quote
(Andrew Davison) writes:
>I want to create a form which
>(1) has a combo-box containing a list of the teams, and
>(2) has a grid containing a list of players on the team being displayed
>    in the combo-box.

>I've tried using lookup fields to solve the first problem, but that just
>seems to give me a list in the drop-down which won't do anything.

>The second part I thought would be easy to solve, using a master/detail
>relationship between TeamTable and CroxxIdxTable, and then another
>between CrossIdxTable and PlayerTable. Unfortunately the second
>master/detail relationship doesn't appear to work.

>Any clues on this task would be greatly appreciated.

Using a connecting table like this is a fairly common solution to a
many-to-many relationship.

1 You can either use a DBComboBox (with a Teams table and a DataSource) and
programmatically fill it with the names from the Teams database, or use a
DBLookUpComboBox and put Team in Key Field and List field, & point the
ListSource to the DataSource for the Teams table.

2 Then use a TQuery and a DataSource, point the DataBase to the directory
holding the three data tables (or an alias), and point the DBGrid to the query
DataSource.
Enter the following in the OnCloseUp event handler of the DB[LookUp]ComboBox :-

  with Query1 do begin
    Close;
    SQL.Clear;
    SQL.Add('SELECT P.PLAYER  FROM PLAYERTABLE P, CROSIDX C, TEAMTABLE T');
    SQL.Add('WHERE (P.PLAYER_ID = C.PLAYER_ID');
    SQL.Add('and C.TEAM_ID = T.TEAM_ID');
    SQL.Add('and T.TEAM = "' + DBLookUpComboBox1.Text + '")');
    {or SQL.Add('and T.TEAM = "' + DBComboBox1.Text + '")');}
    SQL.ADD('Order By PLAYER');
    Open;
  end;

Remember that the CrossIdx table must have a record for every pair of Play_ID
to TEAM_ID which has been played.

Alan Lloyd
alangll...@aol.com

Other Threads