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(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?

LVL 1
grostonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

espsCommented:
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



grostonAuthor Commented:
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?
grostonAuthor Commented:
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.
Computer101Commented:
PAQed, with points refunded (125)

Computer101
E-E Admin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.