I am attempting to save a dynamic dataset to sql ce, and am coming across a
problem.

In the supplied code, it creates the table, which appears to work ok. The
create table command is as follows:

"Create Table \"allbills\" (\"SacID\" NVarChar,\"LineNum\"
Int,\"DateTouched\" NVarChar,\"EstabCode\" NVarChar,\"MAWB\"
NVarChar,\"HAWB\" NVarChar,\"ArrivalDate\" NVarChar,\"Flight\"
NVarChar,\"GoodsDescription\" NVarChar,\"AIMSUpgraded\" Bit,\"FSResultCode\"
NVarChar,\"InspectResultCode\" NVarChar,\"SACOfficer\"
NVarChar,\"ReportableDocInd\" Bit,\"CountSACsWithinMAWB\"
Int,\"CountHAWBsWithinMAWB\" Int,\"HVLVCountSACsInMAWB\"
Int,\"HVLVCountHAWBsInMAWB\" Int,\"SID\" NVarChar Not Null Primary
Key,\"FSModified\" NVarChar,\"InspModified\" NVarChar,\"FSResultOriginal\"
NVarChar,\"InspectResultOriginal\" NVarChar,\"MAWBLevel\"
NVarChar,\"FSUploaded\" NVarChar,\"InspUploaded\" NVarChar)"

The code then generates an Insert Command that appears to be correct, and
assigns a set of parameters to this command.

