The Rows of a Table

When creating an application, to deliver it to the user and make it ready for data entry, you can create one or more forms. When it comes to a data set, the .NET Framework provides the data grid view, which is made of cells that hold values. As opposed to a list view, instead of visually creating the columns and/or the items, you can specify the source of the records. This would equip the data grid view with the necessary columns and the records in the data set.

After creating a data grid view, you can assign it a data source, such as a DataSet object. At design time, to specify the data source of a data grid view, in the Properties window, select the object in the DataSource field. At run time, to specify a data source, assign the appropriate object to the DataSource property. Here is an example:

Public Class Exercise
    Private dsVideos As DataSet

    Private Sub Exercise_Load(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) _
                              Handles MyBase.Load
        dgvVideos = New DataGridView

        dsVideos = New DataSet("VideoSet")

        dgvVideos.DataSource = dsVideos
    End Sub
End Class

To find out what the data source of a data grid view is, get the value of its DataSource property.

After assigning a data source, you should assign a data member to the data grid view. To visually specify the data members, in the Properties window of the data grid view, after setting the data source, access the DataMember field and click its arrowed button. Then, in the list, select an object, such as a table of a data set.

To programmatically specify the data member, after assigning the appropriate variable to the DataSource property, assign the name of the list, such as the object name of a table, to the DataMember property of the DataGridView object. Here is an example:

Public Class Exercise
    Private colTitle As DataColumn
    Private colDirector As DataColumn
    Private colLength As DataColumn
    Private colRating As DataColumn
    Private tblVideos As DataTable

    Private dgvVideos As DataGridView
    Private dsVideos As DataSet

    Private Sub Exercise_Load(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) _
                              Handles MyBase.Load
        Text = "Video Collection"

        dgvVideos = New DataGridView
        dgvVideos.Location = New Point(12, 12)
        dgvVideos.Size = New Size(450, 80)
        Controls.Add(dgvVideos)

        tblVideos = New DataTable("Videos")

        colTitle = New DataColumn("Title")
        tblVideos.Columns.Add(colTitle)
        colDirector = New DataColumn("Director")
        tblVideos.Columns.Add(colDirector)
        colLength = New DataColumn("Length")
        tblVideos.Columns.Add(colLength)
        colRating = New DataColumn("Rating")
        tblVideos.Columns.Add(colRating)

        dsVideos = New DataSet("VideoSet")
        dsVideos.Tables.Add(tblVideos)

        dgvVideos.DataSource = dsVideos
        dgvVideos.DataMember = "Videos"
    End Sub
End Class

Once you have specified the data source and the data member, the columns would appear on the data grid view:

A record on a table is represented as a row (horizontal) of data. A row, or record, is an object based on the DataRow class. To support the various records that belong to a table, the DataTable class is equipped with a property called Rows. The DataTable.Rows property is an object of the DataRowCollection class. The DataRowCollection class provides the necessary properties and methods you can use to create and manage the records of a table.

A record on a table is an object of type DataRow.

When performing data entry and while doing it on a record, the record has a status that can be identified by the DataRow.RowState property which is a value based on the DataRowState enumeration. Before adding a new record to a table, you must let the table know. This is done by calling the DataTable.NewRow() method. Its syntax is:

Public Function NewRow As DataRow

The DataTable.NewRow() method returns a DataRow object. Here is an example of calling it:

Private Sub btnRecord_Click(ByVal sender As System.Object, _
                                ByVal e As System.EventArgs) _
                                Handles btnRecord.Click
    Dim VideoRecord As DataRow = tblVideos.NewRow()
End Sub

When you call the DataTable.NewRow() method, the record's status is DataRowState.Detached. After calling the DataTable.NewRow() method, you can specify the value that the column would carry. To do this, you must specify the table's column whose value you want to provide. You can locate a column based on an index as we mentioned already that the columns of a table are stored in the DataTable.Columns property which is based on the DataColumnCollection class. An example would be StudentRecord("First Name"), which specifies the column named First Name. After specifying the column, assign it the desired but appropriate value. Here are examples of assigning values to the columns of a table:

