Constraint error on DataSet filll

[Corey Wirun]

Hi All,

I've got a strongly typed dataset that I'm trying to Fill in with an
adapter. The dataset has 3 tables in it, PageData, RowData and SummaryData.
I'm trying to ::Fill into PageData with the following SQL:

SELECT distinct SRC_DBINDEX, SRC_TAG, src_descript FROM RPDCY_T
UNION
SELECT distinct DST_DBINDEX, DST_TAG, dst_descript FROM RPDCY_T

And I get this famous error:

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

The XSD schema has no keys or relations in it. The wierd part is that I can
run this in SQL EM and I see there are no NULL columns and no duplicates
anywhere in the result set.

How do people chase down these errors since the cause is hidden behind the
generic error message?

Thanks in Advance!
Corey.

Here's the XSD snippet for 'PageData':

....
<xs:element name="PageData">
<xs:annotation>
<xs:documentation>Page Heading Information</xs:documentation>
</xs:annotation>
<xs:complexType>
<xs:all>
<xs:element name="DBINDEX" type="xs:long">
<xs:annotation>
<xs:documentation>DBINDEX of PageData Object</xs:documentation>
</xs:annotation>
</xs:element>
<xs:element name="TAG">
<xs:annotation>
<xs:documentation>Name of PageData Object</xs:documentation>
</xs:annotation>
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="20" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="DESCRIPT">
<xs:annotation>
<xs:documentation>Description of PageData Object</xs:documentation>
</xs:annotation>
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="50" />
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:all>
</xs:complexType>
</xs:element>
....

 

[William Ryan eMVP]

Corey:

If you skip the union and just call fill with each respective query on the
same datatable, will it work? This isn't the problem, but I'd verify that I
can get at least one of the select statements in there. My guess is that
one of your fields somewhere is violating a rule.

I know you've checked this but I've had trouble a few times in a similar
situation. Make sure nothing in the xml is indicating that nulls aren't
allowed too.

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/

 

[Miha Markic [MVP C#]]

How do people chase down these errors since the cause is hidden behind the
generic error message?

Check out each row's RowError string.

 

[Corey Wirun]

Hi William,

No, I need the two SELECTs. The table RPDCY_T defines endpoints for
pipelines. It's possible the source endpoint for one pipe can be the
destination endpoint for another. The intention of the query is to return
me a list of all endpoints for all pipes, with dups removed (i.e. the
UNION).

How do you set the nullable property in the XML for non-key columns? I set
it on the key for PageData in the XML, but it didn't make a difference.

Thanks for the reply.
Corey.

 

[Corey Wirun]

How do you do that? There's no rows in the target strongly typed Dataset,
is there?

Thanks!
Corey.

 

[Corey Wirun]

Okay, some more information. It appears the SQL has nothing to do with the
problem. I tried these variations (all failed):

SELECT distinct SRC_DBINDEX, SRC_TAG, src_descript FROM RPDCY_T
SELECT DST_DBINDEX, DST_TAG, dst_descript FROM RPDCY_T
SELECT DST_DBINDEX, DST_TAG, dst_descript FROM RPDCY_T where DST_DBINDEX =
10003 <-- returns 1 row.
SELECT DISTINCT DST_DBINDEX, DST_TAG, dst_descript FROM RPDCY_T where
DST_DBINDEX = 10003

So, this leads me back to the XML, I guess.

I have NO NULLS ANYWHERE in the source table, so I'm at a loss to explain
why the error occurs.

Thanks for any help.
Corey.

 

[Corey Wirun]

Okay, thanks for the pointer Miha. I found this code in MSDN (under
'RowError'). And it gave me more information on the constraint problem.
Thanks!

private void TestForErrors(DataSet myDataSet)
{
// Test for errors. If DataSet has errors, test each table.
if(myDataSet.HasErrors)
{
foreach(DataTable tempDataTable in myDataSet.Tables)
{
// If the table has errors, then print them.
if(tempDataTable.HasErrors) PrintRowErrs(tempDataTable);
}
}
}

private void PrintRowErrs(DataTable myTable)
{
foreach(DataRow myDataRow in myTable.Rows)
{
if(myDataRow.HasErrors)
{
DiagnosticLog.Debug(myDataRow.RowError);
}
}
}

 

[Corey Wirun]

Hi again all.

Problem solved.

The issue was with the column name disagreement between the query and the
PageData table columns in the strong DataSet. The ::Fill brought in extra
columns, and NULLing out the ones the DataSet expected - leading to NULLs
inserted. Oops.

So, I changed the query to:

SELECT distinct SRC_DBINDEX as DBINDEX, SRC_TAG as TAG, src_descript as
DESCRIPT from RPDCY
UNION
SELECT distinct DST_DBINDEX, DST_TAG, dst_descript FROM RPDCY_T

And it worked. Thanks to all for help.
Corey.