J
Jon Skeet [C# MVP]
I've just encountered a really strange problem when trying to insert
some values into a SQL CE database.
I create a command, set the parameter types, prepare it, set the
parameter values, and then execute it.
The first time the command is executed, the values are blanked out.
Subsequent times the command is executed, the values appear correctly.
Removing the Prepare() call for the command makes everything work fine.
Here's a program which demonstrates the problem. Note that there is no
primary key in the table - changing either that, *or* not preparing the
statement, makes the problem go away. What's most concerning here is
that no error is thrown, or anything like that. Does anyone know why
this is happening?
using System;
using System.Data;
using System.Data.SqlServerCe;
using System.IO;
using System.Drawing;
using System.Collections;
using System.Windows.Forms;
class Test
{
static void Main()
{
string dbName = "\\My Documents\\test.sdf";
string connString = "Data Source="+dbName;
try
{
if (File.Exists(dbName))
{
File.Delete(dbName);
}
// First create the database
using (SqlCeEngine engine = new SqlCeEngine
(connString))
{
engine.CreateDatabase();
}
using (SqlCeConnection conn=new SqlCeConnection
(connString))
{
conn.Open();
// Then build the table
using(SqlCeCommand cmd=conn.CreateCommand())
{
cmd.CommandText =
"CREATE TABLE Foo "+
"(First nvarchar(10), Second nvarchar(10))";
cmd.ExecuteNonQuery();
}
// Then populate it
// Then populate it
using(SqlCeCommand cmd=conn.CreateCommand())
{
cmd.CommandText =
"INSERT INTO Foo (First, Second) VALUES (?, ?)";
cmd.Parameters.Add("First",
SqlDbType.NVarChar);
cmd.Parameters.Add("Second",
SqlDbType.NVarChar);
cmd.Prepare();
cmd.Parameters[0].Value="X";
cmd.Parameters[1].Value="Y";
cmd.ExecuteNonQuery();
cmd.Parameters[0].Value="1";
cmd.Parameters[1].Value="2";
cmd.ExecuteNonQuery();
}
}
MessageBox.Show
("Test database created and populated");
}
catch(Exception e)
{
MessageBox.Show(e.Message, "Exception");
}
}
}
some values into a SQL CE database.
I create a command, set the parameter types, prepare it, set the
parameter values, and then execute it.
The first time the command is executed, the values are blanked out.
Subsequent times the command is executed, the values appear correctly.
Removing the Prepare() call for the command makes everything work fine.
Here's a program which demonstrates the problem. Note that there is no
primary key in the table - changing either that, *or* not preparing the
statement, makes the problem go away. What's most concerning here is
that no error is thrown, or anything like that. Does anyone know why
this is happening?
using System;
using System.Data;
using System.Data.SqlServerCe;
using System.IO;
using System.Drawing;
using System.Collections;
using System.Windows.Forms;
class Test
{
static void Main()
{
string dbName = "\\My Documents\\test.sdf";
string connString = "Data Source="+dbName;
try
{
if (File.Exists(dbName))
{
File.Delete(dbName);
}
// First create the database
using (SqlCeEngine engine = new SqlCeEngine
(connString))
{
engine.CreateDatabase();
}
using (SqlCeConnection conn=new SqlCeConnection
(connString))
{
conn.Open();
// Then build the table
using(SqlCeCommand cmd=conn.CreateCommand())
{
cmd.CommandText =
"CREATE TABLE Foo "+
"(First nvarchar(10), Second nvarchar(10))";
cmd.ExecuteNonQuery();
}
// Then populate it
// Then populate it
using(SqlCeCommand cmd=conn.CreateCommand())
{
cmd.CommandText =
"INSERT INTO Foo (First, Second) VALUES (?, ?)";
cmd.Parameters.Add("First",
SqlDbType.NVarChar);
cmd.Parameters.Add("Second",
SqlDbType.NVarChar);
cmd.Prepare();
cmd.Parameters[0].Value="X";
cmd.Parameters[1].Value="Y";
cmd.ExecuteNonQuery();
cmd.Parameters[0].Value="1";
cmd.Parameters[1].Value="2";
cmd.ExecuteNonQuery();
}
}
MessageBox.Show
("Test database created and populated");
}
catch(Exception e)
{
MessageBox.Show(e.Message, "Exception");
}
}
}