Home

Databases Fundamentals: Data Records

 

Tables Records

 

Introduction to Records

In our description of tables, we saw that a table was made of one or various columns that represented categories of data. After creating such a table and its columns that represent the categories, you (actually the user) can enter values in the table to make it a valuable list. Filling up a table with values is referred to as data entry.

Data entry is performed by entering a value under the column headers. The group of values that correspond to the same entry or the same line under the columns is called a record. This also means that the records are entered one line, also called a row, at a time. Here is a table filled with various records:

A record on a table is represented as a row (horizontal) of data. 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 in fact an object of the DataRowCollection class. The DataRowCollection class provides the necessary properties and methods you can use to create and manage records of a table. A row itself is an object based on the DataRow class.

This lesson is done in two parts: the explanation of database topics and the Practical Learning sections intended to apply the concepts. The explanation sections are as complete as possible so you don't have to follow the Practical Learning exercises. But if you want to perform the assignments in the Practical Learning sections, you must be familiar with the list view control.
 

Practical Learning Practical Learning: Introducing Data Records

  1. Start Microsoft Visual Studio .NET and create a new Windows Forms Application named AddressBook1
  2. In the Toolbox, click the Data button.
    To create a DataSet, click DataSet and click the form
     
  3. In the Add Dataset dialog box, click the Untyped Dataset radio button and click OK
  4. While the DataSet control is still selected, in the Properties window, click (Name) and type dsAddressBook
  5. Still in the Properties window, set the DataSetName to AddressBook
  6. Right-click the form and click View Code
  7. In the top section of the file, type the following:
     
    Imports System.IO
    Imports System.Xml
    Imports System.Globalization
    
    Public Class Form1
        Inherits System.Windows.Forms.Form
        Private strFilename As String
  8. In the Class Name combo box, select (Form1 Events)
  9. In the Method Name combo box, select Load
  10. Implement the Load event as follows:
     
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) _
    			Handles MyBase.Load
            ' This is the file that will hold the records
            strFilename = "PeopleInMyLife.xml"
    
            ' If that file exists already, open it
            If File.Exists(strFilename) Then
                Me.dsAddressBook.ReadXml(strFilename)
            Else ' If it doesn't exist already, then create it
                Me.dsAddressBook.WriteXml(strFilename)
            End If
    End Sub
  11. Return to the form.
    As done for the lesson on columns, under the form, click dsAddressBook if necessary. In the Properties window, click the Tables field to reveal its ellipsis button ellipsis
  12. Click the ellipsis button ellipsis
  13. In the Tables Collection Editor, click the Add button and change its properties as follows:
    (Name): tblPersons
    TableName:
    Persons
  14. Click the Columns field to reveal its ellipsis button ellipsis and click its ellipsis button ellipsis. In the Columns Collection Editor, click the Add button and, from what we learned about tables and columns, create the columns as follows:
     
    Table Column (Name) ColumnName Data Type
    Persons colFullName FullName  
    Persons colFriendlyName FriendlyName  
    Persons colGender Gender  
    Persons colRelationshipType TypeOfRelationship  
    Persons colKnownSince KnownSince  
    Persons colCurrentResidence CurrentResidence  
    Persons colPhoneNumber PhoneNumber  
    Persons colLastUpdate LastUpdate  
    Persons colNotifyIfEmergency NotifyIfEmergency System.Boolean
  15. Click Close twice
  16. Design the form as follows:
     
    Control Name Text Other Properties
    ListView lvwPeople   AllowColumnReorder: True
    Anchor: Top, Bottom, Left, Right
    FullRowSelect: True
    GridLines: True
    Button btnClose Close Anchor: Bottom, Right
  17. Double-click the Close button and implement its Click event as follows:
     
    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    		Handles btnClose.Click
            End
    End Sub
  18. Return to the form.
    Change the list view's View property to Details and click the ellipsis button of its Columns property
  19. Click Add and create the columns as follows:
     
    Column (Name) Text TextAlign Width
    FullName Full Name   120
    FriendlyName Friendly Name   80
    Gender Gender Center  
    Relationship Relationship   80
    KnownSince Known Since   100
    CurrentResidence Current Residence   100
    PhoneNumber Phone #   80
    LastUpdate Last Update Right 80
    NotifyIfEmergency Notify? Center  
  20. Click OK

