Deleting from sqlite database using IN

Question

I am deleting from an sqlite database using the ids of the records like this (the dirID is an array of the IDs):

Dim i As Integer = 0
        Dim conn As New SQLiteConnection("Data Source=" & DBPath)

        Dim cmd As New SQLiteCommand("DELETE FROM directory WHERE id IN (@ID)", conn)
        cmd.Parameters.AddWithValue("@ID", Join(dirID, ","))
        'conn.SetPassword(dbPassword)
        conn.Open()
        Try

            mytransaction = conn.BeginTransaction()
            '// delete directory //
            If dirID IsNot Nothing Then
                cmd.ExecuteNonQuery()
            End If

            mytransaction.Commit()
            conn.Close()

        Catch ex As Exception
            mytransaction.Rollback()
            strLastError = ex.Message
            Debug.Print(strLastError)
        Finally
            cmd.Dispose()
            conn.Dispose()
        End Try

The problem is that it doesn't always delete from the database, and its not throwing any errors.

Could there be a better way of deleting?

Answer 1

That's not how parameters work.

If your IN list is 1, 2, 3, the command is trying to delete the record where ID equals "1, 2, 3", which is none. So no error is thrown and no record is deleted, because none was found. Thus, you will also find out that your code only works when your list contains 1 item.

Solution: You have to build your delete query (string manipulation), instead of working with parameters. Just beware of SQL Injection.

Update

From your code, it would be something like that:

Dim delcmd = "DELETE FROM directory WHERE id IN (" + Join(dirID, ",") + ")"
Dim cmd As New SQLiteCommand(delcmd, conn)

And no parameter call. Beware: I just tweaked your code, but it is not SQL Injection safe. You should read about that and about what's been written on the subject here in StackOverflow.