Home

The Records of a Table

 

The Rows of a Table

 

Introduction to Records

In our description of tables, we saw that a table was made of one or various columns that represented some categories of data. Here is an example of a table with a few columns:

Shelf Number Title Director Length Year Rating
           
           

After creating such a table and its columns, 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.

 

 

Practical Learning: Introducing Data Sets

  1. Start Microsoft Visual Basic and create a new Windows Forms Application named CollegeParkAutoParts2
  2. To create an icon, in the Solution Explorer, right-click CollegeParkAutoParts2 -> Add -> New Item...
  3. In the Templates list, click Icon File
  4. Set the Name to cpap1 and click Add
  5. Right-click the white area and click Delete Image Type
  6. Design the 16x16, 16 colors version of the icon as follows:
     
  7. On the main menu, click File -> Save cpap1.ico As
  8. Select the bin\Debug folder of the current folder and click Save
  9. On the main menu, click File -> Close
  10. In the Solution Explorer, expand bin and expand Debug
  11. In the Solution Explorer, right-click the Debug folder -> Add -> New Item...
  12. In the Templates list, make sure Icon File is selected.
    Set the Name to cpap2 and click Add
  13. Right-click the white area and click Delete Image Type
  14. Design the 16x16, 16 colors version of the icon as follows:
     
  15. Save the file and close the icon window
  16. In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
  17. In the Templates list, make sure Icon File is selected.
    Set the Name to year1 and click Add
  18. Right-click the white area and click Delete Image Type
  19. Design the 16x16, 16 colors version of the icon as follows:
     
  20. Save the file and close the icon window
  21. In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
  22. In the Templates list, make sure Icon File is selected.
    Set the Name to year2 and click Add
  23. Right-click the white area and click Delete Image Type
  24. Design the 16x16, 16 colors version of the icon as follows:
     
  25. Save the file and close the icon window
  26. In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
  27. In the Templates list, make sure Icon File is selected.
    Set the Name to make1 and click Add
  28. Right-click the white area and click Delete Image Type
  29. Design the 16x16, 16 colors version of the icon as follows:
     
    Icon Design: Diamond
  30. Save the file and close the icon window
  31. In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
  32. In the Templates list, make sure Icon File is selected.
    Set the Name to make2 and click Add
  33. Right-click the white area and click Delete Image Type
  34. Design the 16x16, 16 colors version of the icon as follows:
     
    Icon Design: Diamond
  35. Save the file and close the icon window
  36. In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
  37. In the Templates list, make sure Icon File is selected.
    Set the Name to model1 and click Add
  38. Right-click the white area and click Delete Image Type
  39. Design the 16x16, 16 colors version of the icon as follows:
     
  40. Save the file and close the icon window
  41. In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
  42. In the Templates list, make sure Icon File is selected.
    Set the Name to model2 and click Add
  43. Right-click the white area and click Delete Image Type
  44. Design the 16x16, 16 colors version of the icon as follows:
     
  45. Save the file and close the icon window
  46. In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
  47. In the Templates list, make sure Icon File is selected.
    Set the Name to category1 and click Add
  48. Right-click the white area and click Delete Image Type
  49. Design the 16x16, 16 colors version of the icon as follows:
     
  50. Save the file and close the icon window
  51. In the Solution Explorer, right- click the Debug folder -> Add -> New Item...
  52. In the Templates list, make sure Icon File is selected.
    Set the Name to category2 and click Add
  53. Right-click the white area and click Delete Image Type
  54. Design the 16x16, 16 colors version of the icon as follows:
     
    Icon Design: Minus
  55. Save the file and close the icon window
  56. In the Solution Explorer, right-click Form1.vb and click Rename
  57. Type Central.vb and press Enter twice to display the Central form
  58. From the Components section of the Toolbox, click ImageList and click the form
  59. In the Properties window, click (Name) and type imgAutoParts
  60. Click the ellipsis button of the Images field
  61. In the Image Collection Editor, click Add
  62. Locate the folder that contains the icons you created and display it in the Look In combo box
  63. Select cpap1.ico and click Open
  64. In the same way, add the other pictures in the following order: cpap2.ico, year1.ico, year2.ico, make1.ico, make2.ico, model1.ico, model2.ico, category1.ico, and category1.ico
     
    Image Collection Editor
  65. Click OK
  66. Design the form as follows:
     
    College Park Auto Parts - Form Design
    Control Text Name Other Properties
    Label Label College Park Auto-Parts   Font: Times New Roman, 20.25pt, style=Bold
    ForeColor: Blue
    Panel     Height: 2
    GroupBox GroupBox Part Identification    
    TreeView TreeView   tvwAutoParts ImageList: imgAutoParts
    GroupBox GroupBox Available Parts    
    ListView ListView   lvwAutoParts FullRowSelect: True
    GridLines: True
    View: Details
    Columns   (Name) Text TextAlign Width
    colPartNumber Part #    
    colPartName Part Name   300
    colUnitPrice Unit Price Right 80
    GroupBox GroupBox Customer Order - Selected Parts    
    Label Label Part #    
    Label Label Part Name    
    Label Label Unit Price    
    Label Label Qty    
    Label Label Sub Total    
    TextBox TextBox   txtPartNumber  
    TextBox TextBox   txtPartName  
    TextBox TextBox 0.00 txtUnitPrice TextAlign: Right
    TextBox TextBox 0 txtQuantity TextAlign: Right
    TextBox TextBox 0.00 txtSubTotal TextAlign: Right
    Button Button Add/Select btnAdd
    ListView ListView   lvwSelectedParts FullRowSelect: True
    GridLines: True
    View: Details
    Columns   (Name) Text TextAlign Width
    colPartNumberSelected Part #   45
    colPartNameSelected Part Name   274
    colUnitPriceSelected Unit Price Right 58
    colQuantitySelected Qty Right 28
    colSubTotalSelected Sub-Total Right 58
    GroupBox GroupBox Order Summary
    Button Button New Au&to Part... btnNewAutoPart  
    Label Label Receipt #:  
    TextBox TextBox txtSave
    Button Button Save btnSave
    Label Label Tax Rate:
    TextBox TextBox 7.75 txtTaxRate TextAlign: Right
    Label Label %
    Label Label Parts Total:
    TextBox TextBox 0.00 txtPartsTotal TextAlign: Right
    Button Button &New Customer Order btnNewCustomerOrder  
    Label Label Receipt #:  
    TextBox TextBox txtOpen
    Button Button Save btnOpen
    Label Label Tax Amount:
    TextBox TextBox 0.00 txtTaxAmount TextAlign: Right
    Label Label Order Total:
    TextBox TextBox 0.00 txtOrderTotal TextAlign: Right
    Button Button Close btnClose  
  67. Right-click the form and click View Code
  68. In the Class Name combo box, select lvwAutoParts
  69. In the Method Name combo box, select DoubleClick and implement the event as follows:
     
    Private Sub lvwAutoParts_DoubleClick(ByVal sender As Object, _
                                         ByVal e As System.EventArgs) _
                                         Handles lvwAutoParts.DoubleClick
        Dim lviAutoPart As ListViewItem = lvwAutoParts.SelectedItems(0)
    
        If (lvwAutoParts.SelectedItems.Count = 0) Or _
            (lvwAutoParts.SelectedItems.Count > 1) Then
            Exit Sub
        End If
    
        txtPartNumber.Text = lviAutoPart.Text
        txtPartName.Text = lviAutoPart.SubItems(1).Text
        txtUnitPrice.Text = lviAutoPart.SubItems(2).Text
    
        txtQuantity.Text = "1"
        txtSubTotal.Text = lviAutoPart.SubItems(2).Text
    
        txtQuantity.Focus()
    End Sub
  70. Under the above End Sub line, define the following event for various controls:
     
    Private Sub ControlLeave(ByVal sender As Object, _
                             ByVal e As EventArgs) _
                             Handles txtUnitPrice.Leave, _
                                     txtQuantity.Leave
        Dim UnitPrice As Double
        Dim Quantity As Integer
        Dim SubTotal As Double
    
        Try
            UnitPrice = CDbl(txtUnitPrice.Text)
        Catch Exc As FormatException
            MsgBox("Invalid Unit Price!")
        End Try
    
        Try
            Quantity = CInt(txtQuantity.Text)
        Catch Exc As FormatException
            MsgBox("Invalid Quandtity!")
        End Try
    
        SubTotal = UnitPrice * Quantity
        txtSubTotal.Text = FormatCurrency(SubTotal)
    End Sub
  71. Under the above End Sub line, create the following procedure:
     
    Private Sub CalculateOrder()
        ' Calculate the current total order and update the order
        Dim PartsTotal As Double
        Dim TaxRate As Double
        Dim TaxAmount As Double
        Dim OrderTotal As Double
    
        Dim lviSelectedPart As ListViewItem = lvwSelectedParts.Items(0)
    
        For Each lvi As ListViewItem In lvwSelectedParts.Items
            Dim SubItem As ListViewItem.ListViewSubItem = lvi.SubItems(4)
    
            PartsTotal = PartsTotal + CDbl(SubItem.Text)
        Next
    
        Try
            TaxRate = CDbl(txtTaxRate.Text) / 100
        Catch Exc As FormatException
            MsgBox("Invalid Tax Rate")
        End Try
    
        TaxAmount = PartsTotal * TaxRate
        OrderTotal = PartsTotal + TaxAmount
    
        txtOrderTotal.Text = FormatNumber(PartsTotal)
        txtTaxAmount.Text = FormatNumber(TaxAmount)
        txtOrderTotal.Text = FormatNumber(OrderTotal)
    End Sub
  72. In the Class Name combo box, select lvwSelectedPart
  73. In the Method Name combo box, select DoubleClick
  74. Implement the event as follows:
     
    Private Sub lvwSelectedParts_DoubleClick(ByVal sender As Object, _
                                             ByVal e As System.EventArgs) _
                                             Handles lvwSelectedParts.DoubleClick
        Dim lviSelectedPart As ListViewItem = lvwSelectedParts.SelectedItems(0)
    
        If (lvwSelectedParts.SelectedItems.Count = 0) Or _
            (lvwSelectedParts.SelectedItems.Count > 1) Then
            Exit Sub
        End If
    
        txtPartNumber.Text = lviSelectedPart.Text
        txtPartName.Text = lviSelectedPart.SubItems(1).Text
        txtUnitPrice.Text = lviSelectedPart.SubItems(2).Text
        txtQuantity.Text = lviSelectedPart.SubItems(3).Text
        txtSubTotal.Text = lviSelectedPart.SubItems(4).Text
    
        lvwSelectedParts.Items.Remove(lviSelectedPart)
        CalculateOrder()
    End Sub
  75. Save all

A Data Source and a Data Member for the Data Grid View

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

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

Public Class Exercise
    Private dsVideos As DataSet

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

        dsVideos = New DataSet("VideoSet")

        dgvVideos.DataSource = dsVideos
    End Sub
End Class

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

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

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

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

    Private dgvVideos As DataGridView
    Private dsVideos As DataSet

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

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

        tblVideos = New DataTable("Videos")

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

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

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

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

Data Grid View

Practical LearningPractical Learning: Introducing Data Records

  1. To create a dialog box, on the main menu, click Project -> Add Windows Form...
  2. Set the name to ItemsCategories and click Add
  3. From the Data section of the Toolbox, click DataSet and click the form
  4. On the Add Dataset dialog box, click the Untyped dataset radio button and click OK
  5. In the Properties window, change the characteristics of the data set as follows:
    DataSetName: Categories
    (Name): dsCategories
  6. Click the Tables field and click its ellipsis button
  7. In the Tables Collection Editor, click Add
  8. In the Properties list, change the characteristics of the table as follows:
    TableName: Category
    (Name): tblCategory
  9. Click Columns and click its ellipsis button
  10. In the Columns Collection Editor, click Add
  11. Change the characteristics of the columns as follows:
    ColumnName: CategoryName
    (Name): colCategoryName 
  12. Click Close and click Close
  13. Design the form as follows:
     
    College Park Auto-Parts: Categories
    Control Text Name Additional Properties
    DataGridView   dgvStudents Anchor: Top, Bottom, Left, Right
    DataSource: dsCategories
    DataMember: Category
    Button Close btnClose Anchor: Bottom, Right
  14. To create a dialog box, on the main menu, click Project -> Add Windows Form...
  15. Set the name to CarMakes and click Add
  16. From the Data section of the Toolbox, click DataSet and click the form
  17. On the Add Dataset dialog box, click the Untyped dataset radio button and click OK
  18. In the Properties window, change the characteristics of the data set as follows:
    DataSetName: Makes
    (Name): dsMakes
  19. Click the Tables field and click its ellipsis button
  20. In the Tables Collection Editor, click Add
  21. In the Properties list, change the characteristics of the table as follows:
    TableName: Make
    (Name): tblMake
  22. Click Columns and click its ellipsis button
  23. In the Columns Collection Editor, click Add
  24. Change the characteristics of the columns as follows:
    ColumnName: MakeName
    (Name): colMakeName 
  25. Click Close and click Close
  26. Design the form as follows:
     
    College Park Auto-Parts: Makes
    Control Text Name Additional Properties
    DataGridView   dgvStudents Anchor: Top, Bottom, Left, Right
    DataSource: dsMakes
    DataMember: Makes
    Button Close btnClose Anchor: Bottom, Right
  27. To create a dialog box, on the main menu, click Project -> Add Windows Form...
  28. Set the name to CarModels and click Add
  29. Display the list of makes form
  30. Right-click dsMakes and click Copy
  31. Display the list of models form
  32. Right-click its body and click Paste
  33. From the Data section of the Toolbox, click DataSet and click the form
  34. On the Add Dataset dialog box, click the Untyped dataset radio button and click OK
  35. In the Properties window, change the characteristics of the data set as follows:
    DataSetName: Models
    (Name): dsModels
  36. Click the Tables field and click its ellipsis button
  37. In the Tables Collection Editor, click Add
  38. In the Properties list, change the characteristics of the table as follows:
    TableName: Car
    (Name): tblCar
  39. Click Columns and click its ellipsis button
  40. In the Columns Collection Editor, click Add
  41. Change the characteristics of the columns as follows:
    ColumnName: Make
    (Name): colMake
  42. In the Columns Collection Editor, click Add
  43. Change the characteristics of the columns as follows:
    ColumnName: Model
    (Name): colModel
  44. Click Close and click Close
  45. From the Data section of the Toolbox, click DataGridView and click the form
  46. In the Properties window, change its characteristics as follows:
    DataSource: dsModels
    DataMember: Models
  47. Under the Properties window, click Edit Columns... and change the columns as follows:
     
    Selected Columns: HeaderText DataPropertyName ColumnType DataSource DisplayMember
    Make Make Make DataGridViewComboBoxColumn dsMakes Make.MakeName
    Model Model Model      
  48. Click OK
  49. Design the form as follows:
     
    College Partk Auto Parts: Models
    Control Text Name Additional Properties
    DataGridView   dgvStudents Anchor: Top, Bottom, Left, Right
    Button Close btnClose Anchor: Bottom, Right
  50. To create a new form, in the Solution Explorer, right-click CollegeParkAutoParts2 -> Add -> Windows Form...
  51. Set the Name to AutoParts and click Add
  52. Display the list of models form
  53. Click dsMakes
  54. Press and hold Shift
  55. Click dsModels
  56. Release Shift 
  57. Press Ctrl + C
  58. Display the auto parts form
  59. Right-click its body and click Paste
  60. Display the ItemsCategories form
  61. Right-click dsCategories and click Copy
  62. Display the AutoParts form
  63. Right-click its body and click Paste
  64. From the Data section of the Toolbox, click DataSet and click the form
  65. In the Add Dataset dialog box, click the Untyped dataset radio button and click OK
  66. In the Properties window, change the characteristics of the data set as follows:
    DataSetName: StoreItems
    (Name): dsStoreItems
  67. Click Tables and click its ellipsis button
  68. In the Properties list, change the characteristics of the table as follows:
    TableName: StoreItem
    (Name): tblStoreItem
  69. Click Columns and click its ellipsis button
  70. In the Columns Collection Editor, click Add continuously and create the columns as follows:
     
    ColumnName (Name)
    PartNumber colPartNumber
    Year colYear
    Make colMake
    Model colModel
    Category colCategory
    PartName colPartName
    UnitPrice colUnitPrice
  71. Click Close and click Close
  72. From the Data section of the Toolbox, click DataGridView and click the form
  73. In the Properties window, change its characteristics as follows:
    DataSource: dsStoreItems
    DataMember: StoreItems
  74. Under the Properties window, click Edit Columns... and change the columns as follows:
     
    Selected Columns: HeaderText DataPropertyName ColumnType DataSource DisplayMember Width
    Part # Part # PartNumber 50
    Year Year Year 40
    Make Make Make DataGridViewComboBoxColumn dsMakes Make.MakeName 85
    Model Model Model DataGridViewComboBoxColumn dsModels CarModel.Model 130
    Category Category Category DataGridViewComboBoxColumn dsCategories Category.CategoryName 120
    Part Name Part Name PartName 185
    Unit Price Unit Price UnitPrice 65
  75. Click OK
  76. Design the form as follows:
     
    College Park Auto Parts: Store Items
     
    Control Text Name Other Properties
    DataGridView   dgvStoreItems DataSource: dsStoreItems
    DataMember: AutoPart
    Anchor: Top, Bottom, Left, Right
    Button New Make... btnNewMake Anchor: Bottom, Left
    Button New Model... btnNewModel Anchor: Bottom, Left
    Button New Category... btnNewCategory Anchor: Bottom, Left
    Button Close btnClose Anchor: Bottom, Right
  77. Right-click the form and click View Code
  78. In the Class Name combo box, select btnNewMake
  79. In the Method Name combo box, select Click and change the file as follows:
      
    Imports System.IO
    
    Public Class AutoParts
    
        Private Sub btnNewMake_Click(ByVal sender As Object, _
                                     ByVal e As System.EventArgs) _
                                     Handles btnNewMake.Click
            Dim frmMakes As CarMakes = New CarMakes
            frmMakes.ShowDialog()
        End Sub
    End Class
  80. In the Class Name combo box, select btnNewModel
  81. In the Method Name combo box, select Click and implement the event as follows:
      
    Private Sub btnNewModel_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnNewModel.Click
        Dim frmModels As CarModels = New CarModels
        frmModels.ShowDialog()
    End Sub
  82. In the Class Name combo box, select btnNewCategory
  83. 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 frmCategories As ItemsCategories = New ItemsCategories
        frmCategories.ShowDialog()
    End Sub
  84. In the Solution Explorer, right-click Central.vb and click View Code 
  85. In the Class Name combo box, select btnNewAutoPart
  86. In the Method Name combo box, select Click and implement its event as follows:
     
    Private Sub btnNewAutoPart_Click(ByVal sender As Object, _
                                         ByVal e As System.EventArgs) _
                                         Handles btnNewAutoPart.Click
        Dim frmParts As AutoParts = New AutoParts
    
        frmParts.ShowDialog()
    End Sub
  87. Execute the application and make sure the forms and dialog boxes display
  88. Close the forms and return to your programming environment

