使用listview从数据库中的表中删除一行

I want to know how to delete a row from a table in the database using listview and button.

我想知道如何使用listview和按钮从数据库中的表中删除一行。

My code

我的代码

private void deleteitems()
{
    //DELETE FROM Tbl_Cashier WHERE RecpieId = @RecpieId AND IngredientId = @IngredientId
    string query = "delete from Tbl_Cashier where Cashier_ID = '" + listView1.SelectedIndices+"' ";

    using (SqlConnection connection = new SqlConnection(connectionString1))
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        connection.Open();
        command.Parameters.Remove(listView1.SelectedIndices);

        command.ExecuteNonQuery();
        connection.Close();
    }

    MessageBox.Show("You will see the new data with your next restart of the application", "INFO", MessageBoxButtons.OK, MessageBoxIcon.Information);
}

I am getting this error:

我收到此错误：

An unhandled exception of type 'System.InvalidCastException' occurred in System.Data.dll

System.Data.dll中发生了未处理的“System.InvalidCastException”类型异常

Additional information: The SqlParameterCollection only accepts non-null SqlParameter type objects, not SelectedIndexCollection objects.

附加信息：SqlParameterCollection仅接受非null的SqlParameter类型对象，而不接受SelectedIndexCollection对象。

2 个解决方案

#1

When you mentioned "a Row", we can assume that you only wish to delete one row from your database table.

当您提到“一行”时，我们可以假设您只希望从数据库表中删除一行。

Another important assumption is that the first column of your listview contains the Cashier_ID of your database table.

另一个重要的假设是listview的第一列包含数据库表的Cashier_ID。

Thus, here's what you need to do:

因此，这是你需要做的：

private void DeleteSelectedItemFromListView() {

        var cashierId = listView1.FocusedItem.Text;

        string query = "delete from Tbl_Cashier where Cashier_ID=@id;";

        using (SqlConnection con = new SqlConnection(connectionString1)) {

            try {

                con.Open();

                using (SqlTransaction trans = con.BeginTransaction()) {

                    using (SqlCommand com = new SqlCommand(query, con, trans)) {

                        com.Parameters.AddWithValue("id", cashierId);

                        var should_be_one = com.ExecuteNonQuery();

                        if (should_be_one == 1) {

                            trans.Commit();

                        } else {

                            trans.Rollback();

                            throw new Exception("An attempt to delete multiple rows was detected");
                        }

                    }

                }


            } catch (Exception ex) {
                MessageBox.Show(ex.Message);
            } finally {
                con.Close();
            }

        }

    }

However, if you wish to delete several items from your listview (multiple selected items), that's a different story.

但是，如果您想从列表视图中删除多个项目（多个选定项目），那就是另一个故事。

#2

You should remove this line:
command.Parameters.Remove(listView1.SelectedIndicies)

你应该删除这一行：command.Parameters.Remove（listView1.SelectedIndicies）

And then you should loop over the selection-indicies and fire a query for each selected item, like this:

然后你应该遍历选择指标并为每个选定的项目激发一个查询，如下所示：

private void deleteitems()
{    
    using (SqlConnection connection = new SqlConnection(connectionString1))
    {
        connection.Open();
        foreach(var index in listView1.SelectedIndicies)
        {
            // Modify this to get the 'cashier_id' from you listView at the specified row index...
            // You should also consider using a prepared query...
            string query = "delete from Tbl_Cashier where Cashier_ID = '" + index +"' ";
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                // consider checking the return value here if the delete command was successful
                command.ExecuteNonQuery();
            }
        }
    }
    MessageBox.Show("You will see the new data with your next restart of the application", "INFO", MessageBoxButtons.OK, MessageBoxIcon.Information);
}

** not tested **

**未经测试**

#1