Private Sub btnRecord_Click(ByVal sender As System.Object, _
                                ByVal e As System.EventArgs) _
                                Handles btnRecord.Click
        Dim VideoRecord As DataRow = tblVideos.NewRow()

        VideoRecord("Title") = "The General's Daughter"
        VideoRecord("Director") = "Simon West"
        VideoRecord("Length") = "116"
        VideoRecord("Format") = "DVD"
        VideoRecord("Rating") = "R"
End Sub

Each column can also be identified by its index in the table. 

After specifying the value(s) of the column(s), you must add it (them) to the table. To do this, you must call the Add() method of the DataRowCollection class. This method is overloaded with two versions. One of the versions uses the following syntax:

Public Sub Add(row As DataRow)

This method expects the name of the record as argument, which would be the value returned by a previous call to the DataTable.NewRow() method. Here is an example:

Public Class Exercise
    Private colTitle As DataColumn
    Private colDirector As DataColumn
    Private colLength As DataColumn
    Private colRating As DataColumn
    Private tblVideos As DataTable

    Private dgvVideos As DataGridView
    Private dsVideos As DataSet

    Private Sub Exercise_Load(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) _
                              Handles MyBase.Load
        Text = "Video Collection"

        dgvVideos = New DataGridView
        dgvVideos.Location = New Point(12, 12)
        dgvVideos.Size = New Size(450, 80)
        Controls.Add(dgvVideos)

        tblVideos = New DataTable("Videos")

        colTitle = New DataColumn("Title")
        tblVideos.Columns.Add(colTitle)
        colDirector = New DataColumn("Director")
        tblVideos.Columns.Add(colDirector)
        colLength = New DataColumn("Length")
        tblVideos.Columns.Add(colLength)
        colRating = New DataColumn("Rating")
        tblVideos.Columns.Add(colRating)

        dsVideos = New DataSet("VideoSet")
        dsVideos.Tables.Add(tblVideos)

        Dim VideoRecord As DataRow = tblVideos.NewRow()
        VideoRecord("Title") = "The General's Daughter"
        VideoRecord("Director") = "Simon West"
        VideoRecord("Length") = "116"
        VideoRecord("Rating") = "R"

        tblVideos.Rows.Add(VideoRecord)

        dgvVideos.DataSource = dsVideos
        dgvVideos.DataMember = "Videos"
    End Sub
End Class

This would produce:

When the record has been added to the table, the record has a status of DataRowState.Added.

The above version of the DataRowCollection.Add() method means that you must identify each column before assigning a value to it. If you already know the sequence of columns and do not need to explicitly identify them, you can store all values in an array and simply add the array as a complete record. To support this, the DataRowCollection class provide another version of the Add() method whose syntax is:

Public Function Add(values As Object()) As DataRow

Here is an example:

Private Sub Exercise_Load(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) _
                              Handles MyBase.Load
        Text = "Video Collection"

        dgvVideos = New DataGridView
        dgvVideos.Location = New Point(12, 12)
        dgvVideos.Size = New Size(420, 100)
        Controls.Add(dgvVideos)

        tblVideos = New DataTable("Videos")

        colTitle = New DataColumn("Title")
        tblVideos.Columns.Add(colTitle)
        colDirector = New DataColumn("Director")
        tblVideos.Columns.Add(colDirector)
        colLength = New DataColumn("Length")
        tblVideos.Columns.Add(colLength)
        colRating = New DataColumn("Rating")
        tblVideos.Columns.Add(colRating)

        dsVideos = New DataSet("VideoSet")
        dsVideos.Tables.Add(tblVideos)

        Dim VideoRecord As DataRow = tblVideos.NewRow()
        VideoRecord("Title") = "The General's Daughter"
        VideoRecord("Director") = "Simon West"
        VideoRecord("Length") = "116"
        VideoRecord("Rating") = "R"
        tblVideos.Rows.Add(VideoRecord)

        Dim arrRecord() As Object = {"Hush", "Douglas Wick", "", "PG-13"}
        tblVideos.Rows.Add(arrRecord)

        dgvVideos.DataSource = dsVideos
        dgvVideos.DataMember = "Videos"
