DevExpress Grid约束启用失败报错原因排查求助

算力阿峰

2026-7-1

DevExpress Grid 外键约束错误排查："Constraint cannot be enabled as not all values have parent values"

执行BindExpensesGrid方法时触发错误：Constraint cannot be enabled as not all values have parent values，以下是相关代码及问题分析：

一、Grid列定义代码

'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})

二、获取主表数据方法

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()
        XtraMessageBox.Show(ex.Message, Application.ProductName & " - GetExpensesInvoicesDataTable Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Err.Clear()
        Return table
    End Try
    Return table
End Function

三、获取子表数据方法

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()
        XtraMessageBox.Show(ex.Message, Application.ProductName & " - GetExpensesInvoicesRowsDataTable Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Err.Clear()
        Return table
    End Try
    Return table
End Function

四、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

错误原因及问题点

这个错误的核心是：子表ExpensesInvoicesRows中存在PurchasedAtID值，在主表ExpensesInvoices中找不到对应的父记录，具体代码问题如下：

1. 主表SQL语法错误

主表查询的WHERE子句写法错误：

"WHERE tblExpenses.Yr '&" & strID & "'"

正确的写法应该是：

"WHERE tblExpenses.Yr = '" & strID & "'"

原语法会导致SQL执行失败，主表可能返回空数据或错误结果，使得子表所有记录都找不到匹配的父记录，触发约束错误。

2. 主表未执行排除特定Vendor的逻辑

代码中定义了strV和strM用于排除Mileage或Vehicle Expenses，但SQL中未添加对应的过滤条件，导致主表未排除这些Vendor，但如果子表包含这些Vendor的记录，就会出现子表有记录而主表无对应父记录的情况。

需要在主表SQL的WHERE子句中添加：

AND tblExpenses.PurchasedAtID NOT IN ('" & strV & "', '" & strM & "')

3. 子表未同步过滤条件

子表查询未添加年份过滤（注释掉了WHERE tblExpenses.Yr ='" & strID & "'），也未排除指定Vendor，导致子表返回的记录范围远大于主表，必然存在主表中没有的PurchasedAtID值，触发约束错误。

解决步骤

修复主表SQL语法错误，将WHERE子句改为正确的等值判断。
在主表SQL中添加排除特定Vendor的条件，实现注释中描述的逻辑。
给子表SQL添加相同的过滤条件（年份过滤+排除指定Vendor），确保子表的所有PurchasedAtID都能在主表中找到对应记录。
（可选）如果允许子表存在无父记录的情况，可以在建立DataSet关系时设置enforceConstraints=False，但不推荐，会破坏数据一致性：

_DataSet.Relations.Add("Level1", keyColumn, foreignKeyColumn, False)

内容的提问来源于stack exchange，提问作者Robert

DevExpress Grid约束启用失败报错原因排查求助

一、Grid列定义代码 #

二、获取主表数据方法 #

三、获取子表数据方法 #

四、Grid绑定方法 #

错误原因及问题点 #

1. 主表SQL语法错误 #

2. 主表未执行排除特定Vendor的逻辑 #

3. 子表未同步过滤条件 #

解决步骤 #