C#操作MySQL多次删除行时触发‘@titleID已定义’错误

你遇到的错误是因为类级别的sqlCmd对象被重复使用，第一次删除时添加的@titleID参数会一直保留在它的参数集合中，第二次执行删除操作时再次调用AddWithValue，就会触发“参数已定义”的错误。

有三种简单的修复方式，任选其一即可：

方式1：每次执行前清空参数集合 #

在添加参数前，先清空sqlCmd的参数列表：

private void btnDelete_Click(object sender, EventArgs e)
{
    try
    {
        sqlConn.ConnectionString = "server=" + server + ";"
            + "user =" + user + ";"
            + "password =" + password + ";"
            + "database =" + database;

        sqlConn.Open();

        sqlCmd.CommandText = "delete from games.games where titleID = @titleID";
        
        // 新增：清空已有参数
        sqlCmd.Parameters.Clear();
        
        sqlCmd.Parameters.AddWithValue("@titleID", txtID.Text);
        
        int rowsAffected = sqlCmd.ExecuteNonQuery();

        if (rowsAffected > 0)
        {
            MessageBox.Show("Record deleted successfully.", "Delete Record", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
        else
        {
            MessageBox.Show("No matching record found for deletion.", "Delete Record", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
              
        sqlConn.Close();
        btnReset_Click(sender, e);

        foreach (DataGridViewRow item in this.dataGridView1.SelectedRows)
        {
            dataGridView1.Rows.RemoveAt(item.Index);
        }
        
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

方式2：每次创建新的MySqlCommand实例 #

不再复用类级别的sqlCmd，而是每次删除时新建一个命令对象，避免参数残留：

private void btnDelete_Click(object sender, EventArgs e)
{
    try
    {
        sqlConn.ConnectionString = "server=" + server + ";"
            + "user =" + user + ";"
            + "password =" + password + ";"
            + "database =" + database;

        sqlConn.Open();

        // 新建MySqlCommand实例，而非复用类成员
        using (MySqlCommand deleteCmd = new MySqlCommand("delete from games.games where titleID = @titleID", sqlConn))
        {
            deleteCmd.Parameters.AddWithValue("@titleID", txtID.Text);
            int rowsAffected = deleteCmd.ExecuteNonQuery();

            if (rowsAffected > 0)
            {
                MessageBox.Show("Record deleted successfully.", "Delete Record", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
            {
                MessageBox.Show("No matching record found for deletion.", "Delete Record", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
              
        sqlConn.Close();
        btnReset_Click(sender, e);

        foreach (DataGridViewRow item in this.dataGridView1.SelectedRows)
        {
            dataGridView1.Rows.RemoveAt(item.Index);
        }
        
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

方式3：检查参数是否存在，存在则更新值 #

如果想保留复用sqlCmd，可以先检查参数是否存在，存在就更新值，不存在再添加：

private void btnDelete_Click(object sender, EventArgs e)
{
    try
    {
        sqlConn.ConnectionString = "server=" + server + ";"
            + "user =" + user + ";"
            + "password =" + password + ";"
            + "database =" + database;

        sqlConn.Open();

        sqlCmd.CommandText = "delete from games.games where titleID = @titleID";
        
        // 检查参数是否存在，存在则更新值，否则添加
        if (sqlCmd.Parameters.Contains("@titleID"))
        {
            sqlCmd.Parameters["@titleID"].Value = txtID.Text;
        }
        else
        {
            sqlCmd.Parameters.AddWithValue("@titleID", txtID.Text);
        }
        
        int rowsAffected = sqlCmd.ExecuteNonQuery();

        if (rowsAffected > 0)
        {
            MessageBox.Show("Record deleted successfully.", "Delete Record", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
        else
        {
            MessageBox.Show("No matching record found for deletion.", "Delete Record", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
              
        sqlConn.Close();
        btnReset_Click(sender, e);

        foreach (DataGridViewRow item in this.dataGridView1.SelectedRows)
        {
            dataGridView1.Rows.RemoveAt(item.Index);
        }
        
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

你的代码中btnAddNew和btnSearch方法使用了字符串拼接SQL语句，这会导致SQL注入风险，建议统一改用参数化查询，比如新增数据的代码可以修改为：

private void btnAddNew_Click(object sender, EventArgs e)
{
    try
    {
        sqlConn.ConnectionString = "server=" + server + ";"
            + "user =" + user + ";"
            + "password =" + password + ";"
            + "database =" + database;

        sqlConn.Open();
        sqlQuery = "insert into games.games(titleID, title,year,genre,developer,publisher,score) values(@titleID, @title, @year, @genre, @developer, @publisher, @score)";
        
        using (MySqlCommand insertCmd = new MySqlCommand(sqlQuery, sqlConn))
        {
            insertCmd.Parameters.AddWithValue("@titleID", txtID.Text);
            insertCmd.Parameters.AddWithValue("@title", txtTitle.Text);
            insertCmd.Parameters.AddWithValue("@year", txtYear.Text);
            insertCmd.Parameters.AddWithValue("@genre", txtGenre.Text);
            insertCmd.Parameters.AddWithValue("@developer", txtDev.Text);
            insertCmd.Parameters.AddWithValue("@publisher", txtPub.Text);
            insertCmd.Parameters.AddWithValue("@score", cboScore.Text);
            
            insertCmd.ExecuteNonQuery();
        }
        
        sqlConn.Close();
    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
    finally { sqlConn.Close();}
    uploadData();
    barCodeNum();
}

内容的提问来源于stack exchange，提问作者Sitric