sqlcecommand using sqlceparameters error

  • Thread starter =?ISO-8859-1?Q?S=E9rgio_Almeida?=
  • Start date
?

=?ISO-8859-1?Q?S=E9rgio_Almeida?=

Greetings

I'm having an error on my application when trying to insert data on my
SQL CE database.

I have a method on my data object that constructs a sqlce command. here
is the code

public SqlCeCommand buildInsertCommand()
{
string sqlStatement="INSERT INTO categorias ( ";
sqlStatement+="categoria, ";
sqlStatement+="oid) ";
sqlStatement+="VALUES (";
sqlStatement+="@categoria, ";
sqlStatement+="@oid);";

SqlCeCommand theCommand=new SqlCeCommand();
theCommand.CommandText=sqlStatement;

theCommand.Parameters.Add(new SqlCeParameter("@categoria",
System.Data.SqlDbType.NVarChar, 64, "categoria"));
theCommand.Parameters.Add(new SqlCeParameter("@oid",
System.Data.SqlDbType.BigInt, 8 ,"oid"));

theCommand.Parameters["@categoria"].Value=this.Categoria;
theCommand.Parameters["@oid"].Value=this.OID;

return theCommand;
}

this method works fine (no exceptions are thrown)

this get bad when I execute the following method

void DInsertCategoria(ObjCategoria theCategoriaObj)
{

string connString=ManageDB.connectStringBuild();
SqlCeConnection myConn=new SqlCeConnection(connString);

SqlCeCommand myComm=null;

try
{
myConn.Open();

theCategoriaObj.OID=ManageDB.getUpdateNextOID(myConn);
myComm=theCategoriaObj.buildInsertCommand();
myComm.Connection=myConn;

myComm.ExecuteNonQuery(); // <<-- ERROR OCCURS HERE
}
catch(SqlCeException ex)
{
MessageBox.Show(ManageDB.showSqlCeErrors(ex));
}
finally
{
if(myConn.State!=ConnectionState.Closed)
{
myConn.Close();
}
}
}

I get the following error!

"SqlCeException: There was an error parsing the query. [Token line
number,Token line offset,,Token in error,,]
SqlCeError: There was an error parsing the query. [Token line
number,Token line offset,,Token in error,,]
Source: Microsoft SQL Server 2000 Windows CE Edition
Native Error: 25501
HResult: 80040e14
Parameter=1
Parameter=50
Parameter=0
Parameter=@categoria
Parameter=
Parameter="

any ideas about this error?

And by the way, is this the best practice (use of parameters and
commands) to insert data on an sql ce database? I use this because of
strings. Strings can have the chars " and ' and using parameters there's
no need to format them with the \" and \', right?

TIA

Almeida
 
A

Alex Feinman [MVP]

Sergio,

For SQL CE the parameter names in the query string must be substituted with
'?"
string sqlStatement="INSERT INTO categorias (categoria, oid) VALUES (?,
?)";
 
Top