c# - Why is my SQL Server CE code failing? -

in windowsce / compact framework (.net1.1) project, need create new table in code. thought way:

if (! tableexists("table42")) {     createtable42(); }  public static bool tableexists(string tablename) {     try     {         using (sqlceconnection sqlconn = new sqlceconnection(@"data source=\my documents\platypus.sdf"))         {             sqlconn.open();             string qrystr = "select count(*) information_schema.tables table_name = ?";             sqlcecommand cmd = new sqlcecommand(qrystr, sqlconn);             cmd.parameters[0].value = tablename;             cmd.commandtype = commandtype.text;             int retcount = (int)cmd.executescalar();             return retcount > 0;         }     }     catch (exception ex)     {         messagebox.show("tableexists ex.message == " + ex.message);         messagebox.show("tableexists ex.tostring() == " + ex.tostring());         messagebox.show("tableexists ex.getbaseexception() == " + ex.getbaseexception());         return false;     } } 

...but call tableexists() fails; , shows me:

tableexists ex.message == tableexists ex.tostring() == system.data.sqlserverce.sqlceexception @ system.data.sqlserverce.sqlconnection.processresults(int32 hr) @ ...at open(boolean silent) ... tableexists ex.getbaseexception() == [same ex.tostring() above] 

"int32 hr" ... ??? hec ramsey that?

as documented in these environs, can't step through projct, rely on calls messagebox.show().

the rest of related code, if may of interest, is:

public static void createtable42() {     try     {         using (sqlceconnection con = new sqlceconnection(@"data source=\my documents\platypus.sdf"))         {             con.open();             using (sqlcecommand com =  new sqlcecommand(   "create table table42 (setting_id int identity not null primary key,  setting_name varchar(40) not null, setting_value(63) varchar not null)", con))                 {                     com.executenonquery();                     writesettingsval("table42settingname","table42settingval");                 }             }         }         catch (exception ex)         {             messagebox.show("createtable42 " + ex.message);         }     }  public static void writesettingsval(string settingname, string settingval) {     using (sqlceconnection sqlconn = new sqlceconnection(@"data source=\my documents\platypus.sdf"))     {         sqlconn.open();         string dmlstr = "insert tabld42 (setting_name, setting_value) values(?, ?)";         sqlcecommand cmd = new sqlcecommand(dmlstr, sqlconn);         cmd.commandtype = commandtype.text;          cmd.parameters[0].value = settingname;         cmd.parameters[1].value = settingval;         try         {             cmd.executenonquery();         }         catch (exception ex)         {             messagebox.show("writesettingsval " + ex.message);         }     } } 

update

answer brad rem's comment:

i don't think it's necessary encase param in quotes, other working code like:

cmd.parameters.add("@account_id", dept.accountid); 

-and:

cmd.parameters[0].value = dept.accountid; 

