Solved

Why am I getting this error: Failed to enable constraints

Posted on 2003-11-02
5
1,996 Views
Last Modified: 2007-12-19
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(ConfigurationSettings.AppSettings("Connection"))
        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.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.

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?

0
Comment
Question by:groston
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 

Expert Comment

by:esps
ID: 9692290
If you have access to the SQL database check to see that all your values are correct e.g.

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



0
 
LVL 1

Author Comment

by:groston
ID: 9740771
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"):

0x0029C7BFC642845943E493D2AF2D7122A16A      Joe      User
0x0029864227BB36334A6F9772DB3F51CB3906      Jane      Student

Here is a short test program:

    Private Sub cmdIntegrity()
        Dim con As New SqlConnection(**connection string**)
        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("ID")
        ds.Tables("Badge").PrimaryKey = pk   <<== error occurs here
    End Sub

When I run this subroutine, I get (at the line indicated above):

An unhandled exception of type 'System.ArgumentException' occurred in system.data.dll
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?
0
 
LVL 1

Author Comment

by:groston
ID: 9747696
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.
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 9768552
PAQed, with points refunded (125)

Computer101
E-E Admin
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query Help Top 1 and Distinct? 6 49
Upgrading my SSIS package in VS 2012 6 91
Updating specific fields only in MVC/Entity controller 2 27
IDE for Python 5 73
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question