ADO.NET and Visual Basic .NET Examples: The Music Store

	List-Based Applications

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: Creating the Application

Start Microsoft Visual Studio .NET and create a new Windows Application named MusicStore2
To add a new form, on the main menu, click Project -> Windows Form...
Set the Name to DataCenter and press Enter
To add a new form, on the main menu, click Project -> Windows Form...
Set the Name to Categories and press Enter

Design the form as follows:

Control	Name	Text	Other Properties
DataGrid			Auto Format: Professional 3
Label		New Category:
TextBox	txtNewCategory
Button	btnAdd	Add
Button	btnClose	Close
Form			AcceptButton: btnAdd CancelButton: btnClose FormBorderStyle: FixedDialog MaximizeBox: False ShowInTaskbar: False StartPosition: CenterScreen

To add a new form, on the main menu, click Project -> Windows Form...
Set the Name to ItemTypes and press Enter

Design the form as follows:

Control	Name	Text	Other Properties
DataGrid			Auto Format: Professional 3
Label		New Item Type:
TextBox	txtNewItemType
Button	btnAdd	Add
Button	btnClose	Close
Form			AcceptButton: btnAdd CancelButton: btnClose FormBorderStyle: FixedDialog MaximizeBox: False ShowInTaskbar: False StartPosition: CenterScreen

To add a new form, on the main menu, click Project -> Windows Form...
Set the Name to NewStoreItem and press Enter

Design the form as follows:

Control	Name	Text	Other Properties
Label		Category:
ComboBox	cboCategories		DropDownStyle: DropDownList Sorted: True
Button	btnNewCategory	New
Label		Item Type:
ComboBox	cboItemTypes		DropDownStyle: DropDownList Sorted: True
Button	btnNewType	New
Label		Item Name:
TextBox	txtItemName
Label		Unit Price:
TextBox	txtUnitPrice	0.00	AlignText: Right
Label		Item #:
TextBox	txtItemNumber
Button	btnCreate	Create
Button	btnClose	Close
Form			AcceptButton: btnCreate CancelButton: btnClose MaximizeBox: False StartPosition: CenterScreen

Double-click an unoccupied area of the form to access its Load event and implement its as follows:

Private Sub NewStoreItem_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ' We will generate a random number for the store item
        Dim tmeNow As DateTime = DateTime.Now
        Dim rndNumber As Random = New Random(tmeNow.Millisecond)
        Dim strNumber As String = CStr(rndNumber.Next(100000, 999999))

        ' Display the new number in the Part # text box
        Me.txtItemNumber.Text = strNumber

        ' Disable the OK button to indicate that the item is not ready
        Me.btnCreate.Enabled = False
End Sub

In the Class Name combo box, select btnNewCategory

In the Method Name combo box, select Click and implement the event as follows:

Private Sub btnNewCategory_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnNewCategory.Click
        Dim frmCat As Categories

        frmCat = New Categories
        frmCat.ShowDialog()
End Sub

In the Class Name combo box, select btnNewType

In the Method Name combo box, select Click and implement the event as follows:

Private Sub btnNewType_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnNewType.Click
        Dim frmTypes As ItemTypes

        frmTypes = New ItemTypes
        frmTypes.ShowDialog()
End Sub

To add a new form, on the main menu, click Project -> Add Windows Form...
Set the Name to PurchaseOrder and press Enter

Design the form as follows:

Control	Name	Text	Other Properties
GroupBox		Selection of Sale Item
Label		Category
Label		Types
Label		Available Items
Button	btnNewItem	New Item
ListBox	lbxCategories
ListBox	lbxItemTypes
ListBox	lbxAvailableItems
GroupBox		Items Sold
Label		Item #
Label		Item Name
Label		Unit Price
Label		Qty
Label		Sub Total
TextBox	txtItemNumber1
TextBox	txtItemName1
TextBox	txtUnitPrice1	0.00	AlignText: Right
TextBox	txtQuantity1	0	AlignText: Right
TextBox	txtSubTotal1	0.00	AlignText: Right
TextBox	txtItemNumber2
TextBox	txtItemName2
TextBox	txtUnitPrice2	0.00	AlignText: Right
TextBox	txtQuantity2	0	AlignText: Right
TextBox	txtSubTotal2	0.00	AlignText: Right
TextBox	txtItemNumber3
TextBox	txtItemName3
TextBox	txtUnitPrice3	0.00	AlignText: Right
TextBox	txtQuantity3	0	AlignText: Right
TextBox	txtSubTotal3	0.00	AlignText: Right
TextBox	txtItemNumber4
TextBox	txtItemName4
TextBox	txtUnitPrice4	0.00	AlignText: Right
TextBox	txtQuantity4	0	AlignText: Right
TextBox	txtSubTotal4	0.00	AlignText: Right
TextBox	txtItemNumber5
TextBox	txtItemName5
TextBox	txtUnitPrice5	0.00	AlignText: Right
TextBox	txtQuantity5	0	AlignText: Right
TextBox	txtSubTotal5	0.00	AlignText: Right
TextBox	txtItemNumber6
TextBox	txtItemName6
TextBox	txtUnitPrice6	0.00	AlignText: Right
TextBox	txtQuantity6	0	AlignText: Right
TextBox	txtSubTotal6	0.00	AlignText: Right
GroupBox		Order Processing
Button	btnCalculate	Calculate Order
Button	btnSave	Add to Today's Orders
Button	btnClose	Close
Label		Total Order:
TextBox	txtTotalOrder	0.00	AlignText: Right Enabled: False
Form			CancelButton: btnClose MaximizeBox: False StartPosition: CenterScreen

