current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
1  
I just can't see how it should work. Could you post the markup ? I guess a DataList / ListView / GridView might be a better option to solve your problem. –  jbl Oct 8 '13 at 14:28
    
well does it has to be an sqldatasource if so why.? May you can Achieve your goal by trying something Else,but it you stick to it then @jbl is right you need to provide more of you code and markup.. –  khalid khan Oct 9 '13 at 6:52
    
Hi guys, thanks but... there was an answer which was deleted, I don't know why..... it concluded that this CheckBoxList was just not suited to this case. So you don't need code.... either this has to work, or not. That's my question. –  Craig Stevensson Oct 9 '13 at 19:14
    
I'm about halfway through with a repeater that mimics a checkboxlist. Standby. –  Zerkey Oct 9 '13 at 19:44
    
@Craig Stevensson - After realizing that my answer is not a solution, only pointing to the issues, I had to delete my answer. And I hope someone will come up with a better solution. –  afzalulh Oct 9 '13 at 20:23

2 Answers 2

up vote 3 down vote accepted
+50

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!

share|improve this answer
    
I like Repeaters more than any other templated controls for the versatility they provide and almost always use them. Upvote this useful answer, but it must be said that, when dealing with SqlDataSources, GridViews and ListViews are way better than Repeaters, as they have built-in CRUD logic. –  jbl Oct 10 '13 at 9:12
    
Is there a way to make it work using the ID column instead of the Name column? It doesn't looks good to perform a search (the "where") based on an non-indexed column.. :( –  Craig Stevensson Oct 11 '13 at 13:49
1  
Absolutely right. The easiest way would be to add a hiddenfield bound to ID between the label and checkbox, and change if (c is System.Web.UI.WebControls.Label) to look for the hiddenfield. Then update the database based on the ID. I use a similar method in one of my webapps but I use ajax instead of postbacks. –  Zerkey Oct 11 '13 at 16:52
up vote 0 down vote

Make sure your ID column is unique (key + auto numbered) this way your "WHERE" will only refer to the ID column and the "SET" will refer to the true\false column (the column you checking).

UPDATE [dbo].[Table_1] 
SET  [CheckBoxCol] = @CheckBoxCol 
WHERE (([id] = @Original_id))

In order to catch the "Checked" event you can use the following reffs: ASP.NET 2.0 Gridview CheckBox column How to add event for Checkbox click in Asp.net Gridview Column

You can use the DataSet.xsd for such simple queries, its easy drag and drop object to create databases queries.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.