Introduction to Data Entry

To allow the user to perform data entry, you must create an appropriate object meant for this task. You have various options. You can use various controls on the same view or provide a data sheet type of view such as the one available from the DataGrid control. In all cases, when the user performs data entry, by default, it is by entering one record at a time. Any time while the user is performing an operation on a record, the record has a status that can be identify by the DataRow.RowState property which is a value based on the DataRowState enumerator.

A record on a table is represented as a row of data. To support the various records that belong to a table, the DataTable class is equipped with the Rows property which is an object of type DataRowCollection with each record an object of type DataRow.

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:

Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        dtDirectors = New DataTable
        Dim rowDirector As DataRow = Me.dtDirectors.NewRow()
End Sub
 

Practical Learning Practical Learning: Introducing Data Entry

  1. In the Windows Forms section of the Toolbox, click ContextMenu and click the form
  2. On the form, click ContextMenu and click Type Here. Create the following menu items:
     
    Text (Name) Shortcut
    New Contact mnuNewContact CtrlN
    Edit Details mnuEditDetails CtrlE
    Delete Contact mnuDeleteContact Del
    Remove all Contacts mnuRemoveAll ShiftDel
  3. On the form, click the list view. In the Properties window, set its ContextMenu to contextMenu1
  4. To create a new form, on the main menu, click Project -> Add Windows Form...
  5. Set the Name to NewPerson and press Enter
  6. Design the form as follows:
     
    Control Name Text Other Properties
    Label   Full Name:  
    TextBox txtFullName   Modifiers: Public
    Button btnCreate Create DialogResult: OK
    Modifiers: Public
    Label   Friendly Name:  
    TextBox txtFriendlyName   Modifiers: Public
    Button btnClose Close DialogResult: Cancel
    Label   Gender:  
    ComboBox cboGenders Unknown Modifiers: Public
    Items: Female, Male,
    Unknown
    Relationship cboRelationships Undefined Modifiers: Public
    Items: Friend, Wife, Husband, Son, Daughter, Father, Mother, Brother, Sister, Uncle, Aunt, Cousin, Nephew, Niece, Classmate, Co-Worker, Boss, Employee, Business Partner
    Sorted: True
    Label   Known Since:  
    TextBox txtKnownSince   Modifiers: Public
    Label   Current Residence:  
    TextBox txtCurrentResidence   Modifiers: Public
    Label   Phone Number:  
    TextBox txtPhoneNumber   Modifiers: Public
    Label   Last Update:  
    DateTimePicker dtpLastUpdate   Modifiers: Public
    Format: Custom
    CustomFormat: dddd dd MMM yyyy
    CheckBox chkNotify Notify in case of emergency? Modifiers: Public
  7. Display the first form. Right-click it and click View Code
  8. Create a new procedure above the End Class line as follows:
     
    Private Sub RefreshContacts()
            If File.Exists(strFilename) Then
    
                Me.lvwPeople.Items.Clear()
                Dim xmlPeople As XmlDocument = New XmlDocument
                xmlPeople.Load(strFilename)
    
                Dim nodRoot As XmlElement = xmlPeople.DocumentElement
                Dim nodFullNames As XmlNodeList = nodRoot.GetElementsByTagName("FullName")
                Dim nodFriendlyNames As XmlNodeList = nodRoot.GetElementsByTagName("FriendlyName")
                Dim nodGenders As XmlNodeList = nodRoot.GetElementsByTagName("Gender")
                Dim nodRelationships As XmlNodeList = nodRoot.GetElementsByTagName("TypeOfRelationship")
                Dim nodKnownDates As XmlNodeList = nodRoot.GetElementsByTagName("KnownSince")
                Dim nodResidences As XmlNodeList = nodRoot.GetElementsByTagName("CurrentResidence")
                Dim nodPhoneNumbers As XmlNodeList = nodRoot.GetElementsByTagName("PhoneNumber")
                Dim nodLastUpdates As XmlNodeList = nodRoot.GetElementsByTagName("LastUpdate")
                Dim nodNotifications As XmlNodeList = nodRoot.GetElementsByTagName("NotifyIfEmergency")
    
                Dim i As Integer
                For i = 0 To nodFullNames.Count - 1 Step 1
    
                    Dim lviPeople As ListViewItem = New ListViewItem(nodFullNames(i).InnerText)
                    lviPeople.SubItems.Add(nodFriendlyNames(i).InnerText)
                    lviPeople.SubItems.Add(nodGenders(i).InnerText)
                    lviPeople.SubItems.Add(nodRelationships(i).InnerText)
                    lviPeople.SubItems.Add(nodKnownDates(i).InnerText)
                    lviPeople.SubItems.Add(nodResidences(i).InnerText)
                    lviPeople.SubItems.Add(nodPhoneNumbers(i).InnerText)
                    lviPeople.SubItems.Add(nodLastUpdates(i).InnerText)
                    lviPeople.SubItems.Add(nodNotifications(i).InnerText)
                    Me.lvwPeople.Items.Add(lviPeople)
                Next
            End If
    End Sub
  9. In the Class Name combo box, select (Form1 Events)
  10. In the Method Name combo box, select Load and change its code as follows:
     
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
            ' This is the file that will hold the records
            strFilename = "PeopleInMyLife.xml"
    
            ' If that file exists already, open it
            If File.Exists(strFilename) Then
                Me.dsAddressBook.ReadXml(strFilename)
                RefreshContacts()
            Else ' If it doesn't exist already, then create it
                Me.dsAddressBook.WriteXml(strFilename)
            End If
    End Sub
  11. Save all

