Hi!
This is what I'm trying to do:
Query my SQL server with sqlxmladapter and populate a datagrid with the resulting dataset, make changes to the dataset and update the database.
All works fine until i try to update fields in a table under/below the top-node. This is the exception I get:
"SqlXmlException: <?MSSQLError HResult="0x80040e14" Source=Microsoft OLE DB Provider for SQL Server" Description="SQLOLEDB Error Description: Empty update, no updatable rows found Transaction aborted"?>
Its a bit tricky to explain and I will paste some code to make it easier to understand.
Code:
// Use this to populate dataset and grid
private void button1_Click(object sender, System.EventArgs e)
{
cmd = new SqlXmlCommand("Provider=SQLOLEDB;"+sqlConnection1.ConnectionString);
cmd.RootTag = "ROOT";
cmd.CommandText = "tbl_Arende";
cmd.CommandType = SqlXmlCommandType.XPath;
cmd.SchemaPath = @"..\..\Schema1.xsd";
ad = new SqlXmlAdapter(cmd);
ds = new DataSet();
ad.Fill(ds);
dataGrid1.DataSource = ds;
ds.WriteXml(@"..\..\Output.xml");
}
// Use this to update changes made in the grid
private void button2_Click(object sender, System.EventArgs e)
{
if(ds.HasChanges())
{
ds.GetChanges().WriteXml(@"..\..\Diffgram.xml" ,XmlWriteMode.DiffGram);
string err = "";
try
{
ad.Update(ds.GetChanges());
ds.AcceptChanges();
}
catch (SqlXmlException ex)
{
ex.ErrorStream.Position = 0;
StreamReader errreader = new StreamReader(ex.ErrorStream);
err = errreader.ReadToEnd();
errreader.Close();
MessageBox.Show("SqlXmlException: " + err);
ds.AcceptChanges();
}
}
}
And here is the Schema1.xsd I've made
Code:
<?xml version="1.0" encoding="utf-8" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="FK_tbl_Handelse_tbl_Arende" parent="tbl_Arende" parent-key="PkID" child="tbl_Handelse" child-key="Fk_Arende" />
<sql:relationship name="FK_tbl_ArendeData_tbl_Arende" parent="tbl_Arende" parent-key="PkID" child="tbl_ArendeData" child-key="Fk_Arende" />
<sql:relationship name="FK_tbl_HandelseData_tbl_Handelse" parent="tbl_Handelse" parent-key="PkID" child="tbl_HandelseData" child-key="Fk_Handelse" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="tbl_Arende">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="tbl_ArendeData" sql:relationship="FK_tbl_ArendeData_tbl_Arende">
<xsd:complexType>
<xsd:attribute name="PkID" type="xsd:int" sql:identity="ignore" />
<xsd:attribute name="myField" type="xsd:string" />
<xsd:attribute name="myValue" type="xsd:string" />
</xsd:complexType>
</xsd:element>
<xsd:element name="tbl_Handelse" sql:relationship="FK_tbl_Handelse_tbl_Arende">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="tbl_HandelseData" sql:relationship="FK_tbl_HandelseData_tbl_Handelse">
<xsd:complexType>
<xsd:attribute name="PkID" type="xsd:int" sql:identity="ignore" />
<xsd:attribute name="myField" type="xsd:string" />
<xsd:attribute name="myValue" type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="PkID" type="xsd:int" sql:identity="ignore" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="PkID" type="xsd:int" sql:identity="ignore" />
<xsd:attribute name="DiarieNummer" type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:schema>
And here's an example of a diffgram due to changes made:
Code:
<?xml version="1.0" standalone="yes"?>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<ROOT>
<tbl_Arende diffgr:id="tbl_Arende1" msdata:rowOrder="0" msdata:hiddentbl_Arende_Id="0" PkID="1" DiarieNummer="1234567">
<tbl_ArendeData diffgr:id="tbl_ArendeData1" msdata:rowOrder="0" diffgr:hasChanges="modified" myValue="RemissChanged" PkID="1" myField="ArendeTyp" msdata:hiddentbl_Arende_Id="0" />
</tbl_Arende>
</ROOT>
<diffgr:before>
<tbl_ArendeData diffgr:id="tbl_ArendeData1" msdata:rowOrder="0" myValue="Remiss" PkID="1" myField="ArendeTyp" msdata:hiddentbl_Arende_Id="0" />
</diffgr:before>
</diffgr:diffgram>
If I make changes to tbl_Arende which is the top node, update works fine, but if I make changes in any underlaying related tables I get the exception I described.
All the relations in the DB are correct and corresponds to the ones in the Schema1.xsd.
Thankful for any ideas or suggestions.
// HaJe