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.
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.
Description:
In previous articles I explained jQuery drag and drop gridview rows in asp.net, jQuery custom dropdown with images in asp.net, jQuery multi select dropdownlist with checkboxes, Dynamically create temporary table in asp.net using c#, vb.net, export datagridview data to excel in windows application and many more articles related to asp.net using c#, vb.net. Now I will explain how to connect ms access database in asp.net using c#, vb.net.
In previous articles I explained jQuery drag and drop gridview rows in asp.net, jQuery custom dropdown with images in asp.net, jQuery multi select dropdownlist with checkboxes, Dynamically create temporary table in asp.net using c#, vb.net, export datagridview data to excel in windows application and many more articles related to asp.net using c#, vb.net. Now I will explain how to connect ms access database in asp.net using 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
 |
|
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 Email
|
|||
8 comments :
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
very nice....
Thank you giving wonderful and valuable notes. I done my project using your tutorial.
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
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).
thank you
how to extract image and save it to a file with asp.net or vb.net in server side ?
i have a run time error in this line...
cmd.ExecuteNonQuery();
c# code
Note: Only a member of this blog may post a comment.