End Sub

This would produce:

There is an alternative to this second version of the DataRowCollection.Add() method. As opposed to passing an array of values to the Add() method, you can first define an array, assign that array to a DataRow variable, then pass that DataRow object to the Add() method. To support this technique, the DataRow class is equipped with an ItemArray property that expects an array. Here is an example

Private Sub Exercise_Load(ByVal sender As System.Object, _
                          ByVal e As System.EventArgs) _
                          Handles MyBase.Load
        Text = "Video Collection"

        dgvVideos = New DataGridView
        dgvVideos.Location = New Point(12, 12)
        dgvVideos.Size = New Size(420, 115)
        Controls.Add(dgvVideos)

        tblVideos = New DataTable("Videos")

        colTitle = New DataColumn("Title")
        tblVideos.Columns.Add(colTitle)
        colDirector = New DataColumn("Director")
        tblVideos.Columns.Add(colDirector)
        colLength = New DataColumn("Length")
        tblVideos.Columns.Add(colLength)
        colRating = New DataColumn("Rating")
        tblVideos.Columns.Add(colRating)

        dsVideos = New DataSet("VideoSet")
        dsVideos.Tables.Add(tblVideos)

        Dim VideoRecord As DataRow = tblVideos.NewRow()
        VideoRecord("Title") = "The General's Daughter"
        VideoRecord("Director") = "Simon West"
        VideoRecord("Length") = "116"
        VideoRecord("Rating") = "R"
        tblVideos.Rows.Add(VideoRecord)

        Dim arrRecord() As Object = {"Hush", "Douglas Wick", "", "PG-13"}
        tblVideos.Rows.Add(arrRecord)

        Dim ARecord() As Object = {"Sneakers", "Phil Alden Robinson", _
                                   "126", "PG-13"}
        VideoRecord = tblVideos.NewRow()
        VideoRecord.ItemArray = ARecord
        tblVideos.Rows.Add(VideoRecord)

        dgvVideos.DataSource = dsVideos
        dgvVideos.DataMember = "Videos"
End Sub

This would produce:

After creating the records of a table, if a record contains invalid values, the DataRow.HasErrors property can help you identify them.

After you have created a table and its columns but before adding any row, the number of the table's records is set to 0. Every time you add a new record, the number of records is incremented by 1. To get the number of records that a table contains, access the Count property of its DataRowCollection collection.

When the application closes, unfortunately, all the information created while the application was running is lost. While the first goal of an application is to create one or more lists used to organize information, probably the essence of an information-based or a data-based application is to preserve information created when using the application and be able to retrieve that information the next time the application runs, without re-creating it.

Of course, there are various ways you can save the information created in an application. As the DataSet class is equipped with all the necessary features used to create and manage one or more lists of an application, it also provides a very high level of saving the information stored in its lists.

Once a new record has been created or when the lists of the data set have been populated with information, you can save the changes and store them to a computer file. By default, the DataSet class is equipped to save its lists as XML. To support this, it is equipped with the WriteXml() method that is overloaded with various versions. One of the versions of this method uses the following syntax:

Public Sub WriteXml(fileName As String)

This method takes as argument the name of the new file or its path. When providing this argument, make sure you add the .xml extension to the file name. This method does two things: it checks the existence of the file and it saves it. If the file you provided is not found in the path, this method creates it and writes the record(s) to it. If the file exists already, this method opens it, finds its end, and appends the new data at the end. This makes the method very useful and friendly.

Here is an example of saving a data set using this method:

Private Sub Exercise_Load(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) _
                              Handles MyBase.Load
    . . . No Change

    dsVideos.WriteXml("Exercise.xml")
End Sub

If you want to control whether the file should be created from scratch, instead of passing the name of the file to this method, first create a stream using a Stream-derived class such as FileStream. This allows specifying the necessary options using the FileMode, FileAccess, and FileShare properties. Once the stream is ready, pass it to the WriteXml() method because it is also overloaded with the following syntax:

Public Sub WriteXml(stream As Stream)

Here is an example:

Imports System.IO

