Navigate a Database with VB .NET

 

The first part of Databases and VB .NET can be found here:

Coding your own VB .NET database projects

 

If you want the help of Wizards, there's 2 different sections:

Visual Basic 2005 Express Edition Users

Visual Studio Users

---

 

You saw in the previous section that you can navigate through the records of a database by incrementing or decrementing the Row number of the DataSet. In this section, we're going to see a more practical example of how to do that. It's better if you start a new project for this. With a new form open, do the following:

When you're done, your form should look something like this:

Press F7 to see you code window, and add the following code to the Form1 Declarations area:

(VB 2005 Express Edition users: don't forget to add the references! Click Project > Add References. Locate System.Data.dll and System.Xml.dll on the NET tab. Select these items and click OK. Then add Imports System.Data at the very top of your code window.) Your code will look like this:

All we're doing here is setting up the variables we need. There's one for the Connection Object, one for the DataSet, and one for the Data Adaptor. We've also set up two Integer variables (inc and MaxRows), and a String variable (sql).

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:

What your code window should look like (45K - needs javascript enabled)

You've met all the code before, except for these two lines:

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. (Previous versions of VB had some called an EOF and BOF properties. These checked the End of File and Before End of File. These properties have now gone.)

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

The important part is Rows(inc). This moves us through the Rows in the DataSet. We're then placing the values into the two Textboxes.

The whole of your code so far should look like this (Express Edition user will have the Imports System.Data line at the very top):

What your code window should look like (64K - needs javascript enabled)

 

In the next Part, we'll see how the Buttons on the form work.

 

Learn how to Code for the Navigate Buttons -->

<--Back to the .NET Contents Page

View all our Home Study Computer Courses