Foreign Key Constraint error on a parent-child DataGridView.FillBy method

[afh]

I've two related DataGridViews for prod_category & prod_sub_category tables. I've used a text box to run FillBy method on prod_category table adapter to search records. But when I press any key in the search text box I get "Cannot clear table prod_category because ForeignKeyConstraint prod_categoryprod_sub_category enforces constraints and there are child rows in prod_sub_category" error.

Private Sub txtSubCatg_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtSubCatg.KeyDown        Try
            Me.Prod_sub_categoryTableAdapter.FillBy(Me.SalesDataSet.prod_sub_category, txtSubCatg.Text)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
End Sub

 

[jmcilhinney]

I don't think that the code you've displayed is actually relevant to the issue because you're talking about searching for 'prod_category' records but the code you've shown searches for 'prod_sub_category' records. That said, presumably the two searches are basically the same.

Let me start by saying that you should not ever have a method on your table adapter named 'FillBy'. Each table adapter has methods named 'Fill' and 'GetData' by default and if you then add additional queries with filters, you're supposed to add corresponding methods with names that indicate the filters. For instance, if your 'prod_category' table has a column named 'CategoryName' and you add a query to filter on that column then your methods should be named 'FillByCategoryName' and 'GetDataByCategoryName'.

As for your issue, if your 'FillBy' method is going to clear all the parent records out of the DataSet then, as the error message suggests, that would violate a foreign key constraint by leaving child records with no parent. If you want to be able to clear the parent records then you need to clear the child records first.

My question to you would be, does it make sense to keep querying database over and over anyway? How much data do you have in these tables? It's quite likely that the best option would be to simply retrieve all the data at the start and then filter in-place. That would involve two calls to Fill and then, when you want to filter, simply set the Filter property of the appropriate BindingSource.

Also, why are you searching on KeyDown? Surely TextChanged is the logical event to handle if you're using the Text of that TextBox to filter on.

 

[afh]

BindingSource.Filter property solved my searching problem. My approach was totally wrong.

Thanks...