Bizarre problem with prepared statement in SQL CE

[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");
}
}
}

 

[Ilya Tumanov [MS]]

Jon,

This is a known problem and it will be fixed in upcoming SQL CE release.

As a workaround, call Prepare() after parameters are set or remove call to
Prepare() as ExecuteNonQuery() will call it anyway if not prepared.

Best regards,

Ilya

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

From: Jon Skeet [C# MVP] <[email protected]>
Subject: Bizarre problem with prepared statement in SQL CE
Date: Fri, 13 Feb 2004 09:58:59 -0000
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
User-Agent: MicroPlanet-Gravity/2.70.2061
Newsgroups: microsoft.public.dotnet.framework.compactframework
NNTP-Posting-Host: 81-178-16-65.dsl.pipex.com 81.178.16.65
Lines: 1
Path: cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.
phx.gbl
Xref: cpmsftngxa07.phx.gbl microsoft.public.dotnet.framework.compactframework:45649
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

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");
}
}
}

 

[Jon Skeet [C# MVP]]

This is a known problem and it will be fixed in upcoming SQL CE release.

As a workaround, call Prepare() after parameters are set or remove call to
Prepare() as ExecuteNonQuery() will call it anyway if not prepared.

Ah, so I won't lose any performance this way? That's great - thanks
very much!