The Rows of a Table

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

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

Introduction to Data Entry

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

Public Function NewRow As DataRow

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

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

Data Entry

 

Introduction

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

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

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

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

Adding a Record to a Table

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

Public Sub Add(row As DataRow)

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

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

    Private dgvVideos As DataGridView
    Private dsVideos As DataSet

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

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

        tblVideos = New DataTable("Videos")

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

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

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

        tblVideos.Rows.Add(VideoRecord)

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

This would produce:

Rows

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

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

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

Here is an example:

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

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

        tblVideos = New DataTable("Videos")

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

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

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

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

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

This would produce:

Rows

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

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

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

        tblVideos = New DataTable("Videos")

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

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

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

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

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

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

This would produce:

Row

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

The Number of Records of a Table

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

Saving the Records of a Table

 

Introduction

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

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

Saving a Data Set

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

Public Sub WriteXml(fileName As String)

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

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

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

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

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

Public Sub WriteXml(stream As Stream)

Here is an example:

Imports System.IO

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

    Private dgvVideos As DataGridView
    Private dsVideos As DataSet

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

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

        tblVideos = New DataTable("Videos")

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

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

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

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

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

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

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

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

Public Sub WriteXml(writer As TextWriter)

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

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

    . . . No Change

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

    dsVideos.WriteXml(StmWriter)