Public Class Exercise
    Private colTitle As DataColumn
    Private colDirector As DataColumn
    Private colLength As DataColumn
    Private colRating As DataColumn
    Private tblVideos As DataTable

    Private dgvVideos As DataGridView
    Private dsVideos As DataSet

    Private Sub Exercise_Load(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) _
                              Handles MyBase.Load
        Text = "Video Collection"

        dgvVideos = New DataGridView
        dgvVideos.Location = New Point(12, 12)
        dgvVideos.Size = New Size(420, 115)
        Controls.Add(dgvVideos)

        tblVideos = New DataTable("Videos")

        colTitle = New DataColumn("Title")
        tblVideos.Columns.Add(colTitle)
        colDirector = New DataColumn("Director")
        tblVideos.Columns.Add(colDirector)
        colLength = New DataColumn("Length")
        tblVideos.Columns.Add(colLength)
        colRating = New DataColumn("Rating")
        tblVideos.Columns.Add(colRating)

        dsVideos = New DataSet("VideoSet")
        dsVideos.Tables.Add(tblVideos)

        Dim VideoRecord As DataRow = tblVideos.NewRow()
        VideoRecord("Title") = "The General's Daughter"
        VideoRecord("Director") = "Simon West"
        VideoRecord("Length") = "116"
        VideoRecord("Rating") = "R"
        tblVideos.Rows.Add(VideoRecord)

        Dim arrRecord() As Object = {"Hush", "Douglas Wick", "", "PG-13"}
        tblVideos.Rows.Add(arrRecord)

        Dim ARecord() As Object = {"Sneakers", "Phil Alden Robinson", _
                                   "126", "PG-13"}
        VideoRecord = tblVideos.NewRow()
        VideoRecord.ItemArray = ARecord
        tblVideos.Rows.Add(VideoRecord)

        dgvVideos.DataSource = dsVideos
        dgvVideos.DataMember = "Videos"

        Dim stmVideos As FileStream = New FileStream("Exercise.xml", _
                                                 FileMode.Create, _
                                                 FileAccess.Write)
        dsVideos.WriteXml(stmVideos)
    End Sub
End Class

If you want the file to be formatted as text, you can use the following version of the method:

Public Sub WriteXml(writer As TextWriter)

This method expects a TextWriter-based variable. Here is an example:

Private Sub Exercise_Load(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) _
                              Handles MyBase.Load
    Text = "Video Collection"

    . . . No Change

    Dim StmWriter As StreamWriter
    StmWriter = New StreamWriter("Exercise.xml")

    dsVideos.WriteXml(StmWriter)
End Sub

The name of the file can have any extension, not necessarily .xml but the content of the file would be structured as XML. If you prefer to use an XmlWriter variable to manage the file, use the following version of the method:

Public Sub WriteXml(writer As XmlWriter)

Obviously to use this method, you must first define an XmlWriter type of variable.

To open the data saved from a list, the DataSet class provides the ReadXml() method that is overloaded with various versions. One of the versions of this method uses the following syntax:

Public Function ReadXml(fileName As String) As XmlReadMode

This method takes as argument the name of an existing XML file or its path. The method opens the file and provides the XML formatting as it was done when the file was saved. Here is an example of calling this method:

Imports System.IO

Public Class Exercise
    Private colTitle As DataColumn
    Private colDirector As DataColumn
    Private colLength As DataColumn
    Private colRating As DataColumn
    Private tblVideos As DataTable

    Private dgvVideos As DataGridView
    Private dsVideos As DataSet

    Private Sub Exercise_Load(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) _
                              Handles MyBase.Load
        Text = "Video Collection"

        dgvVideos = New DataGridView
        dgvVideos.Location = New Point(12, 12)
        dgvVideos.Size = New Size(420, 115)
        Controls.Add(dgvVideos)

        tblVideos = New DataTable("Videos")

        colTitle = New DataColumn("Title")
        tblVideos.Columns.Add(colTitle)
        colDirector = New DataColumn("Director")
        tblVideos.Columns.Add(colDirector)
        colLength = New DataColumn("Length")
        tblVideos.Columns.Add(colLength)
        colRating = New DataColumn("Rating")
        tblVideos.Columns.Add(colRating)

        dsVideos = New DataSet("VideoSet")
        dsVideos.Tables.Add(tblVideos)

        Dim Filename As String = "Exercise.xml"

        If File.Exists(Filename) Then
            dsVideos.ReadXml(Filename)
            dgvVideos.DataSource = dsVideos
            dgvVideos.DataMember = "Videos"
        End If
    End Sub
