Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
Medium Priority
?
512 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

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.
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.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.

609 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