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).

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

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

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

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

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

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:
     

    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:
     

    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:
     

    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:
     

    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:
     

    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

     
    
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

     
    
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