Exception Details: System.Data.InvalidConstraintException: Cannot have a relationship between tables in different DataSets.


I am trying to add a relation to relate parent data with one or more child data.

Two different queries.

DBFunction is user define class to handle store procedure calls.

      Dim Ds As New DataSet
        Dim DBFunctions As New DBFunctions

        If (Not IsPostBack) Then


            Dim ParamF2(4) As String

            ' Parameter to run the parent and child query for customer products

            ParamF2(0) = "aaa111"
            ParamF2(1) = " "
            ParamF2(2) = " "
            ParamF2(3) = "GRU"
            ParamF2(4) = "Y"

            Ds = DBFunctions.runStoredProcedure("GetParentDataforProductCustForMktIntelInputPage ", ParamF2)

            Dim DS1 As New DataSet

            DS1 = DBFunctions.runStoredProcedure(".GetChildDataforProductCustForMktIntelInputPage", ParamF2)

            '--------------- add relationship
            Dim RDataset As New DataSet
            Dim Parent As DataColumn = Ds.Tables(0).Columns("RSKEY")
            Dim Child As DataColumn = DS1.Tables(0).Columns("RSKEY")

            Dim RSKEYRelation As DataRelation

            RSKEYRelation = New DataRelation("RelationName", Parent, Child)


            'Lastly, bind your data in .aspx.vb code:
            ParentDataList.DataSource = RSKEYRelation.DataSet.Tables(0).DefaultView
            ParentDataList.DataBind()

        End If

Let me know,

Thanks
LVL 2
Apu_ShahAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NievergeltSenior SW DevCommented:
Hi Apu_Shah,

I think you have to define a relationship between tables and not between Recordsets returned from a Stored Procedure.

Share and Enjoy  Christoph
osiris247Commented:
Yeah. You should the relationship between two datatables that exist in the same dataset.

o

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Apu_ShahAuthor Commented:
But My Parent queries and Child queries are very complex.

I dont know how to combine them and run them and return table(0) and table(1)

Help me in this.
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

osiris247Commented:
You could return the result of your sproc in a datatable each, add the tables to the same dataset and create the relationship.
You seem to return a dataset from your class.

relationship = New DataRelation("RelationName", dataset.Table("ParentTableName").Columns("PrimaryField"), dataset.Table("ChildTableName").Columns("ForeignField"))
dataset.Relations.Add(relationship)

o
Apu_ShahAuthor Commented:
I can only return Dataset.

Standards.

Can i utilize the Dataset some how.
osiris247Commented:
As stated in your exception you cannot have a relationship between datasets.
You could return the dataset, put each table into a new DataTable, add to a new Dataset and create the relationship.

Then you would have your two related tables in one dataset.

what do you think?? :)

o
NievergeltSenior SW DevCommented:
Hi Apu_Shah,

Why do you want to define the relationship?

Maybe it would help, if told us what your problem is.

Share and Enjoy  Christoph
Apu_ShahAuthor Commented:
I have my Parent Query Returning Customer ( RSKEY ) - ID.

When i Click the +/- Parent record ( Javascript )

I should show all the child records for that parent. ( Child being all the product sold by that customer ). RSKEY - ID.

RSKEY column is use to relate parent to childs.

+ Parent 1
 
   Child 1
   Child 2

+ Parent 2

   Child 1

+ Parent 3
 
   Child 1
   Child 2


I want to use javascript for expand and collapse.

My Child row has textboxes, and i have text box change event defined for those textbox.
when enter anything in those textbox and then do expand and collapse i lose that new data which i just entered.

Tell me you suggestions to implement this.
Apu_ShahAuthor Commented:
Look at this link

http://www-personal.engin.umd.umich.edu/~arpans/image.JPG

The Problem is my current design works perfect but the performance is bad.

My Desgin.

 + Parent Data list 1

     + Child Data List 1
     + Child Data List 2

And the child data list have textbox and on textbox change event i update the data in table.

The new data which i enter in the child data list textbox is lost when i do + / - expand collapse.

I handle the + / - thru ItemDataBound of my data list.

When i do + / - i lose the data from my textbox, thus what i do is i requery the whole child data and bind it again.

Bad Performance.

Help me out in this.

Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.