Double-click the New Item button and implement its Click event as follows:

Private Sub btnNewItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNewItem.Click
        Dim frm As NewStoreItem = New NewStoreItem
        frm.ShowDialog()
End Sub

In the Class Name combo box, select btnCalculate

In the Method Name combo box,, select Click and implement its event as follows:

Private Sub btnCalculate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCalculate.Click
        Dim item1UnitPrice As Decimal = 0.0
        Dim item2UnitPrice As Decimal = 0.0
        Dim item3UnitPrice As Decimal = 0.0
        Dim item4UnitPrice As Decimal = 0.0
        Dim item5UnitPrice As Decimal = 0.0
        Dim item6UnitPrice As Decimal = 0.0
        Dim quantity1 As Integer = 0
        Dim quantity2 As Integer = 0
        Dim quantity3 As Integer = 0
        Dim quantity4 As Integer = 0
        Dim quantity5 As Integer = 0
        Dim quantity6 As Integer = 0
        Dim item1SubTotal As Decimal = 0.0
        Dim item2SubTotal As Decimal = 0.0
        Dim item3SubTotal As Decimal = 0.0
        Dim item4SubTotal As Decimal = 0.0
        Dim item5SubTotal As Decimal = 0.0
        Dim item6SubTotal As Decimal = 0.0
        Dim totalOrder As Decimal

        Try
            item1UnitPrice = CDec(Me.txtUnitPrice1.Text)
        Catch ex As FormatException
            MsgBox("Invalid Unit Price")
            Me.txtUnitPrice1.Text = "0.00"
            Me.txtUnitPrice1.Focus()
        End Try

        Try
            quantity1 = CInt(Me.txtQuantity1.Text)

        Catch ex As FormatException
            MsgBox("Invalid Quantity")
            Me.txtQuantity1.Text = "0"
            Me.txtQuantity1.Focus()
        End Try

        Try

            item2UnitPrice = CDec(Me.txtUnitPrice2.Text)
        Catch ex As FormatException
            MsgBox("Invalid Unit Price")
            Me.txtUnitPrice2.Text = "0.00"
            Me.txtUnitPrice2.Focus()
        End Try

        Try
            quantity2 = CInt(Me.txtQuantity2.Text)
        Catch ex As FormatException
            MsgBox("Invalid Quantity")
            Me.txtQuantity2.Text = "0"
            Me.txtQuantity2.Focus()
        End Try

        Try
            item3UnitPrice = CDec(Me.txtUnitPrice3.Text)
        Catch ex As FormatException
            MsgBox("Invalid Unit Price")
            Me.txtUnitPrice3.Text = "0.00"
            Me.txtUnitPrice3.Focus()
        End Try

        Try
            quantity3 = CInt(Me.txtQuantity3.Text)
        Catch ex As FormatException
            MsgBox("Invalid Quantity")
            Me.txtQuantity3.Text = "0"
            Me.txtQuantity3.Focus()
        End Try

        Try
            item4UnitPrice = CDec(Me.txtUnitPrice4.Text)
        Catch ex As FormatException
            MsgBox("Invalid Unit Price")
            Me.txtUnitPrice4.Text = "0.00"
            Me.txtUnitPrice4.Focus()
        End Try

        Try
            quantity4 = CInt(Me.txtQuantity4.Text)
        Catch ex As FormatException
            MsgBox("Invalid Quantity")
            Me.txtQuantity4.Text = "0"
            Me.txtQuantity4.Focus()
        End Try

        Try
            item5UnitPrice = CDec(Me.txtUnitPrice5.Text)
        Catch ex As FormatException
            MsgBox("Invalid Unit Price")
            Me.txtUnitPrice5.Text = "0.00"
            Me.txtUnitPrice5.Focus()
        End Try

        Try
            quantity5 = CInt(Me.txtQuantity5.Text)
        Catch ex As FormatException
            MsgBox("Invalid Quantity")
            Me.txtQuantity5.Text = "0"
            Me.txtQuantity5.Focus()
        End Try

        Try
            item6UnitPrice = CDec(Me.txtUnitPrice6.Text)
        Catch ex As FormatException
            MsgBox("Invalid Unit Price")
            Me.txtUnitPrice6.Text = "0.00"
            Me.txtUnitPrice6.Focus()
        End Try

        Try
            quantity6 = CInt(Me.txtQuantity6.Text)
        Catch ex As FormatException
            MsgBox("Invalid Quantity")
            Me.txtQuantity6.Text = "0"
            Me.txtQuantity6.Focus()
        End Try

        item1SubTotal = item1UnitPrice * quantity1
        item2SubTotal = item2UnitPrice * quantity2
        item3SubTotal = item3UnitPrice * quantity3
        item4SubTotal = item4UnitPrice * quantity4
        item5SubTotal = item5UnitPrice * quantity5
        item6SubTotal = item6UnitPrice * quantity6

        totalOrder = item1SubTotal + item2SubTotal + item3SubTotal + _
                        item4SubTotal + item5SubTotal + item6SubTotal

        Me.txtSubTotal1.Text = Format(item1SubTotal, "F")
        Me.txtSubTotal2.Text = Format(item2SubTotal, "F")
        Me.txtSubTotal3.Text = Format(item3SubTotal, "F")
        Me.txtSubTotal4.Text = Format(item4SubTotal, "F")
        Me.txtSubTotal5.Text = Format(item5SubTotal, "F")
        Me.txtSubTotal6.Text = Format(item6SubTotal, "F")
        Me.txtTotalOrder.Text = Format(totalOrder, "F")
