Constraint cannot be enabled as not all values have parent values

Question

i create a devexpress grid in code, columns defined below i am getting an error (see below) sorry if code snippets a tad long, want to show how i am doing the task!

              'Expense details
        FrmExpenses.GrdExpensesDetails.Columns.Add(New DevExpress.XtraGrid.Columns.GridColumn() With {
        .Caption = "PurchasedAtID",
        .FieldName = "PurchasedAtID",
        .Width = 0,
        .UnboundDataType = GetType(String),
        .Visible = False})

        FrmExpenses.GrdExpensesDetails.Columns.Add(New DevExpress.XtraGrid.Columns.GridColumn() With {
       .Caption = "Purchased At",
        .FieldName = "TName",
       .Width = 450,
       .UnboundDataType = GetType(String),
       .Visible = True})

        'Expense items
        FrmExpenses.GrdExpensesItems.Columns.Add(New DevExpress.XtraGrid.Columns.GridColumn() With {
        .Caption = "PurchasedAtID",
        .FieldName = "PurchasedAtID",
        .Width = 0,
        .UnboundDataType = GetType(String),
        .Visible = False})

        FrmExpenses.GrdExpensesItems.Columns.Add(New DevExpress.XtraGrid.Columns.GridColumn() With {
        .Caption = "ExpID",
        .FieldName = "ExpID",
        .Width = 0,
        .UnboundDataType = GetType(String),
        .Visible = False})

        FrmExpenses.GrdExpensesItems.Columns.Add(New DevExpress.XtraGrid.Columns.GridColumn() With {
       .Caption = "Date",
        .FieldName = "InvDte",
       .Width = 100,
       .UnboundDataType = GetType(String),
       .Visible = True})

        FrmExpenses.GrdExpensesItems.Columns.Add(New DevExpress.XtraGrid.Columns.GridColumn() With {
       .Caption = "InvNo",
       .FieldName = "InvNo",
       .Width = 100,
       .UnboundDataType = GetType(String),
       .Visible = True})

        FrmExpenses.GrdExpensesItems.Columns.Add(New DevExpress.XtraGrid.Columns.GridColumn() With {
       .Caption = "Amount",
       .FieldName = "InvAmt",
       .Width = 100,
       .UnboundDataType = GetType(String),
       .Visible = True})

        FrmExpenses.GrdExpensesItems.Columns.Add(New DevExpress.XtraGrid.Columns.GridColumn() With {
       .Caption = "%",
       .FieldName = "PrjPercentage",
       .Width = 100,
       .UnboundDataType = GetType(String),
       .Visible = True})

