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

Nilesh Jadav

21 Comments

Join the conversation! Your thoughts help the community grow.

Shasahnk Kuchibhotla8y ago
I'm getting the following exception: + $exception {"The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."} System.InvalidOperationException
Arul R11y ago
Good one !!!
- Nilesh Jadav11y ago
  Thank you Arul R
Nilesh Jadav11y ago
Malakondareddy Kurapati is it solved ??
Malakondareddy Kurapati11y ago
I got an error like "Could not find installable ISAM" . Could you please help me to solve this error ?
- Malakondareddy Kurapati11y ago
  thank you so much :)
- Nilesh Jadav11y ago
  hey there ! 1down vote accepted your connection string is not proper. that is main reason this error comes. Try to correct connection string syntax as per http://www.connectionstrings.com/access/ Try with physical path like "C:\studentu-db.accdb" and if you are trying to locate access database from local folder then write "|DataDirectory|\studentu-db.accdb"
Karthikeyan K11y ago
Good one sir...Thanks for sharing
- Nilesh Jadav11y ago
  Thank you Karthikeyan K
Pankaj Kumar Choudhary11y ago
Nice Explain Sir.........
- Nilesh Jadav11y ago
  Thank you Pankaj Kumar Choudhary
Shridhar Sharma11y ago
good one Nilesh.
- Nilesh Jadav11y ago
  Thank you Shridhar Sharma
Santhakumar Munuswamy11y ago
Good One. Thanks for sharing
- Nilesh Jadav11y ago
  Thank you Santhakumar Munuswamy
Mohammed Ibrahim11y ago
nice
- Nilesh Jadav11y ago
  Thank you Mohammed Ibrahim
Rakesh11y ago
Good one
- Nilesh Jadav11y ago
  Thank you Rakesh Chavda
Rajeesh Menoth11y ago
Nice Share
- Nilesh Jadav11y ago
  Thank you Rajeesh Menoth

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.

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.

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

new connection

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:

<%@ 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></title>
<style type="text/css">
.style1
{
text-decoration: underline;
color: #0000FF;
font-size: large;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<span class="style1"><strong>Gridview Databind using MS Access Database</strong></span><br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataKeyNames="id" BackColor="White" BorderColor="White"
BorderStyle="Ridge" BorderWidth="2px" CellPadding="3" CellSpacing="1"
GridLines="None">
<Columns>
<asp:TemplateField HeaderText="ID">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("id") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("id") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="First Name">
<EditItemTemplate>
<asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("fname") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("fname") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Last Name">
<EditItemTemplate>
<asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("lname") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("lname") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="City">
<EditItemTemplate>
<asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("city") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Bind("city") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#C6C3C6" ForeColor="Black" />
<HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#E7E7FF" />
<PagerStyle BackColor="#C6C3C6" ForeColor="Black" HorizontalAlign="Right" />
<RowStyle BackColor="#DEDFDE" ForeColor="Black" />
<SelectedRowStyle BackColor="#9471DE" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F1F1F1" />
<SortedAscendingHeaderStyle BackColor="#594B9C" />
<SortedDescendingCellStyle BackColor="#CAC9C9" />
<SortedDescendingHeaderStyle BackColor="#33276A" />
</asp:GridView>
</div>
</form>
</body>
</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:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
refreshdata();
}
}
public void refreshdata()
{
OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\nilu\Desktop\Database1.accdb");
OleDbCommand cmd = new OleDbCommand("select * from tbl_data",con);
OleDbDataAdapter olda = new OleDbDataAdapter(cmd);
DataTable dt = new DataTable();
olda.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}

Output chamber

Output

Hope you liked it. Thank you for reading.

Have a good day!