End Sub

Display the first form (Form1.vb (Design)) and design it as follows:

Control	Name	Text
Button	btnNewItem	New Store Item
Button	btnNewOrder	New Purchase Order
Button	btnClose	Close

Double-click the New Store Item button and implement its event as follows:

Private Sub btnNewItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNewItem.Click
        Dim frm As NewStoreItem = New NewStoreItem
        frm.ShowDialog()
End Sub

In the Class Name combo box, select btnNewOrder

In the Method Name combo box, select Click and implement its event as follows:

Private Sub btnNewOrder_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnNewOrder.Click
        Dim frm As PurchaseOrder = New PurchaseOrder
        frm.ShowDialog()
End Sub

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

Return to the main form. double-click the Close button and implement its Click event as follows:

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

Save all

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

Display the DataCenter form (DataCenter.vb (Design))
In the Toolbox, click the Data button.
To create a DataSet, click DataSet and click the DataModule 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 dsMusicStore
Still in the Properties window, set the DataSetName to MusicStore
Set its Modifiers property to Public so it can be easily accessed from other objects
Click the Tables field to reveal its ellipsis button and click the ellipsis button
In the Tables Collection Editor, click the Add button
Click (Name) and type tblCategories
Click TableName and type Categories
Set its Modifiers property to Public
Click the Columns field to reveal its ellipsis button and click its ellipsis button
In the Columns Collection Editor, click the Add button
Change the (Name) to colCategory and change the ColumnName field to Category
Set its Modifiers property to Public
Click Close
In the same way, create a table as
(Name): tblItemTypes
TableName: ItemTypes
Modifiers: Public
Add a column with the following properties:
(Name): colItemType
ColumnName: ItemType
Modifiers: Public
In the same way, create a table as
(Name): tblAvailableItems
TableName: AvailableItems
Modifiers: Public

Add the following columns to it

(Name)	ColumnName	Modifiers
colItemNumber	ItemNumber	Public
colItemCategory	Category	Public
colTypeOfItem	ItemType	Public
colItemName	ItemName	Public
colUnitPrice	UnitPrice	Public

Click Close twice

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: Creating a Record From the Column Index

Display the Categories form and double-click its Add button.
double-click an unoccupied area of its body

In the top section of the file, type the following:

Imports System.IO
Imports System.Xml
Imports System.Data

Public Class NewStoreItem
    Inherits System.Windows.Forms.Form

Implement the event as follows:

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        ' Get a reference to the Data Center because 
        ' that's where the DataSet object resides
        Dim frmData As DataCenter = New DataCenter

        ' Create a new record for the Categories table
        Dim rowNewCategory As DataRow = frmData.dsMusicStore.Tables("Categories").NewRow()
        ' Specify only the Category column since the CategoryID is auto-incrementing
        rowNewCategory(0) = Me.txtNewCategory.Text
        ' Add the new record to the Categories table
        frmData.dsMusicStore.Tables("Categories").Rows.Add(rowNewCategory)

        ' Display the current records of the Categories table so
        ' the user would know what categories are already in the table
        Me.dataGrid1.DataSource = frmData.dsMusicStore
        Me.dataGrid1.DataMember = "Categories"

        ' Reset the text box in case the user wants to add another category
        Me.txtNewCategory.Text = ""
        Me.txtNewCategory.Focus()
