Datatable Primary Key not being set from Xml Schema

[Emma Middlebrook]

Hi there,

I have created an XmlSchema to represent the tables in my database and
I have specified a primary key for each of the tables.

However, to be able to execute the following code:

DatabaseTables.CategoryInfoRow categoryRow =
m_dtCategories.Rows.Find(selected.category_ID) as
DatabaseTables.CategoryInfoRow;

I need to explicitly reassign the primary keys for the table with this
as it throws an error saying no primary key defined:

// Specify the primary key columns
DataColumn[] keys = new DataColumn[1];
keys[0] = m_dtCategories.Columns["Category_ID"];
m_dtCategories.PrimaryKey = keys;

Can anyone suggest what has gone wrong with my schema or was this
always the way we had to do it? I didn't manually write the xml for the
schema, I did it through setting properties in the data view but here
is what it generated for my table in question:

<xs:element name="CategoryInfo">
<xs:complexType>
<xs:sequence>
<xs:element name="Category_ID" type="xs:int"
msdata:AutoIncrement="true" />
<xs:element name="Parent_Cat_ID" type="xs:int" />
<xs:element name="Category_Name" type="xs:string" />
</xs:sequence>
</xs:complexType>
<xs:key name="PK_CategoryInfo" msdatarimaryKey="true">
<xs:selector xpath="." />
<xs:field xpath="mstns:Category_ID" />
</xs:key>
</xs:element>

Thanks,

Emma

 

[NuTcAsE]

Hi,

I think the problem lies in the <xs:selector xpath="."/> element. The
xpath attribute of xs:selector should point to the table that the key
is assigned to, which in this case is pointing to the root element '.'.
Change the xpath attribute value to ".//mstns:CategoryInfo", save and
then retry your code. The resulting schema should be like this:

<xs:key name="PK_CategoryInfo" msdatarimaryKey="true">
<xs:selector xpath=".//mstns:CategoryInfo" /> <!--
Changed value -->
<xs:field xpath="mstns:Category_ID" />
</xs:key>


If that doesnt work could you please post the exception that you are
getting?

- NuTcAsE

 

[Emma Middlebrook]

Thanks for the reply. I made the change in the xsd file and rebuilt my
project without the lines of code that explicitly set the Primary Keys
column details.

Here is the exception I get when I attempt to do a find of the rows:

""
An unhandled exception of type 'System.Data.MissingPrimaryKeyException'
occurred in system.data.dll

Additional information: Table doesn't have a primary key.""

When I look at the table in the watch window, the primary key attribute
has a length of 0 and is set to an undefined value. Is it supposed to
be reading this from the xml schema file when it creates the typed data
table instance or is the primary key verified against the actual table
in the database where I load the data into the data table?

Thanks,

Emma

 

[Emma Middlebrook]

I don't know if this will help identify what I have done incorrectly.
When I went back to the data view of the schema after making your
suggested change, the little blue key was missing from my row. Though
in the properties box for the element, it still had details of the
key....

 

[NuTcAsE]

<?xml version="1.0" encoding="utf-8" ?>
Hi,

I tried creating a data set with the schema definition you supplied
earlier, and this is what the xsd file contained. It created the data
set correctly, and I could use the Find function without assigning the
keys manually (FYI: If the Primary key in the xsd file is setup
properly then there should be a FindByCategory_ID functiont that
returns a typed CategoryInfoRow instance).

Can you post the entire schema definition of the xsd file or check the
differences in your file with this. If that doesnt work, I suggest
replacing the CategoryInfo element and the Primary Key definition in
your xsd with the sample below. If that doesnt work, could you post
your xsd file definition?

<xs:schema id="Test" targetNamespace="http://tempuri.org/Test.xsd"
elementFormDefault="qualified"
attributeFormDefault="qualified"
xmlns="http://tempuri.org/Test.xsd"
xmlns:mstns="http://tempuri.org/Test.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="Test" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="CategoryInfo">
<xs:complexType>
<xs:sequence>
<xs:element name="Category_ID"
type="xs:int" minOccurs="0" />
<xs:element name="Parent_Cat_ID"
type="xs:int" minOccurs="0" />
<xs:element name="Category_Name"
type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:key name="CategoryInfo_PK" msdatarimaryKey="true">
<xs:selector xpath=".//mstns:CategoryInfo" />
<xs:field xpath="mstns:Category_ID" />
</xs:key>
</xs:element>
</xs:schema>

Hope this helps.

- NuTcAsE

 

[Emma Middlebrook]

Hi,

Thanks for the help. I recreated my whole schema file based on the
excerpt above and it now seems to have created the primary keys and I
now have the FindBy_ID() functions available.

I think my problem was that I didn't have an element with dataSet=true
that housed all the other elements representing my tables.

I was missing:

<xs:element name="Test" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">

I don't fully understand what they are doing so I'll do some more
reading on XmlSchemas. I can't understand how I got into this mess in
the first place. I chose not to hand write the schema because of my
lack of knowledge and instead opted to use the Schema design page and
there I just right clicked and created new elements with nothing else.
I don't know how I could have told it to do all the segments that you
created for me.

My project no longer builds now because the schema has completely
changed, so I have some work to do now to get that building. But thanks
ever so much for all the help.

Emma

 

[NuTcAsE]

If you used the data set schema designer it should have placed the
msdata:IsDataSet="true" automatically on the root data set element. The
only way (if using schema designer ONLY), that attribute value is
removed or set to false is if in the property pages of the dataset
schema the IsDataSet is set to false.

- NuTcAsE