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 HuibersHi 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
>
>

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
>
>

Problem with update when updating all rows of a table through dataset and saving back to d

Hi,

I have an application where I'm filling a dataset with values from a table. This table has no primary key. Then I iterate through each row of the dataset and I compute the value of one of the columns and then update that value in the dataset row. The problem I'm having is that when the database gets updated by the SqlDataAdapter.Update() method, the same value shows up under that column for all rows. I think my Update Command is not correct since I'm not specifying a where clause and hence it is using just the value lastly computed in the dataset to update the entire database. But I do not know how to specify a where clause for an update statement when I'm actually updating every row in the dataset. Basically I do not have an update parameter since all rows are meant to be updated. Any suggestions?

SqlCommand snUpdate = conn.CreateCommand();

snUpdate.CommandType =CommandType.Text;

snUpdate.CommandText ="Update TestTable set shipdate = @.shipdate";

snUpdate.Parameters.Add("@.shipdate",SqlDbType.Char, 10,"shipdate");

string jdate ="";

for (int i = 0; i < ds.Tables[0].Rows.Count - 1; i++)

{

jdate = ds.Tables[0].Rows[i]["shipdate"].ToString();

ds.Tables[0].Rows[i]["shipdate"] = convertToNormalDate(jdate);

}

da.Update(ds,"Table1");

conn.Close();

-Thanks

a quick once over and i have a few questions--what all in your TestTable? Also in all lines of code you refer to your table as Tables[0] you should always stick to one way. Your right about your Update, so when we see what's in your TestTable we'll be able to help you out a little bit more.

|||

Thanks for your response. My table contains 2 fields SKU numbers and Ship Dates. I changed my update statement as follows and it worked.

snUpdate.CommandText ="Update TestTable set shipdate = @.shipdate where skunum=@.skunum";

snUpdate.Parameters.Add("@.shipdate",SqlDbType.Char, 10,"shipdate");

snUpdate.Parameters.Add("@.skunum",SqlDbType.Int, 4,"skunum");

snUpdate.Parameters["@.skunum"].SourceVersion =DataRowVersion.Original;

-Thanks

|||So is skunum a unique value? If not, then it'll update all rows with that skunum. If it is, then why isn't that your primary key?|||The skunum is not unique. The table does have some duplicated data which I don't have to worry about if they get updated with the same value. This is just an old table whose data will be transformed into a different table and used in an app after my updates are done. Thanks.