Home

Example Application: Watts A Loan

 

Introduction

Data entry is probably the most important aspect of a database. Without data, there is nothing to do with a database. In this application, we explore some of the details of assisting a user with data entry. The various issues include unique values, data types, or default values, etc.

Watts A Loan is a fictitious company that lends money to individuals. The amounts can be small to cover cash need or higher for car purchase. The money can be given directly to a customers or through a store (furniture store or musical instrument store, etc).

 
 

Practical Learning: Introduction to the Application

  1. Start Microsoft Visual Basic and create a Windows Application named WattsALoan1
  2. To create a new form, in the Solution Explorer, right-click WattsALoan1 -> Add -> Windows Forms...
  3. Set the Name to LoanAllocations and click Add
  4. From the Data section of the Toolbox, click DataSet and click the form
  5. Select the Untyped Dataset radio button and click OK
  6. In the Properties window, change the following characteristics:
    DataSetName: dsLoanAllocations
    (Name): LoanAllocations
  7. Click Tables and click its ellipsis button
  8. To create a new table, click Add and change the properties as follows:
    TableName: Loan
    (Name): tblLoan
  9. Click Columns and click its ellipsis button
  10. Click Add 10 times and change the properties as follows:
     
    ColumnName (Name)
    DateAllocated colDateAllocated
    LoanNumber colLoanNumber
    PreparedBy colPreparedBy
    PreparedFor colPreparedFor
    Principal colPrincipal
    InterestRate colInterestRate
    Periods colPeriods
    InterestEarned colInterestEarned
    FutureValue colFutureValue
    MonthlyPayment colMonthlyPayment

Practical Learning: Controlling the Uniqueness of a Column

  1. In the Members list, click LoanNumber
  2. In the Properties list, double-click Unique to change its value to True

Practical Learning: Applying Data Types on Columns

  1. In the Members list, click Principal
  2. In the Properties list, click DataType, click the arrow of its combo box and select System.Double
  3. In the same way, change the data types of the following columns:
     
    Member DataType
    DateAllocated System.DateTime
    LoanNumber System.String
    PreparedBy System.String
    PreparedFor System.String
    Principal System.Double
    InterestRate System.Double
    Periods System.Double
    InterestEarned System.Double
    FutureValue System.Double
    MonthlyPayment System.Double

Practical Learning: Applying Data Types on Columns

  1. In the Members list, click Principal
  2. In the Properties list, click DefaultValue and delete <DBNull>
  3. Type 0.00
  4. In the same way, change the default values of the following columns:
     
    Member DefaultValue
    Principal 0.00
    InterestRate 8.75
    Periods 36

Practical Learning: Creating Expressions on Columns

  1. In the Members list, click FutureValue
  2. In the Properties list, click Expression and type Principal + InterestEarned
  3. In the same way, change the data types of the following columns:
     
    Member Expression
    InterestEarned Principal * (InterestRate / 100) * (Periods / 12)
    FutureValue Principal + InterestEarned
    MonthlyPayment FutureValue / Periods

