"Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."

This has got be the most frustrating error message I've ever had to deal with. I keep getting it in a variety of situations. Most recently I'm simply trying to pull one field from one record in one table in a SQL Server database and load it to a dataset:

SELECT TOP 1 SortOrder FROM Questions where surveyid=12 ORDER BY SortOrder DESC

It throws the error when it hits:

adpQuestions.Fill(DsQuestions3)

Sql Server Enterprise Manager displays a single record when this query is submitted. There are NO relationships explicitly defined between this table and any other in the database. The dataset is CLEAR when the Fill is issued. The stars appear to be favorably aligned, so I just don't understand it!

Web searches for the error message have proven fruitless - I find lots of people asking about it but nobody seems able to provide a concrete answer. I have yet to figure out what it means or what I'm supposed to do about it and usually end up changing my approach and dodging it with work arounds, only to have it rear its ugly head the next tiem I try to do something simple.

The only thing I can say for sure about it is that it only seems to occur when using the DB I/O objects generated when the Server Explorer is used. Are other developers finding it's easier (read: less temperamental) to code the I/O yourself instead of relying on MicroSoft's leaky "plumbing" to do database I/O? Is anybody else having much success using the Server Explorer objects? Can anybody explain why this is happening and what I can do to avoid this in the future? If not, has somebody got simple example code that would successfully submit the query above and allow me to walk the returned data without using any of the Server Explorer objects?

Three days, numerous reads, and not a single response? Either I'm the only one having this problem or nobody here knows what it means!

Meanwhile, I'm still stuck. Doesn't somebody have an example of how to do this manually, IOW without using the Explorer server objects, so I can see whether they're at least somehow related to this problem?

ANY help would be appreciated!

Ron,

What are you trying to do? I am not familiar with the DB I/O object, but I work with ADO.Net and the various SQLClient objects. Explain a little about what you want to do and I'll see if I can help.

Peter

I'm looking for a chunk of code to connect to a SQL Server database, submit a SQL query to retrieve a record, and populate a dataset, all without using those automatically generated objects. Unfortunately, because Microsoft is touting them as the panacea to DB I/O, that's all they bother to teach in their courses, so I have no clue how to go about doing this manually in the .NET world. If I could get such an example to work, I think I could abandon MicroSoft's leaky plumbing and code my own in the future, if that's what it takes to get rid of this pesky error once and for all.

I've tried creating a brand new form, new server created objects, etc., and copied the code to that page. It works there! So, it seems to me that the problem is somehow related to reusing the adapater and/or dataset objects for a second query. This seems odd to me since a dataset can supposedly hold multiple tables and I know this very same logic has worked for me elsewhere. Still looking...

Look into the System.Data.SqlClient classes.

Dim objConn As New System.Data.SqlClient.SqlConnection("<my connection string>")
Dim objCommand As New System.Data.SqlClient.SqlCommand()
Dim objReader As System.Data.SqlClient.SqlDataReader

objCommand = New System.Data.SqlClient.SqlCommand("<my sql statement>", objConn)
objConn.Open()
objReader = objCommand.ExecuteReader
If objReader.Read Then
    txtTextBox1.Text = objReader.GetString(0)
    txtTextBox2.Text = objReader.GetString(1)
    txtTextBox3.Text = objReader.GetString(2)
    txtTextBox4.Text = objReader.GetString(3)
    txtTextBox5.Text = objReader.GetString(4)
    txtTextBox6.Text = objReader.GetString(5)
End If
objConn.Close()

Peter,

That does exactly what I wanted and worked on the very first try. I guess I'll have to do these things this way in the future, since the objects aren't as hassle free as Microsoft would like us to believe. Happily the code to do this isn't significantly longer than what's necessary to use the server generated objects. Thanks!