DataGrid - showing one to one relation in column

[thomas10001]

I have a datagrid and a dataset connected. The dataset contains one master table and one child table with its relation. The child table is an one to one relation.

 

When I run the child gets shown automatically as the relation with a plus sign and a link. But this is not what I want. I want one field in the child table to show up in the column showing the foreign key (in the parent table) that connects to the child table.

 

Is there an easy solution to this?

Or would I have to "manually" search out the value I want to show and add a column and set it into that column?

[Mehyar]

I may be way offline here but i think you can set the Allow Naviagtion property of your datagrid to false.

[Nerseus]

I'm a tad confused on what you have/want. You want to have the grid show the child table PLUS one field from the parent? Or a link to show the whole parent table? Or is that reversed - you want to show the parent table with one column showing a value from the child table OR show the parent table and one column is a hyperlink to show the child table?

 

-Nerseus

[thomas10001]

If you have two tables:

 

table staff with columns:

staffId (primary key)

firstName

lastName

 

and another table department with columns:

departmentId (primary key)

departmentName

staffId (foreign key)

 

In the datagrid I want to show the following on each row

 

department.departmentName, staff.firstName, staff.lastName

[douluvme95630]

Did u get a solution for this? Please let me know.

 

thanks

[thomas10001]

No solution yet! I havent tryed anything yet. But maybe I can just change the select SQL to have a join with the other table and just project out the column I want.

 

I used the wizard to generate the code and there I couldn't find how to show the wanted column on the same row. But maybe this would work as I wanted

[thomas10001]

Yes it worked to add a join to only the select sql. But I have my dataset attached to a datagrid and the columns does not appear in the same sequence as the are in the select sql.

 

What should I do to arrange them in the sequence I want them in the datagrid??

[thomas10001]

This is the select sql I have.

 

select r.roundid, p1.name, p2.name, r.gh, r.ga from results r,player p1, player p2 where p1.playerid = r.playeridhome and p2.playerid = r.playeridaway r.groupid = 1

 

the insert, update and delete sql are the one you get from the wizard and only involves the main table results with all its fields

 

results has the following primary keys:

groupid, playeridhome, playeridaway, roundid

 

player has pk:

playerid

 

When I use the select sql above it show the desired columns in my datagrid but not in the same sequence. How can I correct that?

 

Also when I have the join to player p1 and p2 I am not able to save. I get a message that the parameter for player has no "standard value"

 

If I dont include the join I can save it.

 

If I set the unique constraint with the four primary keys I can also not save. I would get an error saying "concurrency violation" even though there are no other processes or users trying to update.

 

Anyone who know what I should do?

[thomas10001]

I found the solution!

 

One have to create ie an ArrayList which are programatically filled from a dataset. The ArrayList will be the source for the datagrid.

There will be some extra code to do for saving and filling the grid which are not needed when using only one table and just a dataset.

 

Check this links for samples.

http://www.codeproject.com/useritems/grid101.asp

http://msdn.microsoft.com/msdnmag/issues/02/11/DataPoints/

[falcon]

better way

 

I found the solution!

 

One have to create ie an ArrayList which are programatically filled from a dataset. The ArrayList will be the source for the datagrid.

There will be some extra code to do for saving and filling the grid which are not needed when using only one table and just a dataset.

 

Check this links for samples.

http://www.codeproject.com/useritems/grid101.asp

http://msdn.microsoft.com/msdnmag/issues/02/11/DataPoints/

 

you could use a sort in your sql. you data will be sorted by your parents data not the child so if you put a sort on the child column it will sort that for you. its alot faster to roun and the code is done automaticaly by the datadapter. this be added custom by writing a new select command as well.

[pelikan]

You guys are way off base!

 

From my reading of the original post, what is needed is a column based on an "expression".

 

You have to get used to the NET way of doing things. No JOINS, no complicated queries or views. DataRelations between base tables in the DataSet are used.

 

Set it up this way:

//add the relation (Staff is the "parent" table here)
ds.Relations.Add("StaffDept",
    ds.Tables["Staff"].Columns["staffId"],
    ds.Tables["Departments"].Columns["staffId"],
    false);

//add expression based columns to the Departments table for each look-up
//field
ds.Tables["Departments"].Columns.Add("First Name",
    typeof(string),
    "Parent(StaffDept).firstName");
ds.Tables["Departments"].Columns.Add("Last Name",
    typeof(string),
    "Parent(StaffDept).lastName");

//set the Visible property of the staffId column in the DataGrid to false

//bind the DataGrid to the Departments table (view)

 

The simple and correct way to solve the problem in .NET.