Data Entry

 

Adding a Value Based on the Column Index

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. Each column can be identified by its index. Using this index, to assign a new value to the column, you can use the following version of the DataRow.Item property:

Overloads Public Default Property Item(ByVal columnIndex As Integer) As Object

Here is an example:

Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
			Handles btnCreate.Click
        dtDirectors = New DataTable
        Dim rowDirector As DataRow = Me.dtDirectors.NewRow()
        colDirector = dtDirectors.Columns.Add()

        rowDirector(0) = "Jonathan Demme"
End Sub

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 allows you to add a value for one column. To complete a record, you would have to create a value for each column.

 

Practical Learning Practical Learning: Adding a Value Based on the Column Index

  1. In the Class Name, select mnuNewContact
  2. In the Method Name, select Click
  3. To create a record using the column's indices, implement the event as follows:
     
    Private Sub mnuNewContact_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
    				Handles mnuNewContact.Click
            Dim dlgPerson As NewPerson = New NewPerson
    
            If dlgPerson.ShowDialog() = DialogResult.OK Then
                Dim rowPerson As DataRow = Me.tblPersons.NewRow()
                rowPerson(0) = dlgPerson.txtFullName.Text
                rowPerson(1) = dlgPerson.txtFriendlyName.Text
                rowPerson(2) = dlgPerson.cboGenders.Text
                rowPerson(3) = dlgPerson.cboRelationships.Text
                rowPerson(4) = dlgPerson.txtKnownSince.Text
                rowPerson(5) = dlgPerson.txtResidence.Text
                rowPerson(6) = dlgPerson.txtPhoneNumber.Text
                rowPerson(7) = dlgPerson.dtpLastUpdate.Value.ToString("d")
                rowPerson(8) = dlgPerson.chkNotify.Checked.ToString()
                Me.tblPersons.Rows.Add(rowPerson)
    
                Me.dsAddressBook.WriteXml("PeopleInMyLife.xml")
                RefreshContacts()
            End If
    End Sub
  4. Execute the application to test it and try creating a few persons
     
  5. Close the form(s) and preview the XML file in your browser
     
  6. Return to your programming environment

