Wednesday, November 25, 2009

Bind GridView with MS Access Database table in Asp.net




Please visit my new Web Site WWW.Codedisplay.com



In many forums i found that developers struggle "To connect with MS Access Database from Asp.net". Thats why in this post i will show you how one can access MS Access database from Asp.net ASPX page to bind GridView. To do that first open any MS Access Database & then create below table:


Fig: Entity

And ofcourse copy your MS Access mdb Database into your application root folder.

Ok, Now our MS Access Database Article table is ready. So add an aspx page in your asp.net project & then drag & drop a GridView within the page. Named it by gvArticle. Full HTML Markup Language is given below:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="MSACCESSGV.aspx.cs" Inherits="MSACCESSGV" %>
<!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>Test MS ACCESS DATABASE CONNECTION</title>
</head>

<body>
<form id="form1" runat="server">
<div>

<asp:GridView ID="gvArticle" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Id" />
<asp:BoundField DataField="Category" HeaderText="Category" SortExpression="Title" />
<asp:BoundField DataField="Published" HeaderText="Published" SortExpression="Visit" />
<asp:BoundField DataField="Modified" HeaderText="Modified" SortExpression="Modified" />
</Columns>
</asp:GridView>

</div>
</form>

</body>
</html>

Now you need to write code in server side to bind GridView gvArticle. First make connection string for your MS Access Database. And then read data from MS Access database. Complete server side code is given below:

using System;
using System.Data;
using System.Web.UI;
using System.Data.OleDb;

public partial class MSACCESSGV : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

if (!IsPostBack)
{
string sFilePath = Server.MapPath("MSACCESS.mdb");
DataTable dt;
OleDbConnection Conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sFilePath + ";");

using (Conn)
{
Conn.Open();
OleDbCommand cmd = new OleDbCommand("SELECT * FROM ARTICLE", Conn);
OleDbDataAdapter oDA = new OleDbDataAdapter(cmd);
dt = new DataTable();
oDA.Fill(dt);
}

gvArticle.DataSource = dt;
gvArticle.DataBind();

}
}
}

Now run your page. And you will get an output like below:

Fig: Output

Hope now you can make connection with MS-Access Database & also can retrieve data from MS-Access from your asp.net application.

2 comments:

Alex said...

For me MS Access is sifnificant tool on my PC. Because some days ago I was working with it and something happened. And all my data was lost,for luck I asked a friend about tool which can solves like problem and he advised me - Access data Recovery. It helped me for a minute and I was very glad. Moreover it performed this action for free as far as I remember.

Aryan1989 said...

hai this is manesh,
good code friend, but here my fill() method is showing a exception as
"IErrorInfo.GetDescription failed with E_FAIL(0x80004005)"

do you have any idea regarding this error........ if so please reply to my mail id

---------------manesh561@gmail.com------------

Want to say something?
I WOULD BE DELIGHTED TO HEAR FROM YOU

Want To Search More?
Google Search on Internet
Subscribe RSS Subscribe RSS
Article Categories
  • Asp.net
  • Gridview
  • Javascript
  • AJAX
  • Sql server
  • XML
  • CSS
  • Free Web Site Templates
  • Free Desktop Wallpapers
  • TopOfBlogs
     
    Free ASP.NET articles,C#.NET,VB.NET tutorials and Examples,Ajax,SQL Server,Javascript,Jquery,XML,GridView Articles and code examples -- by Shawpnendu Bikash