SqlCeDataAdapter problem

  • Thread starter Thread starter DM
  • Start date Start date
D

DM

Hello.

I have following problem with SqlCeDataAdapter.

Let's take this code as an example:

....
SqlCeAdapter Adapter = new SqlCeAdapter();
SqlCeCommand command = conn.CreateCommand();

command.CommandText =
"INSERT INTO " +
" table " +
" ( " +
" column, " +
" id
" ) " +
"VALUES " +
" ( " +
" REPLACE ( ?, 'a', '' ), " +
" ? " +
" )";
command.Parameters.Add( new SqlCeParameter("@column", DbType.NChar, 20, "column");
command.Parameters.Add( new SqlCeParameter("@id", DBType.Int, 4, "id" );
Adapter.InsertCommand = command;

After adding a row to a DataTable, when I call:
Adapter.Update( table );
There is a SqlCeException excpetion with MissingParameterException error
(it's because of the replace function)

Wheras the following works just fine:

SqlCeCommand command2 = conn.CreateCommand();

command2.CommandText =
"UPDATE " +
" table " +
"SET " +
" column = REPLACE ( ?, 'a', '' )
"WHERE " +
" id = ? "

command2.Parameters.Add( new SqlCeParameter("@column", DbType.NChar, 20, "column");
command2.Parameters.Add( new SqlCeParameter("@id", DBType.Int, 4, "id" );
Adapter.UpdateCommand = command2;

after making changes to a row:
Adapter.Update(table);
works like charm.

I don't know why the update command works while insert fails.
Is there sth wrong with my query or is it a bug?

Thanks for any suggestions.
 
Well, unless you missed some stuff when retyping it here, your CommandText
for the Insert command is missing a " + after "id"
 
Well.

As may notice there are more syntax errors
is this code, but it is obviously not the point.
 
Well, why don't you set a breakpoint, get the actual text of your Insert and
Update commands and post it here?
 
D M said:
As may notice there are more syntax errors
is this code, but it is obviously not the point.
Click to expand...

How are we meant to know which errors *are* in your real code and which
*aren't* in your real code unless you *post* your real code?

Could you post a short but complete program which demonstrates the
problem?

See http://www.pobox.com/~skeet/csharp/complete.html for details of
what I mean by that.
 
Ok, here comes the code:

if (File.Exists("e.sdf"))
File.Delete("e.sdf");
string connStr = "Data Source = e.sdf; Password = p";

SqlCeEngine engine = new SqlCeEngine(connStr);
engine.CreateDatabase();
engine.Dispose();

SqlCeConnection conn = new SqlCeConnection(connStr);
conn.Open();
SqlCeCommand command = conn.CreateCommand();
command.CommandText = "CREATE TABLE tab (col nchar(40), id integer)";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO tab VALUES ('baca', 1)";
command.ExecuteNonQuery();

SqlCeDataAdapter adapter = new SqlCeDataAdapter();

SqlCeCommand selectCommand = conn.CreateCommand();
selectCommand.CommandText = "SELECT col,id FROM tab";
adapter.SelectCommand = selectCommand;

SqlCeCommand insertCommand = conn.CreateCommand();
insertCommand.CommandText = "INSERT INTO tab (col, id) VALUES( REPLACE (
?, 'a',''), ? )";
//commented out version works fine
//insertCommand.CommandText = "INSERT INTO tab (col, id) VALUES( ? , ?
)";
insertCommand.Parameters.Add( new SqlCeParameter ("@Col",
SqlDbType.NChar, 80, "col" ) );
insertCommand.Parameters.Add( new SqlCeParameter ("@id", SqlDbType.Int,
4, "id" ) );
adapter.InsertCommand = insertCommand;

SqlCeCommand updateCommand = conn.CreateCommand();
updateCommand.CommandText = "UPDATE tab SET col = REPLACE (?, 'a', '')
WHERE id = ? ";
updateCommand.Parameters.Add( new SqlCeParameter ("@Col",
SqlDbType.NChar, 80, "col" ) );
updateCommand.Parameters.Add( new SqlCeParameter ("@id", SqlDbType.Int,
4, "id" ) );
adapter.UpdateCommand = updateCommand;

DataTable table = new DataTable();
adapter.Fill(table);

table.Rows[0]["col"] = "a text";
adapter.Update(table);
//is ok
DataRow newRow = table.NewRow();
newRow["col"] = "another text";
newRow["id"] = 2;
table.Rows.Add(newRow);

adapter.Update(table);
//fails
 
Another try:

using System;
using System.Data;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlServerCe;
using System.Data.Common;

namespace err
{
class Class1
{
static void Main(string[] args)
{
TestIt();
}

static void TestIt()
{
if (File.Exists("e.sdf"))
File.Delete("e.sdf");
string connStr = "Data Source = e.sdf; Password = p";

SqlCeEngine engine = new SqlCeEngine(connStr);
engine.CreateDatabase();
engine.Dispose();

SqlCeConnection conn = new SqlCeConnection(connStr);
conn.Open();
SqlCeCommand command = conn.CreateCommand();
command.CommandText = "CREATE TABLE tab (col nchar(40), id integer)";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO tab VALUES ('baca', 1)";
command.ExecuteNonQuery();

SqlCeDataAdapter adapter = new SqlCeDataAdapter();

SqlCeCommand selectCommand = conn.CreateCommand();
selectCommand.CommandText = "SELECT col,id FROM tab";
adapter.SelectCommand = selectCommand;

SqlCeCommand insertCommand = conn.CreateCommand();
insertCommand.CommandText = "INSERT INTO tab (col, id) VALUES(
REPLACE ( ?, 'a',''), ? )";
//insertCommand.CommandText = "INSERT INTO tab (col, id) VALUES( ? ,
? )";
insertCommand.Parameters.Add( new SqlCeParameter ("@col",
SqlDbType.NChar, 80, "col" ) );
insertCommand.Parameters.Add( new SqlCeParameter ("@id",
SqlDbType.Int, 4, "id" ) );
adapter.InsertCommand = insertCommand;

SqlCeCommand updateCommand = conn.CreateCommand();
updateCommand.CommandText = "UPDATE tab SET col = REPLACE (?, 'a',
'') WHERE id = ? ";
updateCommand.Parameters.Add( new SqlCeParameter ("@col",
SqlDbType.NChar, 80, "col" ) );
updateCommand.Parameters.Add( new SqlCeParameter ("@id",
SqlDbType.Int, 4, "id" ) );
adapter.UpdateCommand = updateCommand;

DataTable table = new DataTable();
adapter.Fill(table);

table.Rows[0]["col"] = "a text";
adapter.Update(table);
//is ok
table.AcceptChanges();
DataRow newRow = table.NewRow();
newRow["col"] = "another text";
newRow["id"] = 2;
table.Rows.Add(newRow);

try
{
adapter.Update(table);
MessageBox.Show("OK!");
}
catch (SqlCeException)
{
MessageBox.Show("Ooops!");
}

}

}
}
 
