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
509 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 32

Expert Comment

by:Ephraim Wangoya
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:Ephraim Wangoya
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:Ephraim Wangoya
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
 
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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.

717 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