Tutorials
- ASP.NET
- C#
- C++
- Java
- JavaScript
- MySQL
- PHP
- Ruby
- SQL
- VB.NET
Code
How To
News
Forums
Pictures
Videos
Icons
Newsgroups

Building a C# Chat Client and Server

A step by step tutorial teaching you how to create your own chat client and chat server easily in C#, for local networks or the Internet.

in C# Programming Tutorials

Getting Hard Drive Information

A C# tutorial showing you how to make use of WMI to extract information on disk drives, such as model, capacity, sectors and serial number.

in C# Programming Tutorials

UPS Shipping Calculator

This tutorial will teach you how to calculate the shipping cost based on the weight, height, length and depth of the box, the distance and the UPS service type.

in PHP Programming Tutorials

Create Your Own Rich Text Editor

Creating a Rich Text Editor using JavaScript is easier to do than you might think, thanks to the support of modern browsers; this tutorial will walk you through it.

in JavaScript Programming Tutorials

Tutorials

Programming Tutorials

ActionScript (5)
ASP (7)
ASP.NET (28)
Assembly (1)
C (6)
C# (132)
C++ (33)
CSS (6)
General (4)
HTML (6)
Java (28)
JavaScript (38)
MySQL (2)
Objective C (1)
Pascal (1)
PHP (61)
Python (3)
Ruby (18)
Silverlight (1)
SQL (8)
VB.NET (5)
Visual Basic (3)

IT Jobs

From CareerBuilder

Geekpedia » C# Tutorials » ADO.NET and Access database I

ADO.NET and Access database I

How to connect to an Access database from Visual C# .NET, retrieve data and display it using a DataGrid with ADO.NET, using a dataSet.

On Sunday, June 13th 2004 at 07:09 AM

By Andrew Pociu (View Profile)

(Rated 4.6 with 98 votes)

Contextual Ads

More C# Resources

In the tutorial I assume you already know the basics of the SQL language.

First download the database we will use in this example and save it somewhere:

Start a new 'Windows Application' project named 'accessDB'.

From the View menu of Microsoft Visual C# .NET select Server Explorer, or press Ctrl+Alt+S.

From the list right-click Data Connections and choose 'Add Connection...'.

In the 'Provider' tab select Microsoft Jet 4.0 OLE DB Provider (used for connecting to an Access database), and click Next.

Use the '...' button to browse for an Access Database and choose the database you have downloaded, db1.mdb. After clicking OK, test the connection by clicking the 'Test Connection' button.

It should say 'Test connection succeeded.'.

Press OK and a window pops up that says 'Please Enter MS JET OLE DB Initialization Information'.

Leave the defaults and just press OK.

Just like you see in the above screenshot, you can browse the Access file database. Yet the database is not connected to our program. For this you need to drag the node below 'Data Connections' on the form.

The node is named using the form 'ACCESS.X:\PathToYourDatabase\file.mdb.Admin'. As I said, drag it on the form and 'oleDbConnection1' should appear below the form:

Next open the Toolbox (Ctrl+Alt+X) and from the 'Data' group drag an 'OleDbDataAdapter'. The 'Data Adapter Configuration Wizard' starts.

Clicking next will take you to the part where you need to select the connection you wish to use. Choose the connection we have just created (ends up in db1.mdb.Admin). Press Next and then again Next (leave the default 'Use SQL statements').

Now you are beeing asked 'What data should the data adapter load in the dataset?'. We want all the tables and all the columns therefore we need to take the following steps. Open the Query Builder using the button and you should now be able to add the two tables named 'books' and 'categs'. Add them and close the small window and now we have two small windows representing the two tables. We want to select all the columns, therefore check '* (All Columns)' on both tables.

The following SQL query is created:

SELECT books.*, categs.*

FROM (books INNER JOIN

categs ON books.ID = categs.ID)

We could also do it more simple by typing 'SELECT * FROM books, categs'... it would have been the same.

Press OK to exit the Query Builder and then press Finish.

Now add a DataSet to our application by dragging one from the Data group (Toolbox). Choose 'Untyped DataSet (no schema)'.

DataSets are used to store the query results with which we later populate the DataGrid.

Before we start coding we need something where we display the results. The DataGrid is perfect for that, so drag one from the Toolbox, the Windows Forms group.

