checkboxlist储存多个值

Question

public void insertt()
    {
        SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["strCon"].ConnectionString);
        string sql = "insert into clienttd values(@email,@message,getdate())";
        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.Parameters.Add("email", SqlDbType.VarChar, 200).Value = this.TextBox1.Text;
        cmd.Parameters.Add("message", SqlDbType.VarChar, 200);
        for (int i = 0; i < CheckBoxList1.Items.Count; i++)
        {
            if (CheckBoxList1.Items[i].Selected)
            {
                try
                {
                    cmd.Parameters["message"].Value = Server.HtmlEncode(CheckBoxList1.Items[i].Value);
                    cmd.Connection.Open();
                    if (cmd.ExecuteNonQuery() == 1)
                        Response.Redirect("tdcg.aspx");
                    else
                        this.Label1.Text = "添加失败";
                    cmd.Connection.Close();
                }

                catch (Exception ex)
                {
                    this.Label1.Text = ex.Message;
                }
                finally
                {
                    con.Close();
                    cmd.Dispose();
                }

            }
        }
    }

checkboxlist里面有多个选项，现在多选只能储存第一个选取的值。请教如何能够把多个选项存入数据。上面是代码谢谢

Answer 1

你好！

你的写法有如下几个问题：

1 在这个操作过程中连接只需要打开一次，而你可能是多次打开。

2 在第一次操作成功后你调用了 cmd.Dispose 这也是你的问题所在。

using (SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["strCon"].ConnectionString))
{
    string sql = "insert into clienttd values(@email,@message,getdate())";
    SqlCommand cmd = new SqlCommand(sql, con);

    try
    {
        con.Open();

        for (int i = 0; i < CheckBoxList1.Items.Count; i++)
        {
            if (CheckBoxList1.Items[i].Selected)
            {
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@email", this.TextBox1.Text);
                cmd.Parameters.AddWithValue("@message", Server.HtmlEncode(CheckBoxList1.Items[i].Value));
            }
        }

        Response.Redirect("tdcg.aspx");
    }
    catch (Exception ex)
    {
        this.Label1.Text = ex.Message;
    }
}

---

知识改变命运，奋斗成就人生!

Answer 2

你好！用了您的方法后，数据库中不执行插入操作了。任何信息都没有插入

Answer 3

实在抱歉，掉了一句 cmd.ExecuteNonQuery();

using (SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["strCon"].ConnectionString))
{
    string sql = "insert into clienttd values(@email,@message,getdate())";
    SqlCommand cmd = new SqlCommand(sql, con);

    try
    {
        con.Open();

        for (int i = 0; i < CheckBoxList1.Items.Count; i++)
        {
            if (CheckBoxList1.Items[i].Selected)
            {
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@email", this.TextBox1.Text);
                cmd.Parameters.AddWithValue("@message", Server.HtmlEncode(CheckBoxList1.Items[i].Value));
                cmd.ExecuteNonQuery();
            }
        }

        Response.Redirect("tdcg.aspx");
    }
    catch (Exception ex)
    {
        this.Label1.Text = ex.Message;
    }
}

---

知识改变命运，奋斗成就人生!