End Sub

Save the form

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 ItemTypes form and double-click its Add button

In the top section of the file, type the following:

Imports System.IO
Imports System.Xml
Imports System.Data

Public Class NewStoreItem
    Inherits System.Windows.Forms.Form

Implement the event as follows:

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Dim frmData As DataCenter = New DataCenter
        Dim strFilename As String = "ItemTypes.xml"

        If File.Exists(strFilename) Then
            frmData.dsMusicStore.ReadXml(strFilename)
        End If

        Dim rowNewType As DataRow = frmData.dsMusicStore.Tables("ItemTypes").NewRow()
        rowNewType(frmData.colItemType) = Me.txtNewItemType.Text
        frmData.dsMusicStore.Tables("ItemTypes").Rows.Add(rowNewType)

        frmData.dsMusicStore.WriteXml(strFilename)

        Me.dataGrid1.DataSource = frmData.dsMusicStore
        Me.dataGrid1.DataMember = "ItemTypes"

        Me.txtNewItemType.Text = ""
        Me.txtNewItemType.Focus()
End Sub

Save the form

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

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.

Application Data Storage

Introduction

In Lesson 3, we introduced lists as parts of an application and the information they contain. In this lesson, we learned how to fill such lists with information. When the application closes, unfortunately, all the information created while the application was running is lost. While the first goal of an application is to create one or more lists used to organize information, probably the essence of an information-based or a data-based application is to preserve information created when using the application and be able to retrieve that information the next time the application runs, without re-creating it.

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

Saving a List

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

Overloads Public Sub WriteXml(ByVal fileName As String)

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

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

Overloads Public Sub WriteXml(ByVal stream As Stream)

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

Overloads Public Sub WriteXml(ByVal writer As TextWriter)

If you prefer to use an XmlWriter variable to manage the file, use the following version of the method:

Overloads Public Sub WriteXml(ByVal writer As XmlWriter)

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

Opening a List

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

Overloads Public Function ReadXml(ByVal fileName As String) As XmlReadMode

This method takes as argument the name of an existing XML file or its path. This method opens the file and provides the XML formatting as it was done when the file was saved. Although this method can read any XML file, if you use it to open a file that was saved by someone else or another application and you want to use it in your application, you must be familiar with the names of its nodes. If it contains names that are not "registered" or recognized by your DataSet object, the lists that compose your application may not be able to read it, not because the list was not formatted right, but because the lists of your application would be holding different names.

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

Overloads Public Function ReadXml(ByVal stream As Stream) As XmlReadMode

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

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

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

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

Public Function GetXml() As String

As you can see, this method returns a String string.

Practical Learning: Opening an XML File

Display the Categories form and double-click its Add button

Change the Click event as follows:

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        ' Get a reference to the Data Center because 
        ' that's where the DataSet object resides
        Dim frmData As DataCenter = New DataCenter
        ' This is the XML file that will holds the Categories
        Dim strFilename As String = "Categories.xml"

        ' If the file exists already, open it
        If File.Exists(strFilename) Then
            frmData.dsMusicStore.ReadXml(strFilename)
        End If

        ' Create a new record for the Categories table
        Dim rowNewCategory As DataRow = frmData.dsMusicStore.Tables("Categories").NewRow()
        ' Specify only the Category column since the CategoryID is auto-incrementing
        rowNewCategory(0) = Me.txtNewCategory.Text
        ' Add the new record to the Categories table
        frmData.dsMusicStore.Tables("Categories").Rows.Add(rowNewCategory)

        ' Update the XML file
        frmData.dsMusicStore.WriteXml(strFilename)

        ' Display the current records of the Categories table so
        ' the user would know what categories are already in the table
        Me.dataGrid1.DataSource = frmData.dsMusicStore
        Me.dataGrid1.DataMember = "Categories"

        ' Reset the text box in case the user wants to add another category
        Me.txtNewCategory.Text = ""
        Me.txtNewCategory.Focus()
    End Sub
End Class

Display the New Store Item form.
Double-click an unoccupied area of its body and change its Load event as follows:

