• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 516
  • Last Modified:

how to see values of the child table on the same form where the walue of the parent table is

Hi,
I have the following tables: GOODS, COLOUR, GOODS_COLOUR. ID2 is primary key of table GOODS, NAME is the name of goods in table GOODS. COLID is a primary key of table COLOUR, COLNAME is the name of colour in table COLOUR. ID2, COLID serve as composite primary key of table GOODS_COLOUR. There may be up to 4 records in GOODS_COLOUR relating to the same ID2 in GOODS. What I need is to show NAME and all relating to it COLNAME on the same DELPHI form. I now how to show all four records, but the problem is that I need them on the same raw like
NAME | COLNAME1 | COLNAME2 | COLNAME3 | COLNAME4
 or like this
NAME | COLNAME1
            COLNAME2
            COLNAME3
            COLNAME4
where COLNAME appears like LookUpListBox but there should be only those COLNAMEs relating to this particular ID2.
0
Anni74
Asked:
Anni74
  • 7
  • 3
  • 3
  • +1
1 Solution
 
jimyXCommented:
Do you mean you want to show the result in a LookUpListBox ?
0
 
jimyXCommented:
Add DBLookupListBox and Datasource to your form and set your database connectivity and connect the Datasource to the DBQuery then set the following properties of the DBLookupListBox:

KeyField = ColName
ListField = ColName
ListSource = Datasource

The SQL code for the DBQuery is:

select g.name, c.colname from goods g join color c on (g.id2 = c.colID) where g.id2=1

Open in new window

0
 
jimyXCommented:
BTW this question should be in the zone Delphi Programming and Oracle.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Ephraim WangoyaCommented:

Drop a TEdit on your form and set it to readonly. This will hold the child records
I'm assuming you already defined your MasterSource and MasterFields for the ChildDataset

Create a DataSourceChange event on your master dataset, and add code as follows
procedure TForm1.MasterDatasourceDataChange(Sender: TObject; Field: TField);
begin
  Edit1.Text := '';
  ChildDataset.DiasbleControls;
  while not ChildDataset.Eof do
  begin
     Edit1.Text := ChildDataset.FieldByName('COLNAME').AsString + ',';
     ChildDataset.Next;
  end;
  ChildDataset.EnableControls;
end;
0
 
Ephraim WangoyaCommented:
error on example, change it to

procedure TForm1.MasterDatasourceDataChange(Sender: TObject; Field: TField);
begin
  Edit1.Text := '';
  ChildDataset.DiasbleControls;
  while not ChildDataset.Eof do
  begin
     Edit1.Text := Edit1.Text + ChildDataset.FieldByName('COLNAME').AsString + ',';
     ChildDataset.Next;
  end;
  ChildDataset.EnableControls;
end;

0
 
Ephraim WangoyaCommented:
Second alternative

Create a calculated string field on your master dataset eg 'COLNAMES'

Create an OnCalculate field event and code is similarly to the example above

procedure TForm1.MasterDatasetCalcFields(DataSet: TDataSet);
var
  S: string;
begin
  S := '';
  ChildDataset.DiasbleControls;
  while not Dataset.Eof do
  begin
     S := S + ChildDataset.FieldByName('COLNAME').AsString + ',';
     ChildDataset.Next;
  end;
  ChildDataset.EnableControls;
  DataSet.FieldByName('COLNAMES').AsString := S;
end;

0
 
Anni74Author Commented:
Hi, jimi, thank you very much for your support.
1) Yes, i would like it to be like LookUpListBox but read only for every id2 and relating only to this particular id2. There may be lots of records shown in the DBGrid. So I want LookUpListBox with colnames for every id2 record.
2) I do not understand why in the query you suggest id2 = 1?

Thanks in advance
0
 
jimyXCommented:
>  why in the query you suggest id2 = 1?
That was just an example since you did not specify how it will be matched in the database from your project.

You can use the "DBGrid.Fields[X].Text", I assumed it is field 1 so it can be:

procedure TForm1.DBGridCellClick(Column: TColumn);
begin
  DBQuery.Close;
  DBQuery.SQL.Text := 'select g.name, c.colname from goods g join color c on (g.id2 = c.colID) where g.id2='+ DBGrid.Fields[1].Text;
  DBQuery.Open;
end;

Open in new window

0
 
jimyXCommented:
Also it can be match with the BDQueryMain that fills the DBGrid:

procedure TForm1.DBGridCellClick(Column: TColumn);
begin
  DBQuery.Close;
  DBQuery.SQL.Text := 'select g.name, c.colname from goods g join color c on (g.id2 = c.colID) where g.id2='+ DBQueryMain.FieldByName('ID2').AsString;
  DBQuery.Open;
end;

Open in new window

0
 
Anni74Author Commented:
Hi, JimyX,
there is also a third table GOODS_COLOUR which serves as linking table to solve many -to-many relationship between GOODS and COLOUR with fields ID2, COLID.
0
 
epasquierCommented:
If you use a parameter in your query you don't have to build it always. And you don't need to join the GOOD table in the select if you know the ID of the good :

so in design time, you set the DBQuery SQL that will serve for your DBListBox :
DBQuery.SQL.Text:='SELECT c.*  FROM GOODS_COLOUR gc join COLOUR c on gc.COLID=c.COLID WHERE gc.ID2=:GOOD_ID';


procedure TForm1.GetColoursOfGood(ID_Good:Integer);
begin
 DBQuery.Close;
 DBQuery.Params[0].AsInteger:=ID_Good;
 DBQuery.Open;
end;

Open in new window

0
 
jimyXCommented:
Ok, here you are:

procedure TForm1.DBGridCellClick(Column: TColumn);
begin
  DBQuery.Close;
  DBQuery.SQL.Text := 'select g.name, c.colname from goods g, color c, goods_color gc where g.id2 = c.colID and c.colid = gc.colID and gc.id2='+ DBQueryMain.FieldByName('ID2').AsString;
  DBQuery.Open;
end;

Open in new window

0
 
jimyXCommented:
Please discard my last post, here is an updated one:

procedure TForm1.DBGridCellClick(Column: TColumn);
begin
  DBQuery.Close;
  DBQuery.SQL.Text := 'select g.name, c.colname from goods g, color c, goods_color gc where g.id2 = gc.id2 and c.colid = gc.colID and gc.id2='+ DBQueryMain.FieldByName('ID2').AsString;
  DBQuery.Open;
end;

Open in new window

0
 
Anni74Author Commented:
Thank you very much for your support.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 7
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now