Bind GridView With MS Access Database In ASP.NET C#

We will make our database in MS Access and bind that data to our GridView.

Initial chamber

Step 1: Open Visual Studio 2010 and create an empty website and give it a suitable name Gridview_demo

Step 2: In Solution Explorer you will get your empty website, Add a web form by going like the following:

For Web Form:

Gridview_demo (Your Empty Website) - Right click and Add New Item Web Form. Name it Gridview_demo.aspx.

Database chamber

Open your MS Access 2007 or whatever version you have and click on Blank Database, then Create.

Open your MS Access 2007

blank database

After creating a database in the left pane you see your default table, then right click on that table and go to Design View. Here in Design view we will add some entity to our table.

table design

When you will get the complete design, again go to the table [tbl_data] and right click on Datasheet View. Feed some data in those fields, so that we can bind our data.

table

Creating Connection between MS Access and Visual Studio

Open Visual Studio, then in Server Explorer go to Connect to Database and Add Connection window will open like the following image.

Add Connection

Click on Change, you will get a new window of Change Data Source as in the following image.

new connection

Change Data Source

Here you saw Microsoft Access Database been taken as Data source because we have access database with us. After that you have to browse the database (access database) that you saved, browse it and click Test Connection. If it says Test Connection succeeded, then you can go ahead with the other process, otherwise you have to repeat the process.

Design chamber

Step 3: Open Gridview_demo.aspx file where we will create our design for binding GridView.

Gridview_demo.aspx:

  1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>  
  2.   
  3. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
  4.   
  5. <html xmlns="http://www.w3.org/1999/xhtml">  
  6. <head runat="server">  
  7.     <title></title>  
  8.     <style type="text/css">  
  9.         .style1  
  10.         {  
  11.             text-decoration: underline;  
  12.             color: #0000FF;  
  13.             font-size: large;  
  14.         }  
  15.     </style>  
  16. </head>  
  17. <body>  
  18.     <form id="form1" runat="server">  
  19.     <div>  
  20.       
  21.         <span class="style1"><strong>Gridview Databind using MS Access Database</strong></span><br />  
  22.         <br />  
  23.       
  24.         <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"   
  25.             DataKeyNames="id" BackColor="White" BorderColor="White"   
  26.             BorderStyle="Ridge" BorderWidth="2px" CellPadding="3" CellSpacing="1"   
  27.             GridLines="None">  
  28.             <Columns>  
  29.                 <asp:TemplateField HeaderText="ID">  
  30.                     <EditItemTemplate>  
  31.                         <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("id") %>'></asp:TextBox>  
  32.                     </EditItemTemplate>  
  33.                     <ItemTemplate>  
  34.                         <asp:Label ID="Label1" runat="server" Text='<%# Bind("id") %>'></asp:Label>  
  35.                     </ItemTemplate>  
  36.                 </asp:TemplateField>  
  37.                 <asp:TemplateField HeaderText="First Name">  
  38.                     <EditItemTemplate>  
  39.                         <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("fname") %>'></asp:TextBox>  
  40.                     </EditItemTemplate>  
  41.                     <ItemTemplate>  
  42.                         <asp:Label ID="Label2" runat="server" Text='<%# Bind("fname") %>'></asp:Label>  
  43.                     </ItemTemplate>  
  44.                 </asp:TemplateField>  
  45.                 <asp:TemplateField HeaderText="Last Name">  
  46.                     <EditItemTemplate>  
  47.                         <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("lname") %>'></asp:TextBox>  
  48.                     </EditItemTemplate>  
  49.                     <ItemTemplate>  
  50.                         <asp:Label ID="Label3" runat="server" Text='<%# Bind("lname") %>'></asp:Label>  
  51.                     </ItemTemplate>  
  52.                 </asp:TemplateField>  
  53.                 <asp:TemplateField HeaderText="City">  
  54.                     <EditItemTemplate>  
  55.                         <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("city") %>'></asp:TextBox>  
  56.                     </EditItemTemplate>  
  57.                     <ItemTemplate>  
  58.                         <asp:Label ID="Label4" runat="server" Text='<%# Bind("city") %>'></asp:Label>  
  59.                     </ItemTemplate>  
  60.                 </asp:TemplateField>  
  61.             </Columns>  
  62.             <FooterStyle BackColor="#C6C3C6" ForeColor="Black" />  
  63.             <HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#E7E7FF" />  
  64.             <PagerStyle BackColor="#C6C3C6" ForeColor="Black" HorizontalAlign="Right" />  
  65.             <RowStyle BackColor="#DEDFDE" ForeColor="Black" />  
  66.             <SelectedRowStyle BackColor="#9471DE" Font-Bold="True" ForeColor="White" />  
  67.             <SortedAscendingCellStyle BackColor="#F1F1F1" />  
  68.             <SortedAscendingHeaderStyle BackColor="#594B9C" />  
  69.             <SortedDescendingCellStyle BackColor="#CAC9C9" />  
  70.             <SortedDescendingHeaderStyle BackColor="#33276A" />  
  71.         </asp:GridView>  
  72.       
  73.     </div>  
  74.     </form>  
  75. </body>  
  76. </html>  
Your design will look like the following screenshot:

design

Code chamber

Step 4: Open Gridview_demo.aspx.cs and write some code so that our application starts working.

Gridview_demo.cs:
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.Data;  
  8. using System.Data.OleDb;  
  9.   
  10. public partial class _Default : System.Web.UI.Page  
  11. {  
  12.     protected void Page_Load(object sender, EventArgs e)  
  13.     {  
  14.   
  15.         if (!Page.IsPostBack)  
  16.         {  
  17.             refreshdata();  
  18.         }  
  19.     }  
  20.   
  21.     public void refreshdata()  
  22.     {  
  23.   
  24.   
  25.         OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\nilu\Desktop\Database1.accdb");  
  26.         OleDbCommand cmd = new OleDbCommand("select * from tbl_data",con);  
  27.         OleDbDataAdapter olda = new OleDbDataAdapter(cmd);  
  28.         DataTable dt = new DataTable();  
  29.         olda.Fill(dt);  
  30.           
  31.       
  32.         GridView1.DataSource = dt;  
  33.         GridView1.DataBind();  
  34.    
  35.     }  
  36.   
  37.   
  38. }  
Output chamber

Output

Hope you liked it. Thank you for reading.
Have a good day!