Now i define the Purchased At statement

  Private Function GetExpensesInvoicesDataTable() As DataTable

  Dim table As New DataTable With {
              .TableName = "ExpensesInvoices"
          }

  Try

      table.Columns.Add("PurchasedAtID", GetType(String)).Caption = "PurchasedAtID"
      With FrmExpenses.GrdExpensesDetails.Columns(0)
          .OptionsColumn.AllowFocus = False
          .OptionsColumn.AllowEdit = False
          .OptionsColumn.FixedWidth = True
          .AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Center
      End With

      table.Columns.Add("TName", GetType(String)).Caption = "TName"
      With FrmExpenses.GrdExpensesDetails.Columns(1)
          .OptionsColumn.AllowFocus = True
          .OptionsColumn.AllowEdit = False
          .OptionsColumn.FixedWidth = False
          .AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Default
      End With

      'Do not show Mileage or Vehicle Expenses
      Dim strV As String = "3165054"
      Dim strM As String = "3612231422"

      'show current year data
      Dim strID As String = "2023" 'Format(Now, "yyyy")

      Dim rea As OleDbDataReader

      con.Open()

      'Load
      Dim cmd As New OleDb.OleDbCommand("SELECT DISTINCT tblExpenses.PurchasedAtID, tblTradesVendors.TName FROM tblExpenses INNER JOIN tblTradesVendors ON tblExpenses.PurchasedAtID = tblTradesVendors.TradeID WHERE tblExpenses.Yr '" & strID & "' ORDER BY tblTradesVendors.TName", con)

      rea = cmd.ExecuteReader

      If rea.HasRows Then
          While rea.Read()

              table.Rows.Add(rea("PurchasedAtID"), rea("TName"))

          End While
      End If

      con.Close()

      Return table

  Catch ex As Exception

      con.Close()

      Return table

      XtraMessageBox.Show(ex.Message, Application.ProductName & " - GetExpensesInvoicesDataTable Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
      Err.Clear()

  End Try

  Return table

 End Function

Now we find details for the invoice

Private Function GetExpensesInvoicesRowsDataTable() As DataTable

    Dim table As New DataTable With {
            .TableName = "ExpensesInvoicesRows"
        }

    Try

        'show current year data
        'Dim strID As String = "2023"

        table.Columns.Add("PurchasedAtID", GetType(String)).Caption = "PurchasedAtID"
        With FrmExpenses.GrdExpensesItems.Columns(0)
            .OptionsColumn.AllowFocus = False
            .OptionsColumn.AllowEdit = False
            .OptionsColumn.FixedWidth = False
            .AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Center
        End With

        table.Columns.Add("ExpID", GetType(String)).Caption = "ExpID"
        With FrmExpenses.GrdExpensesItems.Columns(1)
            .OptionsColumn.AllowFocus = False
            .OptionsColumn.AllowEdit = False
            .AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Center
        End With

        table.Columns.Add("InvDte", GetType(String)).Caption = "InvDte"
        With FrmExpenses.GrdExpensesItems.Columns(2)
            .OptionsColumn.AllowFocus = True
            .OptionsColumn.AllowEdit = False
            .AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Far
        End With

        table.Columns.Add("InvNo", GetType(String)).Caption = "InvNo"
        With FrmExpenses.GrdExpensesItems.Columns(3)
            .OptionsColumn.AllowFocus = False
            .OptionsColumn.AllowEdit = False
            .AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Default
        End With

        table.Columns.Add("InvAmt", GetType(String)).Caption = "InvAmt"
        With FrmExpenses.GrdExpensesItems.Columns(4)
            .OptionsColumn.AllowFocus = True
            .OptionsColumn.AllowEdit = False
            .AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Far
        End With

        table.Columns.Add("PrjPercentage", GetType(String)).Caption = "PrjPercentage"
        With FrmExpenses.GrdExpensesItems.Columns(5)
            .OptionsColumn.AllowFocus = False
            .OptionsColumn.AllowEdit = False
            .AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Default
        End With

        Dim rea As OleDbDataReader

        con.Open()

        'Load
        Dim cmd As New OleDb.OleDbCommand("SELECT tblExpenses.PurchasedAtID, tblExpenses.ExpID, tblExpenses.InvDte, tblExpenses.InvNo, tblExpenses.InvAmt, tblExpInvItems.PrjPercentage FROM tblExpenses INNER JOIN tblExpInvItems ON tblExpenses.ExpID = tblExpInvItems.ExpID ORDER BY tblExpenses.InvDte DESC", con) 'WHERE tblExpenses.Yr ='" & strID & "'

        rea = cmd.ExecuteReader

        'Add to the Combo box
        If rea.HasRows Then
            While rea.Read()

                table.Rows.Add(rea("PurchasedAtID"), rea("ExpID"), rea("InvDte"), rea("InvNo"), rea("InvAmt"), rea("PrjPercentage"))

            End While
        End If

        con.Close()

        Return table

    Catch ex As Exception

        con.Close()

        Return table

        XtraMessageBox.Show(ex.Message, Application.ProductName & " - GetExpensesInvoicesRowsDataTable Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Err.Clear()

    End Try

    Return table
End Function

now load the grid

  Public Sub BindExpensesGrid()

Try

        _DataSet = New DataSet()

        _DataSet.Tables.Add(GetExpensesInvoicesDataTable())
        _DataSet.Tables.Add(GetExpensesInvoicesRowsDataTable())

        Dim keyColumn As DataColumn = _DataSet.Tables("ExpensesInvoices").Columns("PurchasedAtID")
        Dim foreignKeyColumn As DataColumn = _DataSet.Tables("ExpensesInvoicesRows").Columns("PurchasedAtID")

        _DataSet.Relations.Add("Level1", keyColumn, foreignKeyColumn)
        FrmExpenses.GrdExpenses.DataSource = _DataSet.Tables("ExpensesInvoices")

        FrmExpenses.GrdExpenses.ForceInitialize()

    Catch ex As Exception

        con.Close()

        XtraMessageBox.Show(ex.Message, Application.ProductName & " - BindExpensesGrid Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Err.Clear()

    Finally

        FrmExpenses.GrdExpenses.EndUpdate()

    End Try

End Sub

I receive the following error

i cannot see the reason?? would some one be able to point out the issue??? thanks in advance

Answer 1

you may be correct my friend, still learning DevExpress. however, for time being if i use same code as i do with Devcomponents, it works???

 'Dim strID As String = "2023"
 _DataSet = New DataSet()
 FrmExpenses.GrdExpenses.BeginUpdate()
 con.Open()

 Dim adapter As New OleDbDataAdapter("SELECT DISTINCT 
 tblExpenses.PurchasedAtID, tblTradesVendors.TName FROM (tblExpenses INNER 
 JOIN tblExpInvItems ON tblExpenses.ExpID = tblExpInvItems.ExpID) INNER 
JOIN tblTradesVendors ON tblExpenses.PurchasedAtID = 
tblTradesVendors.TradeID WHERE tblExpInvItems.Yr ='" & strID & "'  ORDER BY 
tblTradesVendors.TName", con)

adapter.Fill(_DataSet, "ExpensesInvoices")

CType(New OleDbDataAdapter("SELECT tblExpenses.PurchasedAtID, tblExpenses.ExpID, tblExpenses.InvDte, tblExpenses.InvNo, tblExpenses.InvAmt, tblExpInvItems.PrjPercentage FROM tblExpenses INNER JOIN tblExpInvItems ON tblExpenses.ExpID = tblExpInvItems.ExpID ORDER BY tblExpenses.InvDte DESC", con), OleDbDataAdapter).Fill(_DataSet, "ExpensesInvoicesRows")

_DataSet.Relations.Add("Level1", 
_DataSet.Tables("ExpensesInvoices").Columns("PurchasedAtID"), 
_DataSet.Tables("ExpensesInvoicesRows").Columns("PurchasedAtID"), False)

 With FrmExpenses.GrdExpenses
.DataSource = _DataSet
.DataMember = "ExpensesInvoices"

.ForceInitialize()
 End With
 con.Close()