Practical Learning: Nullifying a Column

  1. In the Members list, click DateAllocated
  2. In the Properties list, double-click the value of the AllowDBNull field to set it to False
  3. In the Members list, click LoanNumber
  4. In the Properties list, double-click the value of the AllowDBNull field to set it to False
  5. Click Close and click Close
  6. To create a new form, in the Solution Explorer, right-click WattsALoan1 -> Add -> Windows Forms...
  7. Set the Name to Employees and click Add
  8. From the Data section of the Toolbox, click DataSet and click the form
  9. Select the Untyped Dataset radio button and click OK
  10. In the Properties window, change the following characteristics:
    DataSetName: dsEmployees
    (Name): Employees
  11. Click Tables and click its ellipsis button
  12. To create a new table, click Add and change the properties as follows:
    TableName: Employee
    (Name): tblEmployee
  13. Click Columns and click its ellipsis button
  14. Click Add 5 times and change the properties as follows:
     
    AllowDBNull ColumnName DefaultValue DataType Expression Unique (Name)
    False EmployeeNumber       True colEmployeeNumber
      FirstName         colFirstName
    False LastName         colLastName
      FullName     LastName + ', ' + FirstName   colFullName
      Title         colTitle
      HourlySalary 8.75 System.Double     colHourlySalary
  15. Click Close and click Close
  16. Design the form as follows:
     
    Watts A Loan
     
    Control Text Name Other Properties
    DataGridView   dgvEmployees DataSource: dsEmployees
    DataMember: Employee
    Button Close btnClose  
    Data Grid Columns
     
    DataPropertyName HeaderText Width
    EmployeeNumber Empl # 65
    FirstName First Name 65
    LastName Last Name 65
    FullName Full Name 120
    Title   110
    HourlySalary Salary/hr 60
  17. Right-click the Employees form and click View Code
  18. Above the Public Class Employees line, import the System.IO namespace
    Imports System.IO
    
    Public Class Employees
    
    End Class
  19. In  the Class Name combo box, select (Employees Events)
  20. In the Method Name combo box, select Load and implement the event as follows:
    Imports System.IO
    
    Public Class Employees
    
        Private Sub EmployeesLoad(ByVal sender As Object, 
                                   ByVal e As System.EventArgs) 
                                   Handles Me.Load
            Dim Filename As String = "employees.xml"
    
            If File.Exists(Filename) Then
                dsEmployees.ReadXml(Filename)
            End If
        End Sub
    End Class
  21. In the Method Name combo box, select FormClosing and implement the event as follows:
    Private Sub EmployeesFormClosing(ByVal sender As Object, 
                  ByVal e As System.Windows.Forms.FormClosingEventArgs) 
                                          Handles Me.FormClosing
        dsEmployees.WriteXml("employees.xml")
    End Sub
  22. In  the Class Name combo box, select btnClose
  23. In the Method Name combo box, select Click and implement the event as follows:
    Private Sub btnCloseClick(ByVal sender As Object, 
                                   ByVal e As System.EventArgs) 
                                   Handles btnClose.Click
        Close()
    End Sub
  24. Return to the form
  25. Under the form, right-click dsEmployees and click Copy
  26. Display the LoanAllocations form
  27. Right-click it and click Paste
  28. To create a new form, in the Solution Explorer, right-click WattsALoan1 -> Add -> Windows Forms...
  29. Set the Name to Customers and click Add
  30. From the Data section of the Toolbox, click DataSet and click the form
  31. Select the Untyped Dataset radio button and click OK
  32. In the Properties window, change the following characteristics:
    DataSetName: dsCustomers
    (Name): Customers
  33. Click Tables and click its ellipsis button
  34. To create a new table, click Add and change the properties as follows:
    TableName: Customer
    (Name): tblCustomer
  35. Click Columns and click its ellipsis button
  36. Click Add 5 times and change the properties as follows:
     
    AllowDBNull ColumnName Unique (Name)
    False AccountNumber True colAccountNumber
    False FullName   colFullName
      EmailAddress   colEmailAddress
      PhoneNumber   colPhoneNumber
  37. Click Close and click Close
  38. Design the form as follows:
     
    Watts A Loan: Customers
     
    Control Text Name Other Properties
    DataGridView   dgvCustomers DataSource: dsCustomers
    DataMember: Customer
    Button Close btnClose  
    Data Grid Columns
     
    DataPropertyName HeaderText Width
    AccountNumber Account # 65
    FullName Full Name 120
    EmailAddress Email Address 120
    PhoneNumber Phone # 90
  39. Right-click the Employees form and click View Code
  40. Above the Public Class Customers line, import the System.IO namespace
    Imports System.IO
    
    Public Class Customers
    
    End Class
  41. In  the Class Name combo box, select (Customers Events)
  42. In the Method Name combo box, select Load and implement the event as follows:
    Imports System.IO
    
    Public Class Customers
    
        Private Sub CustomersLoad(ByVal sender As Object, 
                                   ByVal e As System.EventArgs) 
                                   Handles Me.Load
            Dim Filename As String = "customers.xml"
    
            If File.Exists(Filename) Then
                dsCustomers.ReadXml(Filename)
            End If
        End Sub
    End Class
  43. In the Method Name combo box, select FormClosing and implement the event as follows:
    Private Sub CustomersFormClosing(ByVal sender As Object, 
                  ByVal e As System.Windows.Forms.FormClosingEventArgs) 
                                          Handles Me.FormClosing
        dsCustomers.WriteXml("customers.xml")
    End Sub
  44. In  the Class Name combo box, select btn Close
  45. In the Method Name combo box, select Click and implement the event as follows:
    Private Sub btnCloseClick(ByVal sender As Object, 
                                   ByVal e As System.EventArgs) 
                                   Handles btnClose.Click
        Close()
    End Sub
  46. Return to the form
  47. Under the form, right-click dsCustomers and click Copy
  48. Display the LoanAllocations form
  49. Right-click it and click Paste
  50. Design the form as follows:
     
    Watts A Loan
     
    Control Text Name Other Properties
    DataGridView   dgvCustomers DataSource: dsCustomers
    DataMember: Customer
    Button Close btnClose  
    Data Grid Columns
     
    DataPropertyName HeaderText Width DefaultCellStyle -> Format  
    DateAllocated Date Allocated   Date Time  
    LoanNumber Loan # 65    
    PreparedBy Prepared By 110   ColumnType: DataGridViewComboBoxColumn
    DataSource: dsEmployees
    DisplayMember: Employee.EmployeeNumber
    PreparedFor Prepared For 110   ColumnType: DataGridViewComboBoxColumn
    DataSource: dsCustomers
    DisplayMember: Customer.AccountNumber
    Principal   70 Currency  
    InterestRate Rate (%) 65 Numeric  
    Periods Prd (Months) 65 Numeric  
    InterestEarned Interest Earned   Currency  
    FutureValue Future Value   Currency  
    MonthlyPayment Pmt/Month   Currency  
  51. Right-click the Employees form and click View Code
  52. Above the Public Class LoanAllocations line, import the System.IO namespace
     
    Imports System.IO
    
    Public Class LoanAllocations
    
    End Class
  53. In  the Class Name combo box, select (LoanAllocations Events)
  54. In the Method Name combo box, select Load and implement the event as follows:
     
    Private Sub LoanAllocationsLoad(ByVal sender As Object, 
                                         ByVal e As System.EventArgs) 
                                         Handles Me.Load
        Dim Filename As String = "employees.xml"
    
        If File.Exists(Filename) Then
            dsEmployees.ReadXml(Filename)
        End If
    
        Filename = "customers.xml"
    
        If File.Exists(Filename) Then
            dsCustomers.ReadXml(Filename)
        End If
        
        Filename = "loans.xml"
    
        If File.Exists(Filename) Then
            dsLoanAllocations.ReadXml(Filename)
        End If
    End Sub
  55. In the Method Name combo box, select FormClosing and implement the event as follows:
     
    Private Sub LoanAllocationsFormClosing(ByVal sender As Object, 
                  ByVal e As System.Windows.Forms.FormClosingEventArgs) 
                                                Handles Me.FormClosing
        dsLoanAllocations.WriteXml("loans.xml")
    End Sub
  56. In  the Class Name combo box, select btn Close
  57. In the Method Name combo box, select Click and implement the event as follows:
     
    Private Sub btnCloseClick(ByVal sender As Object, 
                                   ByVal e As System.EventArgs) 
                                   Handles btnClose.Click
        Close()
    End Sub
  58. To create a new form, in the Solution Explorer, right-click WattsALoan1 -> Add -> Windows Forms...
  59. Set the Name to Payments and click Add
  60. Display the Employees form
  61. Right-click dsEmployees and click Copy
  62. Display the Payments form
  63. Right-click it and click Paste
  64. Display the LoanAllocations form
  65. Right-click dsLoanAllocations and click Copy
  66. Display the Payments form
  67. Right-click it and click Paste
  68. From the Data section of the Toolbox, click DataSet and click the Payments form
  69. Select the Untyped Dataset radio button and click OK
  70. In the Properties window, change the following characteristics:
    DataSetName: dsPayments
    (Name): Payments
  71. Click Tables and click its ellipsis button
  72. To create a new table, click Add and change the properties as follows:
    TableName: Payment
    (Name): tblPayment
  73. Click Columns and click its ellipsis button
  74. Click Add 6 times and change the properties as follows:
     
    AllowDBNull ColumnName DataType DefaultValue Expression Unique (Name)
    False PaymentNumber       True colPaymentNumber
    False PaymentDate System.DateTime       colPaymentDate
    False ReceivedBy         colReceivedBy
    False PaymentFor         colPaymentFor
    False PaymentAmount System.Double 0.00     colPaymentAmount
      Balance System.Double 0.00     colBalance
  75. Click Close and click Close
  76. Design the form as follows:
     
    Watts A Loan: Payments
     
    Control Text Name Other Properties
    DataGridView   dgvPayments DataSource: dsPayments
    DataMember: Payment
    Button Loans... btnLoans  
    Button Close btnClose  
    Data Grid Columns
     
    DataPropertyName HeaderText Width DefaultCellStyle -> Format  
    PaymentNumber Pmt # 55    
    PaymentDate Pmt Date 70 Date Time  
    ReceivedBy Received By     ColumnType: DataGridViewComboBoxColumn
    DataSource: dsEmployees
    DisplayMember: Employee.EmployeeNumber
    PaymentFor Payment For     ColumnType: DataGridViewComboBoxColumn
    DataSource: dsLoanAllocations
    DisplayMember: Loan.LoanNumber
    PaymentAmount Pmt Amt 70 Currency  
    Balance   80 Currency  
  77. Right-click the Employees form and click View Code
  78. Above the Public Class Payments line, import the System.IO namespace
     
    Imports System.IO
    
    Public Class Payments
    
    End Class
  79. In  the Class Name combo box, select (Payments Events)
  80. In the Method Name combo box, select Load and implement the event as follows:
     
    Private Sub PaymentsLoad(ByVal sender As Object, 
                                  ByVal e As System.EventArgs) 
                                  Handles Me.Load
            Dim Filename As String = "employees.xml"
    
            If File.Exists(Filename) Then
                dsEmployees.ReadXml(Filename)
            End If
    
            Filename = "loans.xml"
    
            If File.Exists(Filename) Then
                dsLoanAllocations.ReadXml(Filename)
            End If
    
            Filename = "payments.xml"
    
            If File.Exists(Filename) Then
                dsPayments.ReadXml(Filename)
            End If
    End Sub
  81. In the Method Name combo box, select FormClosing and implement the event as follows:
     
    Private Sub PaymentsFormClosing(ByVal sender As Object, 
                  ByVal e As System.Windows.Forms.FormClosingEventArgs) 
                                         Handles Me.FormClosing
        dsPayments.WriteXml("payments.xml")
    End Sub
  82. In the Class Name combo box, select btnLoans
  83. In the Method Name combo box, select Click and implement the event as follows:
     
    Private Sub btnLoansClick(ByVal sender As Object, 
                                   ByVal e As System.EventArgs) 
                                   Handles btnLoans.Click
        Dim frmLoans As LoanAllocations = New LoanAllocations
        frmLoans.Show()
    End Sub
  84. In the Class Name combo box, select btn Close
  85. In the Method Name combo box, select Click and implement the event as follows:
     
    Private Sub btnCloseClick(ByVal sender As Object, 
                                   ByVal e As System.EventArgs) 
                                   Handles btnClose.Click
        Close()
    End Sub
  86. In the Solution Explorer, right-click Form1.vb and click Rename
  87. Type Central.vb and press Enter twice to display the form
  88. Design the form as follows:
     
    Watts A Loan
    Control Text Name
    Button ... btnPayments
    Label Loan Payments lblPayments
    Button ... btnAllocations
    Label Loan Allocations lblAllocations
    Button ... btnCustomers
    Label Customers lblCustomers
    Button ... btnEmployees
    Label Employees lblEmployees
    Button Close btnClose
  89. Right-click the form and click View Code
  90. Create the following events:
     
    Public Class Central
        Private Sub PaymentClick(ByVal sender As Object, 
                                 ByVal e As EventArgs) 
                                 Handles btnPayments.Click, 
                                         lblPayments.Click
            Dim frmPayments As Payments = New Payments
            frmPayments.ShowDialog()
        End Sub
    
        Private Sub LoanAllocationsClick(ByVal sender As Object, 
                                         ByVal e As EventArgs) 
                                         Handles btnLoanAllocations.Click, 
                                                 lblLoanAllocations.Click
            Dim frmLoans As LoanAllocations = New LoanAllocations
            frmLoans.ShowDialog()
        End Sub
    
        Private Sub CustomersClick(ByVal sender As Object, 
                                   ByVal e As EventArgs) 
                                   Handles btnCustomers.Click, 
                                           lblCustomers.Click
            Dim frmClients As Customers = New Customers
            frmClients.ShowDialog()
        End Sub
    
        Private Sub EmployeesClick(ByVal sender As Object, 
                                   ByVal e As EventArgs) 
                                   Handles btnEmployees.Click, 
                                           lblEmployees.Click
            Dim frmStaff As Employees = New Employees
            frmStaff.ShowDialog()
        End Sub
    
        Private Sub btnCloseClick(ByVal sender As Object, 
                                   ByVal e As System.EventArgs) 
                                   Handles btnClose.Click
            End
        End Sub
    End Class
  91. Execute the application
  92. Click the Employees label and create the following records:
     
    Employee # First Name Last Name Title Salary/hr
    7973-45 Bernard Wallow Account Manager 24.85
    2497-94 Justine Bogley Sales Representative 12.75
    2930-75 Nestor Rosenblatt Sales Representative 14.25
     
    Watts A Loan
  93. Close the form
  94. Click the Customers label and create the following records:
     
    Account # Full Name Email Address Phone Number
    937-497 Joan Fairbanks fairbie1288@hotmail.com (301) 937-5888
    293-759 Ernie Lipps ernie.rowdie@comcast.net (703) 506-0000
    502-850 Christopher Owens owenchris@yahoo.com (202) 529-6100
    520-840 Ann Rowdy rowdiant@msn.com (301) 855-2090
    602-475 Sarah Thompson lolitta448@yahoo.com (301) 870-7454
     
  95. Close the form
  96. Click the Loan Allocations label and create the following records:
     
    Date Allocated Loan # Prepared By Prepared For Principal Rate (%) Prd (Months)
    08/18/06 52-9739-5 2497-94 937-497 6500 16.25  
    10/26/2006 20-5804-8 7973-45 602-475 3260    
    02/06/07 77-3907-2 2497-94 502-850 25605 12.50 60
    03/20/07 92-7495-4 2930-75 293-759 14800   48
     
    Watts A Loan
  97. Close the form
  98. Click the Loan Payments label and create the following records:
     
    Pmt # Pmt Date Received By Payment For Pmt Amt Balance
    1001 10/25/06 2497-94 52-9739-5 268.58 9400.17
    1002 11/30/06 2930-75 52-9739-5 268.58 9131.59
    1003 12/24/2006 7973-45 20-5804-8 114.33 4001.42
    1004 12/28/06 2497-94 52-9739-5 268.58 8863.01
    1005 01/26/07 2497-94 20-5804-8 114.33 3887.09
    1006 01/31/07 2930-75 52-9739-5 268.58 8594.43
    1007 02/20/07 2497-94 20-5804-8 114.33 3772.76
    1008 03/02/07 2930-75 52-9739-5 268.58 8325.85
    1009 03/25/2007 2930-75 20-5804-8 114.33 3658.43
    1010 04/25/07 7973-45 92-7495-4 416.25 19563.75
    1011 04/28/07 2497-94 77-3907-2 693.47 40914.66
    1012 04/28/07 7973-45 20-5804-8 114.33 3544.10
    1013 05/01/07 7973-45 52-9739-5 268.58 8057.27
    1014 05/26/07 2497-94 77-3907-2 693.47 40221.19
     
  99. Close the forms and return to your programming environment
 

Home Copyright © 2008-2012 FunctionX