groston
asked on
Why am I getting this error: Failed to enable constraints
This is a vb.net application running on my development machine. The SQL database is also on this machine. Here is the code snippet that is causing me grief:
Private Sub cmd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmd.Click
Dim con As New SqlConnection(Configuratio
Dim ds As New DataSet
Dim da As SqlDataAdapter = New SqlDataAdapter("Select * from BADGE", con)
da.FillSchema(ds, SchemaType.Mapped, "Badge")
da.Fill(ds, "Badge")
...
End Sub
Pretty straight forward! I have used this exact snippet elsewhere and it worked perfectly. (I am using FillSchema to assign the primary key so that I can modify the DataSet and then update the underlying data source.) In this application, however, I get the following error (on the da.fll line):
An unhandled exception of type 'System.Data.ConstraintExc
Additional information: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
Note: I did not design the underlying database. It is from an application we have from which I am extracting reports for the purpose of presenting reports.
BADGE is a simple table:
ID (varbinary(18), primary key, no nulls)
Lname (nvarchar(40), no nulls)
Fname (nvarchar(20))
MI (nvarchar(4))
etc
I have done the following using SQL query analyser:
verified that there are no rows with ID = NULL
verified that there are no rows with Lname = NULL
verified that no two rows have the same ID
I have done the following with SQL enterprise manager table designer->properties:
Selected the 'Relationships' tab and found there are no foriegn key relationships defined
Selected the 'Check constraints' tab and found there are no constraints defined
Selected the 'Indexex/Keys tab and found:
- Index using Lname, Fname and MI, 'create unique' is not selected, 'create as clustered' is selected
- primary key (as above)
- Index using Lname, 'create unique' is not selected, 'create as clustered' is not selected
I have done the following with SQL enterprise manager:
-Using 'diagrams', created a new daiagram, selected the table BADGE and check the box to include all related tables - no other tables were inserted and no relationships were displayed.
- Right-clicking on BADGE, I selected 'All tasks'->'Display dependencies'. What I found a number of items that depend on BADGE: a pile of views, a few stored procedures and a trigger (for deleting items from this table)
I am at a loss. As a work-around, it seems possible (likely) that I could simply define the primary key column , but it seems that such definitions should source from the DB schema and not some vb.net code. I would rather not do this. Instead, I would like to determine what I am doing wrong.
What do you suggest?
Private Sub cmd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmd.Click
Dim con As New SqlConnection(Configuratio
Dim ds As New DataSet
Dim da As SqlDataAdapter = New SqlDataAdapter("Select * from BADGE", con)
da.FillSchema(ds, SchemaType.Mapped, "Badge")
da.Fill(ds, "Badge")
...
End Sub
Pretty straight forward! I have used this exact snippet elsewhere and it worked perfectly. (I am using FillSchema to assign the primary key so that I can modify the DataSet and then update the underlying data source.) In this application, however, I get the following error (on the da.fll line):
An unhandled exception of type 'System.Data.ConstraintExc
Additional information: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
Note: I did not design the underlying database. It is from an application we have from which I am extracting reports for the purpose of presenting reports.
BADGE is a simple table:
ID (varbinary(18), primary key, no nulls)
Lname (nvarchar(40), no nulls)
Fname (nvarchar(20))
MI (nvarchar(4))
etc
I have done the following using SQL query analyser:
verified that there are no rows with ID = NULL
verified that there are no rows with Lname = NULL
verified that no two rows have the same ID
I have done the following with SQL enterprise manager table designer->properties:
Selected the 'Relationships' tab and found there are no foriegn key relationships defined
Selected the 'Check constraints' tab and found there are no constraints defined
Selected the 'Indexex/Keys tab and found:
- Index using Lname, Fname and MI, 'create unique' is not selected, 'create as clustered' is selected
- primary key (as above)
- Index using Lname, 'create unique' is not selected, 'create as clustered' is not selected
I have done the following with SQL enterprise manager:
-Using 'diagrams', created a new daiagram, selected the table BADGE and check the box to include all related tables - no other tables were inserted and no relationships were displayed.
- Right-clicking on BADGE, I selected 'All tasks'->'Display dependencies'. What I found a number of items that depend on BADGE: a pile of views, a few stored procedures and a trigger (for deleting items from this table)
I am at a loss. As a work-around, it seems possible (likely) that I could simply define the primary key column , but it seems that such definitions should source from the DB schema and not some vb.net code. I would rather not do this. Instead, I would like to determine what I am doing wrong.
What do you suggest?
ASKER
esps,
Thank you for the response.
I did some looking and there dataAdapters do not have a constraints property, so this apporach is not the answer.
I have done some further experimentation and have been able to reduce this problem to the simplest form possible:
table name: BadgeCopy
Table design:
ID, varbinary(18), no nulls
Lname, nvarchar(40), no nulls
Fname, nvarchar(20)
Here is my test data (extracted using sql query analzyer: Select * from BadgeCopy"):
0x0029C7BFC642845943E493D2
0x0029864227BB36334A6F9772
Here is a short test program:
Private Sub cmdIntegrity()
Dim con As New SqlConnection(**connection
Dim ds As New DataSet
Dim da As SqlDataAdapter = New SqlDataAdapter("Select * from BADGECOPY", con)
da.Fill(dsNex, "Badge")
' Set up the ID column as the primary key
Dim pk(1) As DataColumn
pk(0) = ds.Tables("Badge").Columns
ds.Tables("Badge").Primary
End Sub
When I run this subroutine, I get (at the line indicated above):
An unhandled exception of type 'System.ArgumentException'
Additional information: These columns don't currently have unique values.
The only way (in my way of thinking) for this to occur is if the uniqueness is determined solely by the first two bytes of the ID, but this limitation would be too stupid for words.
Does this provide any further clues as to what might be going wrong?
Thank you for the response.
I did some looking and there dataAdapters do not have a constraints property, so this apporach is not the answer.
I have done some further experimentation and have been able to reduce this problem to the simplest form possible:
table name: BadgeCopy
Table design:
ID, varbinary(18), no nulls
Lname, nvarchar(40), no nulls
Fname, nvarchar(20)
Here is my test data (extracted using sql query analzyer: Select * from BadgeCopy"):
0x0029C7BFC642845943E493D2
0x0029864227BB36334A6F9772
Here is a short test program:
Private Sub cmdIntegrity()
Dim con As New SqlConnection(**connection
Dim ds As New DataSet
Dim da As SqlDataAdapter = New SqlDataAdapter("Select * from BADGECOPY", con)
da.Fill(dsNex, "Badge")
' Set up the ID column as the primary key
Dim pk(1) As DataColumn
pk(0) = ds.Tables("Badge").Columns
ds.Tables("Badge").Primary
End Sub
When I run this subroutine, I get (at the line indicated above):
An unhandled exception of type 'System.ArgumentException'
Additional information: These columns don't currently have unique values.
The only way (in my way of thinking) for this to occur is if the uniqueness is determined solely by the first two bytes of the ID, but this limitation would be too stupid for words.
Does this provide any further clues as to what might be going wrong?
ASKER
Here is the sad answer:
From a poster on tek-tips:
When you load the Byte Array into the dataset it loads the object type. The datarow cannot interpet complex datatypes. Therfore when it checks to see if they are unique it will see that the string "Byte()Array" is in both columns and it is not unique. It does not know how to convert it like you function does. You can of course set the Primary key to the column that you added IDString.
From a poster on tek-tips:
When you load the Byte Array into the dataset it loads the object type. The datarow cannot interpet complex datatypes. Therfore when it checks to see if they are unique it will see that the string "Byte()Array" is in both columns and it is not unique. It does not know how to convert it like you function does. You can of course set the Primary key to the column that you added IDString.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Have a look at the TABLE you are trying to access. Then have a look at the FIELDS values. Sometimes as we have experienced before that it could happen that what you specify in VB.NET and what is actually in the SQL database does not match and therefore you get a constraint error.
For the moment try to disable the constraints with:
da.constraints.enabled = false
I'll get back to you