Adding a Value Based on the Column Variable Name

If you prefer to use the variable name of a column when adding the value, you can use the following version of the property:

Overloads Public Default Property Item(ByVal column As DataColumn) As Object

Here is an example of using this version of the property:

Dim dsVideoCollection As DataSet
    Dim dtDirectors As DataTable
    Dim dtVideoCategories As DataTable
    Dim dtRatings As DataTable
    Dim dtActors As DataTable
    Dim dtFormats As DataTable
    Dim colCategoryID As DataColumn
    Dim colCategory As DataColumn
    Dim colDirector As DataColumn
    Dim colDirectorID As DataColumn
    Dim colVideoTitle As DataColumn
    Dim colActor As DataColumn
    Dim dtVideos As DataTable

    Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
			Handles btnCreate.Click
        dtDirectors = New DataTable
        Dim rowDirector As DataRow = Me.dtDirectors.NewRow()
        colDirector = dtDirectors.Columns.Add()

        rowDirector(0) = "Jonathan Demme"
        dtDirectors = New DataTable

        dtVideoCategories = New DataTable("Categories")
        colCategory = New DataColumn("Category")
        dtVideoCategories.Columns.Add(colCategory)

        Dim rowCategory As DataRow = Me.dtVideoCategories.NewRow()
        rowCategory(colCategory) = "Documentary"
End Sub

Practical Learning Practical Learning: Adding a Value Based on the Column Variable Name

  1. Display the form and add a new Button
  2. Set its properties as follows:
    (Name): btnNewPerson
    Text: New Person
    Anchor: Bottom, Left
     
  3. Double-click the new button and implement its Click event as follows:
     
    Private Sub btnNewPerson_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNewPerson.Click
            Dim dlgPerson As NewPerson = New NewPerson
    
            If dlgPerson.ShowDialog() = DialogResult.OK Then
                Dim rowPerson As DataRow = Me.tblPersons.NewRow()
                rowPerson(colFullName) = dlgPerson.txtFullName.Text
                rowPerson(colFriendlyName) = dlgPerson.txtFriendlyName.Text
                rowPerson(colGender) = dlgPerson.cboGenders.Text
                rowPerson(colTypeOfRelationship) = dlgPerson.cboRelationships.Text
                rowPerson(colKnownSince) = dlgPerson.txtKnownSince.Text
                rowPerson(colCurrentResidence) = dlgPerson.txtResidence.Text
                rowPerson(colPhoneNumber) = dlgPerson.txtPhoneNumber.Text
                rowPerson(colLastUpdate) = dlgPerson.dtpLastUpdate.Value.ToString("d")
                rowPerson(colNotifyIfEmergency) = dlgPerson.chkNotify.Checked.ToString()
                Me.tblPersons.Rows.Add(rowPerson)
    
                Me.dsAddressBook.WriteXml("PeopleInMyLife.xml")
                RefreshContacts()
            End If
    End Sub
  4. Execute the application and try creating a few persons using the New Person button
     
  5. Close the form(s) and return to your programming environment

Adding a Value Based on the Column Object Name

To specify the name of the column, the DataRow class is equipped with an Item property that allows you to identify a column by its object name, by its variable name, or by its index. Based on this, the DataRow property is overloaded with three versions. One of the versions uses the following syntax:

Overloads Public Default Property Item(ByVal columnName As String) As Object

