Visual Basic 2005 Guide
Databases - The Ninja Method

Introduction

In the introduction to the previous section, it was mentioned that there are a variety of ways to access databases from within your Visual Basic application. In the previous section, we accessed our database by binding the DataGridView control to a dataset representing data in the database. This allowed for a degree of automation. Sometimes that isn't as flexible as we want it to be or we don't want a bound control.

For the examples below use the Access 2000 database, cd2000.mdb (approx 264kb).

Setting Up The Form

Start a new project and save your files to create a new directory for the new solution. Set up a form with two listboxes called lstArtists and lstDisks. Your form should look something like this,

form design

The label above the right hand listbox is called lblChosenArtist an has its UseMnemonic property set to false. This will allow us to display the special characters that can be part of a band name.

Filling The Artist Listbox

The following code is placed in the Form's load event. The underscore is where I had to break a line to display the code on this web page - do not break the line in your program.

Dim con As New OleDb.OleDbConnection
con.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source_
= D:\Documents\VB Express Projects\CD Database\cd2000.mdb"
con.Open()
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
da = New OleDb.OleDbDataAdapter("SELECT DISTINCT Artist FROM disks;", con)
da.Fill(ds, "tmpT")
Dim dr As DataRow
For Each dr In ds.Tables("tmpT").Rows
   lstArtists.Items.Add(dr.Item(0))
Next
con.Close()

The connection string represents our first link to the database. It supplies Visual Basic with information about the file that we are using (the provider part) as well as the path to the file. Check out MSDN for ways to store a connection string in a project so that it can be reused across the project as a whole. It is usually more helpful to work out the path to a file at runtime - you can get a reference to the location of a running application by using the following line of code,

My.Application.Info.DirectoryPath

You would then be able to form the connection string by concatenating the provider information, this path and the name of the file that you want to connect to.

When the connection has been opened we then need to set up a few objects to manage the interface between the database and Visual Basic. The dataset holds the data we want to use. The Data Adapter manages the transfer of that data to the dataset. More on these objects can be found in the MSDN help supplied with Visual Basic.

Dynamically Filling The Second List Box

When the user selects an artist from the first listbox, we want to fill the second box with the albums by that artist. We use the SelectedIndexChanged event of lstArtists to do this. The following code is used.

If lstArtists.SelectedIndex = -1 Then Exit Sub
lstDisks.Items.Clear()
Dim strItem As String
strItem = lstArtists.SelectedItem
strItem = strItem.Replace("'", "''")
Dim con As New OleDb.OleDbConnection
con.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source_
= D:\Documents\VB Express Projects\CD Database\cd2000.mdb"
con.Open()
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim strSQL As String
strSQL = "SELECT Title FROM disks WHERE Artist = '" & strItem & "';"
da = New OleDb.OleDbDataAdapter(strSQL, con)
da.Fill(ds, "tmpT")
Dim dr As DataRow
For Each dr In ds.Tables("tmpT").Rows
   lstDisks.Items.Add(dr.Item(0))
Next
lblChosenArtist.Text = "CDs By " & lstArtists.SelectedItem & "."
con.Close()

First we check that an item is still selected (it is just about possible to change the selected index of the list box to no selection) and exit the sub if nothin is selected.

Next we set a string to equal the selected item. We need to replace any single quotes in the artist name with 2 single quotes - this is because single quotes are used to contain strings in SQL statements. Two single quotes allows the second quote to be read as a literal (see MSDN).

We establish the connection as before but make the SQL from the string that we formed. Finally, we place the name of the artist in the lable above the list box.