End Class

Although this method can read any XML file, if you use it to open a file that was saved by someone else or another application and you want to use it in your application, you should be familiar with the names of its nodes. If it contains names that are not "registered" or recognized by your DataSet object, the lists that compose your application may not be able to read it, not because the list was not formatted right, but because the lists of your application would be holding different names.

If the file was saved using a Stream-based class, you can pass a stream to the method based on the following syntax:

public XmlReadMode ReadXml(Stream stream)

Here is an example:

Private Sub Exercise_Load(ByVal sender As System.Object, _
                          ByVal e As System.EventArgs) _
                          Handles MyBase.Load
    Text = "Video Collection"

    dgvVideos = New DataGridView
    dgvVideos.Location = New Point(12, 12)
    dgvVideos.Size = New Size(420, 115)
    Controls.Add(dgvVideos)

    tblVideos = New DataTable("Videos")

    colTitle = New DataColumn("Title")
    tblVideos.Columns.Add(colTitle)
    colDirector = New DataColumn("Director")
    tblVideos.Columns.Add(colDirector)
    colLength = New DataColumn("Length")
    tblVideos.Columns.Add(colLength)
    colRating = New DataColumn("Rating")
    tblVideos.Columns.Add(colRating)

    dsVideos = New DataSet("VideoSet")
    dsVideos.Tables.Add(tblVideos)

    Dim Filename As String = "Exercise.xml"

    Dim stmVideos As FileStream = New FileStream("Exercise.xml", _
                                                 FileMode.OpenOrCreate, _
                                                 FileAccess.ReadWrite)
    If File.Exists(Filename) Then
        dsVideos.ReadXml(stmVideos)
        dgvVideos.DataSource = dsVideos
        dgvVideos.DataMember = "Videos"
    End If
End Sub

In the same way, the method provides an equivalent version for the TextWriter and the XmlWriter versions:

Public Function ReadXml(reader As TextReader) As XmlReadMode
Public Function ReadXml(reader As XmlReader) As XmlReadMode

To use one of these versions, you must first define a TextWriter or an XmlReader type of variable.

When retrieving the content of the XML file, if you want it delivered as text, call the DataSet.GetXml() method. Its syntax is:

Public Function GetXml As String

As you can see, this method returns a string.

When a user has created a record, the data set that holds the information is considered to have been modified because, obviously, it does not have the same information or the same records it had when the application was launched. You, as the programmer, have the option of accepting the changes or rejecting them. To accept the changes, call the DataSet.AcceptChanges() method. Its syntax is:

Public Sub AcceptChanges

If you do not want the changes to take effect, you can reject them by calling the DataSet.RejectChanges() method. Its syntax is:

Public Overridable Sub RejectChanges

This method can be called to dismiss whatever changes were made on the records of the list(s).

Consider the following data set:

Imports System.IO