This property expects the object name of the column passed in its square brackets. When calling this property, you can assign it the desired value for the column. Here is an example:

Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        dtDirectors = New DataTable
        Dim rowDirector As DataRow = Me.dtDirectors.NewRow()
        colDirector = New DataColumn("Director")
        dtDirectors.Columns.Add(colDirector)

        rowDirector(0) = "Jonathan Demme"

        rowDirector = Me.dtDirectors.NewRow()
        rowDirector("Director") = "John Landis"

        dtVideoCategories = New DataTable("Categories")
        colCategory = New DataColumn("Category")
        dtVideoCategories.Columns.Add(colCategory)

        Dim rowCategory As DataRow = Me.dtVideoCategories.NewRow()
        rowCategory(colCategory) = "Documentary"
End Sub

After assigning the desired value to the row, to add the new value to a table, the DataRowCollection class provides the Add() method that is overloaded with two versions. The first version of this method uses the following syntax:

Overloads Public Sub Add(ByVal row As DataRow)

This method simply expects you to pass the DataRow object you previously defined. Here is an example:

Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        dtDirectors = New DataTable
        Dim rowDirector As DataRow = Me.dtDirectors.NewRow()
        colDirector = New DataColumn("Director")
        dtDirectors.Columns.Add(colDirector)

        rowDirector(0) = "Jonathan Demme"

        rowDirector = Me.dtDirectors.NewRow()
        rowDirector("Director") = "John Landis"

        Me.dtDirectors.Rows.Add(rowDirector)
End Sub

In the same way, you can identify each column of a table by its object name and assign it the appropriate value. Once the record is complete, you can add it to the table. Here is an example:

Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        dtVideos = New DataTable("ListOfVideos")
        Dim rowVideo As DataRow = Me.dtVideos.NewRow()

        Dim colVideos(7) As DataColumn
        colVideos(0) = New DataColumn("Title")
        colVideos(0).DataType = System.Type.GetType("System.String")
        colVideos(1) = New DataColumn("Director")
        colVideos(1).DataType = System.Type.GetType("System.String")
        colVideos(2) = New DataColumn("YearReleased", System.Type.GetType("System.Int16"))
        colVideos(3) = New DataColumn("Length", System.Type.GetType("System.String"))
        colVideos(4) = New DataColumn("Rating", System.Type.GetType("System.String"))
        colVideos(5) = New DataColumn("Format", System.Type.GetType("System.String"))
        colVideos(6) = New DataColumn("Category", System.Type.GetType("System.String"))

        dtVideos.Columns.AddRange(colVideos)

        rowVideo("Title") = "A Few Good Men"
        rowVideo("Director") = "Rob Reiner"
        rowVideo("YearReleased") = 1993
        rowVideo("Length") = "138 Minute"
        rowVideo("Rating") = "R"
        rowVideo("Format") = "VHS"
        rowVideo("Category") = "Drama"
        Me.dtVideos.Rows.Add(rowVideo)
End Sub

Practical Learning Practical Learning: Adding a Value Based on the Column Object Name

  1. Display the Code Editor for the first form. In the Class Name combo box, select lvwPeople
  2. In the Method Name combo box, select KeyDown
  3. Implement the event of the list view as follows:
     
    Private Sub lvwPeople_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles lvwPeople.KeyDown
            ' If the user pressed F4...
            If e.KeyCode = Keys.F4 Then
    
                ' ... Get ready to create a new record
                Dim dlgPerson As NewPerson = New NewPerson
    
                If dlgPerson.ShowDialog() = DialogResult.OK Then
    
                    Dim rowPerson As DataRow = Me.tblPersons.NewRow()
                    rowPerson("FullName") = dlgPerson.txtFullName.Text
                    rowPerson("FriendlyName") = dlgPerson.txtFriendlyName.Text
                    rowPerson("Gender") = dlgPerson.cboGenders.Text
                    rowPerson("TypeOfRelationship") = dlgPerson.cboRelationships.Text
                    rowPerson("KnownSince") = dlgPerson.txtKnownSince.Text
                    rowPerson("CurrentResidence") = dlgPerson.txtResidence.Text
                    rowPerson("PhoneNumber") = dlgPerson.txtPhoneNumber.Text
                    rowPerson("LastUpdate") = dlgPerson.dtpLastUpdate.Value.ToString("d")
                    rowPerson("NotifyIfEmergency") = dlgPerson.chkNotify.Checked.ToString()
                    Me.tblPersons.Rows.Add(rowPerson)
    
                    Me.dsAddressBook.WriteXml("PeopleInMyLife.xml")
                    RefreshContacts()
                End If
            End If
    End Sub
  4. Execute the application
  5. Press F4 and try creating a person
     
  6. Close the form(s) and return to your programming environment