Private Sub NewStoreItem_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim frmData As DataCenter = New DataCenter
        Dim rdrMusicStore As XmlTextReader = Nothing

        Try
            Dim strFilename As String = "Categories.xml"

            If File.Exists(strFilename) Then
                rdrMusicStore = New XmlTextReader(strFilename)

                ' Scan the XML file
                While rdrMusicStore.Read()
                    ' every time you find an element, find out what type it is
                    ' If you find a category, put it in the Categories list box
                    If (XmlNodeType.Element <> 0) And (rdrMusicStore.Name = "Category") Then
                        Me.cboCategories.Items.Add(rdrMusicStore.ReadElementString("Category"))
                    End If
                End While
            End If

            strFilename = "ItemTypes.xml"

            If File.Exists(strFilename) Then
                rdrMusicStore = New XmlTextReader(strFilename)

                ' Scan the XML file
                While rdrMusicStore.Read()
                    ' every time you find an element, find out what type it is
                    ' If you find an ItemType, put it in the Item Types list box
                    If (XmlNodeType.Element <> 0) And (rdrMusicStore.Name = "ItemType") Then
                        Me.cboItemTypes.Items.Add(rdrMusicStore.ReadElementString("ItemType"))
                    End If
                End While
            End If
        Catch ex As XmlException
            MsgBox("Invalid XML file")
        Finally
            If Not (rdrMusicStore Is Nothing) Then rdrMusicStore.Close()
        End Try

        ' We will generate a random number for the store item
        Dim tmeNow As DateTime = DateTime.Now
        Dim rndNumber As Random = New Random(tmeNow.Millisecond)
        Dim strNumber As String = CStr(rndNumber.Next(100000, 999999))

        ' Display the new number in the Part # text box
        Me.txtItemNumber.Text = strNumber

        ' Disable the Create button to indicate that the item is not ready
        Me.btnCreate.Enabled = False
End Sub

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

Change the event as follows:

Private Sub btnNewCategory_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnNewCategory.Click
        Dim frmCat As Categories = New Categories

        frmCat.ShowDialog()

        Dim rdrMusicStore As XmlTextReader = Nothing

        Try
            Dim strFilename As String = "Categories.xml"

            If File.Exists(strFilename) Then
                Me.cboCategories.Items.Clear()

                rdrMusicStore = New XmlTextReader(strFilename)

                ' Scan the XML file
                While rdrMusicStore.Read()
                    ' every time you find an element, find out what type it is
                    ' If you find a category, put it in the Categories list box
                    If (XmlNodeType.Element <> Nothing) And (rdrMusicStore.Name = "Category") Then
                        Dim strNew As String = rdrMusicStore.ReadElementString("Category")
                        If Not (Me.cboCategories.Items.Contains(strNew)) Then
                            Me.cboCategories.Items.Add(strNew)
                        End If
                    End If
                End While
            End If
        Catch ex As XmlException
            MsgBox("Invalid XML file")
        Finally
            If Not (rdrMusicStore Is Nothing) Then
                rdrMusicStore.Close()
            End If
	 end try
End Sub

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

Change the event as follows:

Private Sub btnNewType_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnNewType.Click
        Dim frmTypes As ItemTypes = New ItemTypes

        frmTypes.ShowDialog()

        Dim rdrMusicStore As XmlTextReader = Nothing

        Try
            Dim strFilename As String = "ItemTypes.xml"

            If File.Exists(strFilename) Then
                rdrMusicStore = New XmlTextReader(strFilename)

                While rdrMusicStore.Read()
                    If (XmlNodeType.Element <> 0) And (rdrMusicStore.Name = "ItemType") Then
                        Dim strNewType As String = rdrMusicStore.ReadElementString("ItemType")
                        If Not Me.cboItemTypes.Items.Contains(strNewType) Then
                            Me.cboItemTypes.Items.Add(strNewType)
                        End If
                    End If
                End While
            End If
        Catch ex As XmlException
            MsgBox("Invalid XML file")
        Finally
            If Not (rdrMusicStore Is Nothing) Then
                rdrMusicStore.Close()
            End If
        End try
End Sub

In the Class Name combo box, select txtItemName

In the Method Name combo box, select TextChanged and implement its event as follows:

Private Sub txtItemName_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) _
		Handles txtItemName.TextChanged
        If Me.txtItemName.Text = "" Then
            Me.btnCreate.Enabled = False
        Else
            Me.btnCreate.Enabled = True
        End If
End Sub

In the Class Name combo box, select txtUnitPrice

In the Method Name combo box, select TextChanged and implement its event as follows:

Private Sub txtUnitPrice_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) _
		Handles txtUnitPrice.TextChanged
        If Me.txtUnitPrice.Text = "" Then
            Me.btnCreate.Enabled = False
        Else
            Me.btnCreate.Enabled = True
        End If
End Sub

In the Class Name combo box, select txtItemNumber

In the Method Name combo box, select TextChanged and implement its event as follows:

