C# – Asp.Net CheckBoxList to update a SqlDataSource

asp.netccheckboxlistsql serversqldatasource

I need to update some table values using Checkboxes. The table consist of 3 simple columns: id, name and selected (bool).

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.

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?

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.

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 ?

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?

UPDATE

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

You can see now it sends "False" and "0" instead of nulls, but the Id is still being sent as "null".

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.

Best Answer

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:

Sample View

Here is the .aspx code:

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

And the codebehind:

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!

Debug

Obviously there is much room for improvement, but hopefully this will give you a good idea of how to proceed!

Related Topic