Now doubleclick the form so we can go to the 'Form1_Load' event.

First we should set the SQL query:

oleDbDataAdapter1.SelectCommand.CommandText = "SELECT * FROM books, categs where books.bookCateg = categs.ID";

Next we clear the DataSet preparing it for the operation

dataSet1.Clear();

Next we use the 'Fill()' method of oleDbDataAdapter1 to fill the dataSet1 with the result of the SQL query:

oleDbDataAdapter1.Fill(dataSet1, "Books");

Finally we bind it using the DataGrid:

dataGrid1.SetDataBinding(dataSet1, "Books");

Here is all the code we wrote in one place:

private void Form1_Load(object sender, System.EventArgs e)

{

oleDbDataAdapter1.SelectCommand.CommandText = "SELECT * FROM books, categs where books.bookCateg = categs.ID";

dataSet1.Clear();

oleDbDataAdapter1.Fill(dataSet1, "Books");

dataGrid1.SetDataBinding(dataSet1, "Books");

}

ADO.NET and Access database II

Digg It!

Del.icio.us Reddit

StumbleIt

Newsvine

Furl

BlinkList

Rate this tutorial

Current Comments

by farhad on Thursday, April 28th 2005 at 03:58 AM

hello

by thearith on Wednesday, June 22nd 2005 at 06:48 AM

Can you give me a good hotel DBMS(Microsoft Access) code.thank any way.

by abrakadabra on Thursday, October 27th 2005 at 05:19 PM

chuttia aadmi, that does not work, saala..!

by Jared on Monday, January 2nd 2006 at 07:53 PM

I found this tutorial very helpful, saved me much time, One grip tho... for newbie’s. You might want to tell them to drag a dataGrid on the form. Otherwise awesome!!

by Jared on Monday, January 2nd 2006 at 07:53 PM

I found this tutorial very helpful, saved me much time, One grip tho... for newbie’s. You might want to tell them to drag a dataGrid on the form. Otherwise awesome!!

by Anup on Tuesday, March 7th 2006 at 07:32 AM

Its a very helpful and easy to learn tutorial

by premnath on Saturday, April 15th 2006 at 12:22 AM

This site is so nice to follow. thank u.
i need the code to connect to the access db in asp.net using c#.net

by Prasanna on Thursday, May 11th 2006 at 06:48 AM

Saves so much time - thanks

by Mohsin on Sunday, May 21st 2006 at 08:12 AM

Thanks for the tutorial

by Elliott on Monday, May 22nd 2006 at 12:26 AM

Thanks heaps for the help!!

by Karl on Wednesday, May 24th 2006 at 07:34 PM

That's all well & good, but how do I programmatically modify data inside an Access database?

by on Wednesday, August 23rd 2006 at 11:11 AM

This tutorial does NOT WORK! REPEATING-- THIS TUTORIAL DOES NOT WORK!

by DOES NOT WORK DOES N on Wednesday, August 23rd 2006 at 11:22 AM

This tutorial does NOT WORK! REPEATING-- THIS TUTORIAL DOES NOT WORK! The database gets LOCKED and you have to reboot for each time you try to run the tutorial. Also, if you are using ACCESS with default security settings -- Medium -- it cannot open the database AT ALL because it "might contain macros". Basically, this tutorial creates an airtight way for you to lock up every database on your computer!

THIS TUTORIAL DOES NOT WORK!

by Dima on Monday, October 9th 2006 at 02:11 PM

Great! Very helpful

by lokriek on Wednesday, January 10th 2007 at 04:42 PM

Excellent tutorial!

by indika on Monday, March 12th 2007 at 09:32 AM

it's good tut. but I wanted to know how to retrive data from data base and show them using text field/labels by separetly. not in data grid. when we want build address book or such application the data grid is useless.

by Anita on Tuesday, April 3rd 2007 at 07:06 AM

Great Job brother! I was stuck with the handling data grids thing for a whole 3 days.
You just solved my problem.

Thanks a million.

by vijay on Tuesday, May 15th 2007 at 01:18 AM

hi,
i could not able to drag the node on to the form , therefore i could not able to establish the oleDbConnection on the form ,
can i know the whats wrong with it .

by Anil Kishore on Wednesday, May 23rd 2007 at 06:55 AM

