Fundamentals of Record Maintenance

When a change has been made to a record, the compiler would like that record to hold a flag that indicates the type of change it has just received. This is referred to as the row state of a record. To support the flags, the System.Data namespace provides an enumeration named DataRowState. To apply this flag to a record, the DataRow class is equipped with a property named RowState.

Imagine you have a table in a data set and the table has the type of structure you want including the values, you can copy the content of that table and replicate it into a table of your data set. To support this operation, you can call the Copy() method of the DataTable class. Its syntax is:

Public Function Copy As DataTable

In the same way, you can copy various tables from a data set and replicate them in your data set. If you want to copy all tables from an existing data set into your data set, you can call the Copy() method from the DataSet class. Its syntax is:

Public Function Copy As DataSet

Here is an example:

Private Sub Exercise_Load(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) _
                              Handles MyBase.Load
    Dim dsSchool As DataSet = dsStudents.Copy()
    dgvStudents.DataSource = dsSchool
    dgvStudents.DataMember = dsSchool.Tables(0).TableName
End Sub

We have already learned different techniques of creating records for a table. Besides those, the DataRowCollection provides a method named InsertAt. Its syntax is:

Public Sub InsertAt(row As DataRow, pos As Integer)

The first argument is the collection of values to be added as the new record of the table. The pos argument is the position that the new record should assume in the table. If the position is set to 0, the record would be added as the first. Here are examples:

Private Sub Exercise_Load(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) _
                              Handles MyBase.Load
        Dim Record As DataRow = tblStudent.NewRow()

        Record(0) = "255084"
        Record(1) = "Gertrude"
        Record(2) = "Monay"
        Record(3) = "Female"
        tblStudent.Rows.InsertAt(Record, 0)

        Record = tblStudent.NewRow()

        Record(0) = "947225"
        Record(1) = "Raymond"
        Record(2) = "Kouma"
        Record(3) = "Male"
        tblStudent.Rows.InsertAt(Record, 0)

        Record = tblStudent.NewRow()

        Record(0) = "735395"
        Record(1) = "Alain"
        Record(2) = "Paulson"
        Record(3) = "Male"
        tblStudent.Rows.InsertAt(Record, 0)

        Record = tblStudent.NewRow()

        Record(0) = "293744"
        Record(1) = "Robert"
        Record(2) = "Bidoula"
        Record(3) = "Male"
        tblStudent.Rows.InsertAt(Record, 0)
End Sub

This would produce:

If the position is equal to or higher than the total number of records (DataRowCollection.Count), the new record would be added as the last record to the table (in both cases, the compiler would not throw an exception). Here are examples:

Private Sub Exercise_Load(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) _
                              Handles MyBase.Load
    Dim Record As DataRow = tblStudent.NewRow()

    Record(0) = "255084"
    Record(1) = "Gertrude"
    Record(2) = "Monay"
    Record(3) = "Female"
    tblStudent.Rows.InsertAt(Record, 0)

    Record = tblStudent.NewRow()

    Record(0) = "947225"
    Record(1) = "Raymond"
    Record(2) = "Kouma"
    Record(3) = "Male"
    tblStudent.Rows.InsertAt(Record, 0)

    Record = tblStudent.NewRow()

    Record(0) = "735395"
    Record(1) = "Alain"
    Record(2) = "Paulson"
    Record(3) = "Male"
    ' Add this as the last record
    tblStudent.Rows.InsertAt(Record, 100)

    Record = tblStudent.NewRow()

    Record(0) = "293744"
    Record(1) = "Robert"
    Record(2) = "Bidoula"
    Record(3) = "Male"
    ' Add this as the last record
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)
End Sub

Otherwise, you can insert a record between two existing records. Here is an example:

Private Sub Exercise_Load(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) _
                              Handles MyBase.Load
    Dim Record As DataRow = Nothing

    Record = tblStudent.NewRow()
    Record(0) = "255084"
    Record(1) = "Gertrude"
    Record(2) = "Monay"
    Record(3) = "Female"
    tblStudent.Rows.InsertAt(Record, 0)

    Record = tblStudent.NewRow()
    Record(0) = "947225"
    Record(1) = "Raymond"
    Record(2) = "Kouma"
    Record(3) = "Male"
    tblStudent.Rows.InsertAt(Record, 1)

    Record = tblStudent.NewRow()
    Record(0) = "735395"
    Record(1) = "Alain"
    Record(2) = "Paulson"
    Record(3) = "Male"
    tblStudent.Rows.InsertAt(Record, 2)

    Record = tblStudent.NewRow()
    Record(0) = "293744"
    Record(1) = "Robert"
    Record(2) = "Bidoula"
    Record(3) = "Male"
    ' Add the new record in the second position
    tblStudent.Rows.InsertAt(Record, 1)
