Introduction:
Here I will explain how to insert data into ms access database in asp.net using c#, vb.net or get or fetch or retrieve data from ms access database in asp.net using c#, vb.net.
Here I will explain how to insert data into ms access database in asp.net using c#, vb.net or get or fetch or retrieve data from ms access database in asp.net using 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
|
|||
1 comments :
Office 2007 uses the same OLE DB driver. In fact I tested it out and proved successful. I tried "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\Employee.accdb" with Access 2007.
Note: Only a member of this blog may post a comment.