Jitesh Byahut 8/10/2014 5156

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:\Asphelps 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();

    }

 

}

Demo:



Please give your feedback for improving this page