problem with updateing foreign key to null value

Hi,
I was wondering if anyone out there can help me with the following problem:
I've written an annotated XSD for retrieving some related tables from
sql-server.
It's used in an XPATH Query that populates a strongly typed dataset
(generated using xsd.exe).
So far everyting works like a dream!
There is however one nagging problem i've encounterd so far. I'm trying to
"deassign" a child from it's parent by updating it's foreign key field to
null (by using the setnull method generated by xsd.exe).
The dataset records this but when I use it to try to update the database, no
update statements are executed and no errors are raised.
Updated the child with a different parent key does however work!!!
So it would seem SQLXML doesn't detect a change when updating to null?!
Any help would be greatly appreciated.
Regards, Rob Huibers
Hi Rob,
It is not clear which SQLXML API you are using? Are you using
SqlXmlAdapter and its Update method? Is it possible to post your schema so
that I can try to repro the issue here.
Thank you,
Amar Nalla
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rob Huibers" <Rob Huibers@.discussions.microsoft.com> wrote in message
news:E482DAFF-76A7-4A88-993C-A75DB20C5954@.microsoft.com...
> Hi,
> I was wondering if anyone out there can help me with the following
problem:
> I've written an annotated XSD for retrieving some related tables from
> sql-server.
> It's used in an XPATH Query that populates a strongly typed dataset
> (generated using xsd.exe).
> So far everyting works like a dream!
> There is however one nagging problem i've encounterd so far. I'm trying to
> "deassign" a child from it's parent by updating it's foreign key field to
> null (by using the setnull method generated by xsd.exe).
> The dataset records this but when I use it to try to update the database,
no
> update statements are executed and no errors are raised.
> Updated the child with a different parent key does however work!!!
> So it would seem SQLXML doesn't detect a change when updating to null?!
> Any help would be greatly appreciated.
> Regards, Rob Huibers
|||Hi Amar,
Thanks for looking into the problem.
You're right in that I'm using SqlXmlAdapter.Update (SQLXML 3.0 sp3).
I've included the schema. The element I'm trying to nullify is
"sub_claim_id" belonging to parent "xyz_assigned_part_claim".
As I've mentioned before, the dataset record records the change but does not
serialize it to the database. When I generate an XmlDataDocument from the
dataset the sub_claim_id" element does not exist anymore. I would have
expected the element appearing with the "xsi:nil" attribute.
Hope you can figure it out, regards, Rob
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
xmlns:codegen="urn:schemas-microsoft-com:xml-msprop">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="ClaimSubclaims" parent="xyz_claim"
parent-key="claim_id" child="xyz_sub_claim"
child-key="claim_id" />
<sql:relationship name="ClaimSubclaimPartclaims" parent="xyz_sub_claim"
parent-key="sub_claim_id"
child="xyz_part_claim" child-key="sub_claim_id" />
<sql:relationship name="ClaimPartclaims" parent="xyz_claim"
parent-key="claim_id" child="xyz_part_claim"
child-key="claim_id" />
<sql:relationship name="ClaimTasks" parent="xyz_claim"
parent-key="claim_id" child="xyz_task"
child-key="claim_id" />
<sql:relationship name="SubclaimTasks" parent="xyz_sub_claim"
parent-key="sub_claim_id" child="xyz_task"
child-key="sub_claim_id" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType name="xyz_part_claim_type">
<xsd:sequence>
<xsd:element name="part_claim_id" sql:field="part_claim_id"
type="xsd:int" sql:identity="ignore" />
<xsd:element name="claim_id" sql:field="claim_id" type="xsd:int" />
<xsd:element name="sub_claim_id" sql:field="sub_claim_id" type="xsd:int"
nillable="true" minOccurs="1" />
<xsd:element name="description" sql:field="description" type="xsd:string"
/>
</xsd:sequence>
</xsd:complexType>
<xsd:attributeGroup name="xyz_part_claim_investigation_type">
<xsd:attribute name="part_claim_investigation_id"
sql:field="part_claim_investigation_id" type="xsd:int"
sql:identity="ignore" />
<xsd:attribute name="part_claim_id" sql:field="part_claim_id"
type="xsd:int" />
<xsd:attribute name="investigation_id" sql:field="investigation_id"
type="xsd:int" />
</xsd:attributeGroup>
<xsd:attributeGroup name="xyz_investigation_type">
<xsd:attribute name="investigation_id" sql:field="investigation_id"
type="xsd:int" sql:identity="ignore" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
</xsd:attributeGroup>
<xsd:attributeGroup name="xyz_task_type">
<xsd:attribute name="task_id" sql:field="task_id" type="xsd:int"
sql:identity="ignore" />
<xsd:attribute name="type" sql:field="type" type="xsd:int" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
<xsd:attribute name="creator" sql:field="creator" type="xsd:string" />
<xsd:attribute name="performer" sql:field="performer" type="xsd:string" />
<xsd:attribute name="status" sql:field="status" type="xsd:int" />
<xsd:attribute name="date_started" sql:field="date_started"
type="xsd:dateTime" />
<xsd:attribute name="date_closed" sql:field="date_closed"
type="xsd:dateTime" />
<xsd:attribute name="date_created" sql:field="date_created"
type="xsd:dateTime" />
<xsd:attribute name="claim_id" sql:field="claim_id" type="xsd:int" />
<xsd:attribute name="sub_claim_id" sql:field="sub_claim_id" type="xsd:int"
/>
</xsd:attributeGroup>
<xsd:element name="xyz_claims" sql:is-constant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="xyz_claim" sql:key-fields="claim_id"
sql:relation="xyz_claim">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="xyz_claim_task" sql:key-fields="task_id"
sql:relation="xyz_task" sql:relationship="ClaimTasks"
sql:limit-field="sub_claim_id">
<xsd:complexType>
<xsd:attributeGroup ref="xyz_task_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
<xsd:element name="xyz_sub_claim" sql:key-fields="sub_claim_id"
sql:relation="xyz_sub_claim"
sql:relationship="ClaimSubclaims">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="xyz_sub_claim_task" sql:key-fields="task_id"
sql:relation="xyz_task"
sql:relationship="SubclaimTasks">
<xsd:complexType>
<xsd:attributeGroup ref="xyz_task_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
<xsd:element name="xyz_assigned_part_claim"
sql:key-fields="part_claim_id" sql:relation="xyz_part_claim"
sql:relationship="ClaimSubclaimPartclaims">
<xsd:complexType>
<xsd:complexContent>
<xsd:extension base="xyz_part_claim_type">
<xsd:sequence>
<xsd:element name="xyz_investigation"
sql:key-fields="investigation_id" sql:relation="xyz_investigation">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="xyz_part_claim"
parent-key="part_claim_id" child="xyz_part_claim_investigation"
child-key="part_claim_id" />
<sql:relationship parent="xyz_part_claim_investigation"
parent-key="investigation_id" child="xyz_investigation"
child-key="investigation_id" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:attributeGroup
ref="xyz_investigation_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:extension>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="sub_claim_id" sql:field="sub_claim_id"
type="xsd:int" sql:identity="ignore" />
<xsd:attribute name="claim_id" sql:field="claim_id" type="xsd:int" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
<xsd:element name="xyz_unassigned_part_claim"
sql:key-fields="part_claim_id" sql:relation="xyz_part_claim"
sql:relationship="ClaimPartclaims" sql:limit-field="sub_claim_id">
<xsd:complexType>
<xsd:complexContent>
<xsd:extension base="xyz_part_claim_type">
<xsd:sequence>
<xsd:element name="xyz_unassigned_investigation"
sql:key-fields="investigation_id" sql:relation="xyz_investigation">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="xyz_part_claim"
parent-key="part_claim_id" child="xyz_part_claim_investigation"
child-key="part_claim_id" />
<sql:relationship parent="xyz_part_claim_investigation"
parent-key="investigation_id" child="xyz_investigation"
child-key="investigation_id" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:attributeGroup
ref="xyz_investigation_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:extension>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="claim_id" sql:field="claim_id" type="xsd:int"
sql:identity="ignore" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
"Amar Nalla [MS]" wrote:

> Hi Rob,
> It is not clear which SQLXML API you are using? Are you using
> SqlXmlAdapter and its Update method? Is it possible to post your schema so
> that I can try to repro the issue here.
> Thank you,
> Amar Nalla
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Rob Huibers" <Rob Huibers@.discussions.microsoft.com> wrote in message
> news:E482DAFF-76A7-4A88-993C-A75DB20C5954@.microsoft.com...
> problem:
> no
>
>