Bulk update gridview checkboxes

[wbodger]

I have a page where I display a set of data that has three bit fields represented by checkboxes. By default I want to have all of those checkboxes editable and then I would like to write the entire dataset with any applicable changes back to the database. So, here is my aspx

<%@ Page Title="" Language="C#" MasterPageFile="Site.master"  AutoEventWireup="true" CodeFile="default.aspx.cs" Inherits="VISA._Import" %>

<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" Runat="Server">

<asp:GridView ID="RegGridView" runat="server" CellPadding="2" AllowSorting="True" OnSorting="RegGridView_Sorting" AutoGenerateColumns="False" Width="500px">  
<Columns>
<asp:BoundField DataField="coname" HeaderText="Company Name" ReadOnly="True" SortExpression="coname" /> 
<asp:BoundField DataField="chname" HeaderText="Cardholder Name" ReadOnly="True" SortExpression="chname" /> 
<asp:BoundField DataField="tdate" HeaderText="Transaction Date" ReadOnly="True" SortExpression="tdate" /> 
<asp:BoundField DataField="pdate" HeaderText="Posting Date" ReadOnly="True" SortExpression="pdate" /> 
<asp:BoundField DataField="mname" HeaderText="Merchant Name" ReadOnly="True" SortExpression="mname" /> 
<asp:BoundField DataField="tamount" HeaderText="Transaction Amount" ReadOnly="True" SortExpression="tamount" /> 
<asp:BoundField DataField="mcc" HeaderText="MCC" ReadOnly="True" SortExpression="mcc" /> 
<asp:BoundField DataField="mccdesc" HeaderText="MCC Description" ReadOnly="True" SortExpression="mccdesc" /> 
<asp:BoundField DataField="glcode" HeaderText="GL Code" ReadOnly="True" SortExpression="glcode" /> 
<asp:BoundField DataField="trantype" HeaderText="Transaction Type" ReadOnly="True" SortExpression="trantype" /> 
<asp:BoundField DataField="expensecat" HeaderText="Expense Category" ReadOnly="True" SortExpression="expensecat" /> 
<asp:BoundField DataField="expensesubcat" HeaderText="Expense Sub-category" ReadOnly="True" SortExpression="expensesubcat" /> 
<asp:BoundField DataField="memo" HeaderText="Memo" ReadOnly="True" SortExpression="memo" /> 
<asp:TemplateField  HeaderText="No Receipt Needed" SortExpression="noreceiptneeded">
    <ItemTemplate>
        <asp:CheckBox runat="server" ID="noreceiptneeded"/>
    </ItemTemplate>
</asp:TemplateField>
</Columns>
<EmptyDataTemplate>
    There are currently no items in this table.
</EmptyDataTemplate>
</asp:GridView>
</asp:Content>

and my codebehind

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

namespace VISA
{
    public partial class _Import : System.Web.UI.Page
    {
        protected void Page_Load(object sender, System.EventArgs e)
        {
            if (!IsPostBack)
            {
                ViewState["sortOrder"] = "";
                bindGridView("", "");
            }
        }

        public void bindGridView(string sortExp, string sortDir)
        {
            string connStr = ConfigurationManager.ConnectionStrings["visa"].ConnectionString;

            SqlConnection mySQLconnection = new SqlConnection(connStr);

            if (mySQLconnection.State == ConnectionState.Closed)
            {
                mySQLconnection.Open();
            }

            SqlCommand mySqlCommand = new SqlCommand("; with cte as " +
                                                    "( " +
                                                    "select vti.*, glc.[Gl Code], case when [Gl Code] is not null then 1 else 0 end as NoReceiptNeeded " +
                                                    "from EcompKviews.dbo.VisaTransactionImport vti left outer join EcompKviews.dbo.VisaMCC_GLCodes glc " +
                                                    "on vti.MCC = glc.mcc " +
                                                    "where [Transaction Amount] is not null " +
                                                    ") " +

                                                    "select cte.[Company Name - Last 4] as coname, cte.[Cardholder Name - Last 4] chname, cte.[Transaction Date] as tdate, cte.[Posting Date] as pdate, cte.[Merchant Name - Access Check Number] as mname, cte.[Transaction Amount] as tamount, cte.MCC, cte.[MCC Description] as mccdesc, " +
	                                                    "coalesce(gls.GlCode, cte.[Gl Code], NULL) as glcode, cte.[Transaction Type] as trantype, cte.[Expense Category] as expensecat, cte.[Expense Sub Category] as expensesubcat, cte.Memo, cte.NoReceiptNeeded " +
                                                    "from cte left outer join EcompKviews.dbo.view_VisaGLSuggestions gls on cte.[Merchant Name - Access Check Number] = gls.Comment " +
                                                    "order by [Cardholder Name - Last 4]", mySQLconnection);
            SqlDataAdapter mySqlAdapter = new SqlDataAdapter(mySqlCommand);
            DataSet myDataSet = new DataSet();
            mySqlAdapter.Fill(myDataSet);

            DataView myDataView = new DataView();
            myDataView = myDataSet.Tables[0].DefaultView;

            if (sortExp != string.Empty)
            {
                myDataView.Sort = string.Format("{0} {1}", sortExp, sortDir);
            }

            RegGridView.DataSource = myDataView;
            RegGridView.DataBind();

            if (mySQLconnection.State == ConnectionState.Open)
            {
                mySQLconnection.Close();
            }

        }

        protected void RegGridView_Sorting(object sender, GridViewSortEventArgs e)
        {
            bindGridView(e.SortExpression, sortOrder);
        }

        public string sortOrder
        {
            get
            {
                if (ViewState["sortOrder"].ToString() == "desc")
                {
                    ViewState["sortOrder"] = "asc";
                }
                else
                {
                    ViewState["sortOrder"] = "desc";
                }

                return ViewState["sortOrder"].ToString();
            }
            set
            {
                ViewState["sortOrder"] = value;
            }
        }  
    }
}

So, I guess I need two things, really. One, how do I populate the checkbox from my dataset and then two, how do I bulk update any changed checkboxes in the dataset? Not small questions, I know. I have looked around at some solutions that use asp:checkboxlist, but I get an error on checkboxlist.

Thanks,
Willie

 

[jbenson001]

My first suggestion, get your SQL out of the page and put it in a stored procedure.
Second, use a checkboxlist. This is a perfect example of where this control would work well.

Now, when the gridview is bound, the rowdatabound event will fire for each row bound. So, in that event, you can view your bit column values and set the checkboxes accordingly.

As for saving, again, a stored procedure is the way to go. This way, you can create a CSV(i.e. "1,5,12") of values and pass that as a parameter to your SP. Then you can parse that and update in your SP.

this is how we handle all of our checkbox type choices on our screens.