How To Edit And Update GridView Using Microsoft Access
Here we are binding a GridView from Microsoft Office Access in Asp.Net. I create a table tblStudent in Access which is containing students information like Name, Branch and City. We are showing all records from Access in GridView and edit any record and update in again Access.
Points Of Remember:
1. Place LinkButton Edit in ItemTemplate and LinkButton Update and Cancel in EditTamplate of Gridview's column.
2. Fire GridView's RowEditing event for LinkButton Edit.
3. Fire GridView's RowUpdating event for LinkButton Update.
4. Fire GridView's RowCancelingEdit event for LinkButton Cancel.
5. Set CommandName propery of LinkButtons according to their respective event.
6. Set GridView's DataKeyNames Property to record ID (ID=Primary/Unique key of student table)
7. Add namespace System.Data and System.Data.OleDb in your C# page.
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>GridView with Access</title>
<link href="../../Css/GridView.css" rel="stylesheet" />
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" CssClass="GridViewStyle" Width="710px"
AutoGenerateColumns="false" DataKeyNames="ID"
OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating"
OnRowCancelingEdit="GridView1_RowCancelingEdit">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID="LB1" runat="server" CommandName="Edit">Edit</asp:LinkButton>
</ItemTemplate>
<EditItemTemplate>
<asp:LinkButton ID="LB2" runat="server" CommandName="Update">Update</asp:LinkButton>
<asp:LinkButton ID="LB3" runat="server" CommandName="Cancel">Cancel</asp:LinkButton>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("Name") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txt_Name" runat="server" Text='<%# Eval("Name") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Branch">
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Eval("Branch") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txt_Branch" runat="server" Text='<%# Eval("Branch") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="City">
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Eval("City") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txt_City" runat="server" Text='<%# Eval("City") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
</Columns>
<HeaderStyle CssClass="HeaderStyle" />
<RowStyle CssClass="RowStyle" />
<AlternatingRowStyle CssClass="AlternatingRowStyle" />
</asp:GridView>
</div>
</form>
</body>
</html>
In above example, I simply used a GridView.css for look and feel, for more about GridView cssClass visit GridView Style article.
C# Codes :
using System.Data;
using System.Data.OleDb;
public partial class GridView_test_GridView_Access
:
System.Web.UI.
Page
{
OleDbConnection con= new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;
Data Source=E:\programidea Demo\DbStudent.mdb; Persist Security Info=False;");
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridView();
}
}
// Bind GridView
protected void BindGridView()
{
DataTable dt = new DataTable();
OleDbDataAdapter da = new OleDbDataAdapter(
"select
* from tblStudent"
, con);
con.Open();
da.Fill(dt);
con.Close();
if (dt.Rows.Count > 0)
{
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
// To edit GridView's row
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
BindGridView();
}
// To cancel edit of GridView's
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
BindGridView();
}
// To update GridView's row
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
int id = (int)GridView1.DataKeys[e.RowIndex].Value;
TextBox name = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txt_Name");
TextBox branch = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txt_Branch");
TextBox city = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txt_City");
OleDbCommand cmd = new OleDbCommand("update tblStudent set Name='" + name.Text +
Branch='"+branch.Text+"', City='"+city.Text+"' where ID="+id, con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
GridView1.EditIndex = -1;
BindGridView();
}
Output:
