SQL Server CE query fails to execute in c#, but works in DBMS

Question

I am trying to run a bunch of SQL statements in sql query. The query and the code look as follows:

    private void cancelTestCaseCreationWizard()
    {
        string deleteTestCaseCmd = @"
                                    DELETE FROM column_correlation WHERE test_id IN (SELECT test_id FROM test WHERE test_case_id = @tcid); 
                                    DELETE FROM data_range WHERE test_id IN (SELECT test_id FROM test WHERE test_case_id = @tcid); 
                                    DELETE FROM permitted_values WHERE test_id IN (SELECT test_id FROM test WHERE test_case_id = @tcid); 
                                    DELETE FROM test WHERE test_case_id = @tcid; 
                                    DELETE FROM parameter WHERE parameter_set_id IN (SELECT parameter_set_id from parameter_set WHERE test_case_id = @tcid); 
                                    DELETE FROM parameter_set WHERE test_case_id = @tcid; 
                                    DELETE FROM test_case WHERE test_case_id = @tcid;";
        List<SqlCeParameter> param = new List<SqlCeParameter>();
        SqlCeParameter tcid = new SqlCeParameter("@tcid", SqlDbType.Int);
        tcid.Value = reservedTestCaseId;
        param.Add(tcid);
        DatabaseInteraction.DMLQuery(deleteTestCaseCmd, param);
        Dispose();
    }


    public static int DMLQuery(string query, List<SqlCeParameter> parameters)
    {
        int rowsAffected = -1;
        try
        {
            DBConn.Open();
            var tran = DBConn.BeginTransaction();
            SqlCeCommand comm = new SqlCeCommand(query, DBConn, tran);
            foreach (SqlCeParameter par in parameters)
            {
                comm.Parameters.Add(par);
            }
            comm.ExecuteNonQuery();
            tran.Commit();
        }
        catch (SqlCeException e)
        {
            MessageBox.Show("Error executing DML query \n" + e.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            return -2;
        }
        DBConn.Close();
        return rowsAffected;
    }

After the execution of the above code I get the message:

Error executing DML query 
There was an error parsing the query. [ Token line number = 3,Token line offset = 41,Token in error = DELETE ]

I looked up the value of @tcid and it is correct. The query itself works okay in DBMS. What might cause this error?

Corrected. Still, that did not solve the problem, the exception remains the same. — kyooryu, May 20 '13 at 21:33
Check out [this](http://stackoverflow.com/a/682775/468973) answer. — Magnus, May 20 '13 at 21:48

score 2 · Accepted Answer · answered May 20 '13 at 21:43

SQL Server Compact does not support batch query execution. You have to run the each statement seperately. You can reuse the SqlCeCommand object to execute more than one query though.

http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/d6f1db96-8724-4376-990e-3f6da18c2d08/

SQL Server CE query fails to execute in c#, but works in DBMS

1 Answers1