Visual Basic .NET Databases: Data Records

	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: Introducing Data Records

Start Microsoft Visual Studio .NET and create a new Windows Forms Application named AddressBook1
In the Toolbox, click the Data button.
To create a DataSet, click DataSet and click the form
In the Add Dataset dialog box, click the Untyped Dataset radio button and click OK
While the DataSet control is still selected, in the Properties window, click (Name) and type dsAddressBook
Still in the Properties window, set the DataSetName to AddressBook
Right-click the form and click View Code

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

In the Class Name combo box, select (Form1 Events)
In the Method Name combo box, select Load

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

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
Click the ellipsis button
In the Tables Collection Editor, click the Add button and change its properties as follows:
(Name): tblPersons
TableName: Persons

Click the Columns field to reveal its ellipsis button

and click its ellipsis button

. 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

Click Close twice

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

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

Return to the form.
Change the list view's View property to Details and click the ellipsis button of its Columns property

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

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: Introducing Data Entry

In the Windows Forms section of the Toolbox, click ContextMenu and click the form

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

On the form, click the list view. In the Properties window, set its ContextMenu to contextMenu1
To create a new form, on the main menu, click Project -> Add Windows Form...
Set the Name to NewPerson and press Enter

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

Display the first form. Right-click it and click View Code

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

In the Class Name combo box, select (Form1 Events)

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

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: Adding a Value Based on the Column Index

In the Class Name, select mnuNewContact
In the Method Name, select Click

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

Execute the application to test it and try creating a few persons
Close the form(s) and preview the XML file in your browser
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: Adding a Value Based on the Column Variable Name

Display the form and add a new Button
Set its properties as follows:
(Name): btnNewPerson
Text: New Person
Anchor: Bottom, Left

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

Execute the application and try creating a few persons using the New Person button
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: Adding a Value Based on the Column Object Name

Display the Code Editor for the first form. In the Class Name combo box, select lvwPeople
In the Method Name combo box, select KeyDown

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

Execute the application
Press F4 and try creating a person
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: Locating a Record

In the Class Name combo box, select lvwPeople

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

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: Locating a Value in a Record and Updating it

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

Return to the form. Under the form, click contextMenu1
On the form, click Context Menu and click Edit Details
In the Events section of the Properties window, set the Click event to lvwPeople_DoubleClick
Execute the application
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: Deleting Records

In the Class Name combo box, select mnuDeleteContact
In the Method Name combo box, select Click

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

In the Class Name combo box, select mnuRemoveAll
In the Method Name combo box, select Click

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

Execute the application
Close the form(s)


Previous	Copyright © 2005-2016, FunctionX	Next