End Sub

This would produce:

There are restrictions to this technique. If the table does not have a primary key whose value is incremental (where a DataColumn.AutoIncrement is set to True), if you specify a position between 0 and the maximum number of records, the new record can be inserted between two existing records. If the table has an DataColumn.AutoIncrement primary key as a column, the record would always be added as the last record.

When a new record has just been created, the compiler changes the status of that record using an appropriate member of the DataRowState enumeration. In this case, the record would be flagged as RowState.Added. This is done automatically, to find out whether a certain record currently has the RowState.Added value, you can check the value of its RowState property.

After creating a new record, to flag it as a new record, you can call the SetAdded() method of the DataRow class. Its syntax is:

Public Sub SetAdded

As seen in Lesson 17, editing a record consists of locating the record, identifying the value that needs to be changed and then changing it. You can perform this operation on one record at a time or on many records. If there are many records to be edited and you do not want a record to be used for anything else while that record is being updated, you can ask the compiler to suspend the activities on that record while the updating operation is going on. Before a record is changed, its status is set to DataRowState.Unchanged value.

To signal to the compiler that you are about to edit a record, the DataRow class is equipped with a method named BeginEdit. Its syntax is:

Public Sub BeginEdit

When this method is called, the compiler puts everything on hold on the record on which this method is called. After calling it, you can perform the desired changes. After performing the changes, to resume, you should call the EndEdit method of the DataRow class. Its syntax is:

Public Sub EndEdit

Here is an example:

Private Sub Exercise_Load(ByVal sender As System.Object, _
                          ByVal e As System.EventArgs) _
                          Handles MyBase.Load
    Dim Record As DataRow = Nothing

    ' Create the first record, its index is 0
    Record = tblStudent.NewRow()
    Record(0) = "255084"
    Record(1) = "Gertrude"
    Record(2) = "Monay"
    Record(3) = "Female"
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)

    ' Create the second record, its index is 1
    Record = tblStudent.NewRow()
    Record(0) = "947225"
    Record(1) = "Raymond"
    Record(2) = "Kouma"
    Record(3) = "Male"
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)
            
    ' Create the third record, its index is 2
    Record = tblStudent.NewRow()
    Record(0) = "735395"
    Record(1) = "Alain"
    Record(2) = "Paulson"
    Record(3) = "Male"
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)

    ' Create the fourth record, its index is 3
    Record = tblStudent.NewRow()
    Record(0) = "293744"
    Record(1) = "Robert"
    Record(2) = "Bidoula"
    Record(3) = "Male"
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)

    ' Edit the second record, whose index is 2
    ' Let the compiler know that you are starting to edit the record
    tblStudent.Rows(2).BeginEdit()
    tblStudent.Rows(2)(1) = "Georgette"
    tblStudent.Rows(2)(3) = "Female"
    tblStudent.Rows(2).BeginEdit()
End Sub

This would produce:

Notice that the third record has values different than the original.

While the changes are going on, if you find out that there is a problem and the record should not be updated, you can cancel the change. To support this, the DataRow class is equipped with a method named CancelEdit. Its syntax is:

Public Sub CancelEdit

When this method is called, the compiler would dismiss the current change that is being performed on the record. Here is an example:

