使用 IN 从 sqlite 数据库中删除
我正在使用这样的记录 ID 从 sqlite 数据库中删除 （dirID 是一个 ID 数组）：
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
问题是它并不总是从数据库中删除，并且不会抛出任何错误。
有没有更好的删除方法？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
参数不是这样工作的。
如果您的
IN列表为1, 2, 3，则该命令尝试删除 ID 等于“1, 2, 3”的记录，没有。因此不会引发错误，也不会删除任何记录，因为没有找到任何记录。因此，您还会发现您的代码仅在列表包含 1 项时才有效。解决方案：您必须构建删除查询（字符串操作），而不是使用参数。请注意SQL 注入。
更新
从您的代码来看，它会是这样的：
并且没有参数调用。请注意：我刚刚调整了您的代码，但是它不是 SQL 注入安全的。您应该阅读有关此内容以及 StackOverflow 中有关该主题的文章。
That's not how parameters work.
If your
INlist is1, 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:
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.