Adding an Array of Records

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 don't need to explicitly identify them, you can store all values in an array and simply add the array as a complete record. To do this, you can use the second version of the DataRowCollection.Add() method whose syntax is:

Overloads Public Overridable Function Add(ByVal values() As Object) As DataRow

Here is an example:

Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        dtVideos = New DataTable("ListOfVideos")
        Dim rowVideo As DataRow = Me.dtVideos.NewRow()

        Dim colVideos(7) As DataColumn
        colVideos(0) = New DataColumn("Title")
        colVideos(0).DataType = System.Type.GetType("System.String")
        colVideos(1) = New DataColumn("Director")
        colVideos(1).DataType = System.Type.GetType("System.String")
        colVideos(2) = New DataColumn("YearReleased", System.Type.GetType("System.Int16"))
        colVideos(3) = New DataColumn("Length", System.Type.GetType("System.String"))
        colVideos(4) = New DataColumn("Rating", System.Type.GetType("System.String"))
        colVideos(5) = New DataColumn("Format", System.Type.GetType("System.String"))
        colVideos(6) = New DataColumn("Category", System.Type.GetType("System.String"))

        dtVideos.Columns.AddRange(colVideos)

        rowVideo("Title") = "A Few Good Men"
        rowVideo("Director") = "Rob Reiner"
        rowVideo("YearReleased") = 1993
        rowVideo("Length") = "138 Minute"
        rowVideo("Rating") = "R"
        rowVideo("Format") = "VHS"
        rowVideo("Category") = "Drama"
        Me.dtVideos.Rows.Add(rowVideo)

        Dim vdoRecord() As String = {"Fatal Attraction", "Adrian Lyne", "1987", _
          "120 Minute", "R", "DVD", "Drama"}

        Me.dtVideos.Rows.Add(vdoRecord)
End Sub
 

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.

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

Locating Records and Their Values

 

Locating a Record

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. To locate a record in the DataTable.Rows collection, the DataRowCollection class provides the Item property that is defined as follows:

Public Default ReadOnly Property Item(ByVal index As Integer) As DataRow

The records of a table are stored in a list (called the DataRowCollection). The first record, which in the example above has the title as "A Few Good Men" and the Director as "Rob Reiner", has an index of 0. The second record has an index of 1, and so on. Here is an example of using it to retrieve the information stored in a record:

Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        Dim row As DataRow = Me.dtVideos.Rows(4)
End Sub

When you pass an index to this property, the compiler would check whether the record exists. If a record with that index exists, its DataRow value is produced. 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.

To get the number of records that a table contains, access the Count property of its DataRowCollection. The Count property is inherited from the InternalDataCollectionBase class, which is the parent of many collection classes.

When the records of a DataTable object have been created, you can get their list as an array using its List property that is inherited from the InternalDataCollectionBase class. This property returns an ArrayList type of list.

 

Practical Learning Practical Learning: Locating a Record

  1. In the Class Name combo box, select lvwPeople
  2. In the Method Name combo box, select DoubleClick and implement its event as follows:
     
    Private Sub lvwPeople_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles lvwPeople.DoubleClick
            Dim selPerson As ListViewItem = Me.lvwPeople.SelectedItems(0)
            Dim indexOfPersonSelected As Integer = selPerson.Index
            Dim rowPerson As DataRow = Me.tblPersons.Rows(indexOfPersonSelected)
    End Sub
  3. Save all

