System.Data.ConstraintException Failed to enable constraints.

  • Thread starter Thread starter john_j_whalen
  • Start date Start date
J

john_j_whalen

I hope someone can help me with my error :
An unhandled exception of type 'System.Data.ConstraintException'
occurred in system.data.dll

Additional information: Failed to enable constraints. One or more rows
contain values violating non-null, unique, or foreign-key constraints.

I am able to create a 2 column table, add a primary key, add rows to
the table, then update the table all using LoadDataRow.

When I read an .XML file and attempt to do the same update I get the
error. Any insight into what I am missing would be greatly
appreciated.


CODE THAT WORKS
Dim dt As New DataTable, dc1 As New DataColumn, dc2 As New
DataColumn
dc1.ColumnName = "ONE"
dc1.DataType = System.Type.GetType("System.String")
dc2.ColumnName = "TWO"
dc2.DataType = System.Type.GetType("System.String")

dt.Columns.Add(dc1)
dt.Columns.Add(dc2)

Dim dcPK(0) As DataColumn
dcPK(0) = dt.Columns(0)
dt.PrimaryKey = dcPK

Dim mydata(1) As Object
mydata(0) = "A"
mydata(1) = "B"
dt.LoadDataRow(mydata, True)
mydata(0) = "C"
mydata(1) = "D"
dt.LoadDataRow(mydata, True)

Dim drow As DataRow
For Each drow In dt.Rows
MsgBox(drow.ItemArray(0))
MsgBox(drow.ItemArray(1))
Next drow

mydata(0) = "A"
mydata(1) = "F"
dt.LoadDataRow(mydata, True)

For Each drow In dt.Rows
MsgBox(drow.ItemArray(0))
MsgBox(drow.ItemArray(1))
Next drow


CODE THAT FAILS
Dim ds As New DataSet
ds.ReadXml("C:\TEST.xml")

'ADD A PK
Dim dcPK(0) As DataColumn
dcPK(0) = ds.Tables(0).Columns(0)
ds.Tables(0).PrimaryKey = dcPK

'CHANGE DATA
Dim ar(1) As Object
ar(0) = "44" 'ORIG VALUE IS INTEGER 44
ar(1) = "Updated" 'ORIG VALUE IS "OrigValue"

ds.Tables(0).BeginLoadData()
ds.Tables(0).LoadDataRow(ar, True)
ds.Tables(0).EndLoadData() 'ERROR HERE
 
Hi John,

Probably ReadXml sets (because of source file) some other constraints.
You might check the error with DataRow's GetColumnError and
GetColumnsInError methods.
It will tell you where the problem lies.
 
I found that the XML method (see KB 307224) seems to lose vital information,
especially constraints. I was originally planning to pass an XML DiffGram
back from my presentation layer all the way back to my Data Layer which
would reconstruct the DataSet and attempt to da.Update(ds,"<tablename>") the
changes. No matter what I did (e.g., rolling my own custom UpdateCommand
after creating a CommandBuilder object), I kept getting the same "Failed to
enable constraints" error you got. My attempts were with System.Data.OleDb
using the Jet 4.0 provider.

Once I passed a real DataSet through the different layers instead of XML
strings, I was able to save my changes with a minimum of effort. While in
the debugger, I noticed the Constraints were present, unlike when I did
everything through XML.

I wouldn't be surprised to learn that this "feature" of passing an XML
string only works for the Microsoft SQL Server provider, which is what KB
307224 uses in the example code.

I'd would have tried the same scenario with the System.Data.Oracle provider
to get another perspective, but my application only needs to read from an
Oracle 9i R2 database, not update it.

Lesson Learned: if you're not a pure Sql Server 2000 applicatoin, stick to
passing DataSet objects between layers of an n-tiered application.
 
Back
Top