Home

Example Data Set Application:
College Park Auto-Parts

 

Introduction to Records

DataSet is the central class of the .NET Framework databases. It can be used to create a file-based database, to save the items of an XML-based application, or to participate in an ADO.NET database. In this example, we explore some of its characteristics.

College Park Auto-Parts is a fictitious company that sells auto parts. Before being able to process a customer order, the employees of the company must enter the information (data) about the different items sold in the store. The items are organized in categories such as the year of the car, the car manufacturer, and the model. Each item is also identified by a category such as Exhaust, Engine Electrical, or Cooling System.

When processing an order, an employee specifies the primary details of the part the customer is requesting. If available, the list of items of that category is displayed. The user can then save the order that can be opened later on.

 

Practical Learning: Introducing the Application

  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:
     
    Icon
  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 lvwAutoPartsDoubleClick(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 = SubTotal.ToString("F")
    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 lvwSelectedPartsDoubleClick(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
  76. To create a dialog box, on the main menu, click Project -> Add Windows Form...
  77. Set the name to ItemsCategories and click Add
  78. From the Data section of the Toolbox, click DataSet and click the form
  79. On the Add Dataset dialog box, click the Untyped dataset radio button and click OK
  80. In the Properties window, change the characteristics of the data set as follows:
    DataSetName: Categories
    (Name): dsCategories
  81. Click the Tables field and click its ellipsis button
  82. In the Tables Collection Editor, click Add
  83. In the Properties list, change the characteristics of the table as follows:
    TableName: Category
    (Name): tblCategory
  84. Click Columns and click its ellipsis button
  85. In the Columns Collection Editor, click Add
  86. Change the characteristics of the columns as follows:
    ColumnName: CategoryName
    (Name): colCategoryName 
  87. Click Close and click Close
  88. 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
  89. To create a dialog box, on the main menu, click Project -> Add Windows Form...
  90. Set the name to CarMakes and click Add
  91. From the Data section of the Toolbox, click DataSet and click the form
  92. On the Add Dataset dialog box, click the Untyped dataset radio button and click OK
  93. In the Properties window, change the characteristics of the data set as follows:
    DataSetName: Makes
    (Name): dsMakes
  94. Click the Tables field and click its ellipsis button
  95. In the Tables Collection Editor, click Add
  96. In the Properties list, change the characteristics of the table as follows:
    TableName: Make
    (Name): tblMake
  97. Click Columns and click its ellipsis button
  98. In the Columns Collection Editor, click Add
  99. Change the characteristics of the columns as follows:
    ColumnName: MakeName
    (Name): colMakeName 
  100. Click Close and click Close
  101. 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
  102. To create a dialog box, on the main menu, click Project -> Add Windows Form...
  103. Set the name to CarModels and click Add
  104. Display the list of makes form
  105. Right-click dsMakes and click Copy
  106. Display the list of models form
  107. Right-click its body and click Paste
  108. From the Data section of the Toolbox, click DataSet and click the form
  109. On the Add Dataset dialog box, click the Untyped dataset radio button and click OK
  110. In the Properties window, change the characteristics of the data set as follows:
    DataSetName: Models
    (Name): dsModels
  111. Click the Tables field and click its ellipsis button
  112. In the Tables Collection Editor, click Add
  113. In the Properties list, change the characteristics of the table as follows:
    TableName: Car
    (Name): tblCar
  114. Click Columns and click its ellipsis button
  115. In the Columns Collection Editor, click Add
  116. Change the characteristics of the columns as follows:
    ColumnName: Make
    (Name): colMake
  117. In the Columns Collection Editor, click Add
  118. Change the characteristics of the columns as follows:
    ColumnName: Model
    (Name): colModel
  119. Click Close and click Close
  1. From the Data section of the Toolbox, click DataGridView and click the form
  2. In the Properties window, change its characteristics as follows:
    DataSource: dsModels
    DataMember: Models
  3. 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      
  4. Click OK
  5. 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
  6. To create a new form, in the Solution Explorer, right-click CollegeParkAutoParts2 -> Add -> Windows Form...
  7. Set the Name to AutoParts and click Add
  8. Display the list of models form
  9. Click dsMakes
  10. Press and hold Shift
  11. Click dsModels
  12. Release Shift 
  13. Press Ctrl + C
  14. Display the auto parts form
  15. Right-click its body and click Paste
  16. Display the ItemsCategories form
  17. Right-click dsCategories and click Copy
  18. Display the AutoParts form
  19. Right-click its body and click Paste
  20. From the Data section of the Toolbox, click DataSet and click the form
  21. In the Add Dataset dialog box, click the Untyped dataset radio button and click OK
  22. In the Properties window, change the characteristics of the data set as follows:
    DataSetName: StoreItems
    (Name): dsStoreItems
  23. Click Tables and click its ellipsis button
  24. In the Properties list, change the characteristics of the table as follows:
    TableName: StoreItem
    (Name): tblStoreItem
  25. Click Columns and click its ellipsis button
  26. 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
  27. Click Close and click Close
  28. From the Data section of the Toolbox, click DataGridView and click the form
  29. In the Properties window, change its characteristics as follows:
    DataSource: dsStoreItems
    DataMember: StoreItems
  30. 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
  31. Click OK
  32. 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
  33. Right-click the form and click View Code
  34. In the Class Name combo box, select btnNewMake
  35. In the Method Name combo box, select Click and change the file as follows:
      
    Imports System.IO
    
    Public Class AutoParts
    
        Private Sub btnNewMakeClick(ByVal sender As Object, 
                                     ByVal e As System.EventArgs) 
                                     Handles btnNewMake.Click
            Dim frmMakes As CarMakes = New CarMakes
            frmMakes.ShowDialog()
        End Sub
    End Class
  36. In the Class Name combo box, select btnNewModel
  37. In the Method Name combo box, select Click and implement the event as follows:
      
    Private Sub btnNewModelClick(ByVal sender As Object, 
                                  ByVal e As System.EventArgs) 
                                  Handles btnNewModel.Click
        Dim frmModels As CarModels = New CarModels
        frmModels.ShowDialog()
    End Sub
  38. In the Class Name combo box, select btnNewCategory
  39. In the Method Name combo box, select Click and implement the event as follows:
      
    Private Sub btnNewCategoryClick(ByVal sender As Object, 
                                     ByVal e As System.EventArgs) 
                                     Handles btnNewCategory.Click
        Dim frmCategories As ItemsCategories = New ItemsCategories
        frmCategories.ShowDialog()
    End Sub
  40. In the Solution Explorer, right-click Central.vb and click View Code 
  41. In the Class Name combo box, select btnNewAutoPart
  42. In the Method Name combo box, select Click and implement its event as follows:
     
    Private Sub btnNewAutoPartClick(ByVal sender As Object, 
                                         ByVal e As System.EventArgs) 
                                         Handles btnNewAutoPart.Click
        Dim frmParts As AutoParts = New AutoParts
    
        frmParts.ShowDialog()
    End Sub
  43. Execute the application and make sure the forms and dialog boxes display
  44. Close the forms and return to your programming environment

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 btnCloseClick(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 btnCloseClick(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 btnCloseClick(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 btnCloseClick(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

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 ItemsCategoriesLoad(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 CarMakesLoad(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 CarModelsLoad(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 AutoPartsLoad(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 AutoPartsActivated(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

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 CentralLoad(ByVal sender As Object, 
                                 ByVal e As System.EventArgs) 
                                 Handles Me.Load
        ShowAutoParts()
        btnNewCustomerOrderClick(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 txtPartNumberLeave(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 btnAddClick(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 btnNewCustomerOrderClick(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 tvwAutoPartsNodeMouseClick(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 btnSaveClick(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
            btnNewCustomerOrderClick(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 btnOpenClick(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 CentralLoad(ByVal sender As Object, 
                                 ByVal e As System.EventArgs) 
                                 Handles Me.Load
        ShowAutoParts()
        btnNewCustomerOrderClick(sender, e)
    End Sub
  30. Return to the Central form and double-click the Close button
  31. Implement the event as follows:
     
    Private Sub btnCloseClick(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

Home Copyright © 2008-2012 FunctionX