将多个列表框值插入数据库，条件不执行只插入一个名称

问题描述

<asp:ListBox runat="server" CssClass="selectpicker form-control" multiple data-live-search="true" ID="ListBox1" DataValueField="username" DataTextField="username">

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindListBox();
        }
 
    }
 

    private void BindListBox()
    {
        using (SqlConnection con = new SqlConnection(CS))
        {
            using (SqlCommand command = new SqlCommand("SELECT DISTINCT [username], [email] FROM [Login]", con))
            {
                SqlDataAdapter da = new SqlDataAdapter(command);
                DataSet ds = new DataSet();
                da.Fill(ds);
                ListBox1.DataSource = ds;
                ListBox1.DataTextField = "username";
                ListBox1.DataValueField = "username";
                ListBox1.DataBind();
           
            }
        }
    }
    
protected void send_Click(object sender, EventArgs e)
    {
        
        foreach (ListItem item in ListBox1.Items)
        {
            if(item.Selected)
            
            {
                using (SqlConnection con = new SqlConnection(CS))
                {
                    con.Open();
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.Connection = con;
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = "INSERT INTO Calendar (parti ) values (@parti)";
  cmd.Parameters.AddWithValue("@parti", item.Value.ToString());
                        cmd.ExecuteNonQuery();
                      }
}
}
}

推荐答案

<asp:listbox runat="server" cssclass="selectpicker form-control" multiple="" data-live-search="true" id="ListBox1" datavaluefield="username" datatextfield="username" selectionmode="Multiple">
</asp:listbox>

protected void send_Click(object sender, EventArgs e)
{
	StringBuilder sb = new StringBuilder(string.Empty);
	int counter = 1;
	SqlCommand cmd = new SqlCommand();
	foreach (ListItem item in ListBox1.Items)
    {
         if (item.Selected)
         {		
			counter += 1;
			const string sqlStatement = "INSERT INTO Calendar (parti) VALUES (@parti" + counter +");";
			sb.Append(sqlStatement);
			cmd.Parameters.AddWithValue("@parti" +counter , item.Value.ToString());
         }
    }
 
	using (SqlConnection con = new SqlConnection(CS))
	{
		con.Open();
		cmd.Connection = con;
		cmd.CommandType = CommandType.Text;
		cmd.CommandText = sb.ToString();
		cmd.ExecuteNonQuery();
		
		cmd.Close();
	}
}

这里我添加了一个属性选择模式作为多个，以便用户可以从列表框中选择多个项目。在代码隐藏中，它循环遍历项目并生成动态SQL查询。然后它执行完整的查询以插入数据库。

Here I added one property selectionmode as multiple so that user can select multiple items from listbox. In code-behind, it is looping over items and generating dynamic SQL query. Then it executes complete query to insert in database.

<asp:ListBox runat="server" CssClass="selectpicker form-control" SelectionMode="Multiple" data-live-search="true" ID="ListBox1" DataValueField="username" DataTextField="username">

这会自动将多个属性添加到渲染HTML，所以你不需要再添加它。





另外，我倾向于使用单个连接/ command对象，并在事务中包装所有insert语句：

This will automatically add the multiple attribute to the rendered HTML, so you don't need to add it again.


Also, I'd be inclined to use a single connection / command object, and wrap all of the insert statements in a transaction:

protected void send_Click(object sender, EventArgs e)
{
    using (SqlConnection con = new SqlConnection(CS))
    using (SqlCommand cmd = new SqlCommand("INSERT INTO Calendar (parti) values (@parti)", con))
    {
        // TODO: Change the data type and size to match your SQL column:
        SqlParameter parti = cmd.Parameters.Add("@parti", SqlDbType.NVarChar, 50);

        con.Open();
        using (SqlTransaction transaction = con.BeginTransaction())
        {
            cmd.Transaction = transaction;

            foreach (ListItem item in ListBox1.Items)
            {
                if (item.Selected)
                {
                    parti.Value = item.Value;
                    cmd.ExecuteNonQuery();
                }
            }

            transaction.Commit();
        }
    }
}

这篇关于将多个列表框值插入数据库，条件不执行只插入一个名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！