Display Related tables in DataGridView

[GilbertB]

Hi all,

I have two tables in SQL Server : Rent and Member . And they are in a relationship.

Member ID (from Memner table) is in the table of Rent.

I would like to know if there is a way that when I view a new Rent in the datagridview, a '+'sign will appear so that when clicked, a table will appear underneath, displaying the information of the member who did the rent. ?

Thanks

[Mitja Bonca]

This is not an easy task to do. You will actually need to have two datatables filled.
1st for all Memebers - display them all in datagrdivirew
2nd for Rents - when clicked on some member, table will get filled every time, and will populate the rows bellow the memeber.

Important here is to populate dgv manually - so no data souce used. Add data row by row, column by column.
This way you can manually set "+" sign in 1st column. And then clicked (1st column clicked) fill new DataTable with data from Rent table (based on foreighn key from members).

[GilbertB]

The Rent Table is filled manualy throught the application .Then I need a plus sign near every Rent so that Memeber table will show for information who took the rent.

Is there any way of 'linking' or viewing of the relationships done is SQL with the '+' sign ? like there is in Access Database after relationships are done ?

thanks

[Mitja Bonca]

Sure, in sql would be like:

tables:
1. Members: MemberID, Name, ....
2. Rents: MemberID_FK, Value,...

now to do an sql query to get all members that are in Rental table:

 "SELECT Members.MemberID, Member.Name FROM Members, Rents WHERE Members.MemberID = Rents.MembersID_FK"

But its not such a good idea to populate the same datagridview - especially from the view of number of columns.
Anyway, it sure is possible to do. Just populate datagridview manually for both queries (1st for all Rents, 2nd for Members that belongs to specific Rent).

[GilbertB]

so how can I view related tables in two seperate Data Grid Views ?

like this : http://code.msdn.microsoft.com/Windows-Form-ParentChild-9cce4e67

BUT with SQL connection

Thanks

[Mitja Bonca]

How?
Simply.
You need one dataTable to Fill it with all Rents. Bind it to dgv1. So you can retreive MemberID_ForeignKey from it (use DataSource property).
I would personally hide this column (because its not good to view columns like ID).
Next...
When user clicks on some row of dgv1, get the MemberID_ForeignKey from DataTable1 (not frm dgv1, because this column is invisible - so no data will be found).
You can retreive value like (using Linq):

 //inside CellDoubleClick event:
    //NOTE: e.RowIndex is a property of this event!!
    int ID = int.Paarse(table1.AsEnumerable().Where(w = > (string)w["Value"].ToString() == e.RowIndex).Select(s => (int)s["MemberID_FK"].ToString()).FirstOrDefault().ToString());

Then based on this MemberID_FK value create a new sql query fill new DataTable, and bind it to dgv2. Use this value in Where clause. Query would be:

"SELECT * FROM Members WHERE MemberID = '" + ID + "'"  //ID is an integer from above!

Hope it helps now.

[GilbertB]

thanks I will try that :)