End Sub

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

Public Sub WriteXml(writer As XmlWriter)

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

Practical Learning Practical Learning: Saving the Records of a Data Set

  1. In the Solution Explorer, right-click ItemsCategories.vb and click View Code
  2. In the Class Name combo box, select btnClose
  3. In the Method Name combo box, select Click and implement the event as follows:
     
    Imports System.IO
    
    Public Class ItemsCategories
    
        Private Sub btnClose_Click(ByVal sender As Object, _
                                   ByVal e As System.EventArgs) _
                                   Handles btnClose.Click
            Dim FolderName As String = "C:\College Park Auto Parts"
            ' If this directory doesn't exist, create it
            Directory.CreateDirectory(FolderName)
    
            dsCategories.WriteXml(FolderName & "\categories.xml")
            Close()
        End Sub
    End Class
  4. In the Solution Explorer, right-click CarMakes.vb and click View Code
  5. In the Class Name combo box, select btnClose button
  6. In the Method Name combo box, select Click and implement the event as follows:
     
    Imports System.IO
    
    Public Class CarMakes
    
        Private Sub btnClose_Click(ByVal sender As Object, _
                                   ByVal e As System.EventArgs) _
                                   Handles btnClose.Click
            Dim FolderName As String = "C:\College Park Auto Parts"
            ' If this directory doesn't exist, create it
            Directory.CreateDirectory(FolderName)
    
            dsMakes.WriteXml(FolderName & "\makes.xml")
            Close()
        End Sub
    End Class
  7. In the Solution Explorer, right-click CarModels.vb and click View Code
  8. In the Class Name combo box, select btnClose button
  9. In the Method Name combo box, select Click and implement the event as follows:
     
    Imports System.IO
    
    Public Class CarModels
    
        Private Sub btnClose_Click(ByVal sender As Object, _
                                   ByVal e As System.EventArgs) _
                                   Handles btnClose.Click
            Dim FolderName As String = "C:\College Park Auto Parts"
            ' If this directory doesn't exist, create it
            Directory.CreateDirectory(FolderName)
    
            dsModels.WriteXml(FolderName & "\models.xml")
            Close()
        End Sub
    End Class
  10. In the Solution Explorer, right-click AutoParts.vb and click View Code
  11. In the Class Name combo box, select btnClose button
  12. In the Method Name combo box, select Click and implement its event as follows:
     
    Private Sub btnClose_Click(ByVal sender As Object, _
                                   ByVal e As System.EventArgs) _
                                   Handles btnClose.Click
        Dim FolderName As String = "C:\College Park Auto Parts"
        ' If this directory doesn't exist, create it
        Directory.CreateDirectory(FolderName)
    
        dsStoreItems.WriteXml(FolderName & "\StoreItems.xml")
        Close()
    End Sub
  13. Save all

Opening a Data Set

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

Public Function ReadXml(fileName As String) As XmlReadMode

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

Imports System.IO

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

    Private dgvVideos As DataGridView
    Private dsVideos As DataSet

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

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

        tblVideos = New DataTable("Videos")

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

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

        Dim Filename As String = "Exercise.xml"

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

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

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

public XmlReadMode ReadXml(Stream stream)

Here is an example:

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

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

    tblVideos = New DataTable("Videos")

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

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

    Dim Filename As String = "Exercise.xml"

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

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

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

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

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

Public Function GetXml As String

As you can see, this method returns a string.