Private Sub Exercise_Load(ByVal sender As System.Object, _
                          ByVal e As System.EventArgs) _
                          Handles MyBase.Load
    Dim Record as DataRow = tblStudent.NewRow()

    Record(0) = "255084"
    Record(1) = "Gertrude"
    Record(2) = "Monay"
    Record(3) = "Female"
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)

    Record = tblStudent.NewRow()

    Record(0) = "947225"
    Record(1) = "Raymond"
    Record(2) = "Kouma"
    Record(3) = "Male"
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)
            
    Record = tblStudent.NewRow()

    Record(0) = "735395"
    Record(1) = "Alain"
    Record(2) = "Paulson"
    Record(3) = "Male"
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)

    Record = tblStudent.NewRow()

    Record(0) = "293744"
    Record(1) = "Robert"
    Record(2) = "Bidoula"
    Record(3) = "Male"
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)

    tblStudent.Rows(2).BeginEdit()
    tblStudent.Rows(2)(1) = "Georgette"
    tblStudent.Rows(2)(3) = "Female"
    tblStudent.Rows(2).BeginEdit()

    tblStudent.Rows(1).BeginEdit()
    tblStudent.Rows(1)(1) = "Orlando"
    tblStudent.Rows(1).CancelEdit()
    tblStudent.Rows(1)(2) = "Khan"
    tblStudent.Rows(1).BeginEdit()
End Sub

This would produce:

When a record has been changed, the compiler changes its flag. In this case, it would be flagged as DataRowState.Modified. If you want to manually set this flag, you can call the SetModified() method of the DataRow class. Its syntax is:

Public Sub SetModified

This method should be called only if the record was previously set to DataRowState.Added or DataRowState.Unchanged.

The operations you perform on records, such as adding a new record, adding a series of records, deleting a record, deleting a group or records, or deleting all records, are referred to as changes. It is not unusual to find out that a change that is about to be made is invalid. After preparing a change but before committing it, if you know the change is right, you can ask the compiler to accept it. To support this, the DataRow class is equipped with a method named AcceptChanges. Its syntax is:

Public Sub AcceptChanges

This method allows you to ask the compiler to validate the changes that are about to be made on a record. Here is an example:

Private Sub Exercise_Load(ByVal sender As System.Object, _
                          ByVal e As System.EventArgs) _
                          Handles MyBase.Load
    Dim Record As DataRow = Nothing
            
    Record = tblStudent.NewRow()
    Record(0) = "255084"
    Record(1) = "Gertrude"
    Record(2) = "Monay"
    Record(3) = "Female"
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)

    Record = tblStudent.NewRow()
    Record(0) = "947225"
    Record(1) = "Raymond"
    Record(2) = "Kouma"
    Record(3) = "Male"
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)
    tblStudent.Rows(1).AcceptChanges()
            
    Record = tblStudent.NewRow()
    Record(0) = "735395"
    Record(1) = "Alain"
    Record(2) = "Paulson"
    Record(3) = "Male"
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)

    Record = tblStudent.NewRow()
    Record(0) = "293744"
    Record(1) = "Robert"
    Record(2) = "Bidoula"
    Record(3) = "Male"
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)
End Sub

On the other hand, if you find out that the change that is about to occur on a record should not be validated, you can cancel it. To support this, the DataRow class is equipped with a method named RejectChanges. Its syntax is:

Public Sub RejectChanges

Here is an example:

Private Sub Exercise_Load(ByVal sender As System.Object, _
                          ByVal e As System.EventArgs) _
                          Handles MyBase.Load
    Dim Record As DataRow = Nothing
            
    Record = tblStudent.NewRow()
    Record(0) = "255084"
    Record(1) = "Gertrude"
    Record(2) = "Monay"
    Record(3) = "Female"
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)

    Record = tblStudent.NewRow()
    Record(0) = "947225"
    Record(1) = "Raymond"
    Record(2) = "Kouma"
    Record(3) = "Male"
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)

    ' Dismiss the changes made to the second record
    tblStudent.Rows(1).RejectChanges()
           
    Record = tblStudent.NewRow()
    Record(0) = "735395"
    Record(1) = "Alain"
    Record(2) = "Paulson"
    Record(3) = "Male"
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)

    Record = tblStudent.NewRow()
    Record(0) = "293744"
    Record(1) = "Robert"
    Record(2) = "Bidoula"
    Record(3) = "Male"
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)
End Sub

This would produce:

Notice that, although 4 records were created, one was rejected.

After editing a record and indicating that you have accepted the changes, the record's status can receive a new status such as DataRowState.Added or DataRowState.Modified. This means that you can then call either the DataRow.SetAdded() or the DataRow.Setmodified() method. Here is an example:

