Show relational data in a second datagrid

[Mikael Syska]

Hi,

I have 2 talbes in a DataSet, and made a DataRelation between the 2
tables in the DataSet.

One of the Tables are displayed in a datagrid, and when one of them is
selected in the datagrid I want its childcolumns to be displayed in a
second datagrid.

How can I filter the dataset or get only the related data into the
second datagrid?

I totally blank on this one.

I use ByteFX to connect to a mysql database.

Here are my code:
private void Form1_Load(object sender, System.EventArgs e)
{
MySqlConnection thisConnection = new MySqlConnection("Server=syska.dk;
Username=root; Password=supermand; Database=c-sharp;");

MySqlDataAdapter thisPollAdapter = new MySqlDataAdapter("SELECT * FROM
poll", thisConnection);

MySqlDataAdapter thisOptionAdapter = new MySqlDataAdapter("SELECT *
FROM pollOption", thisConnection);

thisPollAdapter.Fill(thisDataSet, "first");

thisOptionAdapter.Fill(thisDataSet, "second");

dataGrid.DataSource = thisDataSet.Tables["first"];

DataRelation thisRelation = new DataRelation("thisRelation",
thisDataSet.Tables[0].Columns["pollId"],
thisDataSet.Tables[1].Columns["pollId"]);

// MessageBox.Show( thisDataSet.Tables[1].ParentRelations. );

// her skal de columns smides ind som passer til det ID som er valgt i
det anden datagrid
// dataGrid1.DataSource =
}

As you can see, I havent made anything fancy yet, are this the right way
to go, or are there are better way of doing this, I dont want to get all
the data every time, I choose a new row in the first datagrid..

kind regards
Mikael Syska

 

[Nicholas Paldino [.NET/C# MVP]]

Mikael,

It's relatively easy. For the data source on the child grid, set it to
the DataRelation object that relates the two grids.

Hope this helps.

 

[Mikael Syska]

Hey,

Fairly new to C#,

Can you give me a example

Can't seem to get the idea of how it knows witch item is selected in the
first datagrid, but you can probebly give me a breef description of it

kind regards
Mikael Syska

 

[Nicholas Paldino [.NET/C# MVP]]

Mikael,

You would want to do this:

// The grid is named childDataGrid, the DataSet is named dataSet.
childDataGrid.DataSource = dataSet.Relations[0];

You can substitute the relation for the proper one. The data set knows
it is a relation and will select the appropriate rows.

 

[Mikael Syska]

Nicholas,

I have just tried what you said...

now the code look like this:
MySqlConnection thisConnection = new MySqlConnection("Server=syska.dk;
Username=root; Password=supermand; Database=c-sharp;");

MySqlDataAdapter thisPollAdapter = new MySqlDataAdapter("SELECT *
FROM poll", thisConnection);
MySqlDataAdapter thisOptionAdapter = new MySqlDataAdapter("SELECT * FROM
pollOption", thisConnection);

thisPollAdapter.Fill(thisDataSet, "first");
thisOptionAdapter.Fill(thisDataSet, "second");

dataGrid.DataSource = thisDataSet.Tables["first"];

DataRelation thisRelation = new DataRelation("thisRelation",
thisDataSet.Tables[0].Columns["pollId"],
thisDataSet.Tables[1].Columns["pollId"]);

// My other datagrid1, this seems to work but no data is displayed
dataGrid1.DataSource = thisDataSet.Relations["thisRelation"];

// This gives me a error.... System.IndexOutOfRangeException: Cannot
find relation 0
// dataGrid1.DataSource = thisDataSet.Relations[0];

Can u see any errors or other mistyped things, cause it aint working....

Its so damm annoying that it aint working :-(

 

[Mikael Syska]

Tried a little more, and came a little closer, added this line to the
code and the Relation comes in the first DataGrid, but I would prefer
the other option with 2 datagrids.....

thisDataSet.Relations.Add(thisRelation);

Hope u have a solution for me

thanks on advance
Mikael Syska

Nicholas,

I have just tried what you said...

now the code look like this:
MySqlConnection thisConnection = new MySqlConnection("Server=syska.dk;
Username=root; Password=supermand; Database=c-sharp;");

MySqlDataAdapter thisPollAdapter = new
MySqlDataAdapter("SELECT * FROM poll", thisConnection);
MySqlDataAdapter thisOptionAdapter = new MySqlDataAdapter("SELECT * FROM
pollOption", thisConnection);

thisPollAdapter.Fill(thisDataSet, "first");
thisOptionAdapter.Fill(thisDataSet, "second");

dataGrid.DataSource = thisDataSet.Tables["first"];

DataRelation thisRelation = new DataRelation("thisRelation",
thisDataSet.Tables[0].Columns["pollId"],
thisDataSet.Tables[1].Columns["pollId"]);

// My other datagrid1, this seems to work but no data is displayed
dataGrid1.DataSource = thisDataSet.Relations["thisRelation"];

// This gives me a error.... System.IndexOutOfRangeException: Cannot
find relation 0
// dataGrid1.DataSource = thisDataSet.Relations[0];

Can u see any errors or other mistyped things, cause it aint working....

Its so damm annoying that it aint working :-(

Mikael,

You would want to do this:

// The grid is named childDataGrid, the DataSet is named dataSet.
childDataGrid.DataSource = dataSet.Relations[0];

You can substitute the relation for the proper one. The data set
knows it is a relation and will select the appropriate rows.