Locating a Value

As mentioned already, a record is in fact one value or a group of values from each of the columns of the table. Consider the following table:

The "A Few Good Men" string is a value of the Title column. In the same way, "VH" is a value of the Format column. In some circumstances, you will need to locate a particular value in order to perform an operation on it. As seen above, you can start by locating the record you need and return its DataRow object. To know the table that the record belongs to, access its DataRow.Table property. This property is declared as follows:

Public ReadOnly Property Table As DataTable

To locate the value that a record holds under a particular column, the DataRow class provides the Item property that is overloaded with three versions (actually six, but we are interested in the first three only). One of the versions of this property uses the following syntax:

Overloads Public Default Property Item(ByVal columnName As String) As Object

To use this property, pass the object name of the column in the square brackets. The following example is based on the above table. It retrieves the title of the third video and displays it in the caption of the form:

Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        dtVideos = New DataTable("ListOfVideos")
        Dim rowVideo As DataRow = Me.dtVideos.NewRow()

        Dim colVideos(7) As DataColumn
        colVideos(0) = New DataColumn("Title")
        colVideos(0).DataType = System.Type.GetType("System.String")
        colVideos(1) = New DataColumn("Director")
        colVideos(1).DataType = System.Type.GetType("System.String")
        colVideos(2) = New DataColumn("YearReleased", System.Type.GetType("System.Int16"))
        colVideos(3) = New DataColumn("Length", System.Type.GetType("System.String"))
        colVideos(4) = New DataColumn("Rating", System.Type.GetType("System.String"))
        colVideos(5) = New DataColumn("Format", System.Type.GetType("System.String"))
        colVideos(6) = New DataColumn("Category", System.Type.GetType("System.String"))

        dtVideos.Columns.AddRange(colVideos)

        rowVideo("Title") = "A Few Good Men"
        rowVideo("Director") = "Rob Reiner"
        rowVideo("YearReleased") = 1993
        rowVideo("Length") = "138 Minute"
        rowVideo("Rating") = "R"
        rowVideo("Format") = "VHS"
        rowVideo("Category") = "Drama"
        Me.dtVideos.Rows.Add(rowVideo)

        Dim vdoRecord() As String = {"Fatal Attraction", "Adrian Lyne", "1987", _
          "120 Minute", "R", "DVD", "Drama"}

        Me.dtVideos.Rows.Add(vdoRecord)

        Dim row As DataRow = Me.dtVideos.Rows(1)
        Dim strVideoTitle As String = row("Title")

        Text = strVideoTitle
End Sub

Instead of using the index 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:

Overloads Public Default Property Item(ByVal column As DataColumn) As Object

This property expects the object name of the column passed in its square brackets. The third option you have is to identify the column by its index. To do this, use the following syntax of the DataRow indexed property:

Overloads Public Default Property Item(ByVal columnIndex As Integer) As Object

This property expects the index of the column.

 