(it 1 way first time when in loop, , other way thereafter (don't ask me why).

anyway, grins, did change tableexists() parameter code this:

cmd.parameters[0].value = tablename; 

...to this:

cmd.parameters.add("@table_name", tablename); 

...but still exact same result.

update 2

here (http://msdn.microsoft.com/en-us/library/aa237891(v=sql.80).aspx) found this: "caution must specify sql server ce provider string when open sql server ce database."

they give example:

cn.connectionstring = "provider=microsoft.sqlserver.oledb.ce.2.0; data source=\northwind.sdf" 

i'm not doing that; conn str is:

using (sqlceconnection sqlconn = new sqlceconnection(@"data source=\my documents\ccrdb.sdf")) 

could problem?

update 3

i took gent's advice (http://www.codeproject.com/answers/629613/why-is-my-sqlserver-ce-code-failing?cmt=487657#answer1) , added catch sqlceexcpetions now:

public static bool tableexists(string tablename) {     try     {         using (sqlceconnection sqlconn = new sqlceconnection(@"data source=\my documents\ccrdb.sdf"))         {             sqlconn.open();             string qrystr = "select count(*) information_schema.tables table_name = @table_name";             sqlcecommand cmd = new sqlcecommand(qrystr, sqlconn);             cmd.parameters.add("@table_name", tablename);             cmd.commandtype = commandtype.text;             int retcount = (int)cmd.executescalar();             return retcount > 0;         }     }     catch (sqlceexception sqlceex)     {         messagebox.show("tableexists sqlceex.message == " + sqlceex.message);         messagebox.show("tableexists sqlceex.tostring() == " + sqlceex.tostring());         return false;         . . . 

the sqlceexception message is: "there file sharing violation. different process might using file [,,,,,]" "...processresults ... open ... getinstance ..."

update 4

trying use ctacke's sample code, but: transaction absolutely necessary? had change code following scenario/milieu, , don't know transaction should or how build it:

public static bool tableexists(string tablename) {     string sql = string.format("select count(*) information_schema.tables table_name = '{0}'", tablename);     try     {         using (sqlceconnection sqlconn = new sqlceconnection(@"data source=\my documents\hhsdb.sdf"))         {             sqlcecommand command = new sqlcecommand(sql, sqlconn);             //command.transaction = currenttransaction sqlcetransaction;             command.connection = sqlconn;             command.commandtext = sql;             int count = convert.toint32(command.executescalar());             return (count > 0);         }     }     catch (sqlceexception sqlceex)     {         messagebox.show("tableexists sqlceex.message == " + sqlceex.message);         return false;     } } 

update 5

with code, err msg is, "an err msg available exception cannot displayed because these messages optional , not insallted on device. please install ... netcfv35.messages.en.cab"

update 6

all typically, legacy, ancient-technology project giving me headaches. seems 1 connection allowed open @ time, , app opens 1 outset; so, have use connection. however, dbconnection, not sqlceconnection, can't use code:

using (sqlcecommand com =  new sqlcecommand(        "create table hhs_settings (setting_id int identity (1,1) primary key,  setting_name varchar(40) not null, setting_value(63) varchar not null)", frmcentral.dbconn)) {     com.executenonquery();     writesettingsval("beltprinter", "zebraql220"); } 

...because already-open connection type passed arg sqlcecommand constructor dbcommand, not expected/required sqlceconneection.

the tentacles of code far wide , entrenched rip out roots , refactor make more sensible: single tentative step in foothills causes raging avalanche on everest.

for fun i'd try 2 things. first, replace '?' parameter named parameter '@tablename' , see if changes things. yes, know '?' should work, it's confusing, ugly precedent , maybe since it's system table it's wonky. yes, it's stretch, worth try know.

the second thing i'd method sqlce implementation of opennetcf orm:

    public override bool tableexists(string tablename)     {         var connection = getconnection(true);         try         {             using (var command = getnewcommandobject())             {                 command.transaction = currenttransaction sqlcetransaction;                 command.connection = connection;                 var sql = string.format("select count(*) information_schema.tables table_name = '{0}'", tablename);                 command.commandtext = sql;                 var count = convert.toint32(command.executescalar());                  return (count > 0);             }         }                 {             donewithconnection(connection, true);         }     } 

note didn't bother parameterizing, largely because doubt provide perf benefit (queue hordes whining sql injection). way works - we've got deployed , in use in many live solutions.

edit

for completeness (though i'm not sure adds clarity).

    protected virtual idbconnection getconnection(bool maintenance)     {         switch (connectionbehavior)         {             case connectionbehavior.alwaysnew:                 var connection = getnewconnectionobject();                 connection.open();                 return connection;             case connectionbehavior.holdmaintenance:                 if (m_connection == null)                 {                     m_connection = getnewconnectionobject();                     m_connection.open();                 }                 if (maintenance) return m_connection;                 var connection2 = getnewconnectionobject();                 connection2.open();                 return connection2;             case connectionbehavior.persistent:                 if (m_connection == null)                 {                     m_connection = getnewconnectionobject();                     m_connection.open();                 }                 return m_connection;             default:                 throw new notsupportedexception();         }     }      protected virtual void donewithconnection(idbconnection connection, bool maintenance)     {         switch (connectionbehavior)         {             case connectionbehavior.alwaysnew:                 connection.close();                 connection.dispose();                 break;             case connectionbehavior.holdmaintenance:                 if (maintenance) return;                 connection.close();                 connection.dispose();                 break;             case connectionbehavior.persistent:                 return;             default:                 throw new notsupportedexception();         }     }