Private Sub Exercise_Load(ByVal sender As System.Object, _
                          ByVal e As System.EventArgs) _
                          Handles MyBase.Load
    Dim Record As DataRow = Nothing
            
    Record = tblStudent.NewRow()
    Record(0) = "255084"
    Record(1) = "Gertrude"
    Record(2) = "Monay"
    Record(3) = "Female"
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)

    Record = tblStudent.NewRow()
    Record(0) = "947225"
    Record(1) = "Raymond"
    Record(2) = "Kouma"
    Record(3) = "Male"
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)
            
    Record = tblStudent.NewRow()
    Record(0) = "735395"
    Record(1) = "Alain"
    Record(2) = "Paulson"
    Record(3) = "Male"
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)
    tblStudent.Rows(2).AcceptChanges()

    Record = tblStudent.NewRow()
    Record(0) = "293744"
    Record(1) = "Robert"
    Record(2) = "Bidoula"
    Record(3) = "Male"
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)

    tblStudent.Rows(2).BeginEdit()
    tblStudent.Rows(2)(1) = "Georgette"
    tblStudent.Rows(2)(3) = "Female"
    tblStudent.Rows(2).BeginEdit()
    tblStudent.Rows(2).SetModified()
End Sub

To validate changes at the table level, the DataTable class is equipped with a method named AcceptChanges. Its syntax is:

Public Sub AcceptChanges

This method is used to validate changes that are about to be made on a table. To let you dismiss changes on a table level, the DataTable class is equipped with a method named RejectChanges. Its syntax is:

Public Sub RejectChanges

Here are examples of calling the DataTable.AcceptChange() and the DataTable.RejectChanges() methods:

Private Sub Exercise_Load(ByVal sender As System.Object, _
                          ByVal e As System.EventArgs) _
                          Handles MyBase.Load
    Dim Record As DataRow = tblStudent.NewRow()

    Record(0) = "255084"
    Record(1) = "Gertrude"
    Record(2) = "Monay"
    Record(3) = "Female"
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)
    tblStudent.AcceptChanges()

    Record = tblStudent.NewRow()

    Record(0) = "947225"
    Record(1) = "Raymond"
    Record(2) = "Kouma"
    Record(3) = "Male"
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)
    tblStudent.AcceptChanges()

    Record = tblStudent.NewRow()

    Record(0) = "735395"
    Record(1) = "Alain"
    Record(2) = "Paulson"
    Record(3) = "Male"
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)
    tblStudent.RejectChanges()

    Record = tblStudent.NewRow()

    Record(0) = "293744"
    Record(1) = "Robert"
    Record(2) = "Bidoula"
    Record(3) = "Male"
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)
    tblStudent.AcceptChanges()
End Sub

To validate changes made on a DataSet object, you can call its own AcceptChanges method. To dismiss changes made on a DataSet object, you can call its RejectChanges method.

As we know already, a record can contain one or more values. Each value is identified by the column it belongs to. A column is said to be null if it does not have a value. Either you or the user of your database can set the value of a column to be null. The easiest way for the user is to skip a column when performing data entry. Here is an example:

Notice that the First Name column of the 5th record was left empty. Instead of the user, you too can leave a column empty during data entry. Here is an example:

Private Sub Exercise_Load(ByVal sender As System.Object, _
                          ByVal e As System.EventArgs) _
                          Handles MyBase.Load
    Dim Record As DataRow = Nothing
            
    Record = tblStudent.NewRow()
    Record(0) = "255084"
    Record(1) = "Gertrude"
    Record(2) = "Monay"
    Record(3) = "Female"
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)

    Record = tblStudent.NewRow()
    Record(0) = "947225"
    Record(1) = "Raymond"
    ' Record(2) = "Kouma"
    Record(3) = "Male"
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)
            
    Record = tblStudent.NewRow()
    Record(0) = "735395"
    Record(1) = "Alain"
    Record(2) = "Paulson"
    Record(3) = "Male"
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)
    
    Record = tblStudent.NewRow()
    Record(0) = "293744"
    Record(1) = "Robert"
    Record(2) = "Bidoula"
    Record(3) = "Male"
    tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)
End Sub

This would produce:

Notice that the Last Name column of the second record was left empty.