Private Sub txtItemNumber_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) _
		Handles txtItemNumber.TextChanged
        If Me.txtItemNumber.Text = "" Then
            Me.btnCreate.Enabled = False
        Else
            Me.btnCreate.Enabled = True
        End If
End Sub

In the Class Name combo box, select btnCreate

In the Method Name combo box, select Click and implement the event as follows:

Private Sub btnCreate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        ' Get a reference to the Data Center because 
        ' that's where the DataSet object resides
        Dim frmData As DataCenter = New DataCenter

        ' This is the XML file that will holds the general inventory
        ' of the current items in the store
        Dim strAvailableItems As String = "Inventory.xml"

        ' If the file exists already, open it
        If File.Exists(strAvailableItems) Then
            frmData.dsMusicStore.ReadXml(strAvailableItems)
        End If

        ' Create a new record for the AvailableItems table
        Dim rowNewItem As DataRow = frmData.dsMusicStore.Tables("AvailableItems").NewRow()

        rowNewItem("ItemNumber") = Me.txtItemNumber.Text
        rowNewItem("Category") = Me.cboCategories.Text
        rowNewItem("ItemType") = Me.cboItemTypes.Text
        rowNewItem("ItemName") = Me.txtItemName.Text
        rowNewItem("UnitPrice") = Me.txtUnitPrice.Text
        ' Add the new record to the AvailableItems table
        frmData.dsMusicStore.Tables("AvailableItems").Rows.Add(rowNewItem)

        ' Update the XML file
        frmData.dsMusicStore.WriteXml(strAvailableItems)

        ' Reset the controls in case the user wants to add another record
        Me.cboCategories.SelectedIndex = -1
        Me.cboItemTypes.SelectedIndex = -1
        Me.txtItemName.Text = ""
        Me.txtUnitPrice.Text = "0.00"

        ' We will generate a random number for the store item
        Dim tmeNow As DateTime = DateTime.Now
        Dim rndNumber As Random = New Random(tmeNow.Millisecond)
        Dim strNumber As String = CStr(rndNumber.Next(100000, 999999))

        ' Display the new number in the Part # text box
        Me.txtItemNumber.Text = strNumber

        ' Disable the OK button to indicate that the item is not ready
        Me.btnCreate.Enabled = False
End Sub

Execute the application
Click the New Store Item button

Click the top New button to display the Categories form and create a few categories as follows:

Category

Electric Guitar

Acoustic Guitar

Bass

Keyboard

Stands

Cables

Cases

DJ Equipment

Close the Categories form and open the Item Types form

Create a few item types as follows:

Item Type

4-String

5-String

6-String

12-String

Solid Body

Hollow Body

Left-Handed

Guitar Stand

DJ CD Players

Close the Item Types form

In the New Store Item form, create the following items (let the computer generate item numbers):

Category	Item Type	Item Name	Unit Price
Electric Guitar	Solid Body	Gibson Les Paul Standard Electric Guitar	1950.00
Bass	6-String	Ibanez SR506 6-string Electric Bass	595.50
Electric Guitar	Hollow Body	Oscar Schmidt OE40 Hollow Body Electric Guitar	205.95
Acoustic Guitar	Left-Handed	Yamaha Left-Handed FG413SL Acoustic Guitar	295.95
Cables	Instrument Cable	Monster Cable S-100 Straight Instrument Cable	12.95
Bass	Combo Amps	Behringer BX1200 Ultrabass Amplifier (120 Watts, 2-Channel)	150.00
Keyboard	Synthesizers	Korg Triton Le 61-Key Workstation Synth	895.95
Stands	Guitar Stand	String Swing Metal Guitar Wall Hanger	9.95
Electric Guitar	Solid Body	Epiphone LP-100 Electric Guitar	275.95
Keyboard	Digital Piano	Yamaha YDP223 88-Key Graded Hammer Piano With Bench	1490.00
Cables	Guitar Cable	Spectraflex Guitar Cable	42.25
Electric Guitar	Solid Body	Gibson Les Paul Classic Electric Guitar	1625.95
Stands	Guitar Stand	Locking Tubular Guitar Stand	4.95
Electric Guitar	Solid Body	ESP LTD Viper 400 Electric Guitar	585.50
Acoustic Guitar	12-String	Washburn J28S12DL Cumberland 12-string Guitar	650.75
Electric Guitar	Hollow Body	Ibanez Artcore AF75 Electric Guitar	315.95
Keyboard	Synthesizers	Korg Triton Extreme 61-Key Synth Workstation	1895.00
Electric Guitar	Solid Body	Epiphone Les Paul Standard	525.95
Stands	Guitar Stand	Guitar Folding Stand	12.95
Electric Guitar	Hollow Body	Gibson ES-335 Reissue	1850.00
Acoustic Guitar	12-String	Martin DM12	850.00
Stands	Guitar Stand	Metal Guitar Wall Hanger	9.95
Acoustic Guitar	Left Handed	Yamaha FG413SL	295.95
Electric Guitar	Solid Body	Gibson Faded SG Special	625.95
Bass	4-String	Ibanez SR500	525.95
Electric Guitar	Hollow Body	Oscar Schmidt OE40	225.95
Electric Guitar	Left Handed	Schecher C-1	450.95
Cases	Cases	Les Paul Hardshell Case	39.95
Cables	Instrument Cable	Hosa Dual Instrument Cable	7.25

