C# SQLite delete does not work

Question

I have this helper function:

public bool Delete(String tableName, String where)
    {
        Boolean returnCode = true;
        try
        {
            this.ExecuteNonQuery(String.Format("delete from {0} where {1};", tableName, where));                
        }
        catch (Exception fail)
        {
            MessageBox.Show(fail.Message);
            returnCode = false;
        }
        return returnCode;
    }

TableName contains "[MyTable]" and where contains "[MyTable ID]='4ffbd580-b17d-4731-b162-ede8d698e026'" which is a unique guid representing the row ID.

The function returns true, like it was successful, and no exception, but the rows are not deleted from DB, what's wong?

This is the ExecuteNonQuery function

 public int ExecuteNonQuery(string sql)
    {
        SQLiteConnection cnn = new SQLiteConnection(dbConnection);
        cnn.Open();
        SQLiteCommand mycommand = new SQLiteCommand(cnn);
        mycommand.CommandText = sql;
        int rowsUpdated = mycommand.ExecuteNonQuery();
        cnn.Close();
        return rowsUpdated;
    }

Jon Skeet · Accepted Answer · 2013-02-06 20:14:43Z

7

Firstly, you shouldn't just embed SQL like that. You should use parameterized SQL, to avoid SQL injection attacks.

Next, you should look at the return value of ExecuteNonQuery. Assuming it follows the normal pattern of ExecuteNonQuery, it should return the number of rows affected. I suspect it's returning 0, which will mean that nothing's matching your where clause.

(I'd also stop catching Exception - probably stop catching anything, but at least catch something specific if you must. I'd get rid of the local variable, too, and just return directly when you know what you want to return...)

answered Feb 6, 2013 at 20:14

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

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

10 Comments

Mario Over a year ago

I don't use SQLite on a webserver, this is a Windows application

Mario Over a year ago

Yes the ExecuteNonQuery returns 0 as the rows affected, by why? The ID of the row is a GUID type, not string

tomfanning Over a year ago

Also, at least break out the (horrible) concatenation of the SQL query into its own variable so you can inspect the query before sending it to the database. You can paste the same query into any SQLite database browser (the Firefox add-on is quite good). This should make it obvious what's going on.

Mario Over a year ago

I have pasted the query in SQLite Database browser 2.0 and executed it. The return message is No Error and again no row was deleted.

Mario Over a year ago

It seems that it cannot find the guid type column from my string

|

Collectives™ on Stack Overflow

C# SQLite delete does not work

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related