Tag Archives: frustration

Useless Error Messages and DataSet Hell


I don’t have much experience working with with Visual C# DataSets, but today I looked at an issue with our code that uses them.  The error message was:

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

Great–but which one?  A quick glance at the stack trace and related code pointed to the table in question, but said table has 150+ columns.  Nothing is more annoying than being told there’s an issue without being given enough information to fix it.  Before you read on, I’d like to mention that this will probably be a lengthy post.  As such, I’m going to summarize the solution at the end; feel free to jump there if you’re in a pinch or are otherwise just uninterested in the journey.

I asked around to see if anyone some experience troubleshooting a similar issue, but without much luck.  I tried the MSDN page on troubleshooting the System.Data.ConstraintException, which offered these solutions:

  • Relax or turn off constraints in your DataSets.  Setting the EnforceConstraints property to “False” didn’t help.
  • Make sure you are not trying to assign a value to a primary key field where the primary key already exists in the data table.  Looking at the query result in SQL Developer eliminated this one.
  • Clear datasets before loading them from view state.  The data being processed was in a single batch (group), so I figured this wasn’t the case; tried clearing the DataSet and Table just before the call to fill anyway, but no luck.

A colleague did a quick search as well, and I was pointed toward a page on MSDN on how to handle null values on a data column, as a handful of the columns being returned are in fact null.  So I opened up the XSD in Visual Studio to take a look.  Most nullable columns already had the two related properties set correctly (AllowDBNull set to “True” and DefaultValue set to “<DBNull>”), but the third [that the article points out] didn’t make much sense to me:  NullValue, described as “a value returned if this column is null,” was set to “(Throw exception)”.  The MSDN page reiterates this, stating that property value will cause null values to throw an Exception.  I questioned the validity since this code has been in use for awhile and many fields were likely null in previous scenarios, but I decided to give it a try anyway.

The drop-down box for the NullValue field offered two other options, “(Empty)” and “(Null)”.  I tried the null value first, but both caused a “Property is not valid” error message to be shown.  I could type in “(Nothing)” like the article mentions and the designer would allow it, but this also didn’t seem right.  Not knowing what particular column(s) were causing the issue, I decided to try an experiment with a test project. After firing up SQL Developer, I quickly added some test data to our Oracle development environment to play with:

create table nulltest (
  id            number primary key,
  sugar         varchar2(10),
  spice         number(9),
  everything    date,
  nice          number(9,2)
);

create sequence nulltest_id_seq
  start with 1
  increment by 1
  nomaxvalue;

insert into nulltest (id, sugar, spice, everything, nice)
  values (nulltest_id_seq.nextval, 'chocolate', 42, sysdate, 3.14);
insert into nulltest (id, sugar, spice, everything, nice)
  values (nulltest_id_seq.nextval, null, null, null, null);

commit;

And also a quick Windows Forms Application to test it out. I added a Button to the form and a DataSet item, and dragged the table over from the Server Explorer after connecting to the database. The code:

// in the code for the DataSet:
private TestDataSet mData;
private TestDataSetTableAdapters.NULLTESTTableAdapter mTableAdapter;

public TestData()
{
	mTableAdapter = new TestDataSetTableAdapters.NULLTESTTableAdapter();
	mData = new TestDataSet();
}

public List<TestDataSet.NULLTESTRow> GetAllRows()
{
	TestDataSet.NULLTESTDataTable data = mTableAdapter.GetData();
	List<TestDataSet.NULLTESTRow> result = new List<TestDataSet.NULLTESTRow>();
	foreach (TestDataSet.NULLTESTRow row in data)
	{
		result.Add(row);
	}

	return result;
}

// and in the test form:
private void btnTest_Click(object sender, EventArgs e)
{
	TestData data = new TestData();
	List<TestDataSet.NULLTESTRow> rows = data.GetAllRows();
	MessageBox.Show("Returned " + rows.Count + " data rows.");
}

Leaving NullValue set to “(Throw exception)” did not matter; both rows were returned, even though everything but the id column for the second was null. I then turned to this blog post on DataSet Hell. One of the first posts links to Useless Exception Messages where drub0y expresses the same complaints, suggesting:

“Here we have this beautiful abstract model of constraints based off the Constraint class, but this exception does nothing except say a constraint has failed. Hello!? How about at least telling me the ConstraintName and Table of the constraint that failed in the message? While we’re at it, it would be even more useful to add a property to the ConstraintException class, perhaps called ‘FailedConstraint’, which is a reference to the Constraint that failed so I could pull even more detail based off of it’s concrete type if I needed to.”

