edit, Update, Cancel, Delete rows in Gridview(using MS Access DB)

lperumb

Rate me:

3.67/5 (3 votes)

23 Sep 2011CPOL

34.2K

Grid Functionalities

Hi, I will show you how to edit, Update, Cancel the update, delete the rows of a gridview(using MS Access DB). Here I have a column by name Percentage where I will restrict the user from entering the alphabets and enable him to enter only percentage values.

The code is all self explanatory.

Default.aspx.cs

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

public partial class _Default : System.Web.UI.Page 

{
    //SqlConnection conn; 
    OleDbConnection conn;
    protected void Page_Load(object sender, EventArgs e)
    {
        // for sql db put your connection string here
        conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Server.MapPath
              ("~/App_Data/EditUpdateGrid.mdb"));
        if(!IsPostBack )  
         {  
             bind();  
         }  
    }  

     protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)  
     {  
         GridView1.EditIndex = e.NewEditIndex;  
         bind();  
     }  

     protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)  
     {  
         GridView1.EditIndex = -1;  
         bind();  
     }  
     

     protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)  
     {
         GridViewRow row = (GridViewRow)GridView1.Rows[e.RowIndex];
         Label lbl = (Label)row.FindControl("lblid");  
         TextBox textname = (TextBox)row.FindControl("textbox1");  
         TextBox textmarks = (TextBox)row.FindControl("textbox2");  
         GridView1.EditIndex = -1;  
         conn.Open();
         OleDbCommand cmd = new OleDbCommand("update emp set marks=" + textmarks.Text + " , Username='" + textname.Text + "' where rowid=" + lbl.Text + "", conn);
         //SqlCommand cmd = new SqlCommand("update emp set marks=" + textmarks.Text + " , name='" + textname.Text + "' where rowid=" + lbl.Text + "", conn);  
         cmd.ExecuteNonQuery();  
         conn.Close();  
         bind();  
     }  

     public void bind()  
     {  
         conn.Open();
         OleDbDataAdapter da = new OleDbDataAdapter("select * from emp", conn);
         //SqlDataAdapter da = new SqlDataAdapter("select * from emp", conn);  
         DataSet ds = new DataSet();  
         da.Fill(ds,"emp");  
         GridView1.DataSource = ds.Tables[0];  
         GridView1.DataBind();  
         conn.Close();  
     }  


     protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)  
     {  
         GridViewRow row = (GridViewRow)GridView1.Rows[e.RowIndex];  
         Label lbldeleteID = (Label)row.FindControl("lblid");  
         conn.Open();
         OleDbCommand cmd = new OleDbCommand("delete from emp where rowid=" + lbldeleteID.Text + "", conn);
         //SqlCommand cmd = new SqlCommand("delete  emp where rowid=" + lbldeleteID.Text + "", conn);  
         cmd.ExecuteNonQuery();  
         conn.Close();  
         bind();  
     }  

     protected void GridView1_PageIndexChanging1(object sender, GridViewPageEventArgs e)
     {

         GridView1.PageIndex = e.NewPageIndex;
         GridView1.EditIndex = -1;
         bind(); 
     }
     
}

Default.aspx

ASP.NET

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head  runat="server">
    <title>Edit,Delete,Update the Grid</title>
</head>
<body>
    
    <script type="text/javascript">
 function ispercentage(obj, e, allowDecimal, allowNegative) 
 {
 
   var key;
   var isCtrl = false;
   var keychar;
   var reg;
   if (window.event) 
   {
     key = e.keyCode;
     isCtrl = window.event.ctrlKey
   }
   else if (e.which)
   {
     key = e.which;
     isCtrl = e.ctrlKey;
   }
   if (isNaN(key)) return true;
   keychar = String.fromCharCode(key);
   // check for backspace or delete, or if Ctrl was pressed
   if (key == 8 || isCtrl)
   {
     return true;
   }
   ctemp = obj.value;
   var index = ctemp.indexOf(".");
   var length = ctemp.length;
   ctemp = ctemp.substring(index, length);
   if (index < 0 && length > 1 && keychar != '.' && keychar != '0')
   {
     obj.focus();
     return false;
   }
   if (ctemp.length > 2)
   {
     obj.focus();
     return false;
   }
   if (keychar == '0' && length >= 2 && keychar != '.' && ctemp != '10') {
     obj.focus();
     return false;
   }
   reg = /\d/;
   var isFirstN = allowNegative ? keychar == '-' && obj.value.indexOf('-') == -1 : false;
   var isFirstD = allowDecimal ? keychar == '.' && obj.value.indexOf('.') == -1 : false;
   return isFirstN || isFirstD || reg.test(keychar);
}
    </script>

    <form id="form1"  runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" Style="z-index: 100;
            left: 177px; position: absolute; top: 118px; width: 315px;" AllowPaging="true"
            AllowSorting="True" AutoGenerateEditButton="True" OnRowEditing="GridView1_RowEditing"
            OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowUpdating="GridView1_RowUpdating"
            OnRowDeleting="GridView1_RowDeleting" BackColor="#FF8080" BorderColor="SaddleBrown"
            BorderStyle="None" CellPadding="4" ForeColor="#333333" GridLines="None" PageSize="5"
            OnPageIndexChanging="GridView1_PageIndexChanging1" 
            AutoGenerateDeleteButton="True">
            <columns>
                <asp:TemplateField HeaderText="IDNO">
                    <itemtemplate>
                        <asp:Label ID="lblid" runat="server" Text='<%#Eval("rowid") %>'></itemtemplate>
                
                <asp:TemplateField HeaderText="Name">
                    <itemtemplate>
                        <%#Eval("Username")%></itemtemplate>
                    <edititemtemplate>
                        <asp:TextBox ID="textbox1" runat="server" Text='<%#Eval("Username") %>' CausesValidation="true">
                        <asp:RequiredFieldValidator ID="namevalidate" runat="server" Text="Please enter Name"
                            ControlToValidate="textbox1">
                    </edititemtemplate>
                
                <asp:TemplateField HeaderText="Percentage">
                    <itemtemplate>
                        <%#Eval("marks") %>
                    </itemtemplate>
                    <edititemtemplate>
                        <asp:TextBox ID="textbox2" runat="server" Text='<%#Eval("marks") %>' CausesValidation="true"
                            onkeypress="return ispercentage(this, event, true, false);" MaxLength="5">
                        <asp:RequiredFieldValidator ID="marksValidate" runat="server" Text="Please enter marks"
                            ControlToValidate="textbox2">
                        <br></br>
                        <%-- <asp:RangeValidator MinimumValue="0" MaximumValue="100" ID="marksrangeValidate" runat="server" ControlToValidate="textbox2" Text="Enter with in 0 and 100">--%>
                    </edititemtemplate>
                
            </columns>
            <footerstyle backcolor="#990000" font-bold="True" forecolor="White" />
            <rowstyle backcolor="#FFFBD6" forecolor="#333333" />
            <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
            <pagerstyle backcolor="#FFCC66" forecolor="#333333" horizontalalign="Center" />
            <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <alternatingrowstyle backcolor="White" />
        
    </div>
    </form>
</body>
</html>

Here I have a script called isPercentage which allows only numbers to be entered in the edit textbox. Rest of the code is all self explanatory. I have coded it using MS Access DB and the same using SQL has been provided in the comment lines. Provide the intial connection string and then proceed with the things.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By

lperumb

Software Developer Wipro Technologies

India

This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

edit, Update, Cancel, Delete rows in Gridview(using MS Access DB)

License

Comments and Discussions