Adding relationship between tables in dataset

while working on vb.net I am trying to  add multiple tables with different columns to the single datagrid but no luck so far

Can some please provide me a Sample code for how to create two tables and then add a relationship betwen them and finally add them to single dataset which i will bind to the ultragrid

Thanks
LVL 1
shieldguyAsked:
Who is Participating?
 
DarrenSenior Software EngineerCommented:
You will have to a test on your database to see what values in the parent id do not have values in the child.

should be...

You will have to a test on your database to see what values in the child id do not have values in the parent.
0
 
DarrenSenior Software EngineerCommented:
Hi,

Here is a link to some code

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

Darren
0
 
DarrenSenior Software EngineerCommented:
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
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
DarrenSenior Software EngineerCommented:
0
 
DarrenSenior Software EngineerCommented:
0
 
shieldguyAuthor Commented:
I really appriciate if you could post that C# exxample I will convert it in vb.net
0
 
shieldguyAuthor Commented:
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

0
 
DarrenSenior Software EngineerCommented:
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
      
0
 
DarrenSenior Software EngineerCommented:
"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

0
 
shieldguyAuthor Commented:
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)
0
 
DarrenSenior Software EngineerCommented:
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
0
 
shieldguyAuthor Commented:
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
0
 
DarrenSenior Software EngineerCommented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.