ADO.Net DataSets and constraints error

[Neil]

I have a database table TBLCOMPANYCONTACTS :

CREATE TABLE RELMAN.TBLCOMPANYCONTACTS (
COMPANY_CODE VARCHAR2(10) NOT NULL,
CONTACT_IDX NUMBER(3) NOT NULL,
TITLE VARCHAR2(4) NULL,
FIRSTNAME VARCHAR2(50) NULL,
LASTNAME VARCHAR2(50) NULL,
POSITION VARCHAR2(50) NULL,
TELNO VARCHAR2(50) NULL,
MOBILE VARCHAR2(50) NULL,
FAX VARCHAR2(50) NULL,
EMAIL VARCHAR2(50) NULL,
NOTES VARCHAR2(2000) NULL
) TABLESPACE "RELMAN_DATA"
/

With a primary key :

ALTER TABLE RELMAN.TBLCOMPANYCONTACTS
ADD CONSTRAINT TBLCOMPANYCONTACTS_PK PRIMARY KEY (
COMPANY_CODE,
CONTACT_IDX
) USING INDEX TABLESPACE "RELMAN_INDEX"

From this I generated a strongly typed dataset in C# which matched up
exactly - primary keys and nullable values correct. When I come to
fill the DataSet with the statement

SELECT * FROM RELMAN.TBLCOMPANYCONTACTS and I get the error:

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

However - I know that there are no primary key constraints - checked
on the database. There cannot be any foreign key constraints - there
is only 1 tabe currently in my xsd schema for the dataset. Some values
are null but that tallies with the xsd (minOccurs=0, for all columns
bar the primary key).

A rundown of how I created the problem -

1: Create a new Windows app. Drag an OracleDataAdapter ont the form
and specify the connection, and select statement (above).
2: Right click the DataAdapter and Generate DataSet. I dont have it
add it to my form designer.
3: I then select TableMappings from the DataAdapter and select the
table from the dataset and map all the columns exactly.
4: On a button click event I have the code:

DS ds= new DS();
oracleDataAdapter1.Fill(ds.TBLCOMPANYCONTACTS);
dataGrid1.DataSource=ds;

Which errors on the Fill Line.

If I set EnforceConstraints to False, everything works, but obviously
I'd like to have my primary key set to try and make sure that my data
has *some* integrity.

This technique has always worked perfectly in the past (on much larger
and more complex schemas than this), but seems to be a problem on this
one table.

Any ideas incredibly welcome.

Regards

Neil Danson

 

[William Ryan eMVP]

Neil:

Maybe I'm reading this wrong, but the Dataset (not the table in the db) has
a PK defined and there are nulls in the DB? If so, that won't work...Nulls
don't equal anything, not even themselves so they violate the unique
constraint.

 

[Neil Danson]

"Maybe I'm reading this wrong, but the Dataset (not the table in the db)
has
a PK defined and there are nulls in the DB? If so, that won't
work...Nulls
don't equal anything, not even themselves so they violate the unique
constraint."

no - The primary key is

COMPANY_CODE
CONTACT_IDX NUMBER

Which is defined as not-nullable on the database AND on the xsd schema.

Cheers

Neil

 

[William Ryan eMVP]

What values are null then? Are there null values in either column? I know
you say they are prohibited, but I don't understand the part:

<<Some values
are null but that tallies with the xsd>>

 

[Neil Danson]

Database Table Structure

COMPANY_CODE VARCHAR2(10) NOT NULL,
CONTACT_IDX NUMBER(3) NOT NULL,
TITLE VARCHAR2(4) NULL,
FIRSTNAME VARCHAR2(50) NULL,
LASTNAME VARCHAR2(50) NULL,
POSITION VARCHAR2(50) NULL,
TELNO VARCHAR2(50) NULL,
MOBILE VARCHAR2(50) NULL,
FAX VARCHAR2(50) NULL,
EMAIL VARCHAR2(50) NULL,
NOTES VARCHAR2(2000) NULL

XSD Table Structure

<xs:element name="TBLCOMPANYCONTACTS">
<xs:complexType>
<xs:sequence>
<xs:element name="COMPANY_CODE" type="xs:string" />
<xs:element name="CONTACT_IDX" type="xs:decimal" />
<xs:element name="TITLE" type="xs:string" minOccurs="0" />
<xs:element name="FIRSTNAME" type="xs:string" minOccurs="0" />
<xs:element name="LASTNAME" type="xs:string" minOccurs="0" />
<xs:element name="POSITION" type="xs:string" minOccurs="0" />
<xs:element name="TELNO" type="xs:string" minOccurs="0" />
<xs:element name="MOBILE" type="xs:string" minOccurs="0" />
<xs:element name="FAX" type="xs:string" minOccurs="0" />
<xs:element name="EMAIL" type="xs:string" minOccurs="0" />
<xs:element name="NOTES" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>

With the constraint

<xs:unique name="Constraint1" msdatarimaryKey="true">
<xs:selector xpath=".//mstns:TBLCOMPANYCONTACTS" />
<xs:field xpath="mstns:COMPANY_CODE" />
<xs:field xpath="mstns:CONTACT_IDX" />
</xs:unique> in there too.

None of the primary key columns can be null in either the database or
the xsd. If i disable constraints it loads and should be completely fine
- none of the data displayed violates primary key in schema. And none of
the primary key values are null in the datagrid.

Cheers.

Neil Danson

 

[William Ryan eMVP]

Hmm, that sure seems weird. I guess i'd stick a where on that select
statement and see if i can narrow it down, maybe do x records at a time and
see if you can fill anything, if its something in every row or just one or a
few that's causing the drama.