Practical LearningPractical Learning: Opening the Records for a Data Set

  1. In the Solution Explorer, right-click ItemsCategories.vb and click View Code
  2. In the Class Name combo box, select (ItemsCategories Events)
  3. In the Method Name combo box, select Load and implement the event as follows:
     
    Private Sub ItemsCategories_Load(ByVal sender As Object, _
                                          ByVal e As System.EventArgs) _
                                          Handles Me.Load
        Dim Filename As String = "C:\College Park Auto Parts\categories.xml"
    
        If File.Exists(Filename) Then
            dsCategories.ReadXml(Filename)
        End If
    End Sub
  4. In the Solution Explorer, right-click CarMakes.vb and click View Code
  5. In the Class Name combo box, select (CarMakes Events)
  6. In the Method Name combo box, select Load and implement the event as follows:
     
    Private Sub CarMakes_Load(ByVal sender As Object, _
                                     ByVal e As System.EventArgs) _
                                     Handles Me.Load
        Dim Filename As String = "C:\College Park Auto Parts\makes.xml"
    
        If File.Exists(Filename) Then
            dsMakes.ReadXml(Filename)
        End If
    End Sub
  7. In the Solution Explorer, right-click CarModels.vb and click View Code
  8. In the Class Name combo box, select (CarModels Events)
  9. In the Method Name combo box, select Load and implement the event as follows:
     
    Private Sub CarModels_Load(ByVal sender As Object, _
                                      ByVal e As System.EventArgs) _
                                      Handles Me.Load
        Dim Filename As String = "C:\College Park Auto Parts\makes.xml"
    
        If File.Exists(Filename) Then
            dsMakes.ReadXml(Filename)
        End If
    
        Filename = "C:\College Park Auto Parts\models.xml"
    
        If File.Exists(Filename) Then
            dsModels.ReadXml(Filename)
        End If
    End Sub 
  10. In the Solution Explorer, right-click AutoParts.vb and click View Code
  11. In the Class Name combo box, select (AutoParts Events)
  12. In the Method Name combo box, select Load and implement the event as follows:
     
    Private Sub AutoParts_Load(ByVal sender As Object, _
                                   ByVal e As System.EventArgs) _
                                   Handles Me.Load
        Dim Filename As String = "C:\College Park Auto Parts\StoreItems.xml"
    
        If File.Exists(Filename) Then
            dsStoreItems.ReadXml(Filename)
        End If
    End Sub
  13. In the Class Name combo box, select (AutoParts Events)
  14. In the Method Name combo box, select Activated and implement the event as follows:
     
    Private Sub AutoParts_Activated(ByVal sender As Object, _
                                        ByVal e As System.EventArgs) _
                                        Handles Me.Activated
    
        Dim Filename As String = "C:\College Park Auto Parts\makes.xml"
    
        If File.Exists(Filename) Then
            dsMakes.ReadXml(Filename)
        End If
    
        Filename = "C:\College Park Auto Parts\models.xml"
    
        If File.Exists(Filename) Then
            dsModels.ReadXml(Filename)
        End If
    
        Filename = "C:\College Park Auto Parts\categories.xml"
    
        If File.Exists(Filename) Then
            dsCategories.ReadXml(Filename)
        End If
    End Sub
  15. Execute the application
  16. Click the New Auto Part button and click the New Make button
  17. Create a few makes as follows:
     
    Acura
    Toyota
    Buick
    Dodge
    Honda
    Volvo
  18. Click the New Model button and create a few models as follows:
     
    Acura MDX 3.5 4WD
    Dodge Caravan SE L4 2.4
    Ford Escort SE L4 2.0
    Toyota Rav4 2WD/4-Door
    Honda Civic 1.7 EX 4DR
    Ford Taurus LX V6 3.0
    Honda Accord 2.3 LX 4DR
  19. Click the New Category button and create a few categories as follows:
     
    Exhaust
    Air Intake
    Cooling System
    Engine Electrical
  20. Close the form(s) 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 does not have the same information or the same records it had when the application was launched. You, as the programmer, have the option of accepting the changes or rejecting them. To accept the changes, call the DataSet.AcceptChanges() method. Its syntax is:

Public Sub AcceptChanges

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

Public Overridable Sub RejectChanges

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

Locating Records and their Values

 

Locating a Record

Consider the following data set:

Imports System.IO

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


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

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

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

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

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

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

This would produce:

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

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

Public ReadOnly Property Item(index As Integer) As DataRow

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

Imports System.IO

Public Class Exercise
    Private lvwStudents As ListView

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


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

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

        Controls.Add(lvwStudents)

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

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

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

        dsRedOakHighSchool.ReadXml("students.xml")

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

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

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

Locating a Value by a Column's Index

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

Rows

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

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

Public Property Item(columnIndex As Integer) As Object

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

Imports System.IO

Public Class Exercise
    Private lvwStudents As ListView

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


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

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

        Controls.Add(lvwStudents)

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

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

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

        dsRedOakHighSchool.ReadXml("students.xml")

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

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

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

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

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

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

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

        lvwStudents.Items.Add(lviStudent)
    Next
End Sub

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

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

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

        lvwStudents.Items.Add(lviStudent)
    Next
End Sub

Locating a Value by the Column Object Name

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

Public Property Item(columnName As String) As Object

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

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

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

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

        lvwStudents.Items.Add(lviStudent)
    Next
End Sub

Locating a Value by the Column Variable Name

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

Public Property Item(column As DataColumn) As Object

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

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

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

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

        lvwStudents.Items.Add(lviStudent)
    Next
End Sub

Locating a Value From the Column Collection

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

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

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

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

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

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

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

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

