getting an error trying to fill datatable

[dotolee]

I have a parent / child relationship defined using the data designer in vb. I've specified that on DELETE, it should set the child records to null.
The delete itself it working just fine. But since adding this relationship, my code to refresh the two datagrids that hold the parent and child records is failing.

The error message is:
Message="Cannot clear table Territory because ForeignKeyConstraint FK_Contacts_Territory enforces constraints and there are child rows in Contacts"

The code behind my Refresh button looks like:
If Me.ContactsTableAdapter.ClearBeforeFill And Me.TerritoryTableAdapter.ClearBeforeFill Then
Me.TerritoryTableAdapter.FillBySortedName(TerritoryDS.Territory)
Me.ContactsTableAdapter.Fill(TerritoryDS.Contacts)
End If

Any suggestions?

 

[genomon]

Check ForeignKeyConstraint FK_Contacts_Territory on your database. Could be nulls are a no-no.

"Rome did not create a great empire by having meetings, they did it by killing all those who opposed them."

 

[macleod1021]

Well, the reason is because your TableAdapters don't know about the relationship now. Either delete the tableadapters and recreate or go inand modify the relationships on the table adapters.

im in ur stakz, overflowin ur heapz!

 

[dotolee]

macleod1021,
I've opened my TerritoryDS.xsd via the designer window and i've added the Delete rule by just editing the properties of the existing relationship....
Is this not enough?

genomon, the firld territoryID on my contacts table does allow for NULLS.

Thank you both for responding / trying to help.

 

[macleod1021]

You said the delete rule is working fine though.

There's something troubling me about what's happening...I can't put my finger on it though. If you're just trying to refresh the view of the tables, it shouldn't be attempting to clear the database tables...it just just go out and get the current information.

Personally, the way that I do this type of work is create my own DataSet with the required tables and then bind the datagrid view to datatable within the dataset. It's essentially the same thing as what the wizard does, but I have more control over the process, and what sql queries are performed. If you're not familiar with ADO, then that may not be an option.

im in ur stakz, overflowin ur heapz!

 

[dotolee]

macleod1021, unfortunately, i'm not too good with ADO just yet.

The silly thing is when i load the form initially, it calls pretty much the same code, and it works fine - it displays all the records properly....

Private Sub editterritory_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.TerritoryTableAdapter.FillBySortedName(TerritoryDS.Territory)
Me.ContactsTableAdapter.Fill(TerritoryDS.Contacts)
End Sub

The only difference between the code above and the refresh is that i call the clearbeforefill on the table adapter.

If Me.ContactsTableAdapter.ClearBeforeFill And Me.TerritoryTableAdapter.ClearBeforeFill Then
Me.TerritoryTableAdapter.FillBySortedName(TerritoryDS.Territory)
Me.ContactsTableAdapter.Fill(TerritoryDS.Contacts)
End If

 

[dotolee]

the only way i've gotten it work is by setting:
Me.ContactsTableAdapter.ClearBeforeFill = false Me.TerritoryTableAdapter.ClearBeforeFill = false

but this doesn't give me the desired results in my datagrid... although it has gotten rid of the error message.
This is why the system is trying to clear the TABLE ... if it's set to true.
Question is: why can't it clear the table since adding the delete rule on the relationship?
argh... time for a break.