This project is read-only.

Unable to delete anything when using SQLCe

Topics: Business Logic Layer
Dec 19, 2010 at 10:36 AM
Edited Dec 19, 2010 at 11:52 AM

Whenever I try to delete any type of object like a Category or a Post I get this error:

"There was an error parsing the query. [ Token line number = 1,Token line offset = 55,Token in error = DELETE ]"

Deleting users works. My guess is that has something to do with multiple statements. The DeleteUser(...) method runs only one statement (DELETE FROM {0}Users WHERE userName = {1}name) whereas deleting a category or a post run multiple statements (DELETE FROM {0}PostTag WHERE PostID = {1}id;DELETE FROM {0}PostCategory WHERE PostID = {1}id;...)

I have not checked this, but my assumption is that SQLCe does not support multiple statements, so a solution would be using only one statement per SqlCeCommand. But if this change is made in the DbBlogProvider.cs it would be under performing with all the other data storage options.

Edit: I did a quick test and I guess I am right. I changed the DeleteCategory(...) method to execute one statement at the time and it worked.

Dec 19, 2010 at 5:08 PM

I have noticed a delete issue with SQL_CE also.  I cannot delete a user that had a role assigned without first unchecking the role, if I try to delete a user without unchecking the role, it tells me the user is deleted but after refreshing the user is still there, one must first edit the profile, uncheck the role, enter a display name to save and then delete.

Dec 19, 2010 at 9:06 PM

Pedro & Jerry, thanks for the reports.  BE fixes these two problems.

For the multiple SQL statement problem, it appears the only 2 times this was happening were the 2 instances you brought up Pedro -- deleting Categories and deleting Posts.  Those SQL statements have been broken up into separate SQL statements.  Glad we caught this since users will want to of course be able to delete Posts!

The user/role deletion was a separate issue, but this is fixed too.  You no longer need to remove the user from the roles before deleting them.  You can just delete the User and they will automatically be taken out of the roles.