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.

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: