asked on

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

Nievergelt

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

ASKER CERTIFIED SOLUTION

osiris247

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Apu_Shah

ASKER

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.

osiris247

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_Shah

ASKER

I can only return Dataset.

Standards.

Can i utilize the Dataset some how.

osiris247

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

Nievergelt

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_Shah

ASKER

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_Shah

ASKER

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