Public Class Exercise
    Private dsRedOakHighSchool As DataSet
    Private tblRegistration As DataTable
    Private colStudentNumber As DataColumn
    Private colFirstName As DataColumn
    Private colLastName As DataColumn
    Private colGender As DataColumn


    Private Sub Exercise_Load(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) _
                              Handles MyBase.Load
        Text = "Students Records"

        colStudentNumber = New DataColumn("StudentNumber")
        colFirstName = New DataColumn("FirstName")
        colLastName = New DataColumn("LastName")
        colGender = New DataColumn("Gender")

        tblRegistration = New DataTable("Student")
        tblRegistration.Columns.Add(colStudentNumber)
        tblRegistration.Columns.Add(colFirstName)
        tblRegistration.Columns.Add(colLastName)
        tblRegistration.Columns.Add(colGender)

        dsRedOakHighSchool = New DataSet("SchoolRecords")
        dsRedOakHighSchool.Tables.Add(tblRegistration)

        Dim objStudents1() As Object = {"920759", "Pauline", "Simms", "Female"}
        tblRegistration.Rows.Add(objStudents1)
        Dim objStudents2() As Object = {"281174", "Geraldine", "Rodetsky", "Unknown"}
        tblRegistration.Rows.Add(objStudents2)
        Dim objStudents3() As Object = {"400795", "Edward", "Zaks", "Male"}
        tblRegistration.Rows.Add(objStudents3)
        Dim objStudents4() As Object = {"931579", "Jeannete", "Palau", "Female"}
        tblRegistration.Rows.Add(objStudents4)
        Dim objStudents5() As Object = {"315825", "Kate", "Hooks", "Unknown"}
        tblRegistration.Rows.Add(objStudents5)

        dsRedOakHighSchool.WriteXml("students.xml")
    End Sub
End Class

This would produce:

<?xml version="1.0" standalone="yes"?>
<SchoolRecords>
  <Student>
    <StudentNumber>920759</StudentNumber>
    <FirstName>Pauline</FirstName>
    <LastName>Simms</LastName>
    <Gender>Female</Gender>
  </Student>
  <Student>
    <StudentNumber>281174</StudentNumber>
    <FirstName>Geraldine</FirstName>
    <LastName>Rodetsky</LastName>
    <Gender>Unknown</Gender>
  </Student>
  <Student>
    <StudentNumber>400795</StudentNumber>
    <FirstName>Edward</FirstName>
    <LastName>Zaks</LastName>
    <Gender>Male</Gender>
  </Student>
  <Student>
    <StudentNumber>931579</StudentNumber>
    <FirstName>Jeannete</FirstName>
    <LastName>Palau</LastName>
    <Gender>Female</Gender>
  </Student>
  <Student>
    <StudentNumber>315825</StudentNumber>
    <FirstName>Kate</FirstName>
    <LastName>Hooks</LastName>
    <Gender>Unknown</Gender>
  </Student>
</SchoolRecords>

Before performing any operation on a record, you must be able to locate it. That is, you must be able to identify a record among the various records of a table. The records of a table are stored in a list based on the DataRowCollection class. To locate a record in the DataTable.Rows collection, the DataRowCollection class has an indexed property that is defined as follows:

Public ReadOnly Property Item(index As Integer) As DataRow

The first record has an index of 0. The second record has an index of 1, and so on. Here is an example of identifying each column:

Imports System.IO

Public Class Exercise
    Private lvwStudents As ListView

    Private dsRedOakHighSchool As DataSet
    Private tblRegistration As DataTable
    Private colStudentNumber As DataColumn
    Private colFirstName As DataColumn
    Private colLastName As DataColumn
    Private colGender As DataColumn


    Private Sub Exercise_Load(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) _
                              Handles MyBase.Load
        Text = "Students Records"
        Size = New Size(320, 160)

        lvwStudents = New ListView()
        lvwStudents.Location = New Point(12, 12)
        lvwStudents.Size = New Size(290, 110)
        lvwStudents.View = View.Details
        lvwStudents.GridLines = True
        lvwStudents.FullRowSelect = True

        Controls.Add(lvwStudents)

        colStudentNumber = New DataColumn("StudentNumber")
        colFirstName = New DataColumn("FirstName")
        colLastName = New DataColumn("LastName")
        colGender = New DataColumn("Gender")

        tblRegistration = New DataTable("Student")
        tblRegistration.Columns.Add(colStudentNumber)
        tblRegistration.Columns.Add(colFirstName)
        tblRegistration.Columns.Add(colLastName)
        tblRegistration.Columns.Add(colGender)

        dsRedOakHighSchool = New DataSet("SchoolRecords")
        dsRedOakHighSchool.Tables.Add(tblRegistration)

        dsRedOakHighSchool.ReadXml("students.xml")

        lvwStudents.Columns.Add("Student #")
        lvwStudents.Columns.Add("First Name", 75)
        lvwStudents.Columns.Add("Last Name", 75)
        lvwStudents.Columns.Add("Gender")

        For i As Integer = 0 To tblRegistration.Rows.Count - 1
            Dim StudentRecord As DataRow = tblRegistration.Rows(i)
        Next
    End Sub
