Reading Xml into a dataset then updating the sqlce datatable causes castexception

[Tom]

This works up until I try to FILL the tbl. It then throws an
InvalidCastException. My fealing is that this doesnt work because My
xml is producing a table with all string values, and MyTable is int
and ntext. Any ideas apreciated. The XmlString has only data no
schema. I tried the TableMapping to see if I could force the schema
but that didnt do anything.

public bool PopulateFromXML(string xmlString)
{
System.IO.StringReader str = new StringReader(xmlString);
System.Xml.XmlTextReader xmlRdr = new System.Xml.XmlTextReader(str);
DataSet ds = new DataSet();
DataSet desDS = new DataSet();
try
{
ds.ReadXml(xmlRdr);
}
catch (Exception)
{
MessageBox.Show("Failed to load dataset. Make sure you have created
database
and exported table data");
return false;
}

for(int i = 0;i < ds.Tables.Count;i++)
{
DataTable tbl = ds.Tables;
try
{
string sSQL = "SELECT * FROM "+tbl.TableName;
string delSQL = "DELETE FROM "+tbl.TableName;
SqlCeConnection sqlConn = new SqlCeConnection(ConnString);
sqlConn.Open();
SqlCeCommand cmdDel = new SqlCeCommand(delSQL, sqlConn);
cmdDel.ExecuteNonQuery();
SqlCeDataAdapter sqlAdptr = new SqlCeDataAdapter(sSQL, sqlConn);
sqlAdptr.TableMappings.Add(tbl.TableName, tbl.TableName);
sqlAdptr.Fill(tbl);<----This throws an
InvalidCastException***************
SqlCeCommandBuilder cb = new SqlCeCommandBuilder(sqlAdptr);
sqlAdptr.InsertCommand = cb.GetInsertCommand();
sqlAdptr.Update(tbl);
sqlConn.Close();
sqlAdptr.Dispose();
cmdDel.Dispose();
cb.Dispose();
}
catch(SqlCeException ex)
{
ShowErrors(ex);
}

}
return true;
}

 

[Tom]

This works up until I try to FILL the tbl. It then throws an
InvalidCastException. My fealing is that this doesnt work because My
xml is producing a table with all string values, and MyTable is int
and ntext. Any ideas apreciated. The XmlString has only data no
schema. I tried the TableMapping to see if I could force the schema
but that didnt do anything.

I tried DataSet.ReadXMLSchema and that did the trick. Has anyone got a better
way to handle this.

public bool PopulateFromXML(string xmlString)
{
System.IO.StringReader str = new StringReader(xmlString);
System.Xml.XmlTextReader xmlRdr = new System.Xml.XmlTextReader(str);
DataSet ds = new DataSet();
DataSet desDS = new DataSet();
try
{
ds.ReadXml(xmlRdr);
}
catch (Exception)
{
MessageBox.Show("Failed to load dataset. Make sure you have created
database
and exported table data");
return false;
}

for(int i = 0;i < ds.Tables.Count;i++)
{
DataTable tbl = ds.Tables;
try
{
string sSQL = "SELECT * FROM "+tbl.TableName;
string delSQL = "DELETE FROM "+tbl.TableName;
SqlCeConnection sqlConn = new SqlCeConnection(ConnString);
sqlConn.Open();
SqlCeCommand cmdDel = new SqlCeCommand(delSQL, sqlConn);
cmdDel.ExecuteNonQuery();
SqlCeDataAdapter sqlAdptr = new SqlCeDataAdapter(sSQL, sqlConn);
sqlAdptr.TableMappings.Add(tbl.TableName, tbl.TableName);
sqlAdptr.Fill(tbl);<----This throws an
InvalidCastException***************
SqlCeCommandBuilder cb = new SqlCeCommandBuilder(sqlAdptr);
sqlAdptr.InsertCommand = cb.GetInsertCommand();
sqlAdptr.Update(tbl);
sqlConn.Close();
sqlAdptr.Dispose();
cmdDel.Dispose();
cb.Dispose();
}
catch(SqlCeException ex)
{
ShowErrors(ex);
}

}
return true;
}