INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Bulk update gridview checkboxes

Bulk update gridview checkboxes

(OP)
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

CODE

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

CODE

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

RE: Bulk update gridview checkboxes

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close