End Class

Each record of a table is an object of type DataRow. When you access a record, the compiler would check whether the record exists. If a record with that index exists, its DataRow value is produced.

Once you have located a record, you can find a particular value you are interested in, and you have tremendous options. Consider the following table of records:

To locate a value in a data set, you need two pieces of information: the record and the column. We have seen how to located a record. Each value of a table is created under a particular column. Therefore, you must be able to specify the column under which the value exists. To identify the columns of a table, the DataRow class is equipped with the overloaded indexed property which comes in 6 versions.

As seen in previous lessons, the columns of a table are indexed with the first column at 0, the second at 1, and so on. To allow you to identify a column by its index, one of the versions of the DataRow's indexed property uses the following syntax:

Public Property Item(columnIndex As Integer) As Object

This property expects the index of the column. Here are examples:

Imports System.IO

Public Class Exercise
    Private lvwStudents As ListView

    Private dsRedOakHighSchool As DataSet
    Private tblRegistration As DataTable
    Private colStudentNumber As DataColumn
    Private colFirstName As DataColumn
    Private colLastName As DataColumn
    Private colGender As DataColumn


    Private Sub Exercise_Load(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) _
                              Handles MyBase.Load
        Text = "Students Records"
        Size = New Size(320, 160)

        lvwStudents = New ListView()
        lvwStudents.Location = New Point(12, 12)
        lvwStudents.Size = New Size(290, 100)
        lvwStudents.View = View.Details
        lvwStudents.GridLines = True
        lvwStudents.FullRowSelect = True

        Controls.Add(lvwStudents)

        colStudentNumber = New DataColumn("StudentNumber")
        colFirstName = New DataColumn("FirstName")
        colLastName = New DataColumn("LastName")
        colGender = New DataColumn("Gender")

        tblRegistration = New DataTable("Student")
        tblRegistration.Columns.Add(colStudentNumber)
        tblRegistration.Columns.Add(colFirstName)
        tblRegistration.Columns.Add(colLastName)
        tblRegistration.Columns.Add(colGender)

        dsRedOakHighSchool = New DataSet("SchoolRecords")
        dsRedOakHighSchool.Tables.Add(tblRegistration)

        dsRedOakHighSchool.ReadXml("students.xml")

        lvwStudents.Columns.Add("Student #")
        lvwStudents.Columns.Add("First Name", 75)
        lvwStudents.Columns.Add("Last Name", 75)
        lvwStudents.Columns.Add("Gender")

        For i As Integer = 0 To tblRegistration.Rows.Count - 1
            Dim StudentRecord As DataRow = tblRegistration.Rows(i)

            Dim lviStudent As ListViewItem = New ListViewItem(CInt(StudentRecord(0)))
            lviStudent.SubItems.Add(StudentRecord(1))
            lviStudent.SubItems.Add(StudentRecord(2))
            lviStudent.SubItems.Add(StudentRecord(3))

            lvwStudents.Items.Add(lviStudent)
        Next
    End Sub
End Class

To access a record directly without first declaring a DataRow variable, the above code can also be written as follows:

Private Sub Exercise_Load(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) _
                              Handles MyBase.Load
    . . . No Change

    For i As Integer = 0 To tblRegistration.Rows.Count - 1
        Dim lviStudent As ListViewItem = _
		New ListViewItem(CInt(tblRegistration.Rows(i)(0)))
        lviStudent.SubItems.Add(tblRegistration.Rows(i)(1))
        lviStudent.SubItems.Add(tblRegistration.Rows(i)(2))
        lviStudent.SubItems.Add(tblRegistration.Rows(i)(3))

        lvwStudents.Items.Add(lviStudent)
    Next
End Sub