Close the forms and return to your programming environment

Committing or Rejecting Changes to a List

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

Public Sub AcceptChanges()

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

Public Overridable Sub RejectChanges()

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

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

Display the Purchase Order form and double-click an unoccupied area of its body away from (outside of) any group box

In the top section of the file, type the following:

Imports System.IO
Imports System.Xml
Imports System.Data

Public Class PurchaseOrder
    Inherits System.Windows.Forms.Form

Implement the Load event as follows:

Private Sub PurchaseOrder_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
	Handles MyBase.Load
        ' Get a reference to the object that holds the DataSet
        Dim frmData As DataCenter = New DataCenter
        ' Identify the file that holds the categories
        Dim strCategories As String = "Categories.xml"

        ' If that file exists, then open it
        If File.Exists(strCategories) Then
            frmData.dsMusicStore.ReadXml(strCategories)
        End If

        ' Just in case, empty the Categories list box
        Me.lbxCategories.Items.Clear()

        Dim row As DataRow = Nothing
        Dim i As Integer
        ' Scan the whole file to locate each category and retrieve it
        For i = 0 To frmData.tblCategories.Rows.Count - 1 Step 1
            row = frmData.tblCategories.Rows(i)
            Me.lbxCategories.Items.Add(row("Category"))
        Next
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 Values

In the Class Name combo box, select lbxCategories

In the Method Name combo box, select SelectedIndexChanged and implement its event as follows:

Private Sub lbxCategories_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) _
		Handles lbxCategories.SelectedIndexChanged
        ' Get a reference to the object that holds the DataSet
        Dim frmData As DataCenter = New DataCenter
        ' Identify the file that holds the items
        Dim strAvailableItems As String = "Inventory.xml"
        ' Identify the category that the user selected
        Dim strSelectedCategory As String = Me.lbxCategories.Text

        ' If that file exists, then open it
        If File.Exists(strAvailableItems) Then
            frmData.dsMusicStore.ReadXml(strAvailableItems)
        End If

        ' Empty the Item Types list box
        Me.lbxItemTypes.Items.Clear()
        ' Also empty the Available Items list box
        Me.lbxAvailableItems.Items.Clear()

        Dim row As DataRow = Nothing
        Dim i As Integer
        ' Scan the whole table to locate each record
        For i = 0 To frmData.tblAvailableItems.Rows.Count - 1 Step 1
            ' Get a reference to the current record
            row = frmData.tblAvailableItems.Rows(i)
            ' Get the name of the category of the current record
            Dim strCurrentCategory As String = row("Category")

            ' If the current category matches the one the user selected...
            If strCurrentCategory = strSelectedCategory Then
                ' ... then get the corresponding item type
                Dim strType As String = row("ItemType")

                ' Find out if the Item Types list box already contains the item type
                ' If it doesn't, then put it in the Item Types list box
                If Not (Me.lbxItemTypes.Items.Contains(strType)) Then
                    Me.lbxItemTypes.Items.Add(strType)
                End If
            End If
        Next
End Sub

In the Class Name combo box, select lbxItemTypes
In the Method Name combo box, select SelectedIndexChanged

Implement the event as follows:

Private Sub lbxItemTypes_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) _
		Handles lbxItemTypes.SelectedIndexChanged
        ' Get a reference to the object that holds the DataSet
        Dim frmData As DataCenter = New DataCenter
        ' Identify the file that holds the items
        Dim strAvailableItems As String = "Inventory.xml"
        ' Identify the category that the user selected
        Dim strSelectedCategory As String = Me.lbxCategories.Text
        Dim strSelectedType As String = Me.lbxItemTypes.Text

        ' If file exists, then open it
        If File.Exists(strAvailableItems) Then
            frmData.dsMusicStore.ReadXml(strAvailableItems)
        End If
        ' Empty the Available Items list box
        Me.lbxAvailableItems.Items.Clear()

        Dim row As DataRow = Nothing
        Dim i As Integer
        ' Scan the whole table to locate each record
        For i = 0 To frmData.tblAvailableItems.Rows.Count - 1 Step 1
            ' Get a reference to the current record
            row = frmData.tblAvailableItems.Rows(i)
            ' Get the name of the category of the current record
            Dim strCurrentCategory As String = row("Category")
            ' Get the item type of the current record
            Dim strCurrentType As String = row("ItemType")

            ' If the current category matches the one the user selected
            ' and the current item type matches the type the user selected ...
            If (strCurrentCategory = strSelectedCategory) And (strCurrentType = strSelectedType) Then
                ' ... then get the corresponding item name
                Me.lbxAvailableItems.Items.Add(row("ItemName"))
            End If
        Next
