Form Datagrid Parent/Child details databinding to SQL dataset

[Chris]

Hi, i have the following script which creates the two tables, i wish to have
a master/details form (with two datagrids on the same form, i have a many to
one relationship with tracks to album) but having i cant quite get it to
work. Please see the sql script below:

CREATE TABLE album(
id nvarchar(10) NOT NULL PRIMARY KEY,
title nvarchar(100),
artist nvarchar(100));

CREATE TABLE track(
album nvarchar(10),
dsk INTEGER,
posn INTEGER,
song nvarchar(255),
FOREIGN KEY (album) REFERENCES album(id));

All is well and i can add this as a new Datasource, i add both tables. In
the datasource explorer in VS2005 i click on album and it indeed shows the
track table as a sub table as shown below.

Album
|------ Id
|------ Title
|------ Artist
|------ Track (this is the table)

I then create a form and drag this dataset above but only the Album datagrid
is shown, how do i go about getting the Track sub-datagrid details on there
also? Can it be done automatically or do i need to add code?

Thanks in advance,

Chris

 

[Guest]

Try creating a view (below is the code) in SQL server joining the table

IF OBJECT_ID('dbo.albumAndSongs')IS NOT NULL
DROP VIEW dbo.albumAndSongs
GO

CREATE VIEW dbo.albumAndSongs
AS
SELECT * FROM album a
INNER JOIN
track t ON a.id = t.album
GO

You should then be able to bind the view, like you did with the ablum table
to the gridview control through the datasource explorer in VS2005

 

[Richard Carpenter]

Hi, i have the following script which creates the two tables, i wish to have
a master/details form (with two datagrids on the same form, i have a many to
one relationship with tracks to album) but having i cant quite get it to
work. Please see the sql script below:

CREATE TABLE album(
id nvarchar(10) NOT NULL PRIMARY KEY,
title nvarchar(100),
artist nvarchar(100));

CREATE TABLE track(
album nvarchar(10),
dsk INTEGER,
posn INTEGER,
song nvarchar(255),
FOREIGN KEY (album) REFERENCES album(id));

All is well and i can add this as a new Datasource, i add both tables. In
the datasource explorer in VS2005 i click on album and it indeed shows the
track table as a sub table as shown below.

Album
|------ Id
|------ Title
|------ Artist
|------ Track (this is the table)

I then create a form and drag this dataset above but only the Album datagrid
is shown, how do i go about getting the Track sub-datagrid details on there
also? Can it be done automatically or do i need to add code?

It really should be as simple as first dragging the Album table (not
the dataset, itself) onto the form, and then dragging the Track table
to the form *from the Album table field list* (as you show it above)
in the datasource explorer.

 

[morleyc]

It really should be as simple as first dragging the Album table (not

the dataset, itself) onto the form, and then dragging the Track table
to the form *from the Album table field list* (as you show it above)
in the datasource explorer

Thats strange, i did that just now and it works fine! I did do exactly
that before and it didnt work, still must be my fault somehow! Works a
treat thanks!