You can use the For Each loop to visit the members of a DataColumnCollection collection. Like the DataColumnCollection class, the DataRowCollection class implements the GetEnumerator() method of the IEnumerable interface. This means that you can use the For Each loop on a collection of records to visit each member. Here is an example:

Private Sub Exercise_Load(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) _
                              Handles MyBase.Load
    . . . No Change

    For Each StudentRecord As DataRow In tblRegistration.Rows
        Dim lviStudent As ListViewItem = _
		New ListViewItem(CInt(StudentRecord(0)))
        lviStudent.SubItems.Add(StudentRecord(1))
        lviStudent.SubItems.Add(StudentRecord(2))
        lviStudent.SubItems.Add(StudentRecord(3))

        lvwStudents.Items.Add(lviStudent)
    Next
End Sub

Instead of using the index of a column, you can locate a value using the object name of its column. To do this, you can use the following syntax of the DataRow indexed property:

Public Property Item(columnName As String) As Object

This property expects the object name of the column passed in its square brackets. Here are examples:

Private Sub Exercise_Load(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) _
                              Handles MyBase.Load
    . . . No Change

    For i As Integer = 0 To tblRegistration.Rows.Count - 1
        Dim StudentRecord As DataRow = tblRegistration.Rows(i)

        Dim lviStudent As ListViewItem = _
                New ListViewItem(CInt(StudentRecord("StudentNumber")))
        lviStudent.SubItems.Add(StudentRecord("FirstName"))
        lviStudent.SubItems.Add(StudentRecord("LastName"))
        lviStudent.SubItems.Add(StudentRecord("Gender"))

        lvwStudents.Items.Add(lviStudent)
    Next
End Sub

Instead of using the index or the object name of a column, you can also locate a value using the variable name of its column. To do this, you can use the following syntax of the DataRow indexed property:

Public Property Item(column As DataColumn) As Object

This property expects the object name of the column passed in its square brackets. Here are examples:

Private Sub Exercise_Load(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) _
                              Handles MyBase.Load
    . . . No Change

    For i As Integer = 0 To tblRegistration.Rows.Count - 1
        Dim StudentRecord As DataRow = tblRegistration.Rows(i)

        Dim lviStudent As ListViewItem = _
                New ListViewItem(CInt(StudentRecord(colStudentNumber)))
        lviStudent.SubItems.Add(StudentRecord(colFirstName))
        lviStudent.SubItems.Add(StudentRecord(colLastName))
        lviStudent.SubItems.Add(StudentRecord(colGender))

        lvwStudents.Items.Add(lviStudent)
    Next
End Sub

As mentioned already, to access a record, you can pass its index to the indexed property of the DataRowCollection, which produces a DataRow object. Using these concepts, you can access the values of a table. Here is an example:

Private Sub Exercise_Load(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) _
                              Handles MyBase.Load
    . . . No Change

    For Each StudentRecord As DataRow In tblRegistration.Rows
        For Each col As DataColumn In tblRegistration.Columns
            MsgBox(CStr(StudentRecord(col)))
        Next
        Exit For
    Next
End Sub

This code allows you to access a record using a row of a table and to locate a value based on the name of its column, but the above code does not allow you to clearly identify the column whose value you want to access. To clearly locate a value, you should name its column and to do this, you can pass the column name to the indexed property of the record. Here are examples:

Private Sub Exercise_Load(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) _
                              Handles MyBase.Load
    . . . No Change

    For Each StudentRecord As DataRow In tblRegistration.Rows
        For Each col As DataColumn In tblRegistration.Columns
            Dim lviStudent As ListViewItem = _
                New ListViewItem(CInt(StudentRecord("StudentNumber")))
            lviStudent.SubItems.Add(StudentRecord("FirstName"))
            lviStudent.SubItems.Add(StudentRecord("LastName"))
            lviStudent.SubItems.Add(StudentRecord("Gender"))

            lvwStudents.Items.Add(lviStudent)
            Exit For
        Next
    Next
End Sub

When using any of these previous techniques (whether using For or For Each), if you specify an index that is either less than 0 or beyond the number of records in the table, the compiler would throw an IndexOutOfRangeException exception.