Update SQL from Checkbox in ListView

Question

Goal: Update row(s) in SQL table when Checkbox inside ListView is clicked. I would like to update the following columns from sql: IsApproved ApprovalType

I am able to find the checkbox in the row that is clicked from the Listview on the page. However, that is as far as I got. Current code below, as well as what I've tried and the subsequent errors.

Code:

aspx:

<asp:ListView ID="lstUsers" runat="server" ItemType="Program.Data.Table" DataKeyNames="ID" SelectMethod="lstUsers_GetData"
    OnItemCommand="lstUsers_ItemCommand" DeleteMethod="lstUsers_DeleteItem" OnSorting="lstUsers_Sorting"
    OnItemDataBound="lstUsers_ItemDataBound"
    ItemPlaceholderID="litPlaceHolder" >

<LayoutTemplate>
        <div class="table-responsive">
            <table class="table">
                <thead>
                    <tr>                            
                        <th>Last Name</th>
                        <th>First Name</th>
                        <th>UserName</th>
                       <th>Approve User</th>

            <td><%# Item.LastName %></td>
            <td><%# Item.FirstName %></td>
            <td><%# Item.UserName %></td>
            <td> <asp:CheckBox ID="cbApproved" runat="server" AutoPostBack="true" OnCheckedChanged="Approve_Click" /> </td>

c#:

protected void Approve_Click(object sender, EventArgs e)
    {
        foreach (ListViewItem item in lstUsers.Items)
        {
            if ((item.FindControl("cbApproved") as CheckBox).Checked == true)
            {
                System.Diagnostics.Debug.WriteLine("it was checked!");

I've tried:

item.ColumnName -- but get error ListView does not contain a definition for ''
item.DataItem -- the output displays what looks like an object as a whole (System.Web.UI.WebControls.ListViewDataItem)
item.DataItem.ColumnName -- but get error Object does not contain a definition for ''

Do the column values im looking to update in SQL have to be displayed on the ASPX page?

I looked through:

https://msdn.microsoft.com/en-us/library/bb398790.aspx#ModifyingDataUsingTheListViewControl

Asp.Net CheckBoxList to update a SqlDataSource

VDWWD · Accepted Answer · 2018-10-17 06:27:25Z

0

Why not simply cast the sender to a CheckBox?

protected void Approve_Click(object sender, EventArgs e)
{
    if ((sender as CheckBox).Checked == true)
    {
        System.Diagnostics.Debug.WriteLine("it was checked!");
    }
}

You must bind data to the ListView inside an IsPostBack check for it to work.

UPDATE

What you want can be done easily with DataKeyNames. Add it to the ListView as a property.

<asp:ListView ID="ListView1" runat="server" DataKeyNames="IDColumn">

Then when the checkbox is changed read the correct key

protected void Page_Load(object sender, EventArgs e)
{
    if (IsPostBack == false)
    {
        ListView1.DataSource = Common.fillBooks();
        ListView1.DataBind();
    }
}

protected void Approve_Click(object sender, EventArgs e)
{
    //cast the sender back to the checkbox
    CheckBox cb = sender as CheckBox;

    if (cb.Checked)
    {
        //get the parent of the parent (itemtemplate > listiew)
        ListView lv = cb.Parent.Parent as ListView;

        //get the dataitem from the namingcontainer
        ListViewDataItem item = cb.NamingContainer as ListViewDataItem;

        //get the correct datakey with the index of the dataitem
        int ID = Convert.ToInt32(lv.DataKeys[item.DataItemIndex].Values[0]);

        //update database here with the ID
    }
}

ListView

<asp:ListView ID="ListView1" runat="server" DataKeyNames="id" ItemType="Namespace.Class">
    <ItemTemplate>
        <div class="table-responsive">
            <table class="table">
                <tr>
                    <td>
                        <asp:CheckBox ID="cbApproved" runat="server" AutoPostBack="true" OnCheckedChanged="Approve_Click" />
                    </td>
                </tr>
            </table>
        </div>
    </ItemTemplate>
</asp:ListView>

edited Oct 17, 2018 at 6:27

answered Oct 16, 2018 at 16:43

VDWWD

35.7k23 gold badges67 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

seesharp Over a year ago

My WriteLine is printing to Output. That part is fine, the question is more centered on once we are inside the if statement, how do I update the row in SQL associated with the row where they clicked the checkbox (every row has a checkbox, but I only care about the row the user clicked and HOW to update the associated SQL data)

VDWWD Over a year ago

Updated my answer.

seesharp Over a year ago

Thanks. I actually had DataKeyNames already. I should have included it, just updated my code. Using your updated solution I'm getting a NullReference error: System.NullReferenceException: 'Object reference not set to an instance of an object.' lv was null. " ..This makes me think if lv is null that cb must be null? Which means its not recognizing sender?

VDWWD Over a year ago

Strange, because I can assure you it works. See my complete example. Are you sure you implememt the code correctly? What happens if you use just my code, do you still get a nullreference?

seesharp Over a year ago

I had commented out mine and pasted yours. The only thing that would be different would be the ListView1.DataSource = Common.fillBooks(); What is Common.fillBooks() ?

|

seesharp · Accepted Answer · 2018-10-19 15:26:05Z

This is what is currently working for me. Too long to add in comment or clutter original post:

ASPX:

  <ItemTemplate>
            <tr>
                <td><asp:Label ID="UserNameLabel" runat="server" Text='<%#Eval("UserName") %>' /></td>
                <td> <asp:CheckBox ID="Approved" runat="server" AutoPostBack="true" OnCheckedChanged="Approve_Click" /> </td>
            </tr>
    </ItemTemplate>

Changed the ItemTemplate to use asp Label controls so I could reference them in the code behind:

protected void Approve_Click(object sender, EventArgs e)
        {
            foreach (ListViewItem item in listUsers.Items)
            {
                if ((item.FindControl("Approved") as CheckBox).Checked == true)
                {
                    Label myLabel = (Label)item.FindControl("UserNameLabel");
                    try
                        {
                            IQueryable<SUR> user = null;
                            user = db.SURs.Where(m => !m.IsApproved && m.UserName == myLabel.Text);

                            if(user != null)
                            {                                   
                                db.sp_SUA(user.FirstOrDefault().ID, SessionMgr.CurrentUserID.ToString(), "Manual");
                                db.SaveChanges();
                            }
                            Response.Redirect(Request.RawUrl);
                        }

                    catch(Exception ex)
                    {
                        Elmah.ErrorSignal.FromCurrentContext().Raise(ex);
                    }
                }
            } 
        }

Once getting the control that had the username, I check if the DB has a row with the same value If so, use a stored procedure to update the DB

EDIT:

After refactoring, I was wondering if it would be more efficient to get rid of the foreach loop, and instead use the OnItemCommand property of ListView and set the OnCheckedChanged for the Checkbox to point to the ItemCommand method? My thinking is since I am not looping through each item, would having the array index be better in terms of Big O?

Code would be:

protected void listSTUFF_ItemCommand(object sender, ListViewCommandEventArgs e)
    if (int.TryParse(list.DataKeys[e.Item.DisplayIndex].Value.ToString(), out id)) {
        var myQuery = db.Table.Where(a=>a.ID == id).FirstOrDefault();
        db.StoredProcedure(myQuery.ID)
        db.SaveChanges();

@VDWWD Any thoughts on the "EDIT" above? Replacing foreach with OnItemCommand?

Collectives™ on Stack Overflow

Update SQL from Checkbox in ListView

2 Answers 2

7 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related