Practical Learning Practical Learning: Getting the Values of a Data Set

  1. In the Solution Explorer, Double-click Central.vb
  2. From the Data section of the Toolbox, click DataSet and click the form
  3. In the Add Dataset dialog box, click the Untyped dataset radio button and click OK
  4. In the Properties window, change the characteristics of the data set as follows:
    DataSetName: StoreItems
    (Name): dsStoreItems
  5. Click Tables and click its ellipsis button
  6. In the Properties list, change the characteristics of the table as follows:
    TableName: AutoPart
    (Name): tblAutoPart
  7. Click Columns and click its ellipsis button
  8. In the Columns Collection Editor, click Add continuously and create the columns as follows:
     
    ColumnName (Name)
    PartNumber colPartNumber
    Year colYear
    Make colMake
    Model colModel
    Category colCategory
    PartName colPartName
    UnitPrice colUnitPrice
  9. Click Close and click Close
  10. Right-click the form and click View Code
  11. In the top section of the file, import the System.IO and the System.Xml namespaces
     
    Imports System.IO
    Imports System.Xml
    
    Public Class Central
  12. In the bottom section of the file, just above the End Class line, create a procedure as follows:
     
    Private Sub ShowAutoParts()
        Dim Years As Integer
        tvwAutoParts.Nodes.Clear()
        Dim RootReceipts As TreeNode = _
                tvwAutoParts.Nodes.Add("College Park Auto-Parts", _
                                       "College Park Auto-Parts", 0, 1)
    
        ' Show the years nodes
        For Years = DateTime.Today.Year + 1 To 1960 Step -1
            RootReceipts.Nodes.Add(CStr(Years), CStr(Years), 2, 3)
        Next
    
        tvwAutoParts.SelectedNode = RootReceipts
        ' Expand the root node
        tvwAutoParts.ExpandAll()
    
        ' This is the file that holds the list of store items on sale
        Dim Filename As String = "C:\College Park Auto Parts\StoreItems.xml"
    
        If File.Exists(Filename) Then
            dsStoreItems.ReadXml(Filename)
    
            ' Add the makes to the years
            For Each NodeYear As TreeNode In RootReceipts.Nodes
                Dim ListOfMakes As List(Of String) = New List(Of String)()
    
                For Each row As DataRow In tblAutoPart.Rows
                    If NodeYear.Text = row("Year") Then
                        If Not ListOfMakes.Contains(row("Make")) Then
                            ListOfMakes.Add(row("Make"))
                        End If
                    End If
                Next
    
                For Each strMake As String In ListOfMakes
                    NodeYear.Nodes.Add(strMake, strMake, 4, 5)
                Next
            Next
    
            ' Add the models to the makes
            For Each NodeYear As TreeNode In RootReceipts.Nodes
                For Each NodeMake As TreeNode In NodeYear.Nodes
                    Dim ListOfModels As List(Of String) = New List(Of String)
    
                    For Each row As DataRow In tblAutoPart.Rows
                        If (NodeYear.Text = row("Year")) And _
                             (NodeMake.Text = row("Make")) Then
                            If Not ListOfModels.Contains(row("Model")) Then
                                ListOfModels.Add(row("Model"))
                            End If
                        End If
                    Next
    
                    For Each strModel As String In ListOfModels
                        NodeMake.Nodes.Add(strModel, strModel, 6, 7)
                    Next
                Next
             Next
    
            ' Show the categories nodes
            For Each NodeYear As TreeNode In RootReceipts.Nodes
                For Each NodeMake As TreeNode In NodeYear.Nodes
                    For Each NodeModel As TreeNode In NodeMake.Nodes
                        Dim ListOfCategories As List(Of String) = New List(Of String)
    
                        For Each row As DataRow In tblAutoPart.Rows
                            If (NodeYear.Text = row("Year")) And _
                                (NodeMake.Text = row("Make")) And _
                                (NodeModel.Text = row("Model")) Then
                                If Not ListOfCategories.Contains(row("Category")) Then
                                    ListOfCategories.Add(row("Category"))
                                End If
                            End If
                        Next
    
                        For Each strCategory As String In ListOfCategories
                            NodeModel.Nodes.Add(strCategory, strCategory, 8, 9)
                        Next
                    Next
                Next
            Next
        End If
    End Sub
  13. In the Class Name combo box, select (Central Events)
  14. In the Method Name combo box, select Load and implement the event as follows:
     
    Private Sub Central_Load(ByVal sender As Object, _
                                 ByVal e As System.EventArgs) _
                                 Handles Me.Load
        ShowAutoParts()
        btnNewCustomerOrder_Click(sender, e)
    End Sub
  15. In the Class Name combo box, select txtPartNumber
  16. In the Method Name combo box, select Leave and implement the event as follows:
     
    Private Sub txtPartNumber_Leave(ByVal sender As Object, _
                                    ByVal e As System.EventArgs) _
                                    Handles txtPartNumber.Leave
        For Each row As DataRow In tblAutoPart.Rows
            If row("PartNumber") = txtPartNumber.Text Then
                txtPartName.Text = row("PartName")
                txtUnitPrice.Text = row("UnitPrice")
                txtQuantity.Text = "0"
                txtSubTotal.Text = "0.00"
            End If
        Next
    End Sub
  17. In the Class Name combo box, select btnAdd
  18. In the Method Name combo box, select Click and implement the event as follows:
     
    Private Sub btnAdd_Click(ByVal sender As Object, _
                                 ByVal e As System.EventArgs) _
                                 Handles btnAdd.Click
        If txtPartNumber.Text.Length = 0 Then
            MsgBox("There is no part to be added to the order")
            Exit Sub
        End If
    
        Dim lviSelectedPart As ListViewItem = _
                        New ListViewItem(txtPartNumber.Text)
    
        lviSelectedPart.SubItems.Add(txtPartName.Text)
        lviSelectedPart.SubItems.Add(txtUnitPrice.Text)
        lviSelectedPart.SubItems.Add(txtQuantity.Text)
        lviSelectedPart.SubItems.Add(txtSubTotal.Text)
        lvwSelectedParts.Items.Add(lviSelectedPart)
    
        CalculateOrder()
    End Sub
  19. In the Class Name combo box, select btnNewCustomerOrder
  20. In the Method Name combo box, select Click and implement the event as follows:
     
    Private Sub btnNewCustomerOrder_Click(ByVal sender As Object, _
                                          ByVal e As System.EventArgs) _
                                          Handles btnNewCustomerOrder.Click
        ' Create a default receipt number and set it to 0
        Dim ReceiptNumber As Integer = 0
        ' This is the name of the file that holds the customers' receipts
        Dim Filename As String = "C:\College Park Auto Parts\receipts.xml"
        Dim DOMReceipts As XmlDocument = New XmlDocument
    
        ' If the file exists, open it
        If File.Exists(Filename) Then
            DOMReceipts.Load(Filename)
    
            ' After opening the file, get a reference to its root
            Dim RootReceipts As XmlElement = DOMReceipts.DocumentElement
    
            ' Get a list of the receipt numbers
            Dim ListOfReceiptNumbers As XmlNodeList = _
               RootReceipts.GetElementsByTagName("ReceiptNumber")
            ' If there are receipts already, get the last receipt number
            ' and set it as the current receipt number
            ReceiptNumber = CInt(ListOfReceiptNumbers( _
    		ListOfReceiptNumbers.Count - 1).InnerText)
        End If
    
        ' Initialize a new customer number with default values
        txtSave.Text = CStr(ReceiptNumber + 1)
        txtTaxRate.Text = "5.75"
        txtTaxAmount.Text = "0.00"
        txtPartsTotal.Text = "0.00"
        txtOrderTotal.Text = "0.00"
        lvwSelectedParts.Items.Clear()
    End Sub
  21. In the Class Name combo box, select tvwAutoParts
  22. In the Method Name combo box, select NodeMouseClick and implement the event as follows:
     
    Private Sub tvwAutoParts_NodeMouseClick(ByVal sender As Object, _
                  ByVal e As System.Windows.Forms.TreeNodeMouseClickEventArgs) _
                                            Handles tvwAutoParts.NodeMouseClick
        ' Get a reference to the node that was clicked
        Dim nodClicked As TreeNode = e.Node
    
        ' If the user clicked the category of a part,
        ' Then clear the Available Parts list view
        If nodClicked.Level = 4 Then
            lvwAutoParts.Items.Clear()
        End If
    
        Try
            ' Check each record in the list/table of auto parts
            For Each Record As DataRow In tblAutoPart.Rows
                ' If you find a record that corresponds to the
                ' category, make, model, and year that the clerk clicked ...
                If (Record("Category") = nodClicked.Text) And _
                   (Record("Model") = nodClicked.Parent.Text) And _
                   (Record("Make") = nodClicked.Parent.Parent.Text) And _
                   (Record("Year") = nodClicked.Parent.Parent.Parent.Text) Then
                    ' ... get that auto part and display it
                    ' in the Available Parts list view
                    Dim lviAutoPart As ListViewItem = _
                            New ListViewItem(CStr(Record("PartNumber")))
                    lviAutoPart.SubItems.Add(Record("PartName"))
                    lviAutoPart.SubItems.Add(Record("UnitPrice"))
                    lvwAutoParts.Items.Add(lviAutoPart)
                End If
            Next
        Catch Exc As NullReferenceException
             ' If there is no such a part, don't do nothin'
        End Try
    End Sub
  23. In the Class Name combo box, select btnSave
  24. In the Method Name combo box, select Click and implement the event as follows:
     
    Private Sub btnSave_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnSave.Click
            ' This is the file that holds the receipts
            Dim Filename As String = "C:\College Park Auto Parts\receipts.xml"
            ' Get a reference to the root
            Dim DOMReceipts As XmlDocument = New XmlDocument
    
            ' We will create a list of all the parts 
            ' that the customer wants to purchase
            Dim strParts As String = ""
    
            For Each lviPart As ListViewItem In lvwSelectedParts.Items
                strParts = strParts & _
                           "<Part>" & _
                           "<PartNumber>" & lviPart.SubItems(0).Text & "</PartNumber>" & _
                           "<PartName>" & lviPart.SubItems(1).Text & "</PartName>" & _
                           "<UnitPrice>" & lviPart.SubItems(2).Text & "</UnitPrice>" & _
                           "<Quantity>" & lviPart.SubItems(3).Text & "</Quantity>" & _
                           "<SubTotal>" & lviPart.SubItems(4).Text & "</SubTotal>" & _
                           "</Part>"
            Next
    
            ' If this is the first customer order, ...
            If Not File.Exists(Filename) Then
                ' If this is the first receipt to be created,
                ' set the receipt number to 1
                ' and create the structure of the document
                DOMReceipts.LoadXml("<?xml version=""1.0"" encoding=""utf-8""?>" & _
                                    "<CustomersOrders>" & _
                                    "<Receipt>" & _
                                    "<ReceiptNumber>1</ReceiptNumber>" & strParts & _
                                    "<PartsTotal>" & txtPartsTotal.Text & _
                                    "</PartsTotal><TaxRate>" & txtTaxRate.Text & _
                                    "</TaxRate><TaxAmount>" & txtTaxAmount.Text & _
                                    "</TaxAmount><OrderTotal>" & txtOrderTotal.Text & _
                                    "</OrderTotal></Receipt></CustomersOrders>")
    
                ' Save the XML file
                DOMReceipts.Save(Filename)
    
                ' Reset the customer order
                txtSave.Text = "2"
                txtOpen.Text = "1"
                txtTaxRate.Text = "5.75"
                txtTaxAmount.Text = "0.00"
                txtPartsTotal.Text = "0.00"
                txtOrderTotal.Text = "0.00"
                lvwSelectedParts.Items.Clear()
                ' If this is not the first customer order, ...
            Else ' if File.Exists(Filename) Then
                Dim NodeCustomerOrder As XmlNode = Nothing
                ' We will use a receipt number for each receipt
                Dim ReceiptNumber As Integer = CInt(txtSave.Text)
    
                ' If at least one receipt had previously been created,
                ' then open the XML file that holds the receipts
    
                ' Store the XML file structure into the DOM
                DOMReceipts.Load(Filename)
                ' Get a reference to the root element
                Dim RootCustomersOrders As XmlElement = DOMReceipts.DocumentElement
    
                ' Get a list of the receipt numbers
                Dim ListOfReceipts As XmlNodeList = RootCustomersOrders.GetElementsByTagName("Receipt")
    
                ' Check each receipt
                For Each NodeCurrentReceipt As XmlNode In ListOfReceipts
                    ' Look for a receipt that has the same number
                    ' as in the Save text box
                    If NodeCurrentReceipt("ReceiptNumber").InnerText = txtSave.Text Then
                        ' If you find it, reserve it
                        NodeCustomerOrder = NodeCurrentReceipt
                        Exit For
                    End If
                Next
    
                ' Locate the last receipt number
                ReceiptNumber = CInt(txtSave.Text)
    
                ' Create an element named Receipt
                Dim ElementReceipt As XmlElement = DOMReceipts.CreateElement("Receipt")
                Dim strReceipt As String = "<ReceiptNumber>" & ReceiptNumber.ToString() & _
                                        "</ReceiptNumber>" & strParts & _
                                        "<PartsTotal>" & txtPartsTotal.Text & _
                                        "</PartsTotal><TaxRate>" & txtTaxRate.Text & _
                                        "</TaxRate><TaxAmount>" & txtTaxAmount.Text & _
                                        "</TaxAmount><OrderTotal>" & txtOrderTotal.Text & _
                                        "</OrderTotal>"
    
                ' Create the XML code of the new element
                ElementReceipt.InnerXml = strReceipt
    
                ' If this is a new customer order
                If NodeCustomerOrder Is Nothing Then
                    ' Add the new receipt to the file
                    DOMReceipts.DocumentElement.AppendChild(ElementReceipt)
                    ' If the customer order existed already, we will only update it
                Else ' if NodeCustomerOrder <> nothing then
                    ' Replace the existing customer order with the current one
                    DOMReceipts.DocumentElement.ReplaceChild(ElementReceipt, NodeCustomerOrder)
                End If
    
                ' Save the XML file
                DOMReceipts.Save(Filename)
    
                ' Reset the customer order
                btnNewCustomerOrder_Click(sender, e)
            End If
    End Sub
  25. In the Class Name combo box, select btnOpen
  26. In the Method Name combo box, select Click and implement the event as follows:
     
    Private Sub btnOpen_Click(ByVal sender As Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnOpen.Click
            Dim DOMReceipts As XmlDocument = New XmlDocument
            Dim Filename As String = "C:\College Park Auto Parts\receipts.xml"
    
            ' Check that the file exists. If so, open it
            If File.Exists(Filename) Then
                ' This variable will allow us to know if we have the receipt number
                Dim Found As Boolean = False
                ' Empty the list of selected parts
                lvwSelectedParts.Items.Clear()
    
                ' After opening the XML file, store it in the DOM
                DOMReceipts.Load(Filename)
                ' Get a reference to the root element
                Dim RootReceipts As XmlElement = DOMReceipts.DocumentElement
    
                ' Get a list of the receipts in the file
                Dim ListOfReceipts As XmlNodeList = RootReceipts.GetElementsByTagName("Receipt")
    
                ' Check each receipt
                For Each nodReceipt As XmlNode In ListOfReceipts
                    ' Look for an receipt that has the same number
                    ' as on the Open text box
                    If nodReceipt("ReceiptNumber").InnerText = txtOpen.Text Then
                        ' If you find it, make a note
                        Found = True
                        txtOpen.Text = nodReceipt("ReceiptNumber").InnerText
                        txtSave.Text = nodReceipt("ReceiptNumber").InnerText
    
                        ' Retrieve the values of the receipt
                        ' and display them on the form
                        Try
                            For Each nodeReceipt As XmlNode In nodReceipt.ChildNodes
    
                                Dim Node As XmlNode = nodeReceipt.NextSibling.ChildNodes(0)
    
                                Dim lviReceipt As ListViewItem = New ListViewItem(Node.InnerText)
                                lviReceipt.SubItems.Add(Node.NextSibling.InnerText)
                                lviReceipt.SubItems.Add(Node.NextSibling.NextSibling.InnerText)
                                lviReceipt.SubItems.Add(Node.NextSibling.NextSibling.NextSibling.InnerText)
                                lviReceipt.SubItems.Add(Node.NextSibling.NextSibling.NextSibling.NextSibling.InnerText)
    
                                lvwSelectedParts.Items.Add(lviReceipt)
                            Next
    
                            txtPartsTotal.Text = nodReceipt("PartsTotal").InnerText
                            txtTaxRate.Text = nodReceipt("TaxRate").InnerText
                            txtTaxAmount.Text = nodReceipt("TaxAmount").InnerText
                            txtOrderTotal.Text = nodReceipt("OrderTotal").InnerText
                        Catch Exc As NullReferenceException
    
                        End Try
                    End If
                Next
    
                ' If the receipt was not found, let the user know
                If Found = False Then
                    MsgBox("There is no customer order with that receipt number")
                    txtSave.Text = txtOpen.Text
                End If
                ' IF the XML file was not found, let the user know
            Else
                MsgBox("The file " & _ Filename & " was not found")
            End If
    End Sub
  27. Save the file
  28. Display the Central form and double-click an unoccupied area of its body
  29. Add a call to the Click event of the New Customer Order button as follows:
     
    Private Sub Central_Load(ByVal sender As Object, _
                                 ByVal e As System.EventArgs) _
                                 Handles Me.Load
        ShowAutoParts()
        btnNewCustomerOrder_Click(sender, e)
    End Sub
  30. Return to the Central form and double-click the Close button
  31. Implement the event as follows:
     
    Private Sub btnClose_Click(ByVal sender As Object, _
                               ByVal e As System.EventArgs) _
                               Handles btnClose.Click
        End
    End Sub
  32. Execute the application
  33. Click the New Auto Part button and use the Part Editor to create a few parts 
  34. Close the Part Editor
  35. Create a few customer part orders and save them:
     
    College Park Auto Parts: Customer Order
     
    College Park Auto Parts: Part Selection
  36. Close the forms and return to your programming environment
  37. Execute the application again and open a previously saved order
  38. Close the forms and return to your programming environment

Exercises

 

College Park Auto Parts

  1. Open the CollegeParkAutoParts2 application from this lesson
  2. Create a Part Editor form and design it as follows:
     
  3. Add a context menu for the Available Parts list view with the items: Select, Edit..., and Delete
     
  4. Configure the context menu so that
    1. If the user clicks Select, the behavior would be the same as if the user had double-clicked the item
    2. If the user clicks Edit..., the Part Editor dialog box would display with the part in it. The user can then edit any part (year, make, model, category, part name, or unit price) except the part number. Then the user can save the changed part
    3. If the user clicks Delete, a message box would warn the user and ask for confirmation with Yes/No answers. If the user clicks Yes, the part would be deleted from the AutoParts list
  5. Configure the application so that the user can open an order, add new parts to it, or delete parts from it, then save the order
  6. Extend the application so that the store can also sell items that are, or are not, car-related, such as books, t-shirts, cleaning items, maintenance items (steering oils, brake oils, etc), license plates, etc. Every item in the store should have an item number. The user can enter that item number in the Part # text box and press Tab or Enter. The corresponding item would then be retrieved from the database and displayed on the form. If there is no item with that number, a message box should let the user know

 


Previous Copyright 2008, Yevol Next