How to Use CheckBox Control in ASP.NET GridView to Update Database C# VB.Net

Some cases we need to represent tabular data by using GridView. But some cases we need to do operations on a GridView Control. For example an approval system. Here anyone can initiate a process and next level need to approve those processes. In this cases we need to use CheckBox Control within the GridView. That’s why in this article I will explain “How we can use CheckBox Control in GridView to Update Status in DataBase“.

Objective/Outcome:
How to Use Checkbox in ASP.NET GridView Control to Update Database Csharp VB.Net

To do the Example first create the Below Table:

CREATE TABLE [dbo].[Report](
	[ID] [bigint] NOT NULL,
	[ReportName] [varchar](max) NULL,
	[ReportDescription] [varchar](max) NULL,
	[Status] [bit] NULL,
 CONSTRAINT [PK_Report] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Insert Some Data Like Below:
CheckBox Data

Modify Web.Config File to connect to SQL Server Database:

<configuration>
  <connectionStrings>
    <add name="DBConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=TESTDB;Trusted_Connection=yes;" providerName="System.Data.SqlClient"/>
    <!--<add name="BONConnection" connectionString="Data Source=XXX.com;Initial Catalog=DBNAME;User Id=UserName;Password=YourPassword;" providerName="System.Data.SqlClient" />-->
  </connectionStrings>
...................
...................

Note: If you use windows authentication then use the first key. Otherwise use second key line. Update server name, Database name, User name, Password as per your settings.

GridView Control HTML Code with Checkbox:
C# Code:

<asp:GridView ID="GridView1" runat="server" DataKeyNames="ID" AutoGenerateColumns="False" HeaderStyle-BackColor="CornflowerBlue" HeaderStyle-Font-Bold="true" HeaderStyle-ForeColor="White" CellPadding="5">
            <Columns>            
                <asp:TemplateField>
                    <ItemTemplate>
                        <asp:CheckBox ID="chkStatus" runat="server" 
                            AutoPostBack="true" OnCheckedChanged="chkStatus_OnCheckedChanged"
                            Checked='<%# Convert.ToBoolean(Eval("Status")) %>'
                            Text='<%# Eval("Status").ToString().Equals("True") ? " Approved " : " Pending " %>' />
                    </ItemTemplate>                    
                </asp:TemplateField>
                
                <asp:BoundField DataField="ReportName" HeaderText="Report Name" />                    
                <asp:BoundField DataField="ReportDescription" HeaderText="Report Description"  />
            </Columns>
</asp:GridView>

VB.Net Code:

<asp:GridView ID="GridView1" runat="server" DataKeyNames="ID" AutoGenerateColumns="False" HeaderStyle-BackColor="CornflowerBlue" HeaderStyle-Font-Bold="true" HeaderStyle-ForeColor="White" CellPadding="5">
            <Columns>            
                <asp:TemplateField>
                    <ItemTemplate>
                        <asp:CheckBox ID="chkStatus" runat="server" 
                            AutoPostBack="true" OnCheckedChanged="chkStatus_OnCheckedChanged"
                            Checked='<%# Convert.ToBoolean(Eval("Status")) %>'
                            Text='<%# If(Eval("Status").ToString().Equals("True"), " Approved ", " Pending ") %>' />
                    </ItemTemplate>                    
                </asp:TemplateField>
                
                <asp:BoundField DataField="ReportName" HeaderText="Report Name" />                    
                <asp:BoundField DataField="ReportDescription" HeaderText="Report Description"  />
            </Columns>
</asp:GridView>

Note: The above ASP.NET GridView Control will show how you can use TemplateField in the GridView Control to display CheckBox with the current status of the Report from the Database. If you look at the CheckBox Control, you will found that I am setting checked status from Database Report table status column. When user click on the CheckBox control we need to run some server side code to update the Database status. That’s why I have added AutoPostBack Property to true & an event handler named chkStatus_OnCheckedChanged to handle OnCheckedChanged event. In each click on CheckBox, chkStatus_OnCheckedChanged event will fire and update the Database Report table Status field based on CheckBox checked status.

Now Bind the GridView Control with CheckBox Status:
C# Code:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            Bind_Data();
    }

    private void Bind_Data()
    {
        string consString = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
        using (SqlConnection conn = new SqlConnection(consString))
        {
            SqlCommand cmd = new SqlCommand("SELECT * FROM [Report]", conn);
            cmd.CommandType = CommandType.Text;
            conn.Open();
            SqlDataReader drStudents = cmd.ExecuteReader();
            GridView1.DataSource = drStudents;
            GridView1.DataBind();
        }
    }

Note: Don’t forget to add “System.Data”, “System.Data.SqlClient” & “System.Configuration” namespaces.

VB.Net Code:

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Bind_Data()
        End If
    End Sub

    Private Sub Bind_Data()
        Dim consString As String = ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString
        Using conn As New SqlConnection(consString)
            Dim cmd As New SqlCommand("SELECT * FROM [Report]", conn)
            cmd.CommandType = CommandType.Text
            conn.Open()
            Dim drStudents As SqlDataReader = cmd.ExecuteReader()
            GridView1.DataSource = drStudents
            GridView1.DataBind()
        End Using
    End Sub

Note: Don’t forget to add “System.Data”, “System.Data.SqlClient” & “System.Configuration” namespaces.

Now implement CheckBox chkStatus_OnCheckedChanged Event:
C# Code:

    public void chkStatus_OnCheckedChanged(object sender, EventArgs e)
    {
        CheckBox chkStatus = (CheckBox)sender;
        Int64 nID = Convert.ToInt64(GridView1.DataKeys[((GridViewRow)chkStatus.NamingContainer).RowIndex].Value);

        string sQuery = "UPDATE Report SET Status = @Status WHERE ID = @ID";
        string consString = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
        using (SqlConnection conn = new SqlConnection(consString))
        {
            SqlCommand com = new SqlCommand(sQuery, conn);

            com.Parameters.Add("@Status", SqlDbType.Bit).Value = chkStatus.Checked;
            com.Parameters.Add("@ID", SqlDbType.BigInt).Value = nID;

            conn.Open();
            com.ExecuteNonQuery();
        }

        Bind_Data();
    }

VB.Net Code:

    Public Sub chkStatus_OnCheckedChanged(sender As Object, e As EventArgs)
        Dim chkStatus As CheckBox = DirectCast(sender, CheckBox)
        Dim nID As Int64 = Convert.ToInt64(GridView1.DataKeys(DirectCast(chkStatus.NamingContainer, GridViewRow).RowIndex).Value)

        Dim sQuery As String = "UPDATE Report SET Status = @Status WHERE ID = @ID"
        Dim consString As String = ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString
        Using conn As New SqlConnection(consString)
            Dim com As New SqlCommand(sQuery, conn)

            com.Parameters.Add("@Status", SqlDbType.Bit).Value = chkStatus.Checked
            com.Parameters.Add("@ID", SqlDbType.BigInt).Value = nID

            conn.Open()
            com.ExecuteNonQuery()
        End Using

        Bind_Data()
    End Sub

Here I have created CheckBox reference first by type casting the sender parameter of the Event Handler. To get the primary key ReportID, I am using the DataKeyNames. After that using ADO.NET code to execute the Update Query in Database using SqlConnection and SqlCommand object. Since primary key is in your hand you can do everything now.

Download Code Example C#        Download Code Example VB.Net

Posted in .Net, Asp.net, C#, Gridview, VB.Net

Leave a Reply

Your email address will not be published. Required fields are marked *

     

*