I hear ya. Sifting through more of the comments where others mention what I’ve already tried, I came across a new solution suggestion noting the GetErrors method of the DataTable class. This didn’t work either, but I decided to clean up the mess of code I had added while troubleshooting and checking in a method for grabbing the info for future use:

public string GetAllDataSetErrorsAsString(ClaimDataSet dataSet)
{
	StringBuilder result = new StringBuilder();
	DataRow[] rowsInError;

	foreach (DataTable currentTable in dataSet.Tables)
	{   // Test if the table has errors. If not, skip it.
		if (currentTable.HasErrors)
		{   // Get an array of all rows with errors.
			rowsInError = currentTable.GetErrors();
			// Print the error of each column in each row.
			for (int index = 0; index < rowsInError.Length; index++)
			{
				foreach (DataColumn currentColumn in currentTable.Columns)
				{
					result.AppendLine(currentColumn.ColumnName + " " + rowsInError[index].GetColumnError(currentColumn));
				}
				rowsInError[index].ClearErrors();
			}
		}
	}

	return result.ToString();
}

The TableAdapter wasn’t even able to complete the query despite EnforceConstraints being set to “False”. Using the “Preview Data” function with the same ID as the data causing the issue didn’t help pinpoint the problem, either. Then another comment, where Joel seems to be on to something:

“I ran into this problem as well, and it actually had nothing to do with null or unique values. The problem was caused by a discrepancy between the defined max size of a data column in my project’s XSD and the size in the database. When the call was made to fill the table adapter, the above error message appeared, apparently because one of the columns in the db was larger than the XSD / table adapter allowed for. As far as I know, there’s no meaningful way to parse and deal with this error message. Also, I couldn’t find a way to refresh the XSD, even after the reference to the problematic table in my data connections was updated.”

Sure enough, a refresh didn’t fix the problem. Starting fresh wasn’t an option because of the handful of tables being used and additional queries we had added, but some mentioned this was how they fixed the problem. In the end, I went through column by column and made sure that the MaxLength property in my XSD matched up with the associated Oracle database table. Yes, checked all 150+ columns manually–and not just in the designer. Changing the MaxLength column in the designer and saving the changes didn’t push them to the designer-generated code for previously configured queries, so I also had to search through that mess of code and update any queries where the fields I updated were used as parameters, fixing the length. I ran the Solution and gave it the same data file, and sure enough the issue was fixed, allowing the file to process and the database get updated successfully. Finally!

I should also note that while modifying the XSD using the designer in Visual Studio, I ran across another useless error message:

“An unexpected Error has occurred.”

Source: Microsoft.VSDesigner
ErrorCode:10008
Additional Info: Null

Helpful, right? I tried closing and reopening the XSD without much luck, but closing Visual Studio altogether and reopening the Solution did the trick.

The original blog post that I mentioned earlier (on DataSet Hell) is from late 2004; it’s 2011, and developers are still working on this crap. That’s about 7 years, yet it still lingers! If you’ve got the time, you might find this article an interesting read.

If anyone has a different solution for this or you just want to share your story, please leave a comment or drop me a message–I’d love to hear about it!


Summary

  1. If your situation allows it to be easy/quick enough, just remove the Table and TableAdapter from the DataSet and add everything again.  My project had many related Tables and TableAdapters configured as well as many additional queries, so I moved straight on to solution 2.
    • If you run across the helpful “An unexpected Error has occurred.” message while modifying your XSD in the designer, try saving your work, closing Visual Studio, reopening the Solution and try again.
  2. Make sure the MaxLength property in the DataSet matches what is set in the associated database table.  Mark down any fields you find a discrepancy with as you go, and be sure to search the designer-generated code for any previously configured queries using those fields as a parameter and make sure the maximum length matches up there as well; simply changing the MaxLength property value for a column did not populate said code in my case.
  3. Make sure nullable values are allowed in the DataSet for columns that can be null.  The “AllowDBNull” property should be set to “True”, and the “DefaultValue” property to “<DBNull>”.
  4. Try using the “Preview Data” function to pinpoint the problem.  If you try running the troublesome query in the Designer, you might see missing fields; fields not listed in the query should be empty, which makes the binding not work properly.
  5. Relax or turn off constraints in your DataSets.  If setting the EnforceConstraints property to “False” will get your data into the DataSet, you should be able to use the GetErrors method of the DataTable class to find out which columns are the problem.
  6. Make sure you are not trying to assign a value to a primary key field where the primary key already exists in the data table.  Use your database development tool to verify the data you’re working with.
  7. Clear datasets before loading them from view state.  If there’s already data in the DataSet when you try filling it, you might get the ConstraintException.