• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8681
  • Last Modified:

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
0
shieldguy
Asked:
shieldguy
  • 9
  • 4
1 Solution
 
DarrenDCommented:
Hi,

Here is a link to some code

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

Darren
0
 
DarrenDCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
DarrenDCommented:
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
 
DarrenDCommented:
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
 
DarrenDCommented:
"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
 
DarrenDCommented:
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
 
DarrenDCommented:
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
 
DarrenDCommented:
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 9
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now