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

Insert & Fetch Data from Access Database in Asp.net using C#, VB.NET

Mar 9, 2015
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.


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

1 comments :

Zaid Khan said...

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.

Give your Valuable Comments

Other Related Posts

© 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.