End Sub

In the Class Name combo box, select lbxAvailableItems
In the Method Name combo box, select DoubleClick

Implement the DoubleClick event as follows:

Private Sub lbxAvailableItems_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) _
		Handles lbxAvailableItems.DoubleClick
        ' Get a reference to the object that holds the DataSet
        Dim frmData As DataCenter = New DataCenter
        ' Identify the file that holds the items
        Dim strAvailableItems As String = "Inventory.xml"
        ' Identify the item that the user selected
        Dim strSelectedCategory As String = Me.lbxCategories.Text
        Dim strSelectedType As String = Me.lbxItemTypes.Text
        Dim strSelectedName As String = Me.lbxAvailableItems.Text

        ' If file exists, then open it
        If File.Exists(strAvailableItems) Then
            frmData.dsMusicStore.ReadXml(strAvailableItems)

            Dim row As DataRow = Nothing
            ' Scan the whole table to locate each record
            Dim i As Integer
            For i = 0 To frmData.tblAvailableItems.Rows.Count - 1 Step 1
                ' Get a reference to the current record
                row = frmData.tblAvailableItems.Rows(i)
                ' Get the name of the category of the current record
                Dim strCurrentCategory As String = row("Category")
                ' Get the item type of the current record
                Dim strCurrentType As String = row("ItemType")
                ' Get the name of the current item
                Dim strCurrentName As String = row("ItemName")
                ' Get the item number of the current item
                Dim strCurrentNumber As String = row("ItemNumber")
                ' Get the unit price of the current item
                Dim strCurrentPrice As String = row("UnitPrice")

                ' If the current parts match the ones the user selected ...
                If (strCurrentCategory = strSelectedCategory) And _
                   (strCurrentType = strSelectedType) And _
                    (strCurrentName = strSelectedName) Then
                    ' ... then consider the corresponding item
                    If Me.txtItemNumber1.Text = "" Then
                        Me.txtItemNumber1.Text = strCurrentNumber
                        Me.txtItemName1.Text = strCurrentName
                        Me.txtUnitPrice1.Text = strCurrentPrice
                        Me.txtQuantity1.Text = "1"
                        Me.txtSubTotal1.Text = strCurrentPrice
                    ElseIf Me.txtItemNumber2.Text = "" Then
                        Me.txtItemNumber2.Text = strCurrentNumber
                        Me.txtItemName2.Text = strCurrentName
                        Me.txtUnitPrice2.Text = strCurrentPrice
                        Me.txtQuantity2.Text = "1"
                        Me.txtSubTotal2.Text = strCurrentPrice
                    ElseIf Me.txtItemNumber3.Text = "" Then
                        Me.txtItemNumber3.Text = strCurrentNumber
                        Me.txtItemName3.Text = strCurrentName
                        Me.txtUnitPrice3.Text = strCurrentPrice
                        Me.txtQuantity3.Text = "1"
                        Me.txtSubTotal3.Text = strCurrentPrice
                    ElseIf Me.txtItemNumber4.Text = "" Then
                        Me.txtItemNumber4.Text = strCurrentNumber
                        Me.txtItemName4.Text = strCurrentName
                        Me.txtUnitPrice4.Text = strCurrentPrice
                        Me.txtQuantity4.Text = "1"
                        Me.txtSubTotal4.Text = strCurrentPrice
                    ElseIf Me.txtItemNumber5.Text = "" Then
                        Me.txtItemNumber5.Text = strCurrentNumber
                        Me.txtItemName5.Text = strCurrentName
                        Me.txtUnitPrice5.Text = strCurrentPrice
                        Me.txtQuantity5.Text = "1"
                        Me.txtSubTotal5.Text = strCurrentPrice
                    Else ' if me.txtItemNumber6.Text= "" then
                        Me.txtItemNumber6.Text = strCurrentNumber
                        Me.txtItemName6.Text = strCurrentName
                        Me.txtUnitPrice6.Text = strCurrentPrice
                        Me.txtQuantity6.Text = "1"
                        Me.txtSubTotal6.Text = strCurrentPrice
                    End If
                End If
            Next
        End If

        Me.btnCalculate_Click(sender, e)
End Sub

Execute the application and perform a few transactions

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.


Home	Copyright © 2005-2016, FunctionX