Home

Database Example Application: Watts A Loan

 

Introduction

Watts A Loan is a fictitious company that lends money to customers for various reasons, including cash for personal needs or money to purchase something (furniture, musical instrument, boat, etc). The money can be handed to a customer or the transaction can be handled by a third party, such as a furniture store or a car dealer.

This application implements the scenario of a money lending company. It is a Microsoft SQL Server database created and managed from a Microsoft Visual Basic 2008 graphical application. At the time of its creation, the main purpose of this application was to show how to use database commands, mainly SQL commands, including how to pass parameters to a command and how to use stored procedures that use arguments.

 

Practical LearningPractical Learning: Introducing Parameterized Statements

  1. Start Microsoft Visual Basic and create a new Windows Application named WattsALoan2
  2. In the Solution Explorer, right-click Form1.vb and click Rename
  3. Type Central.vb and press Enter
  4. Double-click the middle of the form and implement the Load event as follows:
     
    Imports System.Data.SqlClient
    
    Public Class Central
        Friend Sub CreateDatabase()
            Dim strWattsALoan As String = ""
    
            Using Connect As SqlConnection = _
    	        New SqlConnection("Data Source=(local); " & _
          				  "Integrated Security='SSPI';")
    
                strWattsALoan = _
                	"IF EXISTS ( " & _
               	"SELECT name " & _
               	"FROM sys.databases " & _
              	"WHERE name = N'WattsALoan1' " & _
               	") " & _
               	"DROP DATABASE WattsALoan1; " & _
               	"CREATE DATABASE WattsALoan1"
    
                Dim Command As SqlCommand = _
                New SqlCommand(strWattsALoan, Connect)
    
                Connect.Open()
                Command.ExecuteNonQuery()
    
                MsgBox("A database named WattsALoan1 " & _
                       "has been created.")
            End Using
    
            Using Connect As SqlConnection = _
    	      New SqlConnection("Data Source=(local); " & _
             			"Database='WattsALoan1'; " & _
             			"Integrated Security='SSPI';")
    
                strWattsALoan = _
    		"CREATE TABLE dbo.Employees( " & _
                 	"EmployeeNumber nchar(10) NOT NULL, " & _
                 	"FirstName nvarchar(20) NULL, " & _
                 	"LastName nvarchar(10) NOT NULL, " & _
                    "FullName AS ((LastName + ', ') + FirstName), " & _
                 	"Title nvarchar(100), " & _
                 	"HourlySalary money, " & _
                 	"Username nvarchar(20), " & _
                 	"Password nvarchar(20), " & _
                 	"CONSTRAINT PK_Employees " & _
                 	"    PRIMARY KEY(EmployeeNumber)); "
    
                Dim Command As SqlCommand = _
    	         New SqlCommand(strWattsALoan, Connect)
    
                Connect.Open()
                Command.ExecuteNonQuery()
                MsgBox("A table named Employees has been " & _
                 	   "created in the WattsALoan database.")
            End Using
    
            Using Connect As SqlConnection = _
                  New SqlConnection("Data Source=(local); " & _
                    	        "Database='WattsALoan1'; " & _
                    		"Integrated Security='SSPI';")
    
                strWattsALoan = _
                    "INSERT INTO dbo.Employees(EmployeeNumber, " & _
                    "FirstName, LastName, Title, HourlySalary) " & _
                 	"VALUES('22740', 'Jeanne', 'Tryler', " & _
                  	"'Accounts Manager', 22.24); " & _
     		_
                    "INSERT INTO dbo.Employees(EmployeeNumber, " & _
                    "FirstName, LastName, Title, HourlySalary) " & _
                    "VALUES('40952', 'Helene', 'Gustman', " & _
                 	"'Accounts Representative', 14.55); " & _
     		_
                    "INSERT INTO dbo.Employees(EmployeeNumber, " & _
                    "FirstName, LastName, Title, HourlySalary) " & _
                 	"VALUES('84615', 'Ernest', 'Thomas', " & _
                 	"'Accounts Representative', 12.75);"
    
                Dim Command As SqlCommand = _
    	         New SqlCommand(strWattsALoan, Connect)
    
                Connect.Open()
                Command.ExecuteNonQuery()
                MsgBox("A few records have been added to the " & _
                 	   "Employees table of the WattsALoan database.")
            End Using
    
            Using Connect As SqlConnection = _
                  New SqlConnection("Data Source=(local); " & _
                    		"Database='WattsALoan1'; " & _
                    		"Integrated Security='SSPI';")
    
                strWattsALoan = _
    		"CREATE TABLE dbo.Customers( " & _
      	 	"AccountNumber nchar(10) NOT NULL, " & _
       		"DateCreated nvarchar(50), " & _
       		"CustomerName nvarchar(50) NOT NULL, " & _
       		"BillingAddress nvarchar(100), " & _
       		"BillingCity nvarchar(50), " & _
       		"BillingState nvarchar(50), " & _
       		"BillingZIPCode nvarchar(10), " & _
       		"EmailAddress nvarchar(100), " & _
       		"CONSTRAINT PK_Customers " & _
       		"    PRIMARY KEY(AccountNumber)); "
    
                Dim Command As SqlCommand = _
    	         New SqlCommand(strWattsALoan, Connect)
    
                Connect.Open()
                Command.ExecuteNonQuery()
                MsgBox("A table named Customers has been " & _
                 	   "added to the WattsALoan database.")
            End Using
    
            Using Connect As SqlConnection = _
                  New SqlConnection("Data Source=(local); " & _
                    	        "Database='WattsALoan1'; " & _
                    		"Integrated Security='SSPI';")
    
                strWattsALoan = _
    		"INSERT INTO Customers(AccountNumber, DateCreated, " & _
      		"CustomerName, BillingAddress, BillingCity, " & _
      		"BillingState, BillingZIPCode, EmailAddress) " & _
      		"VALUES('917394', '2/26/2004', 'Julius Ramse', " & _
      		"'927 Feuler Ave', 'Silver Spring', " & _
      		"'MD', '20904', 'ramses1990@netscape.net'); " & _
     		_
         		"INSERT INTO Customers(AccountNumber, DateCreated, " & _
      		"CustomerName, BillingAddress, BillingCity, " & _
      		"BillingState, BillingZIPCode) " & _
         		"VALUES('862864', '06/22/2006', 'Gertrude Vaillant', " & _
       		"'10055 Larsenic Rd', 'Takoma Park', " & _
       		"'MD', '20910'); " & _
     		_
         		"INSERT INTO Customers(AccountNumber, DateCreated, " & _
      		"CustomerName, BillingAddress, BillingCity, " & _
      		"BillingState, BillingZIPCode, EmailAddress) " & _
      		"VALUES('846864', '12/3/2004', 'James Barrouch', " & _
      		"'4204 Fallon Drive', 'Silver Spring', " & _
      		"'MD', '20906', 'barrouchj@hotmail.com'); " & _
     		_
         		"INSERT INTO Customers(AccountNumber, DateCreated, " & _
      		"CustomerName, BillingAddress, BillingCity, " & _
      		"BillingState, BillingZIPCode) " & _
         		"VALUES('248047', '08/02/2006', 'Christine Rougher', " & _
      		"'825 Manning Street', 'Alexandria', " & _
      		"'VA', '22231'); " & _
    		_
     		"INSERT INTO Customers(AccountNumber, DateCreated, " & _
      		"CustomerName, BillingAddress, BillingCity, " & _
      		"BillingState, BillingZIPCode, EmailAddress) " & _
      		"VALUES('131804', '10/08/2006', 'Patrick Heller', " & _
      		"'2480 Clarington Drive NW', 'Washington', " & _
      		"'DC', '20006', 'hellerp@yahooo.com');"
    
                Dim Command As SqlCommand = _
    	         New SqlCommand(strWattsALoan, Connect)
    
                Connect.Open()
                Command.ExecuteNonQuery()
                MsgBox("A few records have been added to the Customers " & _
                 	   "table of the WattsALoan database.")
            End Using
    
            Using Connect As SqlConnection = _
                  New SqlConnection("Data Source=(local); " & _
                    		"Database='WattsALoan1'; " & _
                    		"Integrated Security='SSPI';")
    
                strWattsALoan = _
    		"CREATE TABLE dbo.LoanAllocations( " & _
      		"LoanNumber int identity(1000, 1) NOT NULL, " & _
      		"DatePrepared nvarchar(50), " & _
        		"EmployeeNumber nchar(10), " & _
        		"AccountNumber nchar(10), " & _
        		"LoanType nvarchar(50), " & _
        		"LoanAmount money NOT NULL, " & _
        		"InterestRate decimal(6,2) NOT NULL, " & _
        		"Periods decimal(6,2) NOT NULL, " & _
        		"InterestAmount money, " & _
        		"FutureValue money, " & _
        		"MonthlyPayment money, " & _
        		"Notes ntext, " & _
        		"CONSTRAINT PK_LoanAllocations " & _
         		"    PRIMARY KEY(LoanNumber)); "
    
                Dim Command As SqlCommand = _
    	         New SqlCommand(strWattsALoan, Connect)
    
                Connect.Open()
                Command.ExecuteNonQuery()
                MsgBox("A table named LoanAllocations has been " & _
                 	   "added to the WattsALoan database.")
            End Using
    
            Using Connect As SqlConnection = _
                  New SqlConnection("Data Source=(local); " & _
                    		"Database='WattsALoan1'; " & _
                    		"Integrated Security='SSPI';")
    
                strWattsALoan = _
      		"CREATE TABLE dbo.Payments( " & _
      		"PaymentID int identity(1000, 1) NOT NULL, " & _
        		"PaymentDate datetime NOT NULL, " & _
        		"EmployeeNumber nchar(10), " & _
        		"LoanNumber int NOT NULL " & _
       		"    CONSTRAINT FK_LoanAllocations " & _
        		"    FOREIGN KEY REFERENCES " & _
        		"    LoanAllocations(LoanNumber), " & _
        		"PaymentAmount money NOT NULL, " & _
        		"Balance money, " & _
        		"Notes ntext, " & _
        		"CONSTRAINT PK_Payments " & _
        		"    PRIMARY KEY(PaymentID));"
    
                Dim Command As SqlCommand = _
    	         New SqlCommand(strWattsALoan, Connect)
    
                Connect.Open()
                Command.ExecuteNonQuery()
                MsgBox("A table named Payments has been " & _
                 	   "added to the WattsALoan database.")
            End Using
        End Sub
    
        Private Sub Central_Load(ByVal sender As System.Object, _
                                 ByVal e As System.EventArgs) _
                                 Handles MyBase.Load
            CreateDatabase()
        End Sub
    End Class
  5. Execute the application
  6. Close the form and return to your programming environment
  7. In the Data Sources window, click Add New Data Source
  8. In the first page of the wizard, make sure Database is selected and click Next
  9. If you see a WattsALoan1 connection in the combo box, select it. Otherwise, use the New Connection... button and the Add Connection dialog box to create a connection to the WattsALoan1 database
  10. Click Next
  11. Change the connection string to CstWattsALoan and click Next
  12. In the list, click the check box of Tables
  13. Change the data set name to DsWattsALoan and click Finish
  14. In the Server Explorer, expand the server.WattsALoan1.dbo connection
  15. Click the + button of Database Diagram
  16. When the message box comes up, read it and click Yes
  17. Right-click Database Diagram and click Add New Diagram...
  18. In the dialog box, double-click each table and, when all tables have been added, click Close
  19. Create the relationships using the common fields
     
    Diagram
  20. Save the diagram as DgmWattsALoan and close it
  21. To create a new form, on the main menu, click Project -> Add Windows Form...
  22. Set the Name to Customers and click Add
  23. In the Data Sources window, drag Customers and drop it on the Customers form
  24. Under the form, click the objects and, using the Properties window, change their names as follows:
     
    Object Name
    customersBindingSource BsCustomers
    customersTableAdapter TaCustomers
    customersBindingNavigator BnCustomers
  25. On the form, click the data grid view control and, in the Properties window, change the following properties:
    (Name): DgvCustomers
    ColumnHeadersHeightSizeMode: EnableResizing
    Anchor: Top, Bottom, Left, Right
  26. Click the ellipsis of the Columns field and make the following changes:
     
    Selected Columns HeaderText Width
    AccountNumber Acnt # 50
    DateCreated Date Created 80
    CustomerName Customer Name 100
    BillingAddress Address 120
    BillingCity City 80
    BillingState State 40
    BillingZIPCode ZIP Code 60
    EmailAddress Email Address  
  27. Click OK
  28. Design the form as follows:
     
    Watts A Loan - Customers
     
    Control Text Name
    Button Close btnClose
  29. Double-click the Close button
  30. Implement its even as follows:
     
    Private Sub BtnClose_Click(ByVal sender As System.Object, _
                               ByVal e As System.EventArgs) _
                               Handles BtnClose.Click
        Close()
    End Sub
  31. Access the Central form, add a button and change its properties as follows:
    (Name): BtnCustomers
    Text: Customers...
  32. Double-click the Account Types button and implement its event as follows:
     
    Private Sub Central_Load(ByVal sender As System.Object, _
                             ByVal e As System.EventArgs) _
                             Handles MyBase.Load
        ' CreateDatabase()
    End Sub
    
    Private Sub btnCustomers_Click(ByVal sender As System.Object, _
                                   ByVal e As System.EventArgs) _
                                   Handles btnCustomers.Click
        Dim Clients As Customers = New Customers
        Clients.ShowDialog()
    End Sub
  33. To create a new form, on the main menu, click Project -> Add Windows Form...
  34. Set the Name to Payments and click Add
  35. In the Data Sources window, drag Payments and drop it on the Payments form
  36. Under the form, click the objects and, using the Properties window, change their names as follows:
     
    Object Name
    customersBindingSource BsPayments
    customersTableAdapter TaPayments
    customersBindingNavigator BnPayments
  37. On the form, click the data grid view control and, in the Properties window, change the following properties:
    (Name): DgvPayments
    ColumnHeadersHeightSizeMode: EnableResizing
    Anchor: Top, Bottom, Left, Right
  38. Click the ellipsis of the Columns field and make the following changes:
     
    Selected Columns HeaderText Width
    PaymentID Pmt ID 45
    PaymentDate Pmt Date 80
    EmployeeNumber Received By 75
    LoanNumber Loan # 60
    PaymentAmount Amount Paid 75
    Balance   60
    Notes    
  39. Click OK
  40. Design the form as follows:
     
    Watts A Loan - Customers 
     
    Control Text Name
    Label  View Payments For (Enter Loan #):   
    TextBox    TxtAccountNumber 
    Button Submit BtnSubmit
    Button Close BtnClose
  41. Double-click the Submit button and implement its event as follows:
     
    Private Sub BtnSubmit_Click(ByVal sender As System.Object, _
                                ByVal e As System.EventArgs) _
                                Handles BtnSubmit.Click
        BsPayments.Filter = "LoanNumber = '" & TxtAccountNumber.Text & "'"
    End Sub
  42. In the Class Name combo box, select BtnClose
  43. In the Method Name combo box, select Click and implement the even as follows:
     
    Private Sub BtnClose_Click(ByVal sender As Object, _
                               ByVal e As System.EventArgs) _
                               Handles BtnClose.Click
        Close()
    End Sub
  44. Access the Central form, add a button and change its properties as follows:
    (Name): BtnPayments
    Text: View Payments...
  45. Double-click the View Payments... button and implement its event as follows:
     
    Private Sub btnPayments_Click(ByVal sender As System.Object, _
                                  ByVal e As System.EventArgs) _
                                  Handles btnPayments.Click
        Dim Pmts As Payments = New Payments
        Pmts.ShowDialog()
    End Sub
  46. To create a new form, on the main menu, click Project -> Add Windows Form...
  47. Set the Name to NewLoanAllocation and click Add
  48. Design the form as follows:
     
    Watts A Loan - Customers
     
    Control Text Name Other Properties 
    Label Date Prepared:    
    DateTimePicker   DtpDatePrepared  
    Label Prepared By   BackColor: Gray
    AutoSize: False
    Label Employee #:    
    MaskedTextBox   TxtEmployeeNumber Mask: 00000
    TextBox   TxtEmployeeName  
    Label Prepared For   BackColor: Gray
    AutoSize: False
    Label Account #:    
    MaskedTextBox   TxtAccountNumber Mask: 000000
    TextBox   TxtCustomerName  
    Label Loan Preparation   BackColor: Gray
    AutoSize: False
    Label Loan Type    
    ComboBox   CbxLoanTypes Items:
    Other
    Car Loan
    Credit Card
    Personal Loan
    Furniture Loan
    Label Loan Amount:    
    TextBox 0.00 TxtPresentValue TextAlign: Right
    Label Interest Rate:    
    TextBox 0.00 TxtInterestRae TextAlign: Right
    Label %    
    Label Number of Months:    
    TextBox 0 TxtMonths TextAlign: Right
    Button Calculate BtnCalculate  
    Label Interest Amt:    
    TextBox 0.00 TxtInterestAmount TextAlign: Right
    Label Future Value:    
    TextBox 0.00 TxtFutureValue TextAlign: Right
    Label Monthly Payment:    
    TextBox 0.00 TxtMonthlyPayment TextAlign: Right
    Label Notes   BackColor: Gray
    AutoSize: False
    TextBox   TxtNotes Multiline: True
    ScrollBars: Vertical
    Button Submit BtnSubmit  
    Button Close BtnClose  
  49. Right-click the form and click View Code
  50. Just above the Public Class line, import the System.Data.SqlClient
     
    Imports System.Data.SqlClient
    
    Public Class NewLoanAllocation
    
    End Class
  51. In the Class Name combo box, select TxtEmployeeNumber
  52. In the Method Name combo box, select Leave and implement the event as follows:
     
    Imports System.Data.SqlClient
    
    Public Class NewLoanAllocation
    
        Private Sub TxtEmployeeNumber_Leave(ByVal sender As Object, _
                                      ByVal e As System.EventArgs) _
                                      Handles TxtEmployeeNumber.Leave
            Using Connect As SqlConnection = _
    		  New SqlConnection("Data Source=(local);" & _
          				    "Database='WattsALoan1';" & _
          			 	    "Integrated Security=SSPI;")
    
                Dim strSelect As String = _
                    "SELECT FullName FROM Employees " & _
                     "WHERE EmployeeNumber = '" & _
                     TxtEmployeeNumber.Text & "';"
    
                Dim Command As SqlCommand = _
    	             New SqlCommand(strSelect, Connect)
                Connect.Open()
                Dim rdrWattsALoan As SqlDataReader = _
    	                Command.ExecuteReader()
    
                While rdrWattsALoan.Read()
                    txtEmployeeName.Text = rdrWattsALoan(0)
                End While
            End Using
        End Sub
    End Class
  53. In the Class Name combo box, select TxtAccountNumber
  54. In the Method Name combo box, select Leave and implement the event as follows:
     
    Private Sub TxtAccountNumber_Leave(ByVal sender As Object, _
                                       ByVal e As System.EventArgs) _
                                       Handles TxtAccountNumber.Leave
        Using Connect As SqlConnection = _
    	 New SqlConnection("Data Source=(local);" & _
         			   "Database='WattsALoan1';" & _
    			   "Integrated Security=SSPI;")
    
            Dim strSelect As String = _
                     "SELECT CustomerName FROM Customers " & _
                     "WHERE AccountNumber = '" & _
                     TxtAccountNumber.Text & "';"
    
            Dim Command As SqlCommand = _
                    New SqlCommand(strSelect, Connect)
            Connect.Open()
            Dim rdrWattsALoan As SqlDataReader = _
                Command.ExecuteReader()
    
            While rdrWattsALoan.Read()
                TxtCustomerName.Text = rdrWattsALoan(0)
            End While
        End Using
    End Sub
  55. In the Class Name combo box, select BtnCalculate
  56. In the Method Name combo box, select Click and implement the event as follows:
     
    Private Sub BtnCalculate_Click(ByVal sender As Object, _
                                   ByVal e As System.EventArgs) _
                                   Handles BtnCalculate.Click
            Dim Principal As Double = 0.0, InterestRate As Double = 0.0
            Dim Periods As Double = 0.0, InterestAmount As Double = 0.0
            Dim FutureValue As Double = 0.0, MonthlyPayment As Double = 0.0
    
            Try
                Principal = CDbl(TxtPresentValue.Text)
            Catch ex As Exception
                MsgBox("Invalid Principal Amount")
            End Try
    
            Try
                InterestRate = CDbl(TxtInterestRate.Text)
            Catch ex As Exception
                MsgBox("Invalid Interest Rate")
            End Try
    
            Try
                Periods = CDbl(TxtMonths.Text)
            Catch ex As Exception
                MsgBox("Invalid Number of Months")
            End Try
    
            InterestAmount = Principal * (InterestRate / 100) * Periods / 12
            FutureValue = Principal + InterestAmount
            MonthlyPayment = FutureValue / Periods
    
            TxtInterestAmount.Text = FormatNumber(InterestAmount)
            TxtFutureValue.Text = FormatNumber(FutureValue)
            TxtMonthlyPayment.Text = FormatNumber(MonthlyPayment)
    End Sub
  57. In the Class Name combo box, select BtnSubmit
  58. In the Method Name combo box, select Click and implement the even as follows:
     
    Private Sub BtnSubmit_Click(ByVal sender As Object, _
                                ByVal e As System.EventArgs) _
                                Handles BtnSubmit.Click
        ' Don't save the record if there is no employee number
        If txtEmployeeName.Text.Length = 0 Then
            MsgBox("You must enter a valid employee number.")
            Exit Sub
        End If
    
        ' Don't save the record if there is no account number
        If TxtCustomerName.Text.Length = 0 Then
            MsgBox("You must enter a valid account number.")
            Exit Sub
        End If
    
        ' Make sure the user has specified the type of loan
        If CbxLoanTypes.Text.Length = 0 Then
            MsgBox("You must specify the type of loan.")
            Exit Sub
        End If
    
        ' Don't save the record if there is principal
        If TxtPresentValue.Text.Length = 0 Then
            MsgBox("You must specify the amount " & _
                   "that is being lent.")
            Exit Sub
        End If
    
        ' Don't save the record if there is principal
        If TxtInterestRate.Text.Length = 0 Then
            MsgBox("You must specify the interest rate of the loan.")
            Exit Sub
        End If
    
        ' Don't save the record if there is principal
        If TxtMonths.Text.Length = 0 Then
            MsgBox("You must specify the number " & _
                   "of months as period of the loan.")
            Exit Sub
        End If
    
        ' Before saving the loan, just in case, perform the calculation
        BtnCalculate_Click(sender, e)
    
        Using Connect As SqlConnection = _
     	    New SqlConnection("Data Source=(local);" & _
            		      "Database='WattsALoan1';" & _
    		              "Integrated Security=SSPI;")
                Dim strInsert As String = _
    		  "INSERT INTO LoanAllocations( " & _
    		  "DatePrepared, EmployeeNumber, AccountNumber, " & _
    		  "LoanType, LoanAmount, InterestRate, Periods, " & _
    		  "InterestAmount, FutureValue, MonthlyPayment, " & _
    		  "Notes) VALUES('" & _
    		  DtpDatePrepared.Value.ToString("d") & "', '" & _
    		  TxtEmployeeNumber.Text & "', '" & _
    		  TxtAccountNumber.Text & "', '" & _
    		  CbxLoanTypes.Text & "', '" & _
    		  TxtPresentValue.Text & "', '" & _
    		  TxtInterestRate.Text & "', '" & _
    		  TxtMonths.Text & "', '" & _
    		  TxtInterestAmount.Text & "', '" & _
    		  TxtFutureValue.Text & "', '" & _
    		  TxtMonthlyPayment.Text & "', '" & _
    		  TxtNotes.Text & "');"
    
            Dim Command As SqlCommand = _
                   New SqlCommand(strInsert, Connect)
            Connect.Open()
            Command.ExecuteNonQuery()
            MsgBox("A new loan has been created.")
        End Using
    
        ' Reset the form
        DtpDatePrepared.Value = DateTime.Today
        TxtEmployeeNumber.Text = ""
        txtEmployeeName.Text = ""
        TxtAccountNumber.Text = ""
        TxtCustomerName.Text = ""
        CbxLoanTypes.SelectedIndex = 0
        TxtPresentValue.Text = "0.00"
        TxtInterestRate.Text = "0.00"
        TxtMonths.Text = "0"
        TxtInterestAmount.Text = "0.00"
        TxtFutureValue.Text = "0.00"
        TxtMonthlyPayment.Text = "0.00"
        TxtNotes.Text = ""
    End Sub
  59. In the Class Name combo box, select BtnClose
  60. In the Method Name combo box, select Click and implement the even as follows:
     
    Private Sub BtnClose_Click(ByVal sender As Object, _
                               ByVal e As System.EventArgs) _
                               Handles BtnClose.Click
        Close()
    End Sub
  61. Access the Central form, add a button and change its properties as follows:
    (Name): BtnNewLoanAllocation
    Text: New Loan Allocation...
  62. Double-click the Loan Allocations button and implement its event as follows:
     
    Private Sub btnNewLoanAllocation_Click(ByVal sender As Object, _
                                           ByVal e As System.EventArgs) _
                                           Handles btnNewLoanAllocation.Click
        Dim Loan As NewLoanAllocation = New NewLoanAllocation
        Loan.ShowDialog()
    End Sub
  63. Execute the application and open the Loan Allocations form
  64. Create a few loans
     
    Watts A Loan
  65. Close the forms and return to your programming environment
 

Practical LearningPractical Learning: Creating a Stored Procedure

  1. In the Solution Explorer, right-click Central.vb and click View Code
  2. Just above the Load event, create the following procedure and call it in the Load event:
     
    Friend Sub CurrentBalanceCalculator()
            Using Connect As SqlConnection = _
         New SqlConnection("Data Source=(local); " & _
             "Database='WattsALoan1'; " & _
             "Integrated Security='SSPI';")
    
                ' Here is how we will evaluate the current balance of an account
                ' Get the amount that was lent to the customer
                ' If the customer had already made at least one payment, 
                ' get the current balance of the customer's account
                ' If the customer has never made a payment (yet),
                ' to specify the balance, subtract the current payment
                ' from the original amount of the loan
                ' If the customer had already made at least one payment,
                ' subtract the current payment from the previous balance
                Dim strWattsALoan As String = _
                      "CREATE PROCEDURE SpecifyCurrentBalance " & _
                      "    @PmtDate datetime, " & _
                      "    @EmplNbr nchar(10), " & _
                      "    @LoanNbr int, " & _
                      "    @PmtAmt money, " & _
                      "    @Comments ntext = '' " & _
                      "AS " & _
                      "BEGIN " & _
                  "    DECLARE @AmountOfLoan money; " & _
     	      _
                  "    SET @AmountOfLoan = (SELECT las.FutureValue " & _
                  "    FROM LoanAllocations las " & _
                  "    WHERE (las.LoanNumber = @LoanNbr)); " & _
     	      _
                  "    DECLARE @CurrentBalance money; " & _
                  "    SET @CurrentBalance = (SELECT MIN(pay.Balance) " & _
                  "    FROM Payments pay " & _
                  "    WHERE (pay.LoanNumber = @LoanNbr)); " & _
     	      _
                  "    IF @CurrentBalance IS NULL " & _
                  "    BEGIN " & _
                  "        INSERT INTO Payments(PaymentDate, EmployeeNumber, " & _
                  "        LoanNumber, PaymentAmount, " & _
                  "        Balance, Notes) " & _
                  "        VALUES(@PmtDate, @EmplNbr, @LoanNbr, @PmtAmt, " & _
                  "       @AmountOfLoan - @PmtAmt, @Comments); " & _
                  "    END " & _
                  "    ELSE " & _
                  "    BEGIN " & _
                  "        INSERT INTO Payments(PaymentDate, EmployeeNumber, " & _
                  "  		LoanNumber, PaymentAmount, " & _
                  "      		Balance, Notes) " & _
                  " 	 VALUES(@PmtDate, @EmplNbr, @LoanNbr, " & _
                 " 	 @PmtAmt, @CurrentBalance - @PmtAmt, @Comments); " & _
                  "    END " & _
                  "END;"
    
                Dim Command As SqlCommand = _
                    New SqlCommand(strWattsALoan, Connect)
    
                Connect.Open()
                Command.ExecuteNonQuery()
                msgbox("A new stored procedure has been created.")
        End Using
    End Sub
    
    Private Sub Central_Load(ByVal sender As System.Object, _
                             ByVal e As System.EventArgs) _
                             Handles MyBase.Load
        ' CreateDatabase()
        CurrentBalanceCalculator()
    End Sub
  3. Execute the application
  4. Close the form and return to your programming environment

Practical LearningPractical Learning: Executing an Argumentative Procedure

  1. To create a new form, on the main menu, click Project -> Add Windows Form...
  2. Set the Name to NewPayment and click Add
  3. Design the form as follows:
     
    Watts A Loan - Payment
     
    Control Text Name Other Properties 
    Label Payment Date:    
    DateTimePicker   DtpPaymentDate  
    Label Received By   BackColor: Gray
    AutoSize: False
    Label Employee #:    
    MaskedTextBox   TxtEmployeeNumber Mask: 00000
    TextBox   TxtEmployeeName  
    Label Payment For   BackColor: Gray
    AutoSize: False
    Label Loan #:    
    TextBox   TxtLoanNumber  
    Label Payment Amount:    
    TextBox 0.00 TxtPaymentAmount  
    Label Notes    
    Button Submit BtnSubmit  
    Button Close BtnClose  
  4. Right-click the form and click View Code
  5. Import the System.Data.SqlClient namespace
     
    Imports System.Data.SqlClient
    
    Public Class NewPayment
    
    End Class
  6. In the Class Name combo box, select TxtEmployeeNumber
  7. In the Method Name combo box, select Leave and implement the event as follows:
     
    Private Sub TxtEmployeeNumber_Leave(ByVal sender As Object, _
                                        ByVal e As System.EventArgs) _
                                        Handles TxtEmployeeNumber.Leave
        Using Connect As SqlConnection = _
    	  New SqlConnection("Data Source=(local);" & _
          			    "Database='WattsALoan1';" & _
     			    "Integrated Security=SSPI;")
    
            Dim strSelect As String = _
    	      "SELECT FullName FROM Employees " & _
           	      "WHERE EmployeeNumber = '" & _
      	      TxtEmployeeNumber.Text & "';"
    
            Dim Command As SqlCommand = _
                    New SqlCommand(strSelect, Connect)
            Connect.Open()
            Dim rdrEmployees As SqlDataReader = Command.ExecuteReader()
    
            While rdrEmployees.Read()
                TxtEmployeeName.Text = rdrEmployees(0)
            End While
        End Using
    End Sub
  8. In the Class Name combo box, select BtnSubmit button
  9. In the Method Name combo box, select Click and implement the even as follows:
     
    Private Sub BtnSubmit_Click(ByVal sender As Object, _
                                    ByVal e As System.EventArgs) _
                                    Handles BtnSubmit.Click
            Dim PaymentAmount As Double = 0.0
            Dim LoanNumber As Integer = 1000
    
            ' Don't save the record if there is no employee number
            If TxtEmployeeName.Text.Length = 0 Then
                MsgBox("You must enter a valid employee number.")
                Exit Sub
            End If
    
            ' Don't save the record if there is no loan number
            If TxtLoanNumber.Text.Length = 0 Then
                MsgBox("You must specify the loan number.")
                Exit Sub
            End If
    
            ' Make sure the user has specified the amount paid
            If TxtPaymentAmount.Text.Length = 0 Then
                MsgBox("You must specify the amount paid.")
                Exit Sub
            End If
    
            Try
                PaymentAmount = CDbl(TxtPaymentAmount.Text)
            Catch Exc As FormatException
                MsgBox("Invalid Payment Amount.")
            End Try
    
            Try
                LoanNumber = CInt(TxtLoanNumber.Text)
            Catch Exc As FormatException
                MsgBox("Invalid Loan Number.")
            End Try
    
            Using Connect As SqlConnection = _
         New SqlConnection("Data Source=(local); " & _
             "Database='WattsALoan1'; " & _
             "Integrated Security='SSPI';")
    
                Dim Command As SqlCommand = _
                    New SqlCommand("dbo.SpecifyCurrentBalance", _
                     Connect)
                Command.CommandType = CommandType.StoredProcedure
    
                Dim PrmWattsALoan As SqlParameter = New SqlParameter
                PrmWattsALoan.ParameterName = "@PmtDate"
                PrmWattsALoan.DbType = DbType.DateTime
                PrmWattsALoan.Value = DtpPaymentDate.Value.ToString("d")
                Command.Parameters.Add(PrmWattsALoan)
    
                PrmWattsALoan = New SqlParameter
                PrmWattsALoan.ParameterName = "@EmplNbr"
                PrmWattsALoan.DbType = DbType.String
                PrmWattsALoan.Value = TxtEmployeeNumber.Text
                Command.Parameters.Add(PrmWattsALoan)
    
                PrmWattsALoan = New SqlParameter
                PrmWattsALoan.ParameterName = "@LoanNbr"
                PrmWattsALoan.DbType = DbType.Int32
                PrmWattsALoan.Value = LoanNumber
                Command.Parameters.Add(PrmWattsALoan)
    
                PrmWattsALoan = New SqlParameter
                PrmWattsALoan.ParameterName = "@PmtAmt"
                PrmWattsALoan.DbType = DbType.Double
                PrmWattsALoan.Value = PaymentAmount
                Command.Parameters.Add(PrmWattsALoan)
    
                PrmWattsALoan = New SqlParameter
                PrmWattsALoan.ParameterName = "@Comments"
                PrmWattsALoan.DbType = DbType.String
                PrmWattsALoan.Value = TxtNotes.Text
                Command.Parameters.Add(PrmWattsALoan)
    
                Connect.Open()
                Command.ExecuteNonQuery()
                MsgBox("A new payment has been made.")
    
                DtpPaymentDate.Value = DateTime.Today
                TxtEmployeeNumber.Text = ""
                TxtEmployeeName.Text = ""
                TxtLoanNumber.Text = ""
                TxtPaymentAmount.Text = "0.00"
                TxtNotes.Text = ""
        End Using
    End Sub
  10. In the Class Name combo box, select BtnClose
  11. In the Method Name combo box, select Click and implement the even as follows:
     
    Private Sub BtnClose_Click(ByVal sender As Object, _
                               ByVal e As System.EventArgs) _
                               Handles BtnClose.Click
        Close()
    End Sub
  12. Access the Central form, add a button and change its properties as follows:
    (Name): btnNewPayment
    Text: New Payment...
  13. Double-click the Loan Allocations button and implement its event as follows:
     
    Private Sub Central_Load(ByVal sender As System.Object, _
                             ByVal e As System.EventArgs) _
                             Handles MyBase.Load
        ' CreateDatabase()
        ' CurrentBalanceCalculator()
    End Sub
    
        . . . No Change
    
    Private Sub BtnNewPayment_Click(ByVal sender As Object, _
                                    ByVal e As System.EventArgs) _
                                    Handles BtnNewPayment.Click
        Dim Payment As NewPayment = New NewPayment
        Payment.ShowDialog()
    End Sub
  14. In the Class Name combo box, select Btn Close
  15. In the Method Name combo box, select Click and implement the even as follows:
     
    Private Sub BtnClose_Click(ByVal sender As Object, _
                               ByVal e As System.EventArgs) _
                               Handles BtnClose.Click
        End
    End Sub
  16. Execute the application and open the New Payment form
  17. Create a few Payments and close the New Payment form
  18. Open the Payments form to see a summary of the payments
  19. Close the forms and return to your programming environment
 

Home Copyright © 2008-2012 FunctionX