edit, Update, Cancel, Delete rows in Gridview(using MS Access DB)
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
<%@ 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">
<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">
<asp:TemplateField HeaderText="IDNO">
<asp:Label ID="lblid" runat="server" Text='<%#Eval("rowid") %>'>
<asp:TemplateField HeaderText="Name">
<%#Eval("Username")%>
<asp:TextBox ID="textbox1" runat="server" Text='<%#Eval("Username") %>' CausesValidation="true">
<asp:RequiredFieldValidator ID="namevalidate" runat="server" Text="Please enter Name"
ControlToValidate="textbox1">
<asp:TemplateField HeaderText="Percentage">
<%#Eval("marks") %>
<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">
<%-- <asp:RangeValidator MinimumValue="0" MaximumValue="100" ID="marksrangeValidate" runat="server" ControlToValidate="textbox2" Text="Enter with in 0 and 100">--%>
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.