将多个列表框值插入数据库，条件不执行只插入一个名称 [英] insert multiple listbox value into database that condition not executed only one name will be inserted

问题描述

<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();
                      }
}
}
}

推荐答案

试试以下代码：

Try Below code:

<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.

问题是你的 ListBox 只允许在服务器上进行一次选择。您已添加多个属性，该属性允许您在客户端上选择多个项目，但服务器代码将丢弃除第一个选定项目之外的所有项目。



要修复它，请更改 SelectionMode 属性：

The problem is that your ListBox is only allowing a single selection on the server. You've added the multiple attribute, which lets you select multiple items on the client, but the server code is discarding all but the first selected item.

To fix it, change the SelectionMode property:

<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();
        }
    }
}

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