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
– kyooryu, Commented May 20, 2013 at 21:33

Chris · Accepted Answer · 2013-05-20 21:43:45Z

2

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/

answered May 20, 2013 at 21:43

Chris

8,65710 gold badges36 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related