Solved

Why am I getting this error: Failed to enable constraints

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
C# HTTP GET method sample code 3 57
Vb. Net application freezes 9 46
Regular expression help 2 25
REXEX help Part 2 2 25
Summary Displaying images in RichTextBox is a common requirement with limited solutions available. Pasting through clipboard or embedding into RTF content only support static images.  This article describes how to insert Windows control objects int…
This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

772 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