Asp.Net CheckBoxList更新SqlDataSource [英] Asp.Net CheckBoxList to update a SqlDataSource
问题描述
我需要使用复选框更新一些表值。该表由3个简单的列组成：id，名称和selected（布尔）。
I need to update some table values using Checkboxes. The table consist of 3 simple columns: id, name and selected (bool).
我向表单添加了 asp：CheckBoxList 控件，将其绑定到 SqlDataSource 。
我添加了一个简单代码 MySqlDataSource.Update （）;的按钮；
然后浏览到页面。
I added a asp:CheckBoxList control to a form, bound it to a SqlDataSource. I added a button with the simple code MySqlDataSource.Update(); Then browse to the page.
我单击一个复选框，然后单击按钮以更新数据源并使用Sql Server Profiler，我看到的是发送到数据库：
I click a Checkbox, then click the button to Update the datasource and using the Sql Server Profiler I see this is what was sent to the database:
exec sp_executesql N'UPDATE [MyTable] SET [Name] = @Name, [Selected] = @Selected
WHERE [Id] = @Id',N'@Name nvarchar(4000),@Selected bit,
@Id int',@Name=NULL,@Selected=NULL,@Id=NULL
问题很明显，没有设置值....但是为什么？
The problem is clear, the values are not being set.... but why?
我添加了一个GridView，仅用于测试目的，使用相同的完全相同的SqlDataSource，而无需移动一点，我可以通过单击编辑来修改记录链接，然后是复选框，然后是更新链接，仅此而已，它确实保存到数据库，它正确地将更新命令发送到Sql Server。
I added a GridView, just for testing purposes, using the same exact SqlDataSource without moving a single bit from it, and I'm able to modify the records by clicking the "Edit" link, then the checkbox, then the "Update" link and that's it, that does save to the database, it correctly send the update command to Sql Server.
如果我转到MyDataSource的UpdateQuery属性，我看到有一个设置参数源的选项，但是我看不到n在下拉列表中设置相同数据源的参数源。好像我在这里缺少什么...。但是为什么同一个SqlDataSource可以用于GridView？
If I go to the UpdateQuery property of MyDataSource, I see there's an option to set the "Parameter Source", but I can't see in the dropdownlist one to set the parameter source of the same datasource. Seems like I'm missing something here.... but why the same SqlDataSource does work for the GridView ?
我花了几个小时已经在搜索有关使用CheckboxList的示例为了执行更新，我发现了很多示例，但是...大多数只是出于显示目的。
我想知道CheckboxList是否真的可以进行更新吗？
I spent several hours already searching for samples about using a CheckboxList to perform updates and I found a many sample but... most are just for displaying purpose. I wonder if the CheckboxList can really work for updates?
更新
建议将参数源设置为CheckBoxList。
我将名称和所选列的参数源设置为属性 SelectedValue。
它改变了行为，但仍然起作用，现在发送到数据库的查询是：
Followed the advise to set the Parameter source as the CheckBoxList. I set the parameter source for the Name and Selected columns to the property "SelectedValue". It changed the behavior, but it still works wrong, now the query sent to the database is:
exec sp_executesql N'UPDATE [MyTable] SET [Name] = @Name, [Selected] = @Selected WHERE [Id] = @Id',N'@Name nvarchar(5),@Selected bit,@Id int',@Name=N'False',@Selected=0,@Id=NULL
您现在可以看到它发送了 False和 0（而不是null），但Id仍作为 null发送。
You can see now it sends "False" and "0" instead of nulls, but the Id is still being sent as "null".
因此，我更改了参数以使用参数设置ID源类型为Control，然后为CheckboxList，然后将Selected设置为SelectedValue属性。
现在错误消息是：
So, I changed the pararmeters to set the ID with a Parameter source of type Control, then CheckboxList, then set Selected to the SelectedValue property. Now the error message is:
输入字符串的格式不正确。
"Input string was not in a correct format."
我认为这应该是从当前复选框项中获取基础值的一种方法，但是我不知道为什么，我仍然在寻找示例。
I think it should be a way to get the underlying values from the "current" checkbox item, but I don't know why and I have still searching for examples.
推荐答案
我制作了一个中继器结构，看起来像 asp：CheckBoxList ，但具有其他功能。这是在浏览器中的外观：
I have made a repeater construct that appears like a asp:CheckBoxList, but with additional functionality. Here is what it looks like in a browser:
这是.aspx代码：
<form id="form1" runat="server">
<div>
<asp:Repeater ID="Repeater1" runat="server">
<ItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# (bool)Eval("Selected") %>' />
<asp:Label ID="Label1" runat="server" Text='<%# Eval("Name")%>' AssociatedControlID="CheckBox1"></asp:Label>
<div style="clear:both;height:5px;"></div>
</ItemTemplate>
</asp:Repeater>
<div style="clear:both;height:45px;"></div>
<asp:Button ID="Update_Button" runat="server" Text="Update"
OnClick="Update_Button_Click" />
</div>
</form>
和后面的代码：
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//Fake DataTable below.
//SqlDataSource can be configured to generate a DataTable,
//Or you can use a DataAdapter
DataTable dt = new DataTable();
DataColumn dc1 = new DataColumn("Name");
DataColumn dc2 = new DataColumn("Id");
DataColumn dc3 = new DataColumn("Selected");
dc3.DataType = System.Type.GetType("System.Boolean");
dt.Columns.Add(dc1);
dt.Columns.Add(dc2);
dt.Columns.Add(dc3);
dt.Rows.Add(new object[] { "John Doe", "135681", true });
dt.Rows.Add(new object[] { "Billy Joe", "66541", false });
dt.Rows.Add(new object[] { "Joe Shmoe", "7783654", true });
dt.Rows.Add(new object[] { "Don Sean", "1332451", true });
dt.Rows.Add(new object[] { "Moe H", "632451", false });
dt.Rows.Add(new object[] { "Clicky", "0234354", true });
//Bind DataTable to Repeater
Repeater1.DataSource = dt;
Repeater1.DataBind();
}
}
protected void Update_Button_Click(object sender, EventArgs e)
{
List<Person> Listy = new List<Person>();
ControlCollection CC = Repeater1.Controls;
foreach (RepeaterItem RI in CC)
{
Person p = new Person();
foreach (Control c in RI.Controls)
{
if (c is System.Web.UI.WebControls.CheckBox)
{
if (((System.Web.UI.WebControls.CheckBox)c).Checked)
p.Selected = true;
else p.Selected = false;
}
if (c is System.Web.UI.WebControls.Label)
{
p.Name = ((System.Web.UI.WebControls.Label)c).Text;
}
}
Listy.Add(p);
}
UpdateDatabase(Listy);
}
protected void UpdateDatabase(List<Person> L)
{
foreach (Person p in L)
{
string update = "UPDATE [Table] SET [Selected] = " + p.Selected + "WHERE [Name] = " + p.Name;
// Execute statement
}
}
}
public class Person
{
public string Name { get; set; }
//public int ID { get; set; }
public bool Selected { get; set; }
}
这是在更新语句中打开的调试器。所有值都是准确的！
And here is the debugger open at the update statement. All values are accurate!
显然还有很多改进的空间，但是希望这会给您一个很好的思路！
Obviously there is much room for improvement, but hopefully this will give you a good idea of how to proceed!
这篇关于Asp.Net CheckBoxList更新SqlDataSource的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
