Adding relationship between tables in dataset

Hi,

Here is a link to some code

http://vb-helper.com/howto_net_datagrid_relations.html

Darren

I have it done in C# myself if you want me to post this and you could convert it to VB.NET

Let me know

Darren

Here's another link,

http://www.codeproject.com/cs/database/relationaladonet.asp?df=100&forumid=14883&exp=0&select=1587944

And finally,

Here's a pretty nice one.

http://www.dotnetbips.com/articles/dde2ffdd-d840-4cf8-905c-d1d3426f4cf8.aspx

I really appriciate if you could post that C# exxample I will convert it in vb.net

The table I am adding in single dataset they dont have any kind of relationship between them and also they have different numbers of columns as well

Here you go,

I use stored procedures to retrieve my data. Basically this just calles two select statements in on SP and then I add the rlationship.

The last link I posted is very straight forward from a VB.NET point of view. The first two might have a little more waffle associated with them

public DataSet ExecuteSPQuery(string sConnection, string sStoredProcName)
{

// using guarantees that Dispose is called on m_oconn, which will close the connection.
using (m_oconn = new SqlConnection(sConnection))
{

      // Set the Command propeties.
                m_cmd = new SqlCommand() ;      
                m_cmd.CommandText = sStoredProcName ;
      m_cmd.Connection = m_oconn ;
      m_cmd.CommandType = CommandType.StoredProcedure ;

      DataSet dataSet = new DataSet (sStoredProcName) ;
      SqlDataAdapter adapter = new SqlDataAdapter(m_cmd) ;

      // Open the connection to the database.
      m_oconn.Open() ;

      // Run the stored Procedure and place the results in dataTable.
      adapter.Fill(dataSet) ;

      // Create the array of Parent and Child columns.
      DataColumn ParentCol ;
      DataColumn ChildCol ;

      ParentCol = new DataColumn ;
      ChildCol = new DataColumn ;

      // Set the name of the parent column to hold the relationship.
      ParentCol = dataSet.Tables[0].Columns["NameOfParentCol"] ;
                              
      // Set the name of the child column to hold the relationship.
      ChildCol = dataSet.Tables[1].Columns["NameOfChildCol"] ;

      // Create the relationship
      DataRelation Rel = new DataRelation("NameOfRelationship", ParentCol, ChildCol) ;

      // Add the newly created relationship to the dataset.
      dataSet.Relations.Add(Rel) ;
      
      return dataSet ;
}
}

Hope this helps,

Darren
      

"The table I am adding in single dataset they dont have any kind of relationship between them and also they have different numbers of columns as well"

It does not matter about the number of columns in each table but you do need to have some relationship between the two in order to add a relationship.

Otherwise you can just add the two tables without a relationship but they will not display as you want in the datagrid.

You should look at adding a foregin key to one of the table if there isn't one there and then when the relationship is added they will display properly in the datagrid.

Darren

I tried you code but now i am getting the following error at following line

"        Dim Rel As DataRelation = New DataRelation("NameOfRelationship", dc1, dc2)     "


ERROR:
{"'column' argument cannot be null.
Parameter name: column"}

CODE:
       dsNew = objSitereport.GetTable(arrLocation, intPeriodId)

        Dim dc1 As New DataColumn
        Dim dc2 As New DataColumn

        dc1 = dsNew.Tables(0).Columns("p_emp_id")
        dc2 = dsNew.Tables(1).Columns("p_emp_id")

        Dim Rel As DataRelation = New DataRelation("NameOfRelationship", dc1, dc2)

        dsNew.Relations.Add(Rel)

Hi,

Put a break on the line:
'Dim Rel As DataRelation = New DataRelation("NameOfRelationship", dc1, dc2)'
and check the value of dc1 and dc2 and see if either of them are null.

Is there a column called p_emp_id in both tables within the dataset?

Darren

ok I fixed it now actually the first column is emp_id but their is no name defined so i replace it with 0 index but now i am getting error on following line

" dsNew.Relations.Add(Rel) "

ERROR:
{"This constraint cannot be enabled as not all values have corresponding parent values."}

CODE:
        dsNew = objSitereport.GetTable(arrLocation, intPeriodId)

        Dim dc1 As New DataColumn
        Dim dc2 As New DataColumn

        dc1 = dsNew.Tables(0).Columns(0)
        dc2 = dsNew.Tables(1).Columns(0)

        Dim Rel As DataRelation = New DataRelation("NameOfRelationship", dc1, dc2)

        dsNew.Relations.Add(Rel)

        Me.UltraGrid1.DataSource = dsNew

Hi,

This means that there are values in the child table which do not have corresponding values in the parent table.

You will have to a test on your database to see what values in the parent id do not have values in the child.

Basically you don't have any relationship on the two tables within the database so the data is not in sync.

You can't have a child with no linking parent. It would never make sense.

Hope this helps.

Cheers,

Darren