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

Question

There were three similar questions in StackOverFlow but none gave an answer..

If have found why this error in occurring but don't know the fix.

I am using Strongly Typed Dataset for my project which is created as a dll for DAL.

I have added the Sql Server Table into this dataset using the designer and has created a DataAdapter

It works fine when i insert using DataTableAdapter

daLabTest.Insert(txtLabTestId.Text, cmbLabTestType.Text, cmbTestName.Text, txtLabFees.Text, dtpLabEffDate.Value)

but when i want to show the data from the table in a combobox or gridview i get this error.

i told that i found out what the problem is, I just previewed the data using DataSet designer and found out that the Function returns data like this...

The query i wrote to view this in dataset is

Select distinct(TestType) from LabTestTypes

so this should return only one column but the dataset is returning 5 columns but others as null, and the TestName column is a primary which should not be null when returned, so the problem exists..

To resolve this i tried to change the NullValue & AllowDBNull property to [Empty] and true respectively but that didn't worked for me.

Please help me in this...

Answer 1

That overly general constraint exception is nasty, where's the InnerException after so many complaints?!

This template may help identify the problem row and column but a "Fill" version of the query function is needed. E.g. GetDistinct*() --> Fill*(). Then a table can be created and interrogated for the row's error text.

    SomeTable tTable = new SomeTable()
    try {
       // sorry, if you have a GetData, change to the fill version
         someTable.FillByActiveLogin(tTable, loginName);
    } catch (System.Data.ConstraintException constrExc) {             
            System.Data.DataRow[] rowsErr = tTable.GetErrors();
            for (int i = 0; i < rowsErr.Count(); i++)
                if (rowsErr[i].HasErrors)
                  Dbg.WriteLine(rowsErr[i].RowError);
    }

(Thanks Michael S for this hint whoever/wherever you are!)