A Basic ADO.NET Tutorial in Visual Basic.NET

To work with ADO.NET, the first thing you'll need is a database. A database sample has been provided in the attachment; it consists of a few simple fields in the tbl_master table:

  • EmployeeID
  • FirstName
  • LastName
  • Location

You will be creating a simple form for navigating through the records in the table.

Start by placing three labels, three textboxes, and four buttons on a form as shown in the following figure. Name the textboxes txtFirstName, txtLastName, and txtLocation. The buttons should be self explanatory as well: btnFirst, btnPrevious, btnNext, and btnLast.

Now you can begin the coding. Declare a dataset at the class level and import the System.Data.OleDb namespace.

Dim ds As New DataSet()

In the Form's Load event, fill the dataset. To do this, create a DataAdapter and use its Fill() method to fill up the dataset.

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; _
   Data Source=C:\Documents and Settings\mendhak\My Documents\ _
   Visual Studio 2005\Projects\ADONetTutorial1\ADONetTutorial1\ _
   sample.mdb;User Id=admin;Password=;"

   Dim strSQL As String = "SELECT EmployeeID, FirstName, LastName, _
                           Location FROM tbl_Master"
   Dim da As New OleDbDataAdapter(strSQL, conn)

   da.Fill(ds)

(You will have to modify the connection string to point the location of the MDB file on your machine.)

The dataset has now been filled. If you have worked with classic ADO, think of a dataset as something like a recordset, except that a dataset is disconnected from the dataset, so you don't need to worry about cursors, EOF, BOF, or closing connections. Datasets are .NET collections as well; this makes them more flexible.

Anyway, you now fill the textboxes with the data in the dataset. Remember that a dataset is a collection. More specifically, it is a collection of DataTables. A DataTable simply represents a table of data you have retrieved from the database. You'll start with the first row. Immediately after the Fill() method, do this:

'Check if the table is empty
If ds.Tables(0).Rows.Count > 0 Then
   txtFirstName.Text = ds.Tables(0).Rows(0).Item("FirstName").ToString()
   txtLastName.Text  = ds.Tables(0).Rows(0).Item("LastName").ToString()
   txtLocation.Text  = ds.Tables(0).Rows(0).Item("Location").ToString()
End If[/HIGHLIGHT]

Run your form, and it should look like this.

A Basic ADO.NET Tutorial in Visual Basic.NET

To do the navigation, you will need an integer to hold your current Row position in the dataset's table. Declare an integer where you declared the dataset.

Now, double-click the << button (btnFirst) and in its Click event, set the textboxes to read from Row 0.

'Since 0 is the first row
txtFirstName.Text = ds.Tables(0).Rows(0).Item("FirstName").ToString()
txtLastName.Text  = ds.Tables(0).Rows(0).Item("LastName").ToString()
txtLocation.Text  = ds.Tables(0).Rows(0).Item("Location").ToString()

Double-click the < button (btnPrevious) and in its Click event,

'We move back only if we're not at the first row.
If intCurrentIndex > 0 Then
'Subtract one from the current index.
   intCurrentIndex = intCurrentIndex - 1

   txtFirstName.Text = _
      ds.Tables(0).Rows(intCurrentIndex).Item("FirstName").ToString()
   txtLastName.Text  = _
      ds.Tables(0).Rows(intCurrentIndex).Item("LastName").ToString()
   txtLocation.Text  = _
      ds.Tables(0).Rows(intCurrentIndex).Item("Location").ToString()
Else
   MessageBox.Show("You're already at the first record.")
End If

Double-click the > button (btnNext) and in its Click event,

'ds.Tables(0).Rows.Count - 1 is the index for the last row
If intCurrentIndex < ds.Tables(0).Rows.Count - 1 Then

   'Add one to the current index.
   intCurrentIndex = intCurrentIndex + 1

   txtFirstName.Text = _
      ds.Tables(0).Rows(intCurrentIndex).Item("FirstName").ToString()
   txtLastName.Text  = _
      ds.Tables(0).Rows(intCurrentIndex).Item("LastName").ToString()
   txtLocation.Text  = _
      ds.Tables(0).Rows(intCurrentIndex).Item("Location").ToString()
Else
   MessageBox.Show("You're already at the last record.")
End If

Double-click the >> button (btnLast) and in its Click event,

'ds.Tables(0).Rows.Count - 1 is the index for the last row
intCurrentIndex = ds.Tables(0).Rows.Count - 1
txtFirstName.Text = _
      ds.Tables(0).Rows(intCurrentIndex).Item("FirstName").ToString()
txtLastName.Text  = _
      ds.Tables(0).Rows(intCurrentIndex).Item("LastName").ToString()
txtLocation.Text  = _
      ds.Tables(0).Rows(intCurrentIndex).Item("Location").ToString()

That's it! You've just created a basic navigation form. You should be able to move to other rows.

[ADO3.JPG]

There are many improvements that can be done here. The code to fill up the fields can be placed in a single method to which you pass a parameter. You'll see this in the next part of the tutorial, along with adding, updating, and deleting.



About the Author

SM Altaf

Mendhak is a web developer and a Microsoft MVP who works with ASP.NET and PHP among the usual array[] of web technologies. He is also rumored to be a three eyed frog, but the evidence is lacking. He can be contacted via his website, www.mendhak.com.


Downloads

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: March 19, 2015 @ 1:00 p.m. ET / 10:00 a.m. PT The 2015 Enterprise Mobile Application Survey asked 250 mobility professionals what their biggest mobile challenges are, how many employees they are equipping with mobile apps, and their methods for driving value with mobility. Join Dan Woods, Editor and CTO of CITO Research, and Alan Murray, SVP of Products at Apperian, as they break down the results of this survey and discuss how enterprises are using mobile application management and private …

  • On-demand Event Event Date: February 12, 2015 The evolution of systems engineering with the SysML modeling language has resulted in improved requirements specification, better architectural definition, and better hand-off to downstream engineering. Agile methods have proven successful in the software domain, but how can these methods be applied to systems engineering? Check out this webcast and join Bruce Powel Douglass, author of Real-Time Agility, as he discusses how agile methods have had a tremendous …

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date