Master-Detail givs error when filtering by date

[Kevin Davis]

Hello.  I have set up a Master-detail relationship within a grid control, and everything is working perfect when I load all records.  

However, when I try by code to filter the Master dataset by date, I get the error that 

System.ArgumentException: 'This constraint cannot be enabled as not all values have corresponding parent values.'   Why wouldn't I get that error when loading all records which include the dates I'm trying to filter by?

Here is my code for filtering by date:

    Public Function GetStudentsByDate(ByVal dte As Date) As DataTable

        Dim ds As New DataSet()

        Using cn As New MySqlConnection(Con)

            cn.Open()

            Dim sql As String = "SELECT StudentID,FirstName,LastName,DriversLicenseNumber,Address,City,State,Zip,BirthDate,TrainingChoice," _

& "StartDate,Phone,Payor,Email,Program,Permit,FundingAgency,FundingPhoneNumber,Status,ContractNumber,MMSGateway,Promo,HaveVoucher FROM studentregistrations WHERE StartDate=?Date"

            Using cmd As New MySqlCommand(sql, cn)

                cmd.Parameters.AddWithValue("?Date", dte.ToString("yyyy-MM-dd"))

                cmd.ExecuteNonQuery()

                Dim adp As New MySqlDataAdapter(cmd)

                adp.Fill(ds, "studentregistrations")

            End Using

        End Using

        Using con1 As New MySqlConnection(Con)

            con1.Open()

            Dim adp2 As New MySqlDataAdapter("SELECT * FROM payments", con1)

            adp2.Fill(ds, "payments")

        End Using

        ds.Relations.Add(New DataRelation("studentregistrations_payments", ds.Tables(0).Columns("StudentID"), ds.Tables(1).Columns("StudentID")))

        If ds.Tables.Count > 0 Then

            Return ds.Tables(0)

        Else

            Return Nothing

        End If

        CN.Close()

    End Function

[Sreemon Premkumar Muthukrishnan]

Hi Kevin Davis,

The System.ArgumentException: 'This constraint cannot be enabled as not all values have corresponding parent values' exception occurs when establishing a relationship between two DataTable objects, but there is no corresponding parent row for some rows in the child DataTable. For example, a row with StudentID 6 might exist in the Payments table but not in the studentregistrations table. Ensure that every row in the child table has a corresponding row in the parent table. If it does have corresponding rows, it will work without exception. 

This exception is thrown due to improper data fetching from the database, not because of the WinForms SfDataGrid.

Regards,
Sreemon Premkumar M.

[Kevin Davis]

Hello.  Thanks for your quick reply, but when I load ALL RECORDS I get NO ERRORS, meaning they all have matching Parent ID's.  But when I try to filter THOSE SAME RECORDS  by date, then I get the error.  That doesn't make sense, why I get no errors when loading all my records but when I only want 20 records from those same original records it throws an error.

[Kevin Davis]

Ok here is a snippet of my student table:     

                 

Here is a snippet of my payments table  :

     

Student ID for Ben Stiller is 4.  In the payment table there is an entry for 'StudentID'  4...when I try to search by Ben's start date it throws the "no matching Parent ID" which there clearly is....is my code not correct?

Everything works fine when loading all records

Even if I filter using the built-in grid filters, it works.

Error only occurs if I try to filter by code.

[Sreemon Premkumar Muthukrishnan]

Hi Kevin Davis,

From the code snippet you shared, it appears you are retrieving a table of rows from the studentregistrations table by filtering with a specific date—for example, retrieving only the row with StudentID 4. We believe you are retrieving the entire payments table, for example, which includes all rows with StudentID values from 1 to 5. When establishing a relationship between both tables, the payments table (the child) contains rows with StudentID values from 1 to 5, but the studentregistrations table (the parent) only has the row with StudentID 4. During the relationship creation, the rows in the payments table with StudentID 1, 2, 3, and 5 are also expecting corresponding parent rows from the studentregistrations table, but it only has the row with StudentID 4. This mismatch is the cause of the exception.

That’s why, when loading all the rows, every child row has a corresponding parent row, and it works without exception. Ensure that the parent table contains rows for every StudentID present in the child table.

First table: Retrieved from studentregistrations (Parent)

StudentID	FirstName	LastName
4	Ben	Stiller

Second table: Retrieved from payments (Child)

StudentID	PaymentID	PaymentAmount
1	1	123
2	2	456
3	3	789
4	4	345
5	5	234

You can also filter the SfDataGrid programmatically in code. Please refer to the user guide documentation below for more information.

UG link: https://help.syncfusion.com/windowsforms/datagrid/filtering#column-filtering

Regards,

Sreemon Premkumar M

[Kevin Davis]

Thanks  Sreemon, the built in filtering did the trick.  However, a new issue just arose.  I added a ribbon to my form then decided I didn't want it, so I deleted it, now I get this error when everything was working fine before the ribbon add.  No clue as to why this all of a sudden is happening.

[Santhosh Govindasamy]

Hi Kevin Davis,

Thank you for your response.

 

From the details and the image provided, it appears that after adding and subsequently removing the Ribbon control from your form, you encountered an error. This could be due to some namespace references or residual changes that the Ribbon control left behind in your project.

We recommend reviewing the form to ensure that all references to the Ribbon control are fully removed.

 

Sometimes, removing a control can leave behind references or settings that cause unexpected issues.

Regards,
Santhosh.G