Solved

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

Posted on 2010-11-25
14
504 Views
Last Modified: 2013-12-19
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
Comment
Question by:Anni74
  • 7
  • 3
  • 3
  • +1
14 Comments
 
LVL 24

Expert Comment

by:jimyX
ID: 34214246
Do you mean you want to show the result in a LookUpListBox ?
0
 
LVL 24

Expert Comment

by:jimyX
ID: 34214285
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
 
LVL 24

Expert Comment

by:jimyX
ID: 34214354
BTW this question should be in the zone Delphi Programming and Oracle.
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 34215173

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
 
LVL 32

Expert Comment

by:ewangoya
ID: 34215212
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
 
LVL 32

Expert Comment

by:ewangoya
ID: 34215215
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
 

Author Comment

by:Anni74
ID: 34215540
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 24

Expert Comment

by:jimyX
ID: 34215575
>  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
 
LVL 24

Expert Comment

by:jimyX
ID: 34215601
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
 

Author Comment

by:Anni74
ID: 34215724
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
 
LVL 25

Expert Comment

by:epasquier
ID: 34215838
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
 
LVL 24

Expert Comment

by:jimyX
ID: 34215851
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
 
LVL 24

Accepted Solution

by:
jimyX earned 500 total points
ID: 34216580
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
 

Author Closing Comment

by:Anni74
ID: 34375232
Thank you very much for your support.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now