c# - ASP.NET 中的 Gridview 编辑删除和更新

Question

我尝试实现 gridview 的编辑和更新按钮的代码，但它似乎不适合我。添加按钮运行良好，但删除和更新不起作用。在运行时错误期间，错误为“MySql.Data.dll 中发生了类型‘MySql.Data.MySqlClient.MySqlException’的异常，但未在用户代码中处理附加信息:“where 子句”中的未知列“p001””

注意:数据库中P_Id的类型为varchar(10)，名称varch(100)，级别varchar，值varchar

公共(public)分部类 ManagePractice :System.Web.UI.Page{

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindData();
    }
}
protected void LinkButton1_Click(object sender, EventArgs e)
{
    TextBox txtID = (TextBox)GridView1.FooterRow.FindControl("txtID");
    TextBox txtSubject = (TextBox)GridView1.FooterRow.FindControl("txtSubject");
    RadioButtonList Level = (RadioButtonList)GridView1.FooterRow.FindControl("RadioButtonList2");
    RadioButtonList PType = (RadioButtonList)GridView1.FooterRow.FindControl("RadioButtonList1");
    AddPractice(txtID.Text.Trim(), txtSubject.Text.Trim(), Level.Text.Trim(), PType.Text.Trim());
    BindData();
}
private void AddPractice(string P_Id, string subject, string level, string type)
{
    string connStr = @"Data Source=localhost;Database=ahsschema;User Id=webuser;Password=webuser2014";

    using (MySqlConnection cn = new MySqlConnection(connStr))
    {
        string query = "insert into practice(P_Id,name,level,value) values ('" + P_Id + "','" + subject + "','" + level + "','" + type + "')";
        MySqlCommand cmd = new MySqlCommand(query, cn);
        cn.Open();
        cmd.ExecuteNonQuery();
        cn.Close();

    }
}
private void BindData()
{
    DataTable dt = new DataTable();
   string connStr = @"Data Source=localhost;Database=ahsschema;User Id=webuser;Password=webuser2014";
    using (MySqlConnection cn = new MySqlConnection(connStr))
    {
        MySqlDataAdapter adp = new MySqlDataAdapter("select P_Id,level,name,value from practice", cn);
        adp.Fill(dt);

    }
    if (dt.Rows.Count > 0)
    {
        GridView1.DataSource = dt;
        GridView1.DataBind();

    }
}

protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
    GridView1.EditIndex = -1;
    BindData();
}

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
    string id = (GridView1.DataKeys[e.RowIndex].Value.ToString ());
    DeletePractice(id);
    BindData();


}

protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
    GridView1.EditIndex = e.NewEditIndex;
    BindData();
}

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
  //  int id = int.Parse(GridView1.DataKeys[e.RowIndex].Value.ToString());
    TextBox txtID = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtID");
    TextBox txtSubject = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtSubject");
   // TextBox Level1 = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtlevel");
   // TextBox PType1 = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtPType1");
     RadioButtonList Level = (RadioButtonList)GridView1.Rows[e.RowIndex].FindControl("RadioButtonList2");
     RadioButtonList PType = (RadioButtonList)GridView1.Rows[e.RowIndex].FindControl("RadioButtonList1");

    UpdatePractice( txtID.Text , txtSubject.Text, Level.Text, PType.Text);
    GridView1.EditIndex = -1;
    BindData();

}
private void UpdatePractice( string P_Id, string name, string level, string value)
{
     string connStr = @"Data Source=localhost;Database=ahsschema;User Id=webuser;Password=webuser2014";
    using (MySqlConnection cn = new MySqlConnection(connStr))
    {
        string query = "UPDATE practice SET P_Id='" + P_Id + "',name='" + name + "',level='" + level + "',value='" + value + " WHERE P_Id=" + P_Id + "";
        MySqlCommand cmd = new MySqlCommand(query, cn);
        cn.Open();
        cmd.ExecuteNonQuery();
        cn.Close();
    }
}
private void DeletePractice(string id)
{
   string connStr = @"Data Source=localhost;Database=ahsschema;User Id=webuser;Password=webuser2014";
    using (MySqlConnection cn = new MySqlConnection(connStr))
    {
        string query = "DELETE FROM practice WHERE P_Id=" + id + "";
        MySqlCommand cmd = new MySqlCommand(query, cn);
        cn.Open();
        cmd.ExecuteNonQuery();
        cn.Close();
    }
}

Answer 1

第一眼:

由于 P_Id 列具有 VARCHAR(10) 数据类型，我发现您忘记在 UPDATE 和 DELETE 子句中包含一些额外的撇号。 SQL 语句的正确形式应该是这样(注意 P_Id 列周围的附加撇号):

string query = "UPDATE practice SET P_Id='" + P_Id + "',name='" + name + "',level='" + level + "',value='" + value + "' WHERE P_Id= '" + P_Id + "'";

和

string query = "DELETE FROM practice WHERE P_Id='" + id + "'";

只有字符串值需要用撇号括起来，数字则不需要。