D M said:
Another try:
Click to expand...

<snip>

Thanks. I can now reproduce the problem. My *guess* (and at the moment
it's nothing more than that) is that it's a bug in the parser which
examines the SQL and puts the parameters in.

I'll experiment a bit more and see if I can get anywhere.
 
Hello.

It's probably for my poor english - I didn't
intend to be sarcastic or "bitiching".

Sorry if it sounded that way.
 
Hello.

It's probably for my poor english - I didn't
intend to be sarcastic or "bitiching".

Sorry if it sounded that way.
 
Ok, I see the problem. I was able to narrow it down to *any* SQL function
used in the insert command. YOu don't need a dataadapter to reproduce it.
Even the following produces the error:

command.Text = "Insert into tab (col, id) Values (TRIM(?), ?)";
/// add parameters and set values
command.ExecuteNonQuery();

Give me a few days (a week) to follow up on this. In the mean time a
workaround would be to calculate the values manually and update your
datatable prior to writing to the database.

--
Alex Feinman
---
Visit http://www.opennetcf.org
D M said:
Another try:

using System;
using System.Data;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlServerCe;
using System.Data.Common;

namespace err
{
class Class1
{
static void Main(string[] args)
{
TestIt();
}

static void TestIt()
{
if (File.Exists("e.sdf"))
File.Delete("e.sdf");
string connStr = "Data Source = e.sdf; Password = p";

SqlCeEngine engine = new SqlCeEngine(connStr);
engine.CreateDatabase();
engine.Dispose();

SqlCeConnection conn = new SqlCeConnection(connStr);
conn.Open();
SqlCeCommand command = conn.CreateCommand();
command.CommandText = "CREATE TABLE tab (col nchar(40), id integer)";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO tab VALUES ('baca', 1)";
command.ExecuteNonQuery();

SqlCeDataAdapter adapter = new SqlCeDataAdapter();

SqlCeCommand selectCommand = conn.CreateCommand();
selectCommand.CommandText = "SELECT col,id FROM tab";
adapter.SelectCommand = selectCommand;

SqlCeCommand insertCommand = conn.CreateCommand();
insertCommand.CommandText = "INSERT INTO tab (col, id) VALUES(
REPLACE ( ?, 'a',''), ? )";
//insertCommand.CommandText = "INSERT INTO tab (col, id) VALUES( ? ,
? )";
insertCommand.Parameters.Add( new SqlCeParameter ("@col",
SqlDbType.NChar, 80, "col" ) );
insertCommand.Parameters.Add( new SqlCeParameter ("@id",
SqlDbType.Int, 4, "id" ) );
adapter.InsertCommand = insertCommand;

SqlCeCommand updateCommand = conn.CreateCommand();
updateCommand.CommandText = "UPDATE tab SET col = REPLACE (?, 'a',
'') WHERE id = ? ";
updateCommand.Parameters.Add( new SqlCeParameter ("@col",
SqlDbType.NChar, 80, "col" ) );
updateCommand.Parameters.Add( new SqlCeParameter ("@id",
SqlDbType.Int, 4, "id" ) );
adapter.UpdateCommand = updateCommand;

DataTable table = new DataTable();
adapter.Fill(table);

table.Rows[0]["col"] = "a text";
adapter.Update(table);
//is ok
table.AcceptChanges();
DataRow newRow = table.NewRow();
newRow["col"] = "another text";
newRow["id"] = 2;
table.Rows.Add(newRow);

try
{
adapter.Update(table);
MessageBox.Show("OK!");
}
catch (SqlCeException)
{
MessageBox.Show("Ooops!");
}

}

}
}
Click to expand...
 
Back
Top