Ya, me too faced the same problem.
Even " Microsoft Jet 4.0 OLE DB Provider ", is not thr in my options :(
Why is this so ??
Is the above Tut. designed on .NET 2.0 (or) .NET3.0 ??

by Chandrashekhar Patil on Monday, September 3rd 2007 at 07:56 AM

how connect two database in c# with coding ?

by Success on Tuesday, October 9th 2007 at 04:35 AM

I can't drag the node on to the form, Does anyone perhaps know why?

by Gregor on Tuesday, November 20th 2007 at 06:42 AM

I have the same problem: I can't drag the node on to the form, Does anyone perhaps know why?

by Joseph on Monday, November 26th 2007 at 04:43 PM

Easy to understand -- GREAT JOB !

by yunus on Tuesday, February 5th 2008 at 08:51 AM

thanks 4 the tutorial... i just wanna a code for retriving code from a database ant translating them,......

by flux84 on Tuesday, March 4th 2008 at 07:29 AM

I also can't drag node to the form.

by wanda on Sunday, March 16th 2008 at 12:03 AM

Can't drag the node because only VS2003 can do that. Not for VS2005

by omur on Friday, June 13th 2008 at 05:19 AM

I dont understand these lines or Ä± couldnt "For this you need to drag the node below 'Data Connections' on the form.

The node is named using the form 'ACCESS.X:\PathToYourDatabase\file.mdb.Admin'. As I said, drag it on the form and 'oleDbConnection1' should appear below the form:oledbconnection1"

by Arijit on Tuesday, July 29th 2008 at 03:56 PM

Guys,
I think this one wont work the way it is shown here. I was also stuck trying to follow the author's description. Using Visual Studio 2005 is a bit different than wat's explained.
First make a few changes like go to Toolbox and right click on the 'Data' Section. Remeber to rt. click right on the heading of the section. Select 'Choose Items'>Choose Toolbox Items> .NET Framework Components tab. Here select whatever OleDbs you need to choose. Click OK and the OleDb components will become available in the Data Section. Do the same rt. click on the General Section and select the DataGrid component for assembly System.Windows.Forms(2.0.0.0). This will give you the datagrid. As you can understand you can similarly add whatever component your toolbox is missing from the description. Now add the OleDBConnection1 and go to the properties of it. In the ConnectionString property provide the db1.mdb. Well this makes the connection of your form with the Database. This is the same thing as drag and drop concept. The rest all things are pretty self explanatory. VS2005 has modified many things. But the older components of Framework1.1 are not lost. You can add it when you need it.

by sofiene on Wednesday, November 5th 2008 at 04:28 AM

thanks

by Sanjeewa on Thursday, December 4th 2008 at 09:25 AM

Arijit,

I was also stuck...
Arijit You Completed the job,

Great....

by Sanjeewa on Thursday, December 4th 2008 at 09:25 AM

Arijit,

I was also stuck...
Arijit You Completed the job,

Great....

by Mary-Ann Maniquiz on Wednesday, December 10th 2008 at 01:22 AM

Hi there can you help me with this one. i create a new sql 3.5 using visual 2008 then connect it to the new project my most concern is, how can i connect it and modify the content of each row in column using find, update, add etc in command button?

thanks waiting for your reply in my email

by Khan on Tuesday, May 5th 2009 at 01:19 PM

dataGrid1.SetDataBinding(dataSet1, "Books");
this line does not work in visual studio 2005. whatz the solutions?

by Khan on Tuesday, May 5th 2009 at 01:19 PM

dataGrid1.SetDataBinding(dataSet1, "Books");
this line does not work in visual studio 2005. whatz the solutions?

by Khan on Tuesday, May 5th 2009 at 01:19 PM

dataGrid1.SetDataBinding(dataSet1, "Books");
this line does not work in visual studio 2005. whatz the solutions?

by dree on Friday, July 10th 2009 at 07:19 AM

Cool.

by Shahriar Nour Khondokar on Wednesday, March 31st 2010 at 02:26 AM

Check this link ( http://www.shahriarnk.com/Shahriar-N-K-Research-Embedding-SQL-in-C-Sharp-Java.html ) for details on how to connect to SQL server database from Java database applications as well as C#.NET database applications. It also describes how to pass embedded SQL queries, calling stored procedures, pass parameter etc.

Shahriar Nour Khondokar: http://www.shahriarnk.com/