On an existing record, to find out whether the value of a column is null, you can call the IsNull() method of the DataRow class. It comes in various versions. To specify the column whose value you want to check, you can pass the object name, the index, or the variable name of the column as argument. Here are examples:

Private Sub Exercise_Load(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) _
                              Handles MyBase.Load
        Dim Record As DataRow = Nothing

        Record = tblStudent.NewRow()
        Record(0) = "255084"
        Record(1) = "Gertrude"
        Record(2) = "Monay"
        Record(3) = "Female"
        tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)

        Record = tblStudent.NewRow()
        Record(0) = "947225"
        Record(1) = "Raymond"
        Record(3) = "Male"
        tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)

        Record = tblStudent.NewRow()
        Record(0) = "735395"
        Record(2) = "Paulson"
        tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)

        Record = tblStudent.NewRow()
        Record(0) = "293744"
        Record(1) = "Robert"
        Record(2) = "Bidoula"
        Record(3) = "Male"
        tblStudent.Rows.InsertAt(Record, tblStudent.Rows.Count)

        If tblStudent.Rows(0).IsNull("LastName") Then
            MsgBox("The last name of the first record is null.")
        Else
            MsgBox("The last name of the first record is " & _
                            """" & tblStudent.Rows(0)(2).ToString() & """.")
        End If

        If tblStudent.Rows(1).IsNull(2) Then
            MsgBox("The last name of the second record is null.")
        Else
            MsgBox("The last name of the second record is " & _
                            """" & tblStudent.Rows(1)("LastName").ToString() & ".")
        End If
End Sub

This would produce:

Most of the operations you want to perform on a record require that you identify the record you want to work on. In Lesson 17, we saw different techniques of locating a record. Additional techniques allow you to find a record or to check the existence of a certain record in a table. Consider an application with the following forms:

Imports System.IO

Public Class Exercise

    Private Sub btnNewStudent_Click(ByVal sender As System.Object, _
                                    ByVal e As System.EventArgs) _
                                    Handles btnNewStudent.Click
        Dim i As Integer
        Dim Editor As StudentEditor = New StudentEditor()

        If Editor.ShowDialog() = DialogResult.OK Then
            Dim Record As DataRow = tblStudent.NewRow()

            Record(0) = Editor.txtStudentNumber.Text
            Record(1) = Editor.txtFirstName.Text
            Record(2) = Editor.txtLastName.Text
            Record(3) = Editor.dtpDateOfBirth.Value.ToString("d")
            Record(4) = Editor.CbxGenders.Text

            tblStudent.Rows.Add(Record)
            dsStudents.WriteXml("students.xml")

            lvwStudents.Items.Clear()

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

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

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

    Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        Dim i As Integer
        lvwStudents.Items.Clear()
        Dim Filename As String = "students.xml"

        If File.Exists(Filename) Then
            dsStudents.ReadXml(Filename)

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

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

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

    Private Sub btnClose_Click(ByVal sender As Object, _
                               ByVal e As System.EventArgs) _
                               Handles btnClose.Click
        End
    End Sub
End Class

Once a table has been filled with records, you can perform maintenance operations on it such as changing some records or removing others. Editing a record consists of changing one of the values of the record under a particular column. If you are using a data grid view, to select a record, the user can click the row header; that is, the gray box on the left side of a record. Here is an example:

There are various ways you can programmatically select a record. The general steps you can follow are:

1. Make sure the table has at least one column that can be used to uniquely identify each record. For example, when creating a table for employees, you can assign a unique number to each employee. The same would go for students. If you are creating a table for a collection of items, such as a book or a video collection, a commercial store that sells items such as auto parts, make sure each item has a certain value that is unique to it, such as a shelf number
2. Before editing a record, make the user aware of the existing values. You can do this by displaying the records of the database
3. Let the user specify a value of the unique column. For a table that contains employees information, you can ask the user to enter the employee number of the record to edit. The same would be for a book or video collection, a commercial store that sells items, etc
4. Use that value to locate the particular record that needs to be changed

To perform these steps, you use a combination of the techniques we have reviewed so far: locate the table, display the records, locate the record, locate the column, assign the value to the column of a record, save the table. Here is an example:

Private Sub lvwStudents_DoubleClick(ByVal sender As System.Object, _
                                    ByVal e As System.EventArgs) _
                                    Handles lvwStudents.DoubleClick
    ' If no student ain't selected
    ' or more than one student is selected,
    ' don't do nothing. Know'm Sayin'?
    If (lvwStudents.SelectedItems.Count = 0) Or _
        (lvwStudents.SelectedItems.Count > 1) Then
        Exit Sub
    End If

    ' Get a reference to the Student Editor dialog box
    Dim Editor As StudentEditor = New StudentEditor

    ' Since a student is selected,
    ' get its values and transfer them to the dialog box
    Editor.txtStudentNumber.Text = _
	lvwStudents.SelectedItems(0).SubItems(0).Text
    Editor.txtFirstName.Text = _
	lvwStudents.SelectedItems(0).SubItems(1).Text
    Editor.txtLastName.Text = lvwStudents.SelectedItems(0).SubItems(2).Text
    Editor.dtpDateOfBirth.Value = _
	DateTime.Parse(lvwStudents.SelectedItems(0).SubItems(3).Text)
    Editor.CbxGenders.Text = lvwStudents.SelectedItems(0).SubItems(4).Text

    ' Display the dialog box
    Editor.ShowDialog()
End Sub

The techniques we studied in Lesson 17 to locate a record should work in any table but they can be complex on a large table with many columns and various records. For example, you must make sure you can uniquely identify each record. This can be difficult because one column would not be enough and you may need to use a combination of columns just to isolate one particular record. In the previous lesson, we saw that the use of a primary key in a table makes it possible to have a unique value that can be used to identify each particular record.

Consider the following:

On a table that has a primary key, to assist you with finding a record, the DataRowCollection class provides a method named Find that is overloaded with two versions. One of the versions uses the following syntax:

Public Function Find(key As Object) As DataRow

This method expects a mechanism to find a record. The argument should be the value of a primary key. Here are two examples used to find a record using a primary key:

Private Sub lvwStudents_DoubleClick(ByVal sender As System.Object, _
                                    ByVal e As System.EventArgs) _
                                    Handles lvwStudents.DoubleClick
        Dim i As Integer
        ' If no student ain't selected
        ' or more than one student is selected,
        ' don't do nothing
        If (lvwStudents.SelectedItems.Count = 0) Or _
        (lvwStudents.SelectedItems.Count > 1) Then
            Exit Sub
        End If

        ' Get a reference to the Student Editor dialog box
        Dim Editor As StudentEditor = New StudentEditor

        ' Since a student is selected,
        ' find the record that uses the selected student number as 
        ' its primary key.
        ' Show the values of that record in the dialog box
        Dim SelectedStudent As DataRow = tblStudent.Rows.Find( _
		lvwStudents.SelectedItems(0).SubItems(0).Text)
        Editor.txtStudentNumber.Text = SelectedStudent(0).ToString()
        Editor.txtFirstName.Text = SelectedStudent(1).ToString()
        Editor.txtLastName.Text = SelectedStudent(2).ToString()
        Editor.dtpDateOfBirth.Value = DateTime.Parse(SelectedStudent(3).ToString())
        Editor.CbxGenders.Text = SelectedStudent(4).ToString()
        Editor.txtStudentNumber.Enabled = False

        ' Display the dialog box
        ' If the user makes changes and clicks OK
        If Editor.ShowDialog() = DialogResult.OK Then
            ' Find the record that uses that student number
            ' as its primary key and update it (the record)
            SelectedStudent = tblStudent.Rows.Find(Editor.txtStudentNumber.Text)

            SelectedStudent(1) = Editor.txtFirstName.Text
            SelectedStudent(2) = Editor.txtLastName.Text
            SelectedStudent(3) = Editor.dtpDateOfBirth.Value.ToString("d")
            SelectedStudent(4) = Editor.CbxGenders.Text

            dsStudents.WriteXml("students.xml")

            lvwStudents.Items.Clear()

            For i = 0 To tblStudent.Rows.Count - 1
                SelectedStudent = tblStudent.Rows(i)

                Dim lviStudent As ListViewItem = _
			New ListViewItem(SelectedStudent(0).ToString())
                lviStudent.SubItems.Add(SelectedStudent(1))
                lviStudent.SubItems.Add(SelectedStudent(2))
                lviStudent.SubItems.Add(SelectedStudent(3))
                lviStudent.SubItems.Add(SelectedStudent(4))

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

On a typical table, you may want to find out whether it contains a certain record. To assist you with this, the DataRowCollection class is equipped with a method named Contains and that is overloaded with two versions. One of the versions uses the following syntax:

Public Function Contains(key As Object) As Boolean

This method expects a value that should be a primary key of the table that holds the records. Here is an example:

Private Sub lvwStudents_DoubleClick(ByVal sender As System.Object, _
                                        ByVal e As System.EventArgs) _
                                        Handles lvwStudents.DoubleClick
        ' If no student ain't selected
        ' or more than one student is selected,
        ' don't do nothing
        If (lvwStudents.SelectedItems.Count = 0) Or _
        (lvwStudents.SelectedItems.Count > 1) Then
            Exit Sub
        End If

        If tblStudent.Rows.Contains( _
		lvwStudents.SelectedItems(0).SubItems(0).Text) Then
            MsgBox("This record exists in the data table.")
        End If
End Sub

If you have a record you do not need, you can remove it from the table. If you are using the data grid view, to visually delete a record, first select it by clicking its row header, and then press Delete.

To support the ability to remove a record, the DataRow class is equipped with a method named Delete. Its syntax is:

Public Sub Delete

To programmatically delete a record, first locate it by its index, get a DataRow reference to the record to be removed, and then call the Delete() method on it. Once again, you would need a way to uniquely identify a record.

Here is an example:

Private Sub lvwStudents_KeyUp(ByVal sender As System.Object, _
                                  ByVal e As System.Windows.Forms.KeyEventArgs) _
                                  Handles lvwStudents.KeyUp
        Dim i As Integer
        ' If no student ain't selected
        ' or more than one student is selected,
        ' don't do nothing
        If (lvwStudents.SelectedItems.Count = 0) Or _
            (lvwStudents.SelectedItems.Count > 1) Then
            Exit Sub
        End If

        ' Since/while a student is selected,
        ' find out if the user had pressed Delete
        If e.KeyCode = Keys.Delete Then
            ' Check each record in the Student table
            For Each StudentRecord As DataRow In tblStudent.Rows
                ' Look for the record that has the student number that was selected
                ' If you find such a record
                If StudentRecord("StudentNumber").ToString() = _
			lvwStudents.SelectedItems(0).SubItems(0).Text Then
                    ' Verify that that the user really wants to delete the record
                    If MsgBox("Do you want to delete this student's record?", _
                              MsgBoxStyle.YesNo Or MsgBoxStyle.Question) = _
				MsgBoxResult.Yes Then
                        ' If/since the user wants to delete the record, do it
                        StudentRecord.Delete()
                        ' Save the records                        
                        dsStudents.WriteXml("students.xml")
                        ' Get out
                        Exit For
                    End If
                    ' This code assumes that each record has a unique student number
                End If
            Next

            lvwStudents.Items.Clear()

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

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

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

Besides the DataRow class, the DataRowCollection class provides its own means of deleting a record from a table. To delete a record, you can call the DataRowCollection.Remove() method. Its syntax is:

Public Sub Remove(row As DataRow)

This method takes as argument a DataRow object and checks whether the table contains it. If that record exists, it gets deleted, including all of its entries for each column.

When calling the DataRowCollection.Remove() method, you must pass an exact identification of the record. If you do not have that identification, you can delete a record based on its index. To do this, you would call the DataRowCollection.RemoveAt() method. Its syntax is:

Public Sub RemoveAt(index As Integer)

This method takes as argument the index of the record you want to delete. If a record with that index exists, it would be deleted.

To delete all records of a table, call the DataRowCollection.Clear() method. Its syntax is:

Public Sub Clear

This method is used to clear the table of all records. Here is an example:

Private Sub btnClear_Click(ByVal sender As Object, _
                               ByVal e As System.EventArgs) _
                               Handles btnClear.Click
    tblStudent.Rows.Clear()
End Sub

If you have many tables in a data set and you want to delete all records in all tables, you can call the Clear() method of the DataSet class. Its syntax is:

Public Sub Clear

Here is an example:

Private Sub btnClear_Click(ByVal sender As Object, _
                           ByVal e As System.EventArgs) _
                           Handles btnClear.Click
    tblStudent.Rows.Clear()
End Sub