Cannot Remove DataTable from DataSet

  • Thread starter Thread starter Jeff Cooper
  • Start date Start date
J

Jeff Cooper

Hi folks,

I have a DataSet in which I have a couple of DataTables. At some point in
my code (vb) I'd like to remove one of the tables. The tables, call 'em DT1
and DT2 are created have a relation between them:

myDataSet.Relations.Add("DT1_DT2", Results.Tables(0).Columns(0),
Results.Tables(1).Columns(0), True)

Also, I have a PrimaryKey placed on table DT2 so no duplicate rows get
inserted:

k(0) = DT2.Columns(0)
DT.PrimaryKey = k

So far so good. I do a bunch 'o queries, get a bunch of data. Now, I'd like
to remove DT2 from the myDataSet.Tables collection:

myDataSet.Relations.RemoveAt(0)
myDataSet.Tables.RemoveAt(0)

The second line above gives me an error: "Server was unable to process
request. ---> System.ArgumentException: Cannot remove unique constraint
'Constraint1'. Remove foreign key constraint 'DT1_DT2' first. at
System.Data.UniqueConstraint.CanBeRemovedFromCollection.... etc etc."

As you can see above, I DID remove the relationship.

I tried modifying the above code as follows:

myDataSet.Relations.RemoveAt(0)
myDataSet.Tables(0).PrimaryKey = Nothing 'see if this fixes anything
myDataSet.AcceptChanges() 'try this too.
myDataSet.Tables.RemoveAt(0)

Neither worked.

How can I delete a table from a dataset on which I have created then removed
a relation? You may notice in my original relations.add statement, I have
createConstraints set to True. I think I need that because some of my
queries should only get populated into DT2 where they have existing parents
in DT1. I don't see any "Constraint" or "ForeignKeyConstraint" property or
collection anywhere, so I'm at a loss as to how to get rid of it. Any help
would be much appreciated.

Thanks,

Jeff
 
Thanks Neil.

I'm not using table names because I want to repeatedly add then remove
tables. I have an ever-changing set of queries, the results of which are all
intersected with one another. I'm handling it by doing a query, then doing
another query, intersecting the results with the first, then another, etc...

This is actually a rewrite of an existing VB6 (.exe) app which does a
similar thing but uses temp tables. I'm trying to get it to work in an asp
using the DataSet at follows:

Start off with one table. Run a query, put the results into the table.
Add a new table at myDataSet.Tables(1). Make it a child of the existing
table.

Run a another query, put the results into this new table (since it's a child
of the first table, only rows that exist in the first table should be
inserted)
Remove the first table (myDataSet.Tables(0)) Now, the second table, which
was myDataSet.Tables(1), is now myDataSet.Tables(0).
Add a table (a brand new myDataSet.Tables(1).

Run a query, put the results into this new table.
Remove the first table myDataSet.Tables(0). Now, the second table, which
was myDataSet.Tables(1), is now myDataSet.Tables(0).
Add a table (a brand new myDataSet.Tables(1).

Run a query, put the results into this new table.
Remove the first table myDataSet.Tables(0). Now, the second table, which
was myDataSet.Tables(1), is now myDataSet.Tables(0).
Add a table (a brand new myDataSet.Tables(1).

etc etc.

That's why I'm using indexes rather than names. I tried removing the
constraints as you mentioned, but get an error: : Cannot remove unique
constraint 'Constraint1'. Remove foreign key constraint 'DT1_DT2' first.
Yet, I DO remove the relation and myDataSet.Tables(0).Constraints.Count = 1
when I get to the line where I'm removing the constraint, so there's no
other constraint there! (Unless Foreign Key Constraints are not kept in the
constraint collection for some reason).

Thanks again.

Jeff
 
Just to make sure I wasn't nuts, I redid it as a test with names rather than
indexes. Also, I tried using the column property Unique rather than setting
a Primary Key:

Below is some test code I put into a standard windows application. It
errors at the line " D.Tables(0).Constraints.RemoveAt(0)". It's amazing how
much time gets spent stuck on stupid little things like this that you would
ASSUME are fairly straight forward -- I want to delete a table. Anyway, it
tells me that, before removing constraint 0 I must remove constraint 'Rel1'.
Well, Rel1 is the name of my relation, not the constraint. And, there IS
only one constraint on T1, the one I'm trying to remove.

Any help will get you my first born .. just kidding, but I've spent a day
and a half on this now and I'm about to crack, I know it.

Thanks,

Jeff

======================================

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim D As New DataSet
Dim T1 As New DataTable
Dim T2 As New DataTable

'add a table
T1.Columns.Add("ID")
T1.Columns(0).Unique = True
D.Tables.Add(T1)

'add a second table
T2.Columns.Add("ID")
T2.Columns(0).Unique = True
D.Tables.Add(T2)

'add a relation between then to ensure anything going into T2 already
exists in T1
D.Relations.Add("Rel1", D.Tables(0).Columns(0), D.Tables(1).Columns(0),
True)

'**** do a bunch of imaginary stuff here, put results into T1, then T2
with the constraint that anything going into T2 will have an existing parent
in T1 ***

'Now T2 has everything I want, so I want to get rid of T1...
D.Relations.RemoveAt(0)
D.EnforceConstraints = False 'this seem to have no effect at all, by
the way.
D.Tables(1).Constraints.RemoveAt(0)
D.Tables(0).Constraints.RemoveAt(0) '<------ get an error here
telling me to remove constraint 'Rel1' first. ***********************

' I would like to remove this table, but it won't let me!
D.Tables.Remove (T1)
End Sub

========================================
 
Jeff,

At the point you remove the constraint on table 1, there are actually two
constraints: One is the primary key constraint, the second is the foreign
key constraint created when you create the relation. Hence,
D.Tables(1).Constraints.RemoveAt(0) will only remove the primary key
constraint and not the foreign key constraint. When you try to remove the
primary key constraint on table 0, this reports the error that constraint
Rel1 exists.

Try repeating the line D.Tables(1).Constraints.RemoveAt(0) twice and see if
that helps.

Neil.
 
That did it! Thanks so much!

For some reason, even though D.tables(1).constraints.count says it equals 1,
issuing a remove method twice gets rid of this other one that for some
reason isn't counted.

Jeff
 
Back
Top