index > Windows Forms Data Controls and Databinding > FK constraint error when filling tableadaptors

FK constraint error when filling tableadaptors

Hi All, I've included a filter on the main form of my application to fine tune the data that is returned. When using a parameter sp I receive an FK constraint error during the initial data load. Once the parent tableadaptor is filled, all is well and the filter it works great. Is there a way to delay filling the child tableadaptors? Or is this a bad idea and maybe I should be filtering in a different manner? The filter is a checkedlistbox and all data is returned with sprocs. TblOrganizationTableAdapter is the parent table.

   Public Sub btnOrgFilter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOrgFilter.Click

        'create list that contains the checked items in the org filter checkbox
        Dim orgFilter As New ArrayList
        If clbOrgFilter.CheckedItems.Count <> 0 Then
            Dim X As Integer
            For X = 0 To clbOrgFilter.CheckedItems.Count - 1
                orgFilter.Add(clbOrgFilter.CheckedItems(X))
            Next X
        End If

        'change the list items in the array to thier corresponding org type ID's 
        Dim i As Integer
        For i = 0 To orgFilter.Count - 1
            Select Case orgFilter.Item(i)
                Case "Other"
                    orgFilter.Item(i) = "0"
                Case "Prospect"
                    orgFilter.Item(i) = "1"
                Case "Investment"
                    orgFilter.Item(i) = "2"
                Case "Vendor"
                    orgFilter.Item(i) = "3"
                Case "Client"
                    orgFilter.Item(i) = "4"
            End Select
        Next

        'concatenate the array ID's into a comma-delimited string
        Dim result = String.Join(",", orgFilter.ToArray(GetType(String)))

        'load main form with filter result
        Try
            Me.TblOrganizationTableAdapter.FillByOrgType(Me.OrganizationDataSet.tblOrganization, result)
            Me.TblOrgNoteTableAdapter.Fill(Me.OrganizationDataSet.tblOrgNote)
            Me.TblContactTableAdapter.Fill(Me.OrganizationDataSet.tblContact)
            Me.TblOrgAddressTableAdapter.Fill(Me.OrganizationDataSet.tblOrgAddress)
            Me.TblMeetingTableAdapter.Fill(Me.OrganizationDataSet.tblMeeting)
            Me.TblMeetingParticipantTableAdapter.Fill(Me.OrganizationDataSet.tblMeetingParticipant)

        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

    End Sub

TCD67 Friday, July 17, 2009 5:47 PM

Hi,

It seems thatthe child DataTable contains records that don't have the corresponding parent records in the parent DataTable.A solution is to fill data into the child DataTable according to the filter used on the parent DataTable. Another solution is to only retrieve the child records from the child database table corresponding to a given parent record and fillthe resultsetinto the child DataTable when needed.

Hope this helps.

If you have any question, please feel free to let me know.

Sincerely,
Linda Liu

Please remember to mark the replies as answers if they help and unmark them if they provide no help. end us any feedback you have about the help from MSFT at fbmsdn@microsoft.com.

Marked As Answer byTCD67 Wednesday, July 22, 2009 7:55 PM

Linda Liu Wednesday, July 22, 2009 7:36 AM

Hi TCD,

If you enable FK constraint on the data relation between two DataTables in a DataSet, you should always fill data into the parent DataTable and then fill data into the child DataTable; otherwise, an FK constraint exception will occur.

Please remember to mark the replies as answers if they help and unmark them if they provide no help. end us any feedback you have about the help from MSFT at fbmsdn@microsoft.com.

Linda Liu Tuesday, July 21, 2009 3:04 AM

Thanks for the reply, Linda. As I mention in my original post, I do fill the parent adaptor first and it works just fine if I return ALL of the data. The problem arises when I filter the data on the parent table.

TCD67 Tuesday, July 21, 2009 4:05 PM

Hope this helps.

If you have any question, please feel free to let me know.

Sincerely,
Linda Liu

Please remember to mark the replies as answers if they help and unmark them if they provide no help. end us any feedback you have about the help from MSFT at fbmsdn@microsoft.com.

Marked As Answer byTCD67 Wednesday, July 22, 2009 7:55 PM

Linda Liu Wednesday, July 22, 2009 7:36 AM

Thanks Linda. To correct this problem I created sprocs for the child tables which use the same parameter as the parent. The error was being generated by trying to fill the child adaptors with a non-filtered result set.

TCD67 Wednesday, July 22, 2009 7:56 PM

You can use google to search for other answers

Custom Search

More Threads

• tooltip on hover on Datagridviewcolumnheader

• Update DataGridView from another form

• datagrid

• Custom Business Object and DataGrid

• Returning values from a single cell when the entire row is selected

• Codes with "undo" issue in dvg.

• Procedure has no parameters were supplied

• Printing DatagridView

• PAY PAL CODING....

• System.NullReferenceException when clicking column header