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 II

ADO.NET and Access database II

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

On Monday, June 14th 2004 at 07:22 AM

By Andrew Pociu (View Profile)

(Rated 4.3 with 27 votes)

Contextual Ads

More C# Resources

The first part of the tutorial is similar to the one in the tutorial named 'Connecting to an Access database', although it's recommended to read that tutorial first as it is a bit more simple.

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

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

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.

The main reason of this tutorial is to demonstrate how to add the values in a Access database in a listBox. Therefore create a new listBox (listBox1).

Also create a button named 'btnUpdate' and with the caption 'Update'. Double click it to get to the btnUpdate_Click event.

First use the following code:

// Set the query

oleDbDataAdapter1.SelectCommand.CommandText = "SELECT * FROM books ";

// Prepare the dataset

dataSet1.Clear();

// Fill dataSet1 with result from the query

oleDbDataAdapter1.Fill(dataSet1, "Books");

The code is explained in this tutorial.

Now we create a DataTable named dTable and assign it the collection of data stored by dataSet1:

DataTable dTable = dataSet1.Tables[0];

Method 1 - binding the listBox

This is the way you should do it, although the second method isn't that bad data binding is specially built for this therefore you should use it:

// From where the control will get items

listBox1.DataSource = dTable;

// The column to display

listBox1.DisplayMember = "bookName";

// The column that holds the value

listBox1.ValueMember = "ID";

As you can see, we just set a few properties
and we are done.

Method 2 - doing it manually

The second method is by populating the listBox1 by looping through the DataTable:

for(int x = 0; x < dataSet1.Tables[0].Rows.Count; x++)

{

listBox1.Items.Add(dTable.Rows[x][2].ToString());

}

At dTable.Rows[x][2].ToString() x is the row number and 2 is the column number. x increases every loop because we want to loop through the whole table. 2 represents the second column which in our database is 'bookName', the column that stores the name of the book (first column is the one that stores the ID).

Either method you choose, this is the result:

Digg It!

Del.icio.us Reddit

StumbleIt

Newsvine

Furl

BlinkList

Rate this tutorial

Current Comments

by Nicolae Fieraru on Saturday, January 22nd 2005 at 09:37 PM

Hi, thank you very much for all the freeware tutorials. I just start working with C# and I found them very useful. For the tutorial 62 (ADO .Net and Access databases), it would be good if you would show multple fields from a table as well, or to provide a link to another article which shows how to do that.
Regards,
Nicolae

by Daryl on Tuesday, February 15th 2005 at 06:50 AM

Good tut! Just perfect for helping me with the basics while converting from Borland to .NET.
Thanks!

by Mohamadie on Friday, February 25th 2005 at 03:47 PM

i want more articles about GDI+ using vb.net
i'm in 3rd computer science from egypt
my doctor of AI wants aproject has graphic and animation.
please:
send me several articles about my reuirement
www.powerearth.4t.com
Thank u.

by Jol on Monday, March 14th 2005 at 08:36 AM

Brilliant, this is simple and yet very educational. you must start with the basis and then build on that not put a entire complex program on display and expect novices to understand it well done

by Guenter on Monday, March 21st 2005 at 12:34 PM

I like this tutorial, because it is not some click-cut-paste machinery, but an good and easy intoduction!

by mostafa on Wednesday, June 1st 2005 at 02:19 PM

I want to have save,delete,next,previous,last,first button in my form with data access . If it possible , give a example with its code . Thanks

by Charley on Thursday, July 14th 2005 at 11:35 AM

Really useful!!

by Vivek on Tuesday, January 31st 2006 at 02:45 PM

This was indeed very useful. I think you can add tutorials on how to add records to the existing database.

by Diman on Tuesday, February 14th 2006 at 01:17 AM

Great tut ! Quick and easy, I spent $103 on the book that was not NEARLY as helpfull as this tutorial ! Thanks a lot !!!

by vinayak on Monday, February 20th 2006 at 01:53 AM

great one!really enjoyes doin it

by Dreik on Friday, November 24th 2006 at 12:38 PM

JUST AMAZING!!!!

IT WAS SO USEFULL TO ME

THKS

by Nick on Saturday, July 28th 2007 at 12:48 PM

Hey i'm new to all of this but just started programming with C#....i'm having trouble clicking and dragging the node onto the form....anyone know why?? Thanks

by cevnthn0017 on Thursday, September 27th 2007 at 11:35 AM

nice tutorial

thank's alot!!

by omprakash on Saturday, September 13th 2008 at 12:31 AM

hi this is the method for binding data from Ms Access database in listbox so good i f want take a new question then how to find Answer. please send me a link where i put my ques.

by tyutyu on Tuesday, February 24th 2009 at 11:56 PM

yujyujtyuyikuyiouyi

by eman on Saturday, October 17th 2009 at 02:02 PM

the oleDBbDataAdapter is not working giving me an error when i am trying to add the server name
---------------------------
Microsoft SQL Server Login
---------------------------
Connection failed:
SQLState: '08001'
SQL Server Error: 53
Named Pipes Provider: Could not open a connection to SQL Server [53].
Connection failed:
SQLState: '08001'
SQL Server Error: 53

Connection failed:
SQLState: 'HYT00'
SQL Server Error: 0

---------------------------
OK
---------------------------
how can i solve it?