Jitesh Byahut 12/10/2014 2572

How To Insert New Row In 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 inserting one by one new row in Access.

Points Of Remember:

1. Place LinkButton Insert in FooterTemplate of Gridview's first column.

2. Fire GridView's SelectedIndexChanging event for LinkButton Insert.

3. Set GridView's AutoGenerateDeleteButton propery to false.

4. Set GridView's DataKeyNames Property to record ID (ID=Primary/Unique key of student table)

5. 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"

            DataKeyNames="ID" AutoGenerateColumns="false" ShowFooter="True"

              OnSelectedIndexChanging="GridView1_SelectedIndexChanging">

     <Columns>      

       <asp:TemplateField HeaderText="Sr. No.">

            <ItemTemplate>

                <%#Container.DataItemIndex+1 %>

            </ItemTemplate>

            <FooterTemplate>

               <asp:LinkButton ID="LkB1" runat="server" CommandName="Select">Insert</asp:LinkButton>

            </FooterTemplate>

       </asp:TemplateField>

        <asp:TemplateField HeaderText="Name">

             <ItemTemplate>

                  <asp:Label ID="Label1" runat="server" Text='<%# Eval("Name") %>'></asp:Label>

              </ItemTemplate>

              <FooterTemplate>

                  <asp:TextBox ID="txt_Name" runat="server"></asp:TextBox>

              </FooterTemplate>

        </asp:TemplateField>

         <asp:TemplateField HeaderText="Branch">

              <ItemTemplate>

                 <asp:Label ID="Label2" runat="server" Text='<%# Eval("Branch") %>'></asp:Label>

              </ItemTemplate>

              <FooterTemplate>

                 <asp:TextBox ID="txt_Branch" runat="server"></asp:TextBox>

              </FooterTemplate>

         </asp:TemplateField>

          <asp:TemplateField HeaderText="City">

               <ItemTemplate>

                   <asp:Label ID="Label3" runat="server" Text='<%# Eval("City") %>'></asp:Label>

               </ItemTemplate>

               <FooterTemplate>

                    <asp:TextBox ID="txt_City" runat="server"></asp:TextBox>

                </FooterTemplate>

          </asp:TemplateField>

        </Columns>

            <HeaderStyle CssClass="HeaderStyle" />

            <RowStyle CssClass="RowStyle" />

            <AlternatingRowStyle CssClass="AlternatingRowStyle" />

            <FooterStyle CssClass="FooterStyle" />

        </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 insert new row in GridView

    protected void GridView1_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)

    {

        // Retrieve values for insert

        TextBox txtname = (TextBox)GridView1.FooterRow.FindControl("txt_Name");

        TextBox txtbranch = (TextBox)GridView1.FooterRow.FindControl("txt_Branch");

        TextBox txtcity = (TextBox)GridView1.FooterRow.FindControl("txt_City");

 

        // Insert values into database

        OleDbCommand cmd = new OleDbCommand("insert into tblStudent(Name, Branch, City) values('"
 + txtname.Text + "','" + txtbranch.Text + "','" + txtcity.Text + "')", con);

        con.Open();

        cmd.ExecuteNonQuery();

        con.Close();

        // Refresh the GridView

        BindGridView();

    }

}

Demo:



Please give your feedback for improving this page