Practical Learning Practical Learning: Locating a Value in a Record and Updating it

  1. To locate a value inside a record, change the DoubleClick events of the list view as follows:
     
    Private Sub lvwPeople_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles lvwPeople.DoubleClick
            Dim selPerson As ListViewItem = Me.lvwPeople.SelectedItems(0)
            Dim indexOfPersonSelected As Integer = selPerson.Index
            Dim rowPerson As DataRow = Me.tblPersons.Rows(indexOfPersonSelected)
    
            Dim dlgPerson As NewPerson = New NewPerson
    
            dlgPerson.txtFullName.Text = rowPerson("FullName")
            dlgPerson.txtFriendlyName.Text = rowPerson("FriendlyName")
            dlgPerson.cboGenders.Text = rowPerson("Gender")
            dlgPerson.cboRelationships.Text = rowPerson("TypeOfRelationship")
            dlgPerson.txtKnownSince.Text = rowPerson("KnownSince")
            dlgPerson.txtResidence.Text = rowPerson("CurrentResidence")
            dlgPerson.txtPhoneNumber.Text = rowPerson("PhoneNumber")
            dlgPerson.dtpLastUpdate.Value = rowPerson("LastUpdate")
    
            Dim strNotify As String = rowPerson("NotifyIfEmergency")
            Dim bNotity As Boolean = False
    
            If strNotify = "true" Then bNotity = True
    
            dlgPerson.chkNotify.Checked = bNotity
            dlgPerson.btnCreate.Text = "Update"
    
            If dlgPerson.ShowDialog() = DialogResult.OK Then
                rowPerson("FullName") = dlgPerson.txtFullName.Text
                rowPerson("FriendlyName") = dlgPerson.txtFriendlyName.Text
                rowPerson("Gender") = dlgPerson.cboGenders.Text
                rowPerson("TypeOfRelationship") = dlgPerson.cboRelationships.Text
                rowPerson("KnownSince") = dlgPerson.txtKnownSince.Text
                rowPerson("CurrentResidence") = dlgPerson.txtResidence.Text
                rowPerson("PhoneNumber") = dlgPerson.txtPhoneNumber.Text
                rowPerson("LastUpdate") = dlgPerson.dtpLastUpdate.Value.ToString("d")
                rowPerson("NotifyIfEmergency") = dlgPerson.chkNotify.Checked.ToString()
    
                Me.dsAddressBook.WriteXml("PeopleInMyLife.xml")
                RefreshContacts()
            End If
    End Sub
  2. Return to the form. Under the form, click contextMenu1
  3. On the form, click Context Menu and click Edit Details
  4. In the Events section of the Properties window, set the Click event to lvwPeople_DoubleClick
  5. Execute the application
  6. In the list view, double-click a record, change some of its values and

Record Maintenance

Once a table has been filled with records, you can perform maintenance operations on it such as changing some records or removing others. To remove a record from a table, you can call the DataRowCollection.Remove() method. Its syntax is:

Public Sub Remove(ByVal 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 this method, you must pass an exact identification of the record. If you don't 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(ByVal 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.

 

Practical Learning Practical Learning: Deleting Records

  1. In the Class Name combo box, select mnuDeleteContact
  2. In the Method Name combo box, select Click
  3. Implement the event as follows:
     
    Private Sub mnuDeleteContact_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuDeleteContact.Click
            Dim selPerson As ListViewItem = Me.lvwPeople.SelectedItems(0)
            Dim indexOfPersonSelected As Integer = selPerson.Index
            Dim answer As MsgBoxResult
    
            If indexOfPersonSelected >= 0 Then
                Dim rowPerson As DataRow = Me.tblPersons.Rows(indexOfPersonSelected)
                 answer = MsgBox("Are you sure you want to delete this record?", _
                                                 MsgBoxStyle.YesNo Or MsgBoxStyle.Information, _
                                                 "Deleting a Record")
                If answer = MsgBoxResult.Yes Then
                    Me.tblPersons.Rows.Remove(rowPerson)
                    Me.dsAddressBook.WriteXml(Me.strFilename)
                End If
                RefreshContacts()
            End If
    End Sub
  4. In the Class Name combo box, select mnuRemoveAll
  5. In the Method Name combo box, select Click
  6. Implement the event as follows:
     
    Private Sub mnuRemoveAll_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles mnuRemoveAll.Click
            Dim answer As MsgBoxResult
    
            answer = MsgBox("Are you sure you want to delete all records?", _
                                                 MsgBoxStyle.YesNo Or MsgBoxStyle.Information, _
                                                 "Deleting a Record")
            If answer = DialogResult.Yes Then
                Me.tblPersons.Rows.Clear()
                Me.dsAddressBook.WriteXml(Me.strFilename)
            End If
            RefreshContacts()
    End Sub
  7. Execute the application
  8. Close the form(s)
 

Previous Copyright © 2005-2012 FunctionX Next