"Insert Into \"allbills\"
(\"SacID\",\"LineNum\",\"DateTouched\",\"EstabCode\",\"MAWB\",\"HAWB\",\"ArrivalDate\",\"Flight\",\"GoodsDescription\",\"AIMSUpgraded\",\"FSResultCode\",\"InspectResultCode\",\"SACOfficer\",\"ReportableDocInd\",\"CountSACsWithinMAWB\",\"CountHAWBsWithinMAWB\",\"HVLVCountSACsInMAWB\",\"HVLVCountHAWBsInMAWB\",\"SID\",\"FSModified\",\"InspModified\",\"FSResultOriginal\",\"InspectResultOriginal\",\"MAWBLevel\",\"FSUploaded\",\"InspUploaded\")
Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"

It then creates a dataadapter and assign this insert command to the
dataadapter, then runs dataadapter.update(table), which generates an
exception with the message "Update". Is the code using the Update method
correctly, any suggestions? Third party tools are not an option in this case.
(see source code below)



public static void storeDataToSQLCE( DataSet data, string dataFile )
{
if (File.Exists(dataFile)) // Get rid of existing database file
File.Delete(dataFile);

SqlCeEngine en = new SqlCeEngine("Data Source = " + dataFile ); //
Create new database engine
en.CreateDatabase(); // Create a new database
SqlCeConnection con = new SqlCeConnection("Data Source = " +
dataFile ); // Create a new connection
con.Open(); // Open this connection

foreach (DataTable table in data.Tables )
{
// Create tables one by one

SqlCeCommand cmd = con.CreateCommand();

// Prepare Create table command

StringBuilder command = new StringBuilder(1024); // Assume we have
pretty long command

command.Append("Create Table \"");

// Command string:
command.Append( table.TableName );
// Create Table "TableName" ("ColumnName" ColumnType, ... )
command.Append( "\" (");

foreach ( DataColumn c in table.Columns)
{
// Add all columns
command.Append ("\"");
command.Append (c.ColumnName); // Add column name
command.Append ("\" ");
command.Append (GetSQLTypeName(c.DataType)); // And column SQL type

// Add special column features:
//
// Identity(_seed, _increment) Autoincrement Could not set this as
column will be read only
// Primary Key Primary Key
// Default _value Sets default value
// Unique For unique columns
// Not Null Null is not allowed

if ( c.DefaultValue != DBNull.Value )
{
// Default value is set?
command.Append (String.Format(" Default '{0}'",
c.DefaultValue));
}

if ( !c.AllowDBNull )
{
// Could not be null ?
command.Append (" Not Null"); // Mark it as Not Null
}

if ( c.Unique )
{ // Unique column ?
DataColumn[] pk = table.PrimaryKey; // Get primry key column(s)
if ( pk != null && pk.Length > 1 ) // Only one column
allowed as a primary key in SQL CE
throw new System.Exception("Only one column allowed as a primary key
in SQL CE");

if ( null != pk && pk.Length == 1 && pk[0] == c)
{ // Primary key ?
command.Append (" Primary Key"); // Mark it as such
}
else
{
command.Append (" Unique"); // Mark it as unique
}
}

command.Append (','); // Add separator
}

command.Replace (',', ')', command.Length - 1, 1); // Replace last
comma with ')'

cmd.CommandText = command.ToString(); // Set command

// Console.WriteLine ("Create command: \n{0}", cmd.CommandText);

cmd.ExecuteNonQuery(); // Do it - create a table

cmd.Parameters.Clear(); // Do some cleanup


// At this point we have a database with an empty table ready to be
populated...

SqlCeDataAdapter da = new SqlCeDataAdapter(); // Prepare data
adapter

command.Remove(0, command.Length); // Clean up old
command

command.Append ("Insert Into \""); // Insert command
command.Append ( table.TableName );
command.Append ("\" (");

foreach (DataColumn column in table.Columns)
{ // Add column names
command.Append ('"');
command.Append ( column.ColumnName );
command.Append ("\","); // Separated by commas

cmd.Parameters.Add("@" + column.ColumnName, // Add parameters to q
query
GetSQLType (column.DataType), column.MaxLength > 0 ? column.MaxLength
: 0, column.ColumnName);
}

command.Remove(command.Length - 1, 1); // Remove last comma

command.Append(") Values (");

for (int i = table.Columns.Count; i-- > 0; ) // Add correct
number
command.Append( "?,"); // of queston marks

command.Replace (',', ')', command.Length - 1, 1); // Replace last
comma with ')'

cmd.CommandText = command.ToString(); // Set insert command

da.InsertCommand = cmd; // Store our insert command

try
{
da.Update(table); // Update table in the SQL CE
}
catch (Exception ex)
{
ex.ToString();
}

cmd.Parameters.Clear(); // Do some cleanup

//Verification
Console.WriteLine("VERIFYING");
#region VERIFICATION
cmd = con.CreateCommand(); // Prepare Create table command
string vcommand = String.Format("select COUNT(*) from
{0}",table.TableName);
cmd.CommandText = vcommand; // Set command

int count = (int)cmd.ExecuteScalar(); // Do it - create a table
if(count != table.Rows.Count)
{
Console.WriteLine("We did not store Expected number of records");
Console.WriteLine("Expected was {0}", table.Rows.Count);
Console.WriteLine("Actual was {0}",count);
throw new Exception("Error in Verification");
}
#endregion
Console.WriteLine("DONE WITH VERIFICATION");

}

con.Close(); // Close connection.
}

Try specifying the da.UpdateCommand as well.
Also, what is the exception? SqlCeException? Other?

--
Alex Feinman
---
Visit http://www.opennetcf.org
"lang" <(E-Mail Removed)> wrote in message
news:6B027A66-7C76-4889-84FF-(E-Mail Removed)...
>I am attempting to save a dynamic dataset to sql ce, and am coming across a
> problem.
>
> In the supplied code, it creates the table, which appears to work ok. The
> create table command is as follows:
>
> "Create Table \"allbills\" (\"SacID\" NVarChar,\"LineNum\"
> Int,\"DateTouched\" NVarChar,\"EstabCode\" NVarChar,\"MAWB\"
> NVarChar,\"HAWB\" NVarChar,\"ArrivalDate\" NVarChar,\"Flight\"
> NVarChar,\"GoodsDescription\" NVarChar,\"AIMSUpgraded\"
> Bit,\"FSResultCode\"
> NVarChar,\"InspectResultCode\" NVarChar,\"SACOfficer\"
> NVarChar,\"ReportableDocInd\" Bit,\"CountSACsWithinMAWB\"
> Int,\"CountHAWBsWithinMAWB\" Int,\"HVLVCountSACsInMAWB\"
> Int,\"HVLVCountHAWBsInMAWB\" Int,\"SID\" NVarChar Not Null Primary
> Key,\"FSModified\" NVarChar,\"InspModified\" NVarChar,\"FSResultOriginal\"
> NVarChar,\"InspectResultOriginal\" NVarChar,\"MAWBLevel\"
> NVarChar,\"FSUploaded\" NVarChar,\"InspUploaded\" NVarChar)"
>
> The code then generates an Insert Command that appears to be correct, and
> assigns a set of parameters to this command.
>
> "Insert Into \"allbills\"
> (\"SacID\",\"LineNum\",\"DateTouched\",\"EstabCode\",\"MAWB\",\"HAWB\",\"ArrivalDate\",\"Flight\",\"GoodsDescription\",\"AIMSUpgraded\",\"FSResultCode\",\"InspectResultCode\",\"SACOfficer\",\"ReportableDocInd\",\"CountSACsWithinMAWB\",\"CountHAWBsWithinMAWB\",\"HVLVCountSACsInMAWB\",\"HVLVCountHAWBsInMAWB\",\"SID\",\"FSModified\",\"InspModified\",\"FSResultOriginal\",\"InspectResultOriginal\",\"MAWBLevel\",\"FSUploaded\",\"InspUploaded\")
> Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
>
> It then creates a dataadapter and assign this insert command to the
> dataadapter, then runs dataadapter.update(table), which generates an
> exception with the message "Update". Is the code using the Update method
> correctly, any suggestions? Third party tools are not an option in this
> case.
> (see source code below)
>
>
>
> public static void storeDataToSQLCE( DataSet data, string dataFile )
> {
> if (File.Exists(dataFile)) // Get rid of existing database file
> File.Delete(dataFile);
>
> SqlCeEngine en = new SqlCeEngine("Data Source = " + dataFile ); //
> Create new database engine
> en.CreateDatabase(); // Create a new database
> SqlCeConnection con = new SqlCeConnection("Data Source = " +
> dataFile ); // Create a new connection
> con.Open(); // Open this connection
>
> foreach (DataTable table in data.Tables )
> {
> // Create tables one by one
>
> SqlCeCommand cmd = con.CreateCommand();
>
> // Prepare Create table command
>
> StringBuilder command = new StringBuilder(1024); // Assume we have
> pretty long command
>
> command.Append("Create Table \"");
>
> // Command string:
> command.Append( table.TableName );
> // Create Table "TableName" ("ColumnName" ColumnType, ... )
> command.Append( "\" (");
>
> foreach ( DataColumn c in table.Columns)
> {
> // Add all columns
> command.Append ("\"");
> command.Append (c.ColumnName); // Add column name
> command.Append ("\" ");
> command.Append (GetSQLTypeName(c.DataType)); // And column SQL type
>
> // Add special column features:
> //
> // Identity(_seed, _increment) Autoincrement Could not set this as
> column will be read only
> // Primary Key Primary Key
> // Default _value Sets default value
> // Unique For unique columns
> // Not Null Null is not allowed
>
> if ( c.DefaultValue != DBNull.Value )
> {
> // Default value is set?
> command.Append (String.Format(" Default '{0}'",
> c.DefaultValue));
> }
>
> if ( !c.AllowDBNull )
> {
> // Could not be null ?
> command.Append (" Not Null"); // Mark it as Not Null
> }
>
> if ( c.Unique )
> { // Unique column ?
> DataColumn[] pk = table.PrimaryKey; // Get primry key column(s)
> if ( pk != null && pk.Length > 1 ) // Only one column
> allowed as a primary key in SQL CE
> throw new System.Exception("Only one column allowed as a primary key
> in SQL CE");
>
> if ( null != pk && pk.Length == 1 && pk[0] == c)
> { // Primary key ?
> command.Append (" Primary Key"); // Mark it as such
> }
> else
> {
> command.Append (" Unique"); // Mark it as unique
> }
> }
>
> command.Append (','); // Add separator
> }
>
> command.Replace (',', ')', command.Length - 1, 1); // Replace last
> comma with ')'
>
> cmd.CommandText = command.ToString(); // Set command
>
> // Console.WriteLine ("Create command: \n{0}", cmd.CommandText);
>
> cmd.ExecuteNonQuery(); // Do it - create a table
>
> cmd.Parameters.Clear(); // Do some cleanup
>
>
> // At this point we have a database with an empty table ready to be
> populated...
>
> SqlCeDataAdapter da = new SqlCeDataAdapter(); // Prepare data
> adapter
>
> command.Remove(0, command.Length); // Clean up old
> command
>
> command.Append ("Insert Into \""); // Insert command
> command.Append ( table.TableName );
> command.Append ("\" (");
>
> foreach (DataColumn column in table.Columns)
> { // Add column names
> command.Append ('"');
> command.Append ( column.ColumnName );
> command.Append ("\","); // Separated by commas
>
> cmd.Parameters.Add("@" + column.ColumnName, // Add parameters to q
> query
> GetSQLType (column.DataType), column.MaxLength > 0 ? column.MaxLength
> : 0, column.ColumnName);
> }
>
> command.Remove(command.Length - 1, 1); // Remove last comma
>
> command.Append(") Values (");
>
> for (int i = table.Columns.Count; i-- > 0; ) // Add correct
> number
> command.Append( "?,"); // of queston marks
>
> command.Replace (',', ')', command.Length - 1, 1); // Replace last
> comma with ')'
>
> cmd.CommandText = command.ToString(); // Set insert command
>
> da.InsertCommand = cmd; // Store our insert command
>
> try
> {
> da.Update(table); // Update table in the SQL CE
> }
> catch (Exception ex)
> {
> ex.ToString();
> }
>
> cmd.Parameters.Clear(); // Do some cleanup
>
> //Verification
> Console.WriteLine("VERIFYING");
> #region VERIFICATION
> cmd = con.CreateCommand(); // Prepare Create table command
> string vcommand = String.Format("select COUNT(*) from
> {0}",table.TableName);
> cmd.CommandText = vcommand; // Set command
>
> int count = (int)cmd.ExecuteScalar(); // Do it - create a table
> if(count != table.Rows.Count)
> {
> Console.WriteLine("We did not store Expected number of records");
> Console.WriteLine("Expected was {0}", table.Rows.Count);
> Console.WriteLine("Actual was {0}",count);
> throw new Exception("Error in Verification");
> }
> #endregion
> Console.WriteLine("DONE WITH VERIFICATION");
>
> }
>
> con.Close(); // Close connection.
> }
>
>

I've looked through it but It's a bit crypic in my newsreader. What
specific exception are you getting? The first thing I'd do is verify the
Update statement's syntax. Grab it in the debugger and run it in CE's QUery
anaylzer (just make sure to fully close it afterward). For this one I'd
close the connection too after creating the table, and let the adapter open
and close it itself. To be honest, I'd just go ahead and use the same
syntax but cut down on the fields, just for testing to try to eliminate
things. That's a lot of string manipulation going on there and any number
of 'trivial' things could be causing the blow up

--
W.G. Ryan MVP Windows - Embedded

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/...ity/newsgroups
"lang" <(E-Mail Removed)> wrote in message
news:6B027A66-7C76-4889-84FF-(E-Mail Removed)...
> I am attempting to save a dynamic dataset to sql ce, and am coming across
a
> problem.
>
> In the supplied code, it creates the table, which appears to work ok. The
> create table command is as follows:
>
> "Create Table \"allbills\" (\"SacID\" NVarChar,\"LineNum\"
> Int,\"DateTouched\" NVarChar,\"EstabCode\" NVarChar,\"MAWB\"
> NVarChar,\"HAWB\" NVarChar,\"ArrivalDate\" NVarChar,\"Flight\"
> NVarChar,\"GoodsDescription\" NVarChar,\"AIMSUpgraded\"
Bit,\"FSResultCode\"
> NVarChar,\"InspectResultCode\" NVarChar,\"SACOfficer\"
> NVarChar,\"ReportableDocInd\" Bit,\"CountSACsWithinMAWB\"
> Int,\"CountHAWBsWithinMAWB\" Int,\"HVLVCountSACsInMAWB\"
> Int,\"HVLVCountHAWBsInMAWB\" Int,\"SID\" NVarChar Not Null Primary
> Key,\"FSModified\" NVarChar,\"InspModified\" NVarChar,\"FSResultOriginal\"
> NVarChar,\"InspectResultOriginal\" NVarChar,\"MAWBLevel\"
> NVarChar,\"FSUploaded\" NVarChar,\"InspUploaded\" NVarChar)"
>
> The code then generates an Insert Command that appears to be correct, and
> assigns a set of parameters to this command.
>
> "Insert Into \"allbills\"
>
(\"SacID\",\"LineNum\",\"DateTouched\",\"EstabCode\",\"MAWB\",\"HAWB\",\"Arr
ivalDate\",\"Flight\",\"GoodsDescription\",\"AIMSUpgraded\",\"FSResultCode\"
,\"InspectResultCode\",\"SACOfficer\",\"ReportableDocInd\",\"CountSACsWithin
MAWB\",\"CountHAWBsWithinMAWB\",\"HVLVCountSACsInMAWB\",\"HVLVCountHAWBsInMA
WB\",\"SID\",\"FSModified\",\"InspModified\",\"FSResultOriginal\",\"InspectR
esultOriginal\",\"MAWBLevel\",\"FSUploaded\",\"InspUploaded\")
> Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
>
> It then creates a dataadapter and assign this insert command to the
> dataadapter, then runs dataadapter.update(table), which generates an
> exception with the message "Update". Is the code using the Update method
> correctly, any suggestions? Third party tools are not an option in this
case.
> (see source code below)
>
>
>
> public static void storeDataToSQLCE( DataSet data, string dataFile )
> {
> if (File.Exists(dataFile)) // Get rid of existing database file
> File.Delete(dataFile);
>
> SqlCeEngine en = new SqlCeEngine("Data Source = " + dataFile ); //
> Create new database engine
> en.CreateDatabase(); // Create a new database
> SqlCeConnection con = new SqlCeConnection("Data Source = " +
> dataFile ); // Create a new connection
> con.Open(); // Open this connection
>
> foreach (DataTable table in data.Tables )
> {
> // Create tables one by one
>
> SqlCeCommand cmd = con.CreateCommand();
>
> // Prepare Create table command
>
> StringBuilder command = new StringBuilder(1024); // Assume we have
> pretty long command
>
> command.Append("Create Table \"");
>
> // Command string:
> command.Append( table.TableName );
> // Create Table "TableName" ("ColumnName" ColumnType, ... )
> command.Append( "\" (");
>
> foreach ( DataColumn c in table.Columns)
> {
> // Add all columns
> command.Append ("\"");
> command.Append (c.ColumnName); // Add column name
> command.Append ("\" ");
> command.Append (GetSQLTypeName(c.DataType)); // And column SQL type
>
> // Add special column features:
> //
> // Identity(_seed, _increment) Autoincrement Could not set this as
> column will be read only
> // Primary Key Primary Key
> // Default _value Sets default value
> // Unique For unique columns
> // Not Null Null is not allowed
>
> if ( c.DefaultValue != DBNull.Value )
> {
> // Default value is set?
> command.Append (String.Format(" Default '{0}'",
> c.DefaultValue));
> }
>
> if ( !c.AllowDBNull )
> {
> // Could not be null ?
> command.Append (" Not Null"); // Mark it as Not Null
> }
>
> if ( c.Unique )
> { // Unique column ?
> DataColumn[] pk = table.PrimaryKey; // Get primry key column(s)
> if ( pk != null && pk.Length > 1 ) // Only one column
> allowed as a primary key in SQL CE
> throw new System.Exception("Only one column allowed as a primary key
> in SQL CE");
>
> if ( null != pk && pk.Length == 1 && pk[0] == c)
> { // Primary key ?
> command.Append (" Primary Key"); // Mark it as such
> }
> else
> {
> command.Append (" Unique"); // Mark it as unique
> }
> }
>
> command.Append (','); // Add separator
> }
>
> command.Replace (',', ')', command.Length - 1, 1); // Replace last
> comma with ')'
>
> cmd.CommandText = command.ToString(); // Set command
>
> // Console.WriteLine ("Create command: \n{0}", cmd.CommandText);
>
> cmd.ExecuteNonQuery(); // Do it - create a table
>
> cmd.Parameters.Clear(); // Do some cleanup
>
>
> // At this point we have a database with an empty table ready to be
> populated...
>
> SqlCeDataAdapter da = new SqlCeDataAdapter(); // Prepare data
> adapter
>
> command.Remove(0, command.Length); // Clean up old
> command
>
> command.Append ("Insert Into \""); // Insert command
> command.Append ( table.TableName );
> command.Append ("\" (");
>
> foreach (DataColumn column in table.Columns)
> { // Add column names
> command.Append ('"');
> command.Append ( column.ColumnName );
> command.Append ("\","); // Separated by commas
>
> cmd.Parameters.Add("@" + column.ColumnName, // Add parameters to q
> query
> GetSQLType (column.DataType), column.MaxLength > 0 ? column.MaxLength
> : 0, column.ColumnName);
> }
>
> command.Remove(command.Length - 1, 1); // Remove last comma
>
> command.Append(") Values (");
>
> for (int i = table.Columns.Count; i-- > 0; ) // Add correct
> number
> command.Append( "?,"); // of queston marks
>
> command.Replace (',', ')', command.Length - 1, 1); // Replace last
> comma with ')'
>
> cmd.CommandText = command.ToString(); // Set insert command
>
> da.InsertCommand = cmd; // Store our insert command
>
> try
> {
> da.Update(table); // Update table in the SQL CE
> }
> catch (Exception ex)
> {
> ex.ToString();
> }
>
> cmd.Parameters.Clear(); // Do some cleanup
>
> //Verification
> Console.WriteLine("VERIFYING");
> #region VERIFICATION
> cmd = con.CreateCommand(); // Prepare Create table command
> string vcommand = String.Format("select COUNT(*) from
> {0}",table.TableName);
> cmd.CommandText = vcommand; // Set command
>
> int count = (int)cmd.ExecuteScalar(); // Do it - create a table
> if(count != table.Rows.Count)
> {
> Console.WriteLine("We did not store Expected number of records");
> Console.WriteLine("Expected was {0}", table.Rows.Count);
> Console.WriteLine("Actual was {0}",count);
> throw new Exception("Error in Verification");
> }
> #endregion
> Console.WriteLine("DONE WITH VERIFICATION");
>
> }
>
> con.Close(); // Close connection.
> }
>
>

The exception is of type "InvalidOperationException", and the message is
simply "Update" (and it is definitely when it is trying to run
DataAdapter.Update(table) - is that the correct way I should be using the
Update method?).

From what I can see there is an Insert command for the DataAdapter, but
there is no Update command. Is that meant to happen automatically, or do I
have to generate an Update command?





"William Ryan eMVP" wrote:

> I've looked through it but It's a bit crypic in my newsreader. What
> specific exception are you getting? The first thing I'd do is verify the
> Update statement's syntax. Grab it in the debugger and run it in CE's QUery
> anaylzer (just make sure to fully close it afterward). For this one I'd
> close the connection too after creating the table, and let the adapter open
> and close it itself. To be honest, I'd just go ahead and use the same
> syntax but cut down on the fields, just for testing to try to eliminate
> things. That's a lot of string manipulation going on there and any number
> of 'trivial' things could be causing the blow up
>
> --
> W.G. Ryan MVP Windows - Embedded
>
> Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
> Let Microsoft know!
> https://www.windowsembeddedeval.com/...ity/newsgroups
> "lang" <(E-Mail Removed)> wrote in message
> news:6B027A66-7C76-4889-84FF-(E-Mail Removed)...
> > I am attempting to save a dynamic dataset to sql ce, and am coming across
> a
> > problem.
> >
> > In the supplied code, it creates the table, which appears to work ok. The
> > create table command is as follows:
> >
> > "Create Table \"allbills\" (\"SacID\" NVarChar,\"LineNum\"
> > Int,\"DateTouched\" NVarChar,\"EstabCode\" NVarChar,\"MAWB\"
> > NVarChar,\"HAWB\" NVarChar,\"ArrivalDate\" NVarChar,\"Flight\"
> > NVarChar,\"GoodsDescription\" NVarChar,\"AIMSUpgraded\"
> Bit,\"FSResultCode\"
> > NVarChar,\"InspectResultCode\" NVarChar,\"SACOfficer\"
> > NVarChar,\"ReportableDocInd\" Bit,\"CountSACsWithinMAWB\"
> > Int,\"CountHAWBsWithinMAWB\" Int,\"HVLVCountSACsInMAWB\"
> > Int,\"HVLVCountHAWBsInMAWB\" Int,\"SID\" NVarChar Not Null Primary
> > Key,\"FSModified\" NVarChar,\"InspModified\" NVarChar,\"FSResultOriginal\"
> > NVarChar,\"InspectResultOriginal\" NVarChar,\"MAWBLevel\"
> > NVarChar,\"FSUploaded\" NVarChar,\"InspUploaded\" NVarChar)"
> >
> > The code then generates an Insert Command that appears to be correct, and
> > assigns a set of parameters to this command.
> >
> > "Insert Into \"allbills\"
> >
> (\"SacID\",\"LineNum\",\"DateTouched\",\"EstabCode\",\"MAWB\",\"HAWB\",\"Arr
> ivalDate\",\"Flight\",\"GoodsDescription\",\"AIMSUpgraded\",\"FSResultCode\"
> ,\"InspectResultCode\",\"SACOfficer\",\"ReportableDocInd\",\"CountSACsWithin
> MAWB\",\"CountHAWBsWithinMAWB\",\"HVLVCountSACsInMAWB\",\"HVLVCountHAWBsInMA
> WB\",\"SID\",\"FSModified\",\"InspModified\",\"FSResultOriginal\",\"InspectR
> esultOriginal\",\"MAWBLevel\",\"FSUploaded\",\"InspUploaded\")
> > Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
> >
> > It then creates a dataadapter and assign this insert command to the
> > dataadapter, then runs dataadapter.update(table), which generates an
> > exception with the message "Update". Is the code using the Update method
> > correctly, any suggestions? Third party tools are not an option in this
> case.
> > (see source code below)
> >
> >
> >
> > public static void storeDataToSQLCE( DataSet data, string dataFile )
> > {
> > if (File.Exists(dataFile)) // Get rid of existing database file
> > File.Delete(dataFile);
> >
> > SqlCeEngine en = new SqlCeEngine("Data Source = " + dataFile ); //
> > Create new database engine
> > en.CreateDatabase(); // Create a new database
> > SqlCeConnection con = new SqlCeConnection("Data Source = " +
> > dataFile ); // Create a new connection
> > con.Open(); // Open this connection
> >
> > foreach (DataTable table in data.Tables )
> > {
> > // Create tables one by one
> >
> > SqlCeCommand cmd = con.CreateCommand();
> >
> > // Prepare Create table command
> >
> > StringBuilder command = new StringBuilder(1024); // Assume we have
> > pretty long command
> >
> > command.Append("Create Table \"");
> >
> > // Command string:
> > command.Append( table.TableName );
> > // Create Table "TableName" ("ColumnName" ColumnType, ... )
> > command.Append( "\" (");
> >
> > foreach ( DataColumn c in table.Columns)
> > {
> > // Add all columns
> > command.Append ("\"");
> > command.Append (c.ColumnName); // Add column name
> > command.Append ("\" ");
> > command.Append (GetSQLTypeName(c.DataType)); // And column SQL type
> >
> > // Add special column features:
> > //
> > // Identity(_seed, _increment) Autoincrement Could not set this as
> > column will be read only
> > // Primary Key Primary Key
> > // Default _value Sets default value
> > // Unique For unique columns
> > // Not Null Null is not allowed
> >
> > if ( c.DefaultValue != DBNull.Value )
> > {
> > // Default value is set?
> > command.Append (String.Format(" Default '{0}'",
> > c.DefaultValue));
> > }
> >
> > if ( !c.AllowDBNull )
> > {
> > // Could not be null ?
> > command.Append (" Not Null"); // Mark it as Not Null
> > }
> >
> > if ( c.Unique )
> > { // Unique column ?
> > DataColumn[] pk = table.PrimaryKey; // Get primry key column(s)
> > if ( pk != null && pk.Length > 1 ) // Only one column
> > allowed as a primary key in SQL CE
> > throw new System.Exception("Only one column allowed as a primary key
> > in SQL CE");
> >
> > if ( null != pk && pk.Length == 1 && pk[0] == c)
> > { // Primary key ?
> > command.Append (" Primary Key"); // Mark it as such
> > }
> > else
> > {
> > command.Append (" Unique"); // Mark it as unique
> > }
> > }
> >
> > command.Append (','); // Add separator
> > }
> >
> > command.Replace (',', ')', command.Length - 1, 1); // Replace last
> > comma with ')'
> >
> > cmd.CommandText = command.ToString(); // Set command
> >
> > // Console.WriteLine ("Create command: \n{0}", cmd.CommandText);
> >
> > cmd.ExecuteNonQuery(); // Do it - create a table
> >
> > cmd.Parameters.Clear(); // Do some cleanup
> >
> >
> > // At this point we have a database with an empty table ready to be
> > populated...
> >
> > SqlCeDataAdapter da = new SqlCeDataAdapter(); // Prepare data
> > adapter
> >
> > command.Remove(0, command.Length); // Clean up old
> > command
> >
> > command.Append ("Insert Into \""); // Insert command
> > command.Append ( table.TableName );
> > command.Append ("\" (");
> >
> > foreach (DataColumn column in table.Columns)
> > { // Add column names
> > command.Append ('"');
> > command.Append ( column.ColumnName );
> > command.Append ("\","); // Separated by commas
> >
> > cmd.Parameters.Add("@" + column.ColumnName, // Add parameters to q
> > query
> > GetSQLType (column.DataType), column.MaxLength > 0 ? column.MaxLength
> > : 0, column.ColumnName);
> > }
> >
> > command.Remove(command.Length - 1, 1); // Remove last comma
> >
> > command.Append(") Values (");
> >
> > for (int i = table.Columns.Count; i-- > 0; ) // Add correct
> > number
> > command.Append( "?,"); // of queston marks
> >
> > command.Replace (',', ')', command.Length - 1, 1); // Replace last
> > comma with ')'
> >
> > cmd.CommandText = command.ToString(); // Set insert command
> >
> > da.InsertCommand = cmd; // Store our insert command
> >
> > try
> > {
> > da.Update(table); // Update table in the SQL CE
> > }
> > catch (Exception ex)
> > {
> > ex.ToString();
> > }
> >
> > cmd.Parameters.Clear(); // Do some cleanup
> >
> > //Verification
> > Console.WriteLine("VERIFYING");
> > #region VERIFICATION
> > cmd = con.CreateCommand(); // Prepare Create table command
> > string vcommand = String.Format("select COUNT(*) from
> > {0}",table.TableName);
> > cmd.CommandText = vcommand; // Set command
> >
> > int count = (int)cmd.ExecuteScalar(); // Do it - create a table
> > if(count != table.Rows.Count)
> > {
> > Console.WriteLine("We did not store Expected number of records");
> > Console.WriteLine("Expected was {0}", table.Rows.Count);
> > Console.WriteLine("Actual was {0}",count);
> > throw new Exception("Error in Verification");
> > }
> > #endregion
> > Console.WriteLine("DONE WITH VERIFICATION");
> >
> > }
> >
> > con.Close(); // Close connection.
> > }
> >
> >
>
>
>

I suggest you temprorily turn off integrity checking prior to doing the update
then turning it back on after the update.
You can do the following:
ds.EnforceConstraints = false;
storeDataToSQLCE( ds, "foo.sdf" );
ds.EnforceConstraints = true;



"lang" wrote:

> The exception is of type "InvalidOperationException", and the message is
> simply "Update" (and it is definitely when it is trying to run
> DataAdapter.Update(table) - is that the correct way I should be using the
> Update method?).
>
> From what I can see there is an Insert command for the DataAdapter, but
> there is no Update command. Is that meant to happen automatically, or do I
> have to generate an Update command?
>
>
>
>
>
> "William Ryan eMVP" wrote:
>
> > I've looked through it but It's a bit crypic in my newsreader. What
> > specific exception are you getting? The first thing I'd do is verify the
> > Update statement's syntax. Grab it in the debugger and run it in CE's QUery
> > anaylzer (just make sure to fully close it afterward). For this one I'd
> > close the connection too after creating the table, and let the adapter open
> > and close it itself. To be honest, I'd just go ahead and use the same
> > syntax but cut down on the fields, just for testing to try to eliminate
> > things. That's a lot of string manipulation going on there and any number
> > of 'trivial' things could be causing the blow up
> >
> > --
> > W.G. Ryan MVP Windows - Embedded
> >
> > Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
> > Let Microsoft know!
> > https://www.windowsembeddedeval.com/...ity/newsgroups
> > "lang" <(E-Mail Removed)> wrote in message
> > news:6B027A66-7C76-4889-84FF-(E-Mail Removed)...
> > > I am attempting to save a dynamic dataset to sql ce, and am coming across
> > a
> > > problem.
> > >
> > > In the supplied code, it creates the table, which appears to work ok. The
> > > create table command is as follows:
> > >
> > > "Create Table \"allbills\" (\"SacID\" NVarChar,\"LineNum\"
> > > Int,\"DateTouched\" NVarChar,\"EstabCode\" NVarChar,\"MAWB\"
> > > NVarChar,\"HAWB\" NVarChar,\"ArrivalDate\" NVarChar,\"Flight\"
> > > NVarChar,\"GoodsDescription\" NVarChar,\"AIMSUpgraded\"
> > Bit,\"FSResultCode\"
> > > NVarChar,\"InspectResultCode\" NVarChar,\"SACOfficer\"
> > > NVarChar,\"ReportableDocInd\" Bit,\"CountSACsWithinMAWB\"
> > > Int,\"CountHAWBsWithinMAWB\" Int,\"HVLVCountSACsInMAWB\"
> > > Int,\"HVLVCountHAWBsInMAWB\" Int,\"SID\" NVarChar Not Null Primary
> > > Key,\"FSModified\" NVarChar,\"InspModified\" NVarChar,\"FSResultOriginal\"
> > > NVarChar,\"InspectResultOriginal\" NVarChar,\"MAWBLevel\"
> > > NVarChar,\"FSUploaded\" NVarChar,\"InspUploaded\" NVarChar)"
> > >
> > > The code then generates an Insert Command that appears to be correct, and
> > > assigns a set of parameters to this command.
> > >
> > > "Insert Into \"allbills\"
> > >
> > (\"SacID\",\"LineNum\",\"DateTouched\",\"EstabCode\",\"MAWB\",\"HAWB\",\"Arr
> > ivalDate\",\"Flight\",\"GoodsDescription\",\"AIMSUpgraded\",\"FSResultCode\"
> > ,\"InspectResultCode\",\"SACOfficer\",\"ReportableDocInd\",\"CountSACsWithin
> > MAWB\",\"CountHAWBsWithinMAWB\",\"HVLVCountSACsInMAWB\",\"HVLVCountHAWBsInMA
> > WB\",\"SID\",\"FSModified\",\"InspModified\",\"FSResultOriginal\",\"InspectR
> > esultOriginal\",\"MAWBLevel\",\"FSUploaded\",\"InspUploaded\")
> > > Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
> > >
> > > It then creates a dataadapter and assign this insert command to the
> > > dataadapter, then runs dataadapter.update(table), which generates an
> > > exception with the message "Update". Is the code using the Update method
> > > correctly, any suggestions? Third party tools are not an option in this
> > case.
> > > (see source code below)
> > >
> > >
> > >
> > > public static void storeDataToSQLCE( DataSet data, string dataFile )
> > > {
> > > if (File.Exists(dataFile)) // Get rid of existing database file
> > > File.Delete(dataFile);
> > >
> > > SqlCeEngine en = new SqlCeEngine("Data Source = " + dataFile ); //
> > > Create new database engine
> > > en.CreateDatabase(); // Create a new database
> > > SqlCeConnection con = new SqlCeConnection("Data Source = " +
> > > dataFile ); // Create a new connection
> > > con.Open(); // Open this connection
> > >
> > > foreach (DataTable table in data.Tables )
> > > {
> > > // Create tables one by one
> > >
> > > SqlCeCommand cmd = con.CreateCommand();
> > >
> > > // Prepare Create table command
> > >
> > > StringBuilder command = new StringBuilder(1024); // Assume we have
> > > pretty long command
> > >
> > > command.Append("Create Table \"");
> > >
> > > // Command string:
> > > command.Append( table.TableName );
> > > // Create Table "TableName" ("ColumnName" ColumnType, ... )
> > > command.Append( "\" (");
> > >
> > > foreach ( DataColumn c in table.Columns)
> > > {
> > > // Add all columns
> > > command.Append ("\"");
> > > command.Append (c.ColumnName); // Add column name
> > > command.Append ("\" ");
> > > command.Append (GetSQLTypeName(c.DataType)); // And column SQL type
> > >
> > > // Add special column features:
> > > //
> > > // Identity(_seed, _increment) Autoincrement Could not set this as
> > > column will be read only
> > > // Primary Key Primary Key
> > > // Default _value Sets default value
> > > // Unique For unique columns
> > > // Not Null Null is not allowed
> > >
> > > if ( c.DefaultValue != DBNull.Value )
> > > {
> > > // Default value is set?
> > > command.Append (String.Format(" Default '{0}'",
> > > c.DefaultValue));
> > > }
> > >
> > > if ( !c.AllowDBNull )
> > > {
> > > // Could not be null ?
> > > command.Append (" Not Null"); // Mark it as Not Null
> > > }
> > >
> > > if ( c.Unique )
> > > { // Unique column ?
> > > DataColumn[] pk = table.PrimaryKey; // Get primry key column(s)
> > > if ( pk != null && pk.Length > 1 ) // Only one column
> > > allowed as a primary key in SQL CE
> > > throw new System.Exception("Only one column allowed as a primary key
> > > in SQL CE");
> > >
> > > if ( null != pk && pk.Length == 1 && pk[0] == c)
> > > { // Primary key ?
> > > command.Append (" Primary Key"); // Mark it as such
> > > }
> > > else
> > > {
> > > command.Append (" Unique"); // Mark it as unique
> > > }
> > > }
> > >
> > > command.Append (','); // Add separator
> > > }
> > >
> > > command.Replace (',', ')', command.Length - 1, 1); // Replace last
> > > comma with ')'
> > >
> > > cmd.CommandText = command.ToString(); // Set command
> > >
> > > // Console.WriteLine ("Create command: \n{0}", cmd.CommandText);
> > >
> > > cmd.ExecuteNonQuery(); // Do it - create a table
> > >
> > > cmd.Parameters.Clear(); // Do some cleanup
> > >
> > >
> > > // At this point we have a database with an empty table ready to be
> > > populated...
> > >
> > > SqlCeDataAdapter da = new SqlCeDataAdapter(); // Prepare data
> > > adapter
> > >
> > > command.Remove(0, command.Length); // Clean up old
> > > command
> > >
> > > command.Append ("Insert Into \""); // Insert command
> > > command.Append ( table.TableName );
> > > command.Append ("\" (");
> > >
> > > foreach (DataColumn column in table.Columns)
> > > { // Add column names
> > > command.Append ('"');
> > > command.Append ( column.ColumnName );
> > > command.Append ("\","); // Separated by commas
> > >
> > > cmd.Parameters.Add("@" + column.ColumnName, // Add parameters to q
> > > query
> > > GetSQLType (column.DataType), column.MaxLength > 0 ? column.MaxLength
> > > : 0, column.ColumnName);
> > > }
> > >
> > > command.Remove(command.Length - 1, 1); // Remove last comma
> > >
> > > command.Append(") Values (");
> > >
> > > for (int i = table.Columns.Count; i-- > 0; ) // Add correct
> > > number
> > > command.Append( "?,"); // of queston marks
> > >
> > > command.Replace (',', ')', command.Length - 1, 1); // Replace last
> > > comma with ')'
> > >
> > > cmd.CommandText = command.ToString(); // Set insert command
> > >
> > > da.InsertCommand = cmd; // Store our insert command
> > >
> > > try
> > > {
> > > da.Update(table); // Update table in the SQL CE
> > > }
> > > catch (Exception ex)
> > > {
> > > ex.ToString();
> > > }
> > >
> > > cmd.Parameters.Clear(); // Do some cleanup
> > >
> > > //Verification
> > > Console.WriteLine("VERIFYING");
> > > #region VERIFICATION
> > > cmd = con.CreateCommand(); // Prepare Create table command
> > > string vcommand = String.Format("select COUNT(*) from
> > > {0}",table.TableName);
> > > cmd.CommandText = vcommand; // Set command
> > >
> > > int count = (int)cmd.ExecuteScalar(); // Do it - create a table
> > > if(count != table.Rows.Count)
> > > {
> > > Console.WriteLine("We did not store Expected number of records");
> > > Console.WriteLine("Expected was {0}", table.Rows.Count);
> > > Console.WriteLine("Actual was {0}",count);
> > > throw new Exception("Error in Verification");
> > > }
> > > #endregion
> > > Console.WriteLine("DONE WITH VERIFICATION");
> > >
> > > }
> > >
> > > con.Close(); // Close connection.
> > > }
> > >
> > >
> >
> >
> >

no change

"Mark Ihimoyan [MSFT]" wrote:

> I suggest you temprorily turn off integrity checking prior to doing the update
> then turning it back on after the update.
> You can do the following:
> ds.EnforceConstraints = false;
> storeDataToSQLCE( ds, "foo.sdf" );
> ds.EnforceConstraints = true;
>
>
>
> "lang" wrote:
>
> > The exception is of type "InvalidOperationException", and the message is
> > simply "Update" (and it is definitely when it is trying to run
> > DataAdapter.Update(table) - is that the correct way I should be using the
> > Update method?).
> >
> > From what I can see there is an Insert command for the DataAdapter, but
> > there is no Update command. Is that meant to happen automatically, or do I
> > have to generate an Update command?
> >
> >
> >
> >
> >
> > "William Ryan eMVP" wrote:
> >
> > > I've looked through it but It's a bit crypic in my newsreader. What
> > > specific exception are you getting? The first thing I'd do is verify the
> > > Update statement's syntax. Grab it in the debugger and run it in CE's QUery
> > > anaylzer (just make sure to fully close it afterward). For this one I'd
> > > close the connection too after creating the table, and let the adapter open
> > > and close it itself. To be honest, I'd just go ahead and use the same
> > > syntax but cut down on the fields, just for testing to try to eliminate
> > > things. That's a lot of string manipulation going on there and any number
> > > of 'trivial' things could be causing the blow up
> > >
> > > --
> > > W.G. Ryan MVP Windows - Embedded
> > >
> > > Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
> > > Let Microsoft know!
> > > https://www.windowsembeddedeval.com/...ity/newsgroups
> > > "lang" <(E-Mail Removed)> wrote in message
> > > news:6B027A66-7C76-4889-84FF-(E-Mail Removed)...
> > > > I am attempting to save a dynamic dataset to sql ce, and am coming across
> > > a
> > > > problem.
> > > >
> > > > In the supplied code, it creates the table, which appears to work ok. The
> > > > create table command is as follows:
> > > >
> > > > "Create Table \"allbills\" (\"SacID\" NVarChar,\"LineNum\"
> > > > Int,\"DateTouched\" NVarChar,\"EstabCode\" NVarChar,\"MAWB\"
> > > > NVarChar,\"HAWB\" NVarChar,\"ArrivalDate\" NVarChar,\"Flight\"
> > > > NVarChar,\"GoodsDescription\" NVarChar,\"AIMSUpgraded\"
> > > Bit,\"FSResultCode\"
> > > > NVarChar,\"InspectResultCode\" NVarChar,\"SACOfficer\"
> > > > NVarChar,\"ReportableDocInd\" Bit,\"CountSACsWithinMAWB\"
> > > > Int,\"CountHAWBsWithinMAWB\" Int,\"HVLVCountSACsInMAWB\"
> > > > Int,\"HVLVCountHAWBsInMAWB\" Int,\"SID\" NVarChar Not Null Primary
> > > > Key,\"FSModified\" NVarChar,\"InspModified\" NVarChar,\"FSResultOriginal\"
> > > > NVarChar,\"InspectResultOriginal\" NVarChar,\"MAWBLevel\"
> > > > NVarChar,\"FSUploaded\" NVarChar,\"InspUploaded\" NVarChar)"
> > > >
> > > > The code then generates an Insert Command that appears to be correct, and
> > > > assigns a set of parameters to this command.
> > > >
> > > > "Insert Into \"allbills\"
> > > >
> > > (\"SacID\",\"LineNum\",\"DateTouched\",\"EstabCode\",\"MAWB\",\"HAWB\",\"Arr
> > > ivalDate\",\"Flight\",\"GoodsDescription\",\"AIMSUpgraded\",\"FSResultCode\"
> > > ,\"InspectResultCode\",\"SACOfficer\",\"ReportableDocInd\",\"CountSACsWithin
> > > MAWB\",\"CountHAWBsWithinMAWB\",\"HVLVCountSACsInMAWB\",\"HVLVCountHAWBsInMA
> > > WB\",\"SID\",\"FSModified\",\"InspModified\",\"FSResultOriginal\",\"InspectR
> > > esultOriginal\",\"MAWBLevel\",\"FSUploaded\",\"InspUploaded\")
> > > > Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
> > > >
> > > > It then creates a dataadapter and assign this insert command to the
> > > > dataadapter, then runs dataadapter.update(table), which generates an
> > > > exception with the message "Update". Is the code using the Update method
> > > > correctly, any suggestions? Third party tools are not an option in this
> > > case.
> > > > (see source code below)
> > > >
> > > >
> > > >
> > > > public static void storeDataToSQLCE( DataSet data, string dataFile )
> > > > {
> > > > if (File.Exists(dataFile)) // Get rid of existing database file
> > > > File.Delete(dataFile);
> > > >
> > > > SqlCeEngine en = new SqlCeEngine("Data Source = " + dataFile ); //
> > > > Create new database engine
> > > > en.CreateDatabase(); // Create a new database
> > > > SqlCeConnection con = new SqlCeConnection("Data Source = " +
> > > > dataFile ); // Create a new connection
> > > > con.Open(); // Open this connection
> > > >
> > > > foreach (DataTable table in data.Tables )
> > > > {
> > > > // Create tables one by one
> > > >
> > > > SqlCeCommand cmd = con.CreateCommand();
> > > >
> > > > // Prepare Create table command
> > > >
> > > > StringBuilder command = new StringBuilder(1024); // Assume we have
> > > > pretty long command
> > > >
> > > > command.Append("Create Table \"");
> > > >
> > > > // Command string:
> > > > command.Append( table.TableName );
> > > > // Create Table "TableName" ("ColumnName" ColumnType, ... )
> > > > command.Append( "\" (");
> > > >
> > > > foreach ( DataColumn c in table.Columns)
> > > > {
> > > > // Add all columns
> > > > command.Append ("\"");
> > > > command.Append (c.ColumnName); // Add column name
> > > > command.Append ("\" ");
> > > > command.Append (GetSQLTypeName(c.DataType)); // And column SQL type
> > > >
> > > > // Add special column features:
> > > > //
> > > > // Identity(_seed, _increment) Autoincrement Could not set this as
> > > > column will be read only
> > > > // Primary Key Primary Key
> > > > // Default _value Sets default value
> > > > // Unique For unique columns
> > > > // Not Null Null is not allowed
> > > >
> > > > if ( c.DefaultValue != DBNull.Value )
> > > > {
> > > > // Default value is set?
> > > > command.Append (String.Format(" Default '{0}'",
> > > > c.DefaultValue));
> > > > }
> > > >
> > > > if ( !c.AllowDBNull )
> > > > {
> > > > // Could not be null ?
> > > > command.Append (" Not Null"); // Mark it as Not Null
> > > > }
> > > >
> > > > if ( c.Unique )
> > > > { // Unique column ?
> > > > DataColumn[] pk = table.PrimaryKey; // Get primry key column(s)
> > > > if ( pk != null && pk.Length > 1 ) // Only one column
> > > > allowed as a primary key in SQL CE
> > > > throw new System.Exception("Only one column allowed as a primary key
> > > > in SQL CE");
> > > >
> > > > if ( null != pk && pk.Length == 1 && pk[0] == c)
> > > > { // Primary key ?
> > > > command.Append (" Primary Key"); // Mark it as such
> > > > }
> > > > else
> > > > {
> > > > command.Append (" Unique"); // Mark it as unique
> > > > }
> > > > }
> > > >
> > > > command.Append (','); // Add separator
> > > > }
> > > >
> > > > command.Replace (',', ')', command.Length - 1, 1); // Replace last
> > > > comma with ')'
> > > >
> > > > cmd.CommandText = command.ToString(); // Set command
> > > >
> > > > // Console.WriteLine ("Create command: \n{0}", cmd.CommandText);
> > > >
> > > > cmd.ExecuteNonQuery(); // Do it - create a table
> > > >
> > > > cmd.Parameters.Clear(); // Do some cleanup
> > > >
> > > >
> > > > // At this point we have a database with an empty table ready to be
> > > > populated...
> > > >
> > > > SqlCeDataAdapter da = new SqlCeDataAdapter(); // Prepare data
> > > > adapter
> > > >
> > > > command.Remove(0, command.Length); // Clean up old
> > > > command
> > > >
> > > > command.Append ("Insert Into \""); // Insert command
> > > > command.Append ( table.TableName );
> > > > command.Append ("\" (");
> > > >
> > > > foreach (DataColumn column in table.Columns)
> > > > { // Add column names
> > > > command.Append ('"');
> > > > command.Append ( column.ColumnName );
> > > > command.Append ("\","); // Separated by commas
> > > >
> > > > cmd.Parameters.Add("@" + column.ColumnName, // Add parameters to q
> > > > query
> > > > GetSQLType (column.DataType), column.MaxLength > 0 ? column.MaxLength
> > > > : 0, column.ColumnName);
> > > > }
> > > >
> > > > command.Remove(command.Length - 1, 1); // Remove last comma
> > > >
> > > > command.Append(") Values (");
> > > >
> > > > for (int i = table.Columns.Count; i-- > 0; ) // Add correct
> > > > number
> > > > command.Append( "?,"); // of queston marks
> > > >
> > > > command.Replace (',', ')', command.Length - 1, 1); // Replace last
> > > > comma with ')'
> > > >
> > > > cmd.CommandText = command.ToString(); // Set insert command
> > > >
> > > > da.InsertCommand = cmd; // Store our insert command
> > > >
> > > > try
> > > > {
> > > > da.Update(table); // Update table in the SQL CE
> > > > }
> > > > catch (Exception ex)
> > > > {
> > > > ex.ToString();
> > > > }
> > > >
> > > > cmd.Parameters.Clear(); // Do some cleanup
> > > >
> > > > //Verification
> > > > Console.WriteLine("VERIFYING");
> > > > #region VERIFICATION
> > > > cmd = con.CreateCommand(); // Prepare Create table command
> > > > string vcommand = String.Format("select COUNT(*) from
> > > > {0}",table.TableName);
> > > > cmd.CommandText = vcommand; // Set command
> > > >
> > > > int count = (int)cmd.ExecuteScalar(); // Do it - create a table
> > > > if(count != table.Rows.Count)
> > > > {
> > > > Console.WriteLine("We did not store Expected number of records");
> > > > Console.WriteLine("Expected was {0}", table.Rows.Count);
> > > > Console.WriteLine("Actual was {0}",count);
> > > > throw new Exception("Error in Verification");
> > > > }
> > > > #endregion
> > > > Console.WriteLine("DONE WITH VERIFICATION");
> > > >
> > > > }
> > > >
> > > > con.Close(); // Close connection.
> > > > }
> > > >
> > > >
> > >
> > >
> > >

Please do the following.
Write out the Xml data and schema from the dataset into a file and attach
it so I can investigate this further.

Use this overload with XmlWriteMode.WriteSchema.
DataSet.WriteXml(XmlWriter writer,XmlWriteMode mode)

Thanks.

The code you're using was never designed to put arbitrary DataSet into the
SQL CE. It would work correctly only in some specific cases.
I would suggest disregarding it and creating your own implementation so you
would know exactly how it works and what it can/can not do.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
> Thread-Topic: dataset saving to sql ce
> thread-index: AcSlLIumeCiP1KiaQ6ClgA3o85UC2Q==
> X-WBNR-Posting-Host: 152.91.9.8
> From: "=?Utf-8?B?bGFuZw==?=" <(E-Mail Removed)>
> References: <6B027A66-7C76-4889-84FF-(E-Mail Removed)>
<(E-Mail Removed)>
<759DAA7F-BDC7-41F2-926E-(E-Mail Removed)>
<B5D6CEC7-AB16-45F6-9C9A-(E-Mail Removed)>
> Subject: Re: dataset saving to sql ce
> Date: Tue, 28 Sep 2004 00:27:01 -0700
> Lines: 262
> Message-ID: <C146B2E3-95DE-46CB-98B9-(E-Mail Removed)>
> MIME-Version: 1.0
> Content-Type: text/plain;
> charset="Utf-8"
> Content-Transfer-Encoding: 7bit
> X-Newsreader: Microsoft CDO for Windows 2000
> Content-Class: urn:content-classes:message
> Importance: normal
> Priority: normal
> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> Newsgroups: microsoft.public.dotnet.framework.compactframework
> NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> Path: cpmsftngxa06.phx.gbl!TK2MSFTNGXA03.phx.gbl
> Xref: cpmsftngxa06.phx.gbl
microsoft.public.dotnet.framework.compactframework:62143
> X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework
>
> no change
>
> "Mark Ihimoyan [MSFT]" wrote:
>
> > I suggest you temprorily turn off integrity checking prior to doing the
update
> > then turning it back on after the update.
> > You can do the following:
> > ds.EnforceConstraints = false;
> > storeDataToSQLCE( ds, "foo.sdf" );
> > ds.EnforceConstraints = true;
> >
> >
> >
> > "lang" wrote:
> >
> > > The exception is of type "InvalidOperationException", and the message
is
> > > simply "Update" (and it is definitely when it is trying to run
> > > DataAdapter.Update(table) - is that the correct way I should be using
the
> > > Update method?).
> > >
> > > From what I can see there is an Insert command for the DataAdapter,
but
> > > there is no Update command. Is that meant to happen automatically, or
do I
> > > have to generate an Update command?
> > >
> > >
> > >
> > >
> > >
> > > "William Ryan eMVP" wrote:
> > >
> > > > I've looked through it but It's a bit crypic in my newsreader. What
> > > > specific exception are you getting? The first thing I'd do is
verify the
> > > > Update statement's syntax. Grab it in the debugger and run it in
CE's QUery
> > > > anaylzer (just make sure to fully close it afterward). For this
one I'd
> > > > close the connection too after creating the table, and let the
adapter open
> > > > and close it itself. To be honest, I'd just go ahead and use the
same
> > > > syntax but cut down on the fields, just for testing to try to
eliminate
> > > > things. That's a lot of string manipulation going on there and any
number
> > > > of 'trivial' things could be causing the blow up
> > > >
> > > > --
> > > > W.G. Ryan MVP Windows - Embedded
> > > >
> > > > Have an opinion on the effectiveness of Microsoft Embedded
newsgroups?
> > > > Let Microsoft know!
> > > > https://www.windowsembeddedeval.com/...ity/newsgroups
> > > > "lang" <(E-Mail Removed)> wrote in message
> > > > news:6B027A66-7C76-4889-84FF-(E-Mail Removed)...
> > > > > I am attempting to save a dynamic dataset to sql ce, and am
coming across
> > > > a
> > > > > problem.
> > > > >
> > > > > In the supplied code, it creates the table, which appears to work
ok. The
> > > > > create table command is as follows:
> > > > >
> > > > > "Create Table \"allbills\" (\"SacID\" NVarChar,\"LineNum\"
> > > > > Int,\"DateTouched\" NVarChar,\"EstabCode\" NVarChar,\"MAWB\"
> > > > > NVarChar,\"HAWB\" NVarChar,\"ArrivalDate\" NVarChar,\"Flight\"
> > > > > NVarChar,\"GoodsDescription\" NVarChar,\"AIMSUpgraded\"
> > > > Bit,\"FSResultCode\"
> > > > > NVarChar,\"InspectResultCode\" NVarChar,\"SACOfficer\"
> > > > > NVarChar,\"ReportableDocInd\" Bit,\"CountSACsWithinMAWB\"
> > > > > Int,\"CountHAWBsWithinMAWB\" Int,\"HVLVCountSACsInMAWB\"
> > > > > Int,\"HVLVCountHAWBsInMAWB\" Int,\"SID\" NVarChar Not Null Primary
> > > > > Key,\"FSModified\" NVarChar,\"InspModified\"
NVarChar,\"FSResultOriginal\"
> > > > > NVarChar,\"InspectResultOriginal\" NVarChar,\"MAWBLevel\"
> > > > > NVarChar,\"FSUploaded\" NVarChar,\"InspUploaded\" NVarChar)"
> > > > >
> > > > > The code then generates an Insert Command that appears to be
correct, and
> > > > > assigns a set of parameters to this command.
> > > > >
> > > > > "Insert Into \"allbills\"
> > > > >
> > > >
(\"SacID\",\"LineNum\",\"DateTouched\",\"EstabCode\",\"MAWB\",\"HAWB\",\"Arr
> > > >
ivalDate\",\"Flight\",\"GoodsDescription\",\"AIMSUpgraded\",\"FSResultCode\"
> > > >
,\"InspectResultCode\",\"SACOfficer\",\"ReportableDocInd\",\"CountSACsWithin
> > > >
MAWB\",\"CountHAWBsWithinMAWB\",\"HVLVCountSACsInMAWB\",\"HVLVCountHAWBsInMA
> > > >
WB\",\"SID\",\"FSModified\",\"InspModified\",\"FSResultOriginal\",\"InspectR
> > > > esultOriginal\",\"MAWBLevel\",\"FSUploaded\",\"InspUploaded\")
> > > > > Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
> > > > >
> > > > > It then creates a dataadapter and assign this insert command to
the
> > > > > dataadapter, then runs dataadapter.update(table), which generates
an
> > > > > exception with the message "Update". Is the code using the Update
method
> > > > > correctly, any suggestions? Third party tools are not an option
in this
> > > > case.
> > > > > (see source code below)
> > > > >
> > > > >
> > > > >
> > > > > public static void storeDataToSQLCE( DataSet data, string
dataFile )
> > > > > {
> > > > > if (File.Exists(dataFile)) // Get rid of existing database file
> > > > > File.Delete(dataFile);
> > > > >
> > > > > SqlCeEngine en = new SqlCeEngine("Data Source = " + dataFile ); //
> > > > > Create new database engine
> > > > > en.CreateDatabase(); // Create a new database
> > > > > SqlCeConnection con = new SqlCeConnection("Data Source =
" +
> > > > > dataFile ); // Create a new connection
> > > > > con.Open(); // Open this connection
> > > > >
> > > > > foreach (DataTable table in data.Tables )
> > > > > {
> > > > > // Create tables one by one
> > > > >
> > > > > SqlCeCommand cmd = con.CreateCommand();
> > > > >
> > > > > // Prepare Create table command
> > > > >
> > > > > StringBuilder command = new StringBuilder(1024); // Assume we
have
> > > > > pretty long command
> > > > >
> > > > > command.Append("Create Table \"");
> > > > >
> > > > > // Command string:
> > > > > command.Append( table.TableName );
> > > > > // Create Table "TableName" ("ColumnName" ColumnType, ... )
> > > > > command.Append( "\" (");
> > > > >
> > > > > foreach ( DataColumn c in table.Columns)
> > > > > {
> > > > > // Add all columns
> > > > > command.Append ("\"");
> > > > > command.Append (c.ColumnName); // Add column name
> > > > > command.Append ("\" ");
> > > > > command.Append (GetSQLTypeName(c.DataType)); // And column SQL
type
> > > > >
> > > > > // Add special column features:
> > > > > //
> > > > > // Identity(_seed, _increment) Autoincrement Could not set this
as
> > > > > column will be read only
> > > > > // Primary Key Primary Key
> > > > > // Default _value Sets default value
> > > > > // Unique For unique columns
> > > > > // Not Null Null is not allowed
> > > > >
> > > > > if ( c.DefaultValue != DBNull.Value )
> > > > > {
> > > > > // Default value is set?
> > > > > command.Append (String.Format(" Default '{0}'",
> > > > > c.DefaultValue));
> > > > > }
> > > > >
> > > > > if ( !c.AllowDBNull )
> > > > > {
> > > > > // Could not be null ?
> > > > > command.Append (" Not Null"); // Mark it as Not Null
> > > > > }
> > > > >
> > > > > if ( c.Unique )
> > > > > { // Unique column ?
> > > > > DataColumn[] pk = table.PrimaryKey; // Get primry key
column(s)
> > > > > if ( pk != null && pk.Length > 1 ) // Only one column
> > > > > allowed as a primary key in SQL CE
> > > > > throw new System.Exception("Only one column allowed as a primary
key
> > > > > in SQL CE");
> > > > >
> > > > > if ( null != pk && pk.Length == 1 && pk[0] == c)
> > > > > { // Primary key ?
> > > > > command.Append (" Primary Key"); // Mark it as such
> > > > > }
> > > > > else
> > > > > {
> > > > > command.Append (" Unique"); // Mark it as unique
> > > > > }
> > > > > }
> > > > >
> > > > > command.Append (','); // Add separator
> > > > > }
> > > > >
> > > > > command.Replace (',', ')', command.Length - 1, 1); // Replace
last
> > > > > comma with ')'
> > > > >
> > > > > cmd.CommandText = command.ToString(); // Set command
> > > > >
> > > > > // Console.WriteLine ("Create command: \n{0}", cmd.CommandText);
> > > > >
> > > > > cmd.ExecuteNonQuery(); // Do it - create a table
> > > > >
> > > > > cmd.Parameters.Clear(); // Do some cleanup
> > > > >
> > > > >
> > > > > // At this point we have a database with an empty table ready to
be
> > > > > populated...
> > > > >
> > > > > SqlCeDataAdapter da = new SqlCeDataAdapter(); // Prepare
data
> > > > > adapter
> > > > >
> > > > > command.Remove(0, command.Length); // Clean up
old
> > > > > command
> > > > >
> > > > > command.Append ("Insert Into \""); // Insert
command
> > > > > command.Append ( table.TableName );
> > > > > command.Append ("\" (");
> > > > >
> > > > > foreach (DataColumn column in table.Columns)
> > > > > { // Add column names
> > > > > command.Append ('"');
> > > > > command.Append ( column.ColumnName );
> > > > > command.Append ("\","); // Separated by commas
> > > > >
> > > > > cmd.Parameters.Add("@" + column.ColumnName, // Add parameters
to q
> > > > > query
> > > > > GetSQLType (column.DataType), column.MaxLength > 0 ?
column.MaxLength
> > > > > : 0, column.ColumnName);
> > > > > }
> > > > >
> > > > > command.Remove(command.Length - 1, 1); // Remove last comma
> > > > >
> > > > > command.Append(") Values (");
> > > > >
> > > > > for (int i = table.Columns.Count; i-- > 0; ) // Add correct
> > > > > number
> > > > > command.Append( "?,"); // of queston marks
> > > > >
> > > > > command.Replace (',', ')', command.Length - 1, 1); // Replace
last
> > > > > comma with ')'
> > > > >
> > > > > cmd.CommandText = command.ToString(); // Set insert command
> > > > >
> > > > > da.InsertCommand = cmd; // Store our insert command
> > > > >
> > > > > try
> > > > > {
> > > > > da.Update(table); // Update table in the SQL CE
> > > > > }
> > > > > catch (Exception ex)
> > > > > {
> > > > > ex.ToString();
> > > > > }
> > > > >
> > > > > cmd.Parameters.Clear(); // Do some cleanup
> > > > >
> > > > > //Verification
> > > > > Console.WriteLine("VERIFYING");
> > > > > #region VERIFICATION
> > > > > cmd = con.CreateCommand(); // Prepare Create table
command
> > > > > string vcommand = String.Format("select COUNT(*) from
> > > > > {0}",table.TableName);
> > > > > cmd.CommandText = vcommand; // Set command
> > > > >
> > > > > int count = (int)cmd.ExecuteScalar(); // Do it - create a table
> > > > > if(count != table.Rows.Count)
> > > > > {
> > > > > Console.WriteLine("We did not store Expected number of records");
> > > > > Console.WriteLine("Expected was {0}", table.Rows.Count);
> > > > > Console.WriteLine("Actual was {0}",count);
> > > > > throw new Exception("Error in Verification");
> > > > > }
> > > > > #endregion
> > > > > Console.WriteLine("DONE WITH VERIFICATION");
> > > > >
> > > > > }
> > > > >
> > > > > con.Close(); // Close connection.
> > > > > }
> > > > >
> > > > >
> > > >
> > > >
> > > >
>

Ilya is right.
The code is not meant to be a one size fit all but more or less something
to serve as a baseline for you to build your own implementation.
It would only work in a case where all the rows in the constituent tables
of the dataset were newly created and all the update had to do is to insert
all new rows, Obviously this is not the situation you are facing and you
would have to generate your own update logic.
See the sample chapter from the book ADO.NET core reference for some
helpful pointers on how to do this
http://www.microsoft.com/mspress/boo...pchap/5354.asp

HTH,
Mark

found out what the issue was.

I am returning a dataset from a webservice, adding a few columns to this
dataset. As soon as I do that, the rowstate changes to modified. When I call
da.Update, it is running the update command, and because the data isn't in
the database yet, it is throwing an exception.

Simple solution (because this is all I am using the database for -
persisting the dataset) was to set the UpdateCommand on the DataAdapter to
the insert command.

Appears to work fine, and works a lot quicker than writing the dataset to
xml (which is the original reason why i have had to go down the path of sql
ce)

thanks for your help, it certainly pushed me in the right direction.



"Mark Ihimoyan [MSFT]" wrote:

> Ilya is right.
> The code is not meant to be a one size fit all but more or less something
> to serve as a baseline for you to build your own implementation.
> It would only work in a case where all the rows in the constituent tables
> of the dataset were newly created and all the update had to do is to insert
> all new rows, Obviously this is not the situation you are facing and you
> would have to generate your own update logic.
> See the sample chapter from the book ADO.NET core reference for some
> helpful pointers on how to do this
> http://www.microsoft.com/mspress/boo...pchap/5354.asp
>
> HTH,
> Mark
>
>