Solved

Why am I getting this error: Failed to enable constraints

Posted on 2003-11-02
5
1,847 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
  • 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now