Click Project from the menu bar
Then click Add Reference
From the dialogue box, select the .NET tab. Scroll down and select the System.Data item
Click OK.

At the very top of your code window, before Public Class Form 1, type the following:

Imports System.Data


Setting a Connection String
There are Properties and Methods associated with the Connection Object, We need to pass two Properties to our new Connection Object:

the technology we want to use to do the connecting to our database;

and where the database is.

(If your database was password and user name protected, you would add these two parameters as well. )

The technology is called the Provider; and you use "Data Source" to specify where your database is.

con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\AddressBook.mdb"

Notice the two parts, separated by a semi-colon:

1st Part: PROVIDER=Microsoft.Jet.OLEDB.4.0
2nd Part: Data Source = C:\AddressBook.mdb

Opening the Connection
con.Open()

MsgBox("A Connection to the Database is now open")

con.Close()

MsgBox("The Connection to the Database is now Closed")


ADO.NET uses something called a DataSet to hold all of your information from the database. The DataSet is not something you can draw on your form, like a Button or a Textbox. The DataSet is something that is hidden from you.

The Connection Object and the DataSet can't see each other. They need a Data Adapter to communicate.

The Data Adapter contacts your Connection Object, and then executes a query that you set up. The results of that query are then stored in the DataSet.

The Data Adapter and DataSet are objects. You set them up like this:

Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter

da = New OleDb.OleDbDataAdapter(sql, con)

Structured Query Language
SQL (pronounced SeeKwel), is short for Structured Query Language, and is a way to query and write to databases (not just Access). The basics are quite easy to learn. If you want to grab all of the records from a table in a database, you use the SELECT word. Like this:

SELECT * FROM Table_Name

SQL is not case sensitive, so the above line could be written:


Select * from Table_Name

But your SQL statements are easier to read if you type the keywords in uppercase letters. The keywords in the lines above are SELECT and FROM. The asterisk means "All Records". Table_Name is the name of a table in your database. So the whole line reads:


"SELECT all the records FROM the table called Table_Name"

You don't need to select all (*) the records from your database. You can just select the columns that you need. The name of the table in our database is tblContacts. If we wanted to select just the first name and surname columns from this table, we can specify that in our SQL String:

SELECT tblContacts.FirstName, tblContacts.Surname FROM tblContacts

When this SQL statement is executed, only the FirstName and Surname columns from the database will be returned.

There are a lot more SQL commands, but for our purposes this is enough.


Because we want to SELECT all (*) the records from the table called tblContacts, we pass this string to the string variable we have called sql:

sql = "SELECT * FROM tblContacts"

So add the following code to your database project:

Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim sql As String

sql = "SELECT * FROM tblContacts"
da = New OleDb.OleDbDataAdapter(sql, con)

Filling the DataSet
The Data Adapter can Fill a DataSet with records from a Table. You only need a single line of code to do this:

da.Fill(ds, "AddressBook")

As soon as you type the name of your Data Adapter , you'll get a pop up box of properties and methods. Select Fill from the list, then type a pair of round brackets.

In between the round brackets, you need two things: the Name of your DataSet and an identifying name. This identifying name can be anything you like. But it is just used to identify this particular Data Adapter Fill.

da.Fill(ds, "dpart")

Add the new line after the creation of the Data Adaptor:

da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "AddressBook")

And that's it. The DataSet (ds) will now be filled with the records we selected from the table called tblContact.

We created a Data Adaptor so that it could fill a DataSet with records from our database. What we want to do now is to display the records on a Form, so that people can see them. So so this:

Add two textboxes to your form
Change the Name properties of your textboxes to txtFirstName and txtSurname
Go back to your code window
Add the following two lines:
txtFirstName.Text = ds.Tables("AddressBook").Rows(0).Item(1)
txtSurname.Text = ds.Tables("AddressBook").Rows(0).Item(2)

You can add them after the line that closes the connection to the database. Once the DataSet has been filled, a connection to a database can be closed.

 

When the Form Loads, we can connect to our database, use the data Adaptor to grab some records from the database, and then put these records into the DataSet. So in the Form1 Load Event, add the following code:

MaxRows = ds.Tables("AddressBook").Rows.Count
inc = -1

In the MaxRows variable, we can store how many rows are in the DataSet. You get how many rows are in yout DataSet with Rows.Count:

MaxRows = ds.Tables("AddressBook").Rows.Count

So the Rows property has a Count Method. This simply counts how many rows are in the DataSet. We're passing that number to a variable called MaxRows. You can then test what is in the variable, and see if the inc counter doesn't go past it. You need to do this because VB throws up an error message if try to go past the last row in the DataSet.

To navigate through the records, we're going to use that inc variable. We'll either add 1 to it, or take 1 away. We'll then use the variable for the Rows in the DataSet. It's better to do this in a Subroutine of your own. So add this Sub to your code:

Private Sub NavigateRecords()

txtFirstName.Text = ds.Tables("AddressBook").Rows(inc).Item(1)
txtSurname.Text = ds.Tables("AddressBook").Rows(inc).Item(2)

End Sub

 

How to Move Forward One Record at a Time

Double click your Next Record button to access the code. Add the following If … Else Statement:

If inc <> MaxRows - 1 Then
inc = inc + 1
NavigateRecords()
Else
MsgBox("No More Rows")
End If

We're checking to see if the value in inc does not equal the value in MaxRows - 1. If they are both equal then we know we've reached the last record in the DataSet. In which case, we just display a message box. If they are not equal, these two lines get executed:

inc = inc + 1
NavigateRecords()

First, we move the inc counter on by one. Then we call the Sub we set up:

NavigateRecords()

Our Subroutine is where the action takes place, and the values from the DataSet are placed in the textboxes. Here it is again:

Private Sub NavigateRecords()

txtFirstName.Text = ds.Tables("AddressBook").Rows(inc).Item(1)
txtSurname.Text = ds.Tables("AddressBook").Rows(inc).Item(2)

End Sub

The part that moves the record forward (and backwards soon) is this part:

Rows(inc)

Previously, we hard-coded this with:

Rows(0)

Now the value is coming from the variable called inc. Because we're incrementing this variable with code, the value will change each time the button is clicked. And so a different record will be displayed.

Move Back One Record at a Time