Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

How to Connect MS Access Database in Asp.net using C#, VB.NET

Mar 9, 2015
Introduction:

Here I will explain how to connect ms access database in
asp.net using c#, vb.net. To use Microsoft (MS) access database to insert, update, delete data in asp.net we need to use System.Data.OleDb namespace reference in c#, vb.net.


To use ms access database in asp.net first we need to create database in ms access for that follow below steps.

First Open Access 2013 and Create new database called “Sampledb” for that Open Access 2013 à select Blank Desktop Database à Give Name like “Sampledb” and click Create like as shown below


Now right click on your table and select Design view to design table with required columns like as shown below




Now add required fields in your table with required data type like as shown below




Now we will try to insert data into access database userdetails table for that open your aspx page and write the code like as shown


<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Connect Ms Access Database in Asp.net using C#, VB.NET</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table align="center">
<tr>
<td>UserName:</td>
<td><asp:TextBox ID="txtUsername" runat="server"/></td>
</tr>
<tr>
<td>Education:</td>
<td><asp:TextBox ID="txtEducation" runat="server"/></td>
</tr>
<tr>
<td>Location:</td>
<td><asp:TextBox ID="txtLocation" runat="server"/></td>
</tr>
<tr>
<td></td>
<td><asp:Button ID="btnSubmit" Text="Submit" runat="server" onclick="btnSubmit_Click"/></td>
</tr>
<tr>
<td><b>UserDetails:</b></td>
<td>
<asp:GridView ID="gvDetails" runat="server"></asp:GridView>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>

C# Code


using System;
using System.Data.OleDb;
using System.Data;

VB.NET Code


Imports System.Data.OleDb
Imports System.Data

After completion of adding namespaces you need to write the code like as shown below in code behind

C# Code


OleDbConnection con;
OleDbCommand cmd;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
using (con = new OleDbConnection(@"PROVIDER=Microsoft.ACE.OLEDB.12;" + @"DATA SOURCE=E:\accessdb\Sampledb.accdb"))
{
cmd = new OleDbCommand();
cmd.CommandText = "insert into userdetails(UserName,Education,Location)values(" + txtUsername.Text + "," + txtEducation.Text + "," + txtLocation.Text + ")";
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
txtUsername.Text = "";
txtEducation.Text = "";
txtLocation.Text = "";
}
BindUserDetails();
}
protected void BindUserDetails()
{
DataSet ds = new DataSet();
string strquery = "SELECT * FROM userdetails";
using (con = new OleDbConnection(@"PROVIDER=Microsoft.ACE.OLEDB.12;" + @"DATA SOURCE=E:\accessdb\Sampledb.accdb"))
{
using (cmd = new OleDbCommand(strquery, con))
{
OleDbDataAdapter Da = new OleDbDataAdapter(cmd);
Da.Fill(ds);
}
}
gvDetails.DataSource=ds;
gvDetails.DataBind();
}
VB.NET Code


Imports System.Data.OleDb
Imports System.Data
Partial Class VBCode
Inherits System.Web.UI.Page
Private con As OleDbConnection
Private cmd As OleDbCommand
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
End Sub
Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As EventArgs)
Using con = New OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12;" + "DATA SOURCE=E:\accessdb\Sampledb.accdb")
cmd = New OleDbCommand()
cmd.CommandText = "insert into userdetails(UserName,Education,Location)values(" + txtUsername.Text + "," + txtEducation.Text + "," + txtLocation.Text + ")"
cmd.Connection = con
con.Open()
cmd.ExecuteNonQuery()
con.Close()
txtUsername.Text = ""
txtEducation.Text = ""
txtLocation.Text = ""
End Using

BindUserDetails()
End Sub

Protected Sub BindUserDetails()
Dim ds As New DataSet()
Dim strquery As String = "SELECT * FROM userdetails"
Using con = New OleDbConnection("PROVIDER = Microsoft.ACE.OLEDB.12; "+"DATA SOURCE =E:\accessdb\Sampledb.accdb")
Using cmd = New OleDbCommand(strquery, con)
Dim Da As New OleDbDataAdapter(cmd)
Da.Fill(ds)
End Using
End Using
gvDetails.DataSource = ds
gvDetails.DataBind()
End Sub
End Class

If you observe above oledb connection I used like “@"PROVIDER=Microsoft.ACE.OLEDB.12;" + @"DATA SOURCE=E:\accessdb\Sampledb.accdb"” because I have office 2013 or Access 2013

Note: If you have Access 2007 or Access 2010 you need to use oledb connection like as shown below


con = new OleDbConnection(@"PROVIDER=Microsoft.Jet.OLEDB.4.0;" + @"DATA SOURCE = E:\accessdb\Sampledb.accdb")
Demo


How to Connect MS Access Database in Asp.net using C#, VB.NET

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

8 comments :

Unknown said...

Sir, I am quite new in ASP.NET with c# ,as i think its a very interesting language but i am new in there. I had done two projects in ASP.NET but still i get confused in here. At that time i am not paying much more attention as a result i felt guilty that i am not familier in this language.Sir please help me to learn from basic to become a asp.net programmer.
I study your all asp.net tutorials from your blog.
email:- bappa.dhanbad@rediffmail.com

Anonymous said...

very nice....

Aravind L said...

Thank you giving wonderful and valuable notes. I done my project using your tutorial.

neeraj said...

sir, my 2003 db is password protectd pl. help about it.

I have googled but not found correct solution.
I m using this string in web.confg

with this even in gridview I am getting error as Incorrect Password, though I have saved correct password in sqldatasource for gridview.

bhushan98@gmail.com

Unknown said...

Hello, thanks for your tutorial, but there's an error

"The supplier 'Microsoft.ACE.OLEDB.12' is not registered on the local computer."

I change the platform from "Any CPU" to "x86" but the nothing changed

PS: I'm using (Windows 7 64bits, Office 2013 64 bits, visual studio 2012).

Anonymous said...

thank you

Unknown said...

how to extract image and save it to a file with asp.net or vb.net in server side ?

neha jangid said...

i have a run time error in this line...
cmd.ExecuteNonQuery();
c# code

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.