CodeSnip: The Basics of MS Access with ASP.NET
page 1 of 1
Published: 08 Nov 2004
Unedited - Community Contributed
Abstract
This article for beginners presents a step-by-step approach on how to show data from MS Access on the web through ASP.NET. This opens up many avenues to share the data over web and network.
by Sameer Lal
Feedback
Average Rating: 
Views (Total / Last 10 Days): 15762/ 15

Note: I have assumed in this article that you have IIS running on your PC and have installed Visual Studio .NET.

 

Start Visual Basic .NET and create a new Visual Basic project, selecting "ASP.NET Web Application" from templates and name it "MyApplication" as shown below.

 

   

 

 

Now, create a new MS Access database, name it XYZ.mdb in C:\Inetpub\wwwroot\MyApplication directory. Create a table in the database, adding these fields: SlNo as AutoNumber, Sdate as Date/Time, Symbol as Text, and Message as Memo. Save the table as tblxyz and keep SlNo as primary key.

 

 

 

 

 

 

Now, go back to the Visual Studio project and refresh the Solution Explorer; you should be able to see XYZ.mdb in there, once you choose to Show All Files from the Project menu.

 

Add a new web form to the project by clicking on Project menu and then Add New WebForm. This WebForm will be called WebForm1.aspx; you can always rename it to make more sense out of name.

Ensure you're in Design view.  Open the Toolbox and drag and drop an OleDBConnection and an OleDBCommand from the Data tab and one DataGrid from the Web Forms tab onto the form design surface.

 

OleDBConnection and OleDBCommand will be named as oleDBConnection1 and oleDBCommand1, which can be renamed. To do this, single-click on oleDBConnection1 and go to Properties (or press F4). While there, set up the connection.  Look under Data for ConnectionString and select Create New Connection: You will see a window pop up for Data link properties. Select Microsoft Jet 4.0 OLE DB Provider as below:

 

 

 

Click Next; you will then be on the Connection tab.  Use the "..." browse button to find and select XYZ.mdb from C:\Inetpub\wwwroot\MyApplication\XYZ.mdb and then click on Test Connection.

 

 

 

 

You will see a confirmation box saying the test succeeded. You are now set with your connection, so say OK to Data link properties, and you will be given a choice to include the password in the connection string. Choose not to include the password (for learning purposes).

 

Now single-click on oleDBCommand1. In the Properties window, under Behavior, choose existing connection and select oleDBConnection1. Under Data, type in the command text as Select * from tblxyz;" and under Command Type, keep it as Text.

 

Note: You can write the same query in the MS Access database, name it as (MyQuery or whatever), use the name MyQuery in CommandText property and for the Command Type property, select Stored Procedure--it will work the same way as with above option.

 

Finally, go to the Webform1.aspx.vb page, which is called the code-behind page, and type in the following code in the Private Sub Page_Load method:

 

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        'Put user code to initialize the page here

        If Not Page.IsPostBack Then

            DataBind1()

 

        End If

    End Sub

 

And create another sub like this:

 

    Sub DataBind1()

        Dim adp As New OleDb.OleDbDataAdapter(OleDbCommand1)

        Dim ds As New DataSet

        adp.Fill(ds)

        DataGrid1.DataSource = ds

        DataGrid1.DataBind()

 

    End Sub

 

 (Also, don’t forget to enter some data into your database.)

 

Hit F5 on your keyboard.  This action will save your whole project and run the project's start page in the web browser.  You may need to set the project's start page by right-clicking on the WebForm1.aspx file and choosing Set as Start Page.  Using F5 attaches the debugger, so if you just want to run wihout debugging, you can use CTRL-F5.

 

Once the project runs, will see your DataGrid filled with database items on the web browser. This is how simple it is to display your Access data on the Web. You can customize your DataGrid by right-clicking on it and going to Property Builder. Also, you can automatically format the DataGrid by right-clicking and selecting Auto Format.

 

In other articles, we will discuss how to customize what you want to show in the DataGrid and in what format. Also, we will see, how to make data entry through the Web and edit that data online.



User Comments

Title: thanks   
Name: Hai Nguyen
Date: 2009-11-10 5:29:49 PM
Comment:
great article .I learned a lot.
Title: Meri Abhivyakti   
Name: Deepak Kumar Bhanre
Date: 2008-12-05 6:23:41 AM
Comment:
Sir , First time , I visited this site. Nice Article. thanks for this article!, it really helped me a lot .
Title: thanks   
Name: pratima
Date: 2008-10-14 1:48:40 PM
Comment:
thanks a lot, it's so helpful.can u please help me the same way to connect the ms-access field to textbox to retrieve data into it and update the data back from textbox to database. my mail-id is baitipuli@yahoo.co.in
Title: thanks   
Name: Milad Hamid
Date: 2008-04-30 8:27:55 AM
Comment:
Hi guys
thanks a lot for your efforts
i am greateful
Title: cide dieos not work   
Name: manojkumar
Date: 2008-02-25 4:24:55 AM
Comment:
\
\
Title: Good brief article   
Name: Kara
Date: 2007-10-10 6:24:19 AM
Comment:
Looks pretty easy
Title: thanks   
Name: ranji
Date: 2007-07-24 2:29:49 PM
Comment:
it helped me alot
Title: adapter is not working   
Name: Sini
Date: 2007-06-08 7:50:39 AM
Comment:
I am working in asp.net using c sharp.I do the steps as described here. But there is an error occuring in the adp.Fill(ds); . Please help me.

I am using Microsoft access 2003
Title: hi   
Name: samer
Date: 2006-09-20 5:22:17 AM
Comment:
nice site
Title: Thanks!   
Name: Aliguyon
Date: 2006-08-08 2:08:33 AM
Comment:
thanks for this article!, it really helped me a lot
Title: Error   
Name: Raouf
Date: 2006-06-05 11:19:56 PM
Comment:
I got an error saying "the connection you have already created doesn't work with current adapter"
Title: execelent   
Name: maneesh
Date: 2006-05-22 6:38:44 AM
Comment:
its very good approach!!!!!!!!!!!
Title: Good Morning!   
Name: Mark Anthony
Date: 2006-04-16 10:41:11 PM
Comment:
i exactly followed the steps you have given in your article and i am having problem with that. the error shows that the database (MS ACCESS) file i'm trying to access is exclusively opened by another user, or i need permission to open it yet i am the only person opening the database alone. could you help me resolve my problem? thanks. kindly mail me at hunk_marc_16@yahoo.com. thank you very much..
Title: Need Help   
Name: Totally lunatic
Date: 2006-04-10 2:46:17 PM
Comment:
someone help me...i'm a newbie, i have an assignment which my lecturer give to me... combining with any database application... if i using MS Access.. i do not know how to bind the data to the control... such as text box... or if i use SQL Server... the problem is still the same... don't know how to bind the data to the control... many book and or article i have search only show how to bind the data to the datagrid which is not good looking in term of Good GUI... so... anyone can please help me.. email me diligence@cashette.com...REALLY NEED HELP...! by the way... the article help me how to connect with ms access... thanks anyway...
Title: OleDb cannot open the DB   
Name: Ovais Khan
Date: 2006-03-25 2:38:37 AM
Comment:
Hey Pal
im Having the same problem as Muskan. in the load method i give the fill(ds) command and it says "It is already opened exclusively by another user, or you need permission to view its data".
thanks
Title: Nice Article   
Name: Muskaan
Date: 2006-02-03 2:14:43 AM
Comment:
Hello Pal,
I am using a Excel File and converting it into XML.
But m getting the following error:
Error Message : The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.

ALso, I have cheked the Permissions for the excel file.
still its not working.
Pls help.

Regards,
Muskaan.
Title: DataSet   
Name: Masood
Date: 2006-01-04 11:43:30 AM
Comment:
Hi
I wanted to make a typed dataset without drag and drop in vs.net for asp.net application and wanted the full mapping of constraints in the datatable
can anybody help me in this regard
thanks
Title: tanx u   
Name: sathi
Date: 2006-01-03 1:08:30 AM
Comment:
Hi im a beginner, tis article was so helpful to me.tanxs alot. But still i need codes for inserting,deleting,updating records in the db.
Tanxs again!!!!!!!!!!!!!!!!!!!!!!
Title: Shahzad   
Name: Sameer
Date: 2006-01-01 5:27:26 PM
Comment:
You have to permission the folder and/or database.Right click on folder in which DB is sitting and in security tab, allow full control to everyone or may be to ASP Machine account.
Title: Dev   
Name: Moonis Tahir
Date: 2005-12-07 5:10:29 PM
Comment:
if i place MDB file at shared folder then oledbconnection is not established in ASP.net. at design time it verify it but at run time it says either path does not exist or file is not there. but every thing work fine if i run this code in window form.
Title: Error   
Name: Sameer
Date: 2005-11-26 8:57:20 AM
Comment:
Hi keegan

Did you configure your project on IIS.
Go to control panel,administrative tools, Internet service manager,
Double click and in left nav, click on + sign on your computer name,
then, on + of default web site, find your project, right click, click on properties,
under directory tab, under application setting, hit on creat button and then, close.
I guess, you should be alright then.Let me know at sameerlal@accountant.com
Title: Error   
Name: keegan
Date: 2005-11-23 7:28:23 PM
Comment:
Hi, thanks for the article, it is very straight foward. I am receiving error when trying to view as well. "Server Error in '/accessTrial' Application." Basically thinks that the database is in use or not available. I have checked permissions and closed everything down. any help would be great..
Title: Anjali's reply   
Name: Sameer
Date: 2005-11-04 8:41:34 PM
Comment:
Did you set the permissioning,Anjali.Right click on your access file and folder and permission everyone full control and see what happens.:)
Title: Great Job!   
Name: Ashley
Date: 2005-09-27 9:29:25 AM
Comment:
I was looking for this step by step procedure and thanks alot for this
Title: Need bit more   
Name: Popme
Date: 2005-05-20 11:00:26 AM
Comment:
Still I am looking for codes which can help me to edit or add new rows to the database. And To be honest with you this article is very good. Thanks for ur efford. Still wondering if we upload this file to NET how it will work. What should be the place to xyz.mdb database.
My email is sirajz@gmail.com

thanks
popme
Title: nice one   
Name: priya
Date: 2005-04-05 9:40:04 AM
Comment:
hi
iam a beginner & ur article was a gr8 help 4 me
Title: MS Access   
Name: russell
Date: 2004-11-13 7:42:46 AM
Comment:
Great Article - the equivalent article for SQL connections would be useful as well.
Title: Memo   
Name: Chip
Date: 2004-11-11 11:04:50 PM
Comment:
Lets see how this works then

Really like all your advice

Check
Title: Thanks   
Name: Jonathan
Date: 2004-11-10 5:18:50 PM
Comment:
Hi i am a beginner and everytime i connect to Access i forget the steps. So this article is definetely in my bookmarks for future reference as this is something so common that most web sites do not bother documenting it.
Thanks again !!
John
Title: Great article   
Name: Tim
Date: 2004-11-09 10:04:18 PM
Comment:
This is what I was looking for a long time.

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-29 5:19:42 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search