Home

Database Example Application:
Yugo National Bank

 

Introduction

The Yugo National Bank is a fictitious company that acts as a bank. It creates bank accounts for customers, which is done by the employees of the company. The customers can then deposit or withdraw money.

This is a Microsoft SQL Server database application created using Microsoft Visual Basic 2008. One of the main features of this application is that it explores various features of Transact-SQL views, including the ability to view records and/or to perform data entry.

Besides the views, this application also explores the fundamentals of stored procedures, including how to create, execute, and use one in a graphical application.

 

Practical LearningPractical Learning: Introducing Views

  1. Start Microsoft Visual Basic and create a new Windows Application named YugoNationalBank1
  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 strAction As String
    
            Dim Command As SqlCommand = Nothing
    
            Using Connect As SqlConnection = _
                New SqlConnection("Data Source=(local); " & _
                  "Integrated Security='SSPI';")
    
                strAction = "IF EXISTS ( " & _
                     "SELECT name " & _
                     "FROM sys.databases " & _
                     "WHERE name = N'YugoNationalBank1') " & _
                     "DROP DATABASE YugoNationalBank1; " & _
                     "CREATE DATABASE YugoNationalBank1"
    
                Command = New SqlCommand(strAction, Connect)
    
                Connect.Open()
                Command.ExecuteNonQuery()
    
                MsgBox("A database named YugoNationalBank1 " & _
                  "has been created.")
            End Using
    
            Using Connect As SqlConnection = _
            New SqlConnection("Data Source=(local); " & _
                  "Database='YugoNationalBank1'; " & _
                  "Integrated Security='SSPI';")
    
                strAction = "CREATE TABLE dbo.AccountTypes( " & _
                     "AccountTypeID int Identity(1,1) NOT NULL, " & _
                     "AccountType nvarchar(40) NOT NULL, " & _
                     "Notes ntext NULL, " & _
                     "CONSTRAINT PK_AccountTypes PRIMARY " & _
                     "    KEY (AccountTypeID));"
                Command = New SqlCommand(strAction, Connect)
    
                Connect.Open()
                Command.ExecuteNonQuery()
                MsgBox("A table named AccountTypes " & _
                  "has been added to the database.")
            End Using
    
            Using Connect As SqlConnection = _
            New SqlConnection("Data Source=(local); " & _
                  "Database='YugoNationalBank1'; " & _
                  "Integrated Security='SSPI';")
    
                strAction = "CREATE TABLE dbo.Employees( " & _
                     "EmployeeID int identity(1,1) NOT NULL, " & _
                     "EmployeeNumber char(6), " & _
                     "FirstName nvarchar(32), " & _
                     "LastName nvarchar(32) NOT NULL, " & _
                     "Title nvarchar(50), " & _
                     "CanCreateNewAccount bit, " & _
                     "HourlySalary nvarchar(50), " & _
                     "Username nvarchar(20), " & _
                     "Password nvarchar(20), " & _
                     "EmailAddress nvarchar(100), " & _
                     "Notes ntext, " & _
                     "CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID));"
                Command = New SqlCommand(strAction, Connect)
    
                Connect.Open()
                Command.ExecuteNonQuery()
                MsgBox("A table named Employees has " & _
                  "been added to the database.")
            End Using
    
            Using Connect As SqlConnection = _
            New SqlConnection("Data Source=(local); " & _
                  "Database='YugoNationalBank1'; " & _
                  "Integrated Security='SSPI';")
    
                strAction = "CREATE TABLE dbo.Customers( " & _
                     "CustomerID int Identity(1,1) NOT NULL, " & _
                     "EmployeeID int Constraint FK_Employee " & _
                     "    References Employees(EmployeeID), " & _
                     "DateCreated nvarchar(50), " & _
                     "AccountTypeID int Constraint FK_TypeOfAccount " & _
                     "   References AccountTypes(AccountTypeID), " & _
                     "AccountNumber nvarchar(12), " & _
                     "CustomerName nvarchar(50) NOT NULL, " & _
                     "Address nvarchar(100), " & _
                     "City nvarchar(50), " & _
                     "State nvarchar(50), " & _
                     "ZIPCode nvarchar(50), " & _
                     "AccountStatus nvarchar(50), " & _
                     "Username nvarchar(20), " & _
                     "Password nvarchar(20), " & _
                     "EmailAddress nvarchar(100), " & _
                     "Notes ntext, " & _
                     "CONSTRAINT PK_Customers PRIMARY KEY (CustomerID));"
                Command = New SqlCommand(strAction, Connect)
    
                Connect.Open()
                Command.ExecuteNonQuery()
                MsgBox("A table named Customers has " & _
                  "been added to the database.")
            End Using
    
            Using Connect As SqlConnection = _
            New SqlConnection("Data Source=(local); " & _
                  "Database='YugoNationalBank1'; " & _
                  "Integrated Security='SSPI';")
    
                strAction = "CREATE TABLE dbo.AccountsTransactions( " & _
                     "AccountTransactionID int identity(1, 1) NOT NULL, " & _
                     "EmployeeID int Constraint FK_Clerk " & _
                     "    References Employees(EmployeeID), " & _
                     "CustomerID int Constraint FK_Depositor " & _
                     "    References Customers(CustomerID) NOT NULL, " & _
                     "TransactionDate nvarchar(50), " & _
                     "TransactionType nvarchar(50), " & _
                     "CurrencyType nvarchar(50), " & _
                     "DepositAmount nvarchar(50), " & _
                     "WithdrawalAmount nvarchar(50), " & _
                     "ChargeAmount nvarchar(50), " & _
                     "ChargeReason nvarchar(50), " & _
                     "Balance money, " & _
                     "Notes ntext, " & _
                     "CONSTRAINT PK_AccountTransactions PRIMARY KEY " & _
                     "    (AccountTransactionID));"
                Command = New SqlCommand(strAction, Connect)
    
                Connect.Open()
                Command.ExecuteNonQuery()
                MsgBox("A table named AccountTransactions " & _
                  "has been added to the database.")
            End Using
    
            Using Connect As SqlConnection = _
                    New SqlConnection("Data Source=(local);" & _
                        "Database='YugoNationalBank1';" & _
                        "Integrated Security=SSPI;")
    
                Dim strTimesheets As String = "CREATE TABLE dbo.Timesheets ( " & _
                    "TimesheetID int identity(1, 1) NOT NULL, " & _
                    "EmployeeNumber nvarchar(5), " & _
                    "StartDate nvarchar(50), " & _
                    "TimesheetCode nvarchar(15), " & _
                    "Week1Monday nvarchar(6), " & _
                    "Week1Tuesday nvarchar(6), " & _
                    "Week1Wednesday nvarchar(6), " & _
                    "Week1Thursday nvarchar(6), " & _
                    "Week1Friday nvarchar(6), " & _
                    "Week1Saturday nvarchar(6), " & _
                    "Week1Sunday nvarchar(6), " & _
                    "Week2Monday nvarchar(6), " & _
                    "Week2Tuesday nvarchar(6), " & _
                    "Week2Wednesday nvarchar(6), " & _
                    "Week2Thursday nvarchar(6), " & _
                    "Week2Friday nvarchar(6), " & _
                    "Week2Saturday nvarchar(6), " & _
                    "Week2Sunday nvarchar(6), " & _
                    "Notes ntext, " & _
                    "CONSTRAINT PK_Timesheets PRIMARY KEY (TimesheetID));"
    
                Command = New SqlCommand(strTimesheets, Connect)
                Connect.Open()
                Command.ExecuteNonQuery()
                msgbox("A table named Timesheets 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()
        End Sub
    End Class    
  5. Execute the application to create the database
  6. Close the form and return to your programming environment
  7. To create a data source, on the main menu, click Data -> Add New Data Source...
  8. In the first page of the wizard, make sure Database is selected and click Next
  9. In the combo box
    1. If you see a YugoNationalBank1, select it
    2. If you do not have YugoNationalBank1, click New Connection... In the Server combo box, select the server or type (local). In the Select Or Enter A Database Name combo box, select YugoNationalBank1. Click Test Connection. Click OK twice. In the Data Source Configuration Wizard, make sure the new connection is selected and click Next. Change the Connection String to csYugoNationalBank and click Next. Click the check box of Tables. Change the DataSet Name to DsYugoNationalBank
       
      Data Source Configuration Wizard
  10. Click Finish
  11. To create a new form, on the main menu, click Project -> Add Windows Form...
  12. Set the Name to AccountTypes and click Add
  13. From the Data Sources window, drag the AccountTypes node and drop it on the form
  14. Design the form as follows:
     
    Yugo National Bank - AccountTypes 
     
    Control Text Name Other Properties 
    DataGridView   DgvProperties Anchor: Top, Bottom, Left, Right
    Button Close BtnClose Anchor: Bottom, Right 
  15. Double-click the Close button and 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
  16. Access the Central form, add a button and change its properties as follows:
    (Name): BtnAccountTypes
    Text: Account Types...
  17. 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 btnAccountTypes_Click(ByVal sender As System.Object, _
                                      ByVal e As System.EventArgs) _
                                      Handles btnAccountTypes.Click
        Dim Types As AccountTypes = New AccountTypes
        Types.ShowDialog()
    End Sub
  18. Execute the application and open the Account Types form
  19. Create the following records:
     
    AccountType 
    Saving
    Checking
    Certificate of Deposit
     
    Yugo National Bank - Account Types 
  20. Close the forms and return to your programming environment
  21. To create a new form, on the main menu, click Project -> Add Windows Form...
  22. Set the Name to Employees and click Add
  23. In the Data Sources window, click Employees and click the arrow on its right side to drop the combo box
  24. Select Details
  25. Drag the Employees node and drop it on the form
  26. Design the form as follows:
     
    Yugo National Bank - AccountTypes
     
  27. Double-click the Close button and 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
  28. Access the Central form, add a button and change its properties as follows:
    (Name): BtnEmployees
    Text: Employees...
  29. Double-click the Account Types button and implement its event as follows:
     
    Private Sub btnEmployees_Click(ByVal sender As System.Object, _
                                   ByVal e As System.EventArgs) _
                                   Handles btnEmployees.Click
        Dim Staff As Employees = New Employees()
        Staff.ShowDialog()
    End Sub
  30. Execute the application and open the Employees form
  31. Create the following records:
     
    Yugo National Bank - Account Types
     
  32. Close the forms and return to your programming environment
  33. To create a new form, on the main menu, click Project -> Add Windows Form...
  34. Set the Name to Timesheet and click Add
  35. Design the form as follows:
     
    Time Sheet
    Control Text Name Other Properties
    Label Employee #:    
    MaskedTextBox   TxtEmployeeNumber Mask: 00000
    Label . LblEmployeeName  
    Label Start Date:    
    DateTimePicker   DtpStartDate  
    Label End Date:    
    Label . LblEndDate  
    Label Mon    
    Label Tue    
    Label Wed    
    Label Thu    
    Label Fri    
    Label Sat    
    Label Sun    
    Label Week 1:    
    TextBox 0.00 TxtWeek1Monday TextAlign: Right
    TextBox 0.00 TxtWeek1Tuesday TextAlign: Right
    TextBox 0.00 TxtWeek1Wednesday TextAlign: Right
    TextBox 0.00 TxtWeek1Thursday TextAlign: Right
    TextBox 0.00 TxtWeek1Friday TextAlign: Right
    TextBox 0.00 TxtWeek1Saturday TextAlign: Right
    TextBox 0.00 TxtWeek1Sunday TextAlign: Right
    Label Week 2:    
    TextBox 0.00 TxtWeek2Monday TextAlign: Right
    TextBox 0.00 TxtWeek2Tuesday TextAlign: Right
    TextBox 0.00 TxtWeek2Wednesday TextAlign: Right
    TextBox 0.00 TxtWeek2Thursday TextAlign: Right
    TextBox 0.00 TxtWeek2Friday TextAlign: Right
    TextBox 0.00 TxtWeek2Saturday TextAlign: Right
    TextBox 0.00 TxtWeek2Sunday TextAlign: Right
    Label Notes    
    TextBox   TxtNotes Multiline: true
    Button Submit BtnSubmit  
    Button Reset BtnReset  
    Button Close BtnClose  
  36. Right-click the form and click View Code
  37. In the Class Name combo box, select (TimeSheet Events)
  38. In the Method Name combo box, select Load and make changes as follows:
     
    Imports System.Data.SqlClient
    
    Public Class Timesheet
        Dim EmployeeID As Integer
        Dim IsNewRecord As Boolean
        Dim ValidTimeSheet As Boolean
        Dim StrTimeSheetCode As String
    
        Private Sub Timesheet_Load(ByVal sender As Object, _
                                   ByVal e As System.EventArgs) _
                                   Handles Me.Load
            EmployeeID = 0
            IsNewRecord = True
            ValidTimeSheet = False
            StrTimeSheetCode = ""
        End Sub
    End Class
  39. In the Class Name combo box, select TxtEmployeeNumber
  40. In the Method Name combo box, select Leave and implement the even as follows:
     
    Private Sub TxtEmployeeNumber_Leave(ByVal sender As Object, _
                                            ByVal e As System.EventArgs) _
                                            Handles TxtEmployeeNumber.Leave
            If TxtEmployeeNumber.Text = "" Then
                ValidTimeSheet = False
                Exit Sub
            End If
    
            Dim strSelect As String = "SELECT * FROM Employees " & _
                                  "WHERE EmployeeNumber = '" & _
                                  TxtEmployeeNumber.Text & "';"
    
            Dim Connect As SqlConnection = _
                   New SqlConnection("Data Source=(local); " & _
                                     "Database='YugoNationalBank1';" & _
                                     "Integrated Security=true")
            Dim cmdDatabase As SqlCommand = New SqlCommand(strSelect, Connect)
    
            Dim dsEmployees As DataSet = New DataSet
            Dim sda As SqlDataAdapter = New SqlDataAdapter
    
            sda.SelectCommand = cmdDatabase
            sda.Fill(dsEmployees)
    
            Try
                Dim recEmployee As DataRow = dsEmployees.Tables(0).Rows(0)
    
                If recEmployee.IsNull("EmployeeNumber") Then
                    ValidTimeSheet = False
                    Throw New System.IndexOutOfRangeException("Bad Employee Number!")
                Else
                    ValidTimeSheet = True
                    EmployeeID = CType(recEmployee("EmployeeID"), Integer)
                    Dim strFullName As String = _
                        CStr(recEmployee("FirstName")) & _
                            " " & CStr(recEmployee("LastName"))
                    LblEmployeeName.Text = "Welcome " & strFullName
                End If
    
            Catch Exc As IndexOutOfRangeException
                MsgBox("There is no employee with that number!")
                ValidTimeSheet = False
                LblEmployeeName.Text = ""
                TxtEmployeeNumber.Text = ""
            End Try
    
            DtpStartDate.Value = Date.Today
    
            TxtWeek1Monday.Text = "0.00"
            TxtWeek1Tuesday.Text = "0.00"
            TxtWeek1Wednesday.Text = "0.00"
            TxtWeek1Thursday.Text = "0.00"
            TxtWeek1Friday.Text = "0.00"
            TxtWeek1Saturday.Text = "0.00"
            TxtWeek1Sunday.Text = "0.00"
    
            TxtWeek2Monday.Text = "0.00"
            TxtWeek2Tuesday.Text = "0.00"
            TxtWeek2Wednesday.Text = "0.00"
            TxtWeek2Thursday.Text = "0.00"
            TxtWeek2Friday.Text = "0.00"
            TxtWeek2Saturday.Text = "0.00"
            TxtWeek2Sunday.Text = "0.00"
    
            Connect.Close()
    End Sub
  41. In the Class Name combo box, select BtnReset button
  42. In the Method Name combo box, select Click and implement the event as follows:
     
    Private Sub BtnReset_Click(ByVal sender As Object, _
                               ByVal e As System.EventArgs) _
                               Handles BtnReset.Click
        TxtWeek1Monday.Text = "0.00"
        TxtWeek1Tuesday.Text = "0.00"
        TxtWeek1Wednesday.Text = "0.00"
        TxtWeek1Thursday.Text = "0.00"
        TxtWeek1Friday.Text = "0.00"
        TxtWeek1Saturday.Text = "0.00"
        TxtWeek1Sunday.Text = "0.00"
    
        TxtWeek2Monday.Text = "0.00"
        TxtWeek2Tuesday.Text = "0.00"
        TxtWeek2Wednesday.Text = "0.00"
        TxtWeek2Thursday.Text = "0.00"
        TxtWeek2Friday.Text = "0.00"
        TxtWeek2Saturday.Text = "0.00"
        TxtWeek2Sunday.Text = "0.00"
    
        IsNewRecord = True
    End Sub
  43. In the Class Name combo box, select DtpStartDate
     

    To implement the electronic time, we will use two pieces of information are required: an employee's number and a starting period. After an employee has opened a time sheet:

    1. The employee must first provide an employee number, which we will check in the Employees table. If the employee provides a valid employee number, we can continue with the time sheet. If the employee number is invalid, we will let the user know and we cannot continue with the time sheet
    2. After the employee has provided a valid employee number, we will request the starting period. After entering a (valid) date, we will check the time. If there is a record that holds both the employee number and the start date, this means that the employee had previously worked on a time sheet and we will open that existing time sheet.

    After the the employee or contractor has entered a valid employee number and a start date, we will create a number called a time sheet code, represented in the TimeSheet as the TimeSheetCode column. This number is created as follows:

    0000000000000

    The first 5 digits represent the employee's number. The second 4 digits represent the year of the start date. The next 2 digits represent the month, and the last 2 digits represent the day. This number must be unique so that there would not be a duplicate number throughout the time sheet.

    To make sure the value of the TimeSheetCode is unique for each record, after the employee has provided a valid employee number and a start date, we will create the time sheet code and check if that number exists in the TimeSheet table already:

    • If that number exists already, this means that the employee has previously worked on that time sheet and he or she simply wants to verify or update it. We will then open the time values for that record and let the user view or change it
    • If there is no record with the specified time sheet code, we will conclude that the employee is working on a new time sheet
  44. In the Method Name combo box, select CloseUP and implement the event as follows:
     
    Private Sub DtpStartDate_CloseUp(ByVal sender As Object, _
                                     ByVal e As System.EventArgs) _
                                     Handles DtpStartDate.CloseUp
        LblEndDate.Text = DtpStartDate.Value.AddDays(14).ToString()
    
        If TxtEmployeeNumber.Text.Equals("") Then
            ValidTimeSheet = False
            Exit Sub
        End If
    
        Dim StrMonth As String, StrDay As String
        Dim IntMonth As Integer, IntDay As Integer
        Dim StartDate As DateTime
    
        StartDate = DtpStartDate.Value
        IntMonth = StartDate.Month
        IntDay = StartDate.Day
    
        If IntMonth < 10 Then
            StrMonth = StartDate.Year & "0" & CStr(IntMonth)
        Else
            StrMonth = StartDate.Year & CStr(IntMonth)
        End If
    
        If IntDay < 10 Then
            StrDay = StrMonth & "0" & CStr(IntDay)
        Else
            StrDay = StrMonth & CStr(IntDay)
        End If
    
        strTimeSheetCode = TxtEmployeeNumber.Text & StrDay
    
        Dim ConnectTimeSheet As SqlConnection = Nothing
        Dim StrSQL As String = _
                "SELECT * FROM dbo.Timesheets WHERE TimeSheetCode = '" & _
                                 StrTimeSheetCode & "';"
    
        ConnectTimeSheet = _
                 New SqlConnection("Data Source=(local); " & _
                                   "Database='YugoNationalBank1';" & _
                                   "Integrated Security=true")
        Dim CmdTimeSheet As SqlCommand = _
    	New SqlCommand(StrSQL, ConnectTimeSheet)
    
        Dim dsTimeSheet As DataSet = New DataSet("TimeSheetSet")
        Dim sdaTimeSheet As SqlDataAdapter = New SqlDataAdapter
    
        sdaTimeSheet.SelectCommand = CmdTimeSheet
        sdaTimeSheet.Fill(dsTimeSheet)
    
        ConnectTimeSheet.Close()
    
        Try
            Dim RecordTimeSheet As DataRow = dsTimeSheet.Tables(0).Rows(0)
            StrTimeSheetCode = CStr(RecordTimeSheet("TimeSheetCode"))
    
            If RecordTimeSheet.IsNull("TimeSheetCode") Then
                IsNewRecord = True
                Throw New System.IndexOutOfRangeException( _
                      "No TimeSheet with that number exists!")
            Else
                TxtWeek1Monday.Text = CStr(RecordTimeSheet("Week1Monday"))
                TxtWeek1Tuesday.Text = CStr(RecordTimeSheet("Week1Tuesday"))
                TxtWeek1Wednesday.Text = _
    		CStr(RecordTimeSheet("Week1Wednesday"))
                TxtWeek1Thursday.Text = _
    		CStr(RecordTimeSheet("Week1Thursday"))
                TxtWeek1Friday.Text = CStr(RecordTimeSheet("Week1Friday"))
                TxtWeek1Saturday.Text = _
    		CStr(RecordTimeSheet("Week1Saturday"))
                TxtWeek1Sunday.Text = CStr(RecordTimeSheet("Week1Sunday"))
    
                TxtWeek2Monday.Text = CStr(RecordTimeSheet("Week2Monday"))
                TxtWeek2Tuesday.Text = CStr(RecordTimeSheet("Week2Tuesday"))
                TxtWeek2Wednesday.Text = _
    		CStr(RecordTimeSheet("Week2Wednesday"))
                TxtWeek2Thursday.Text = _
    		CStr(RecordTimeSheet("Week2Thursday"))
                TxtWeek2Friday.Text = CStr(RecordTimeSheet("Week2Friday"))
                TxtWeek2Saturday.Text = _
    		CStr(RecordTimeSheet("Week2Saturday"))
                TxtWeek2Sunday.Text = CStr(RecordTimeSheet("Week2Sunday"))
    
                IsNewRecord = False
            End If
        Catch Exc As IndexOutOfRangeException
            BtnReset_Click(sender, e)
        End Try
    End Sub
  45. Access the Central form, add a button and change its properties as follows:
    (Name): BtnTimesheet
    Text: Employee's Time Sheet...
  46. Double-click the Account Types button and implement its event as follows:
     
    Private Sub BtnTimeSheet_Click(ByVal sender As System.Object, _
                                   ByVal e As System.EventArgs) _
                                   Handles BtnTimeSheet.Click
        Dim Sheet As Timesheet = New Timesheet
        Sheet.ShowDialog()
    End Sub
  47. Save all
 

Practical LearningPractical Learning: Visually Creating a View

  1. In the Server Explorer, expand the YugoNationalBank1 node if necessary.
    Right-click Views and click Add New View
  2. In the Add Table dialog box, click Employees, click Add, and click Close
  3. In the Diagram section of the view, click the check boxes of EmployeeID and EmployeeNumber
  4. In the Criteria section, click the empty box under EmployeeNumber and type LastName + ', ' + FirstName
  5. Set its Alias to EmployeeName
  6. In the Diagram section, click the check box of CanCreateNewAccount
     
    View
  7. Close the view
  8. When asked whether you want to save it, click Yes
  9. In the Choose Name dialog box, set the name to EmployeeIdentification and click OK
  10. In the Data Sources window, right-click DsYugoNationalBank and click Configure DataSet With Wizard...
  11. Click the check box of Views
     
    Data Source Configuration Wizard
  12. Click Finish
  13. To create a new form, on the main menu, click Project -> Add Windows Form...
  14. Set the Name to Customers and click Add
  15. In the Data Sources window, click Customers and click the arrow on its right side to drop the combo box
  16. Select Details
  17. Drag the Customers node and drop it on the form
  18. Under the form, click the objects and, using the Properties window, change their names as follows:
     
    Object Name
    customersBindingSource BsCustomers
    customersTableAdapter TaCustomers
    customersBindingNavigator BnCustomers
  19. Once again, from the Data Sources window, drag EmployeeIdentification and drop it on the form
  20. While the data grid view is still selected, press Delete to remove it
  21. Under the form, click the objects and, using the Properties window, change their names as follows:
     
    Object Name Filter 
    employeeIdentificationBindingSource BsAccountManagers CanCreateNewAccount = True
    employeeIdentificationTableAdapter TaAccountManagers  
  22. Once again, from the Data Sources window, drag AccountTypes and drop it on the form
  23. While the data grid view is still selected, press Delete to remove it
  24. Under the form, click the objects and, using the Properties window, change their names as follows:
     
    Object Name
    accountTypesBindingSource BsAccountTypes
    accountTypesTableAdapter TaAccountTypes
  25. On the form, click the text box on the right side of Employee ID and press Delete
  26. On the form, click the text box on the right side of Date Created and press Delete
  27. On the form, click the text box on the right side of Account Type ID and press Delete
  28. On the form, click the text box on the right side of Account Number and press Delete
  29. On the form, click the text box on the right side of Account Status and press Delete
  30. Design the form as follows:
     
    Yugo National Bank - Customers Form Design
    New Control Text Name Other Properties 
    ComboBox   CbxEmployeeID DropDownStyle: DropDownList
    DataSource: BsAccountManagers
    DisplayMember: EmployeeName
    ValueMember: EmployeeID
    (DataBindings) -> Selected Value: bsCustomers - EmployeeID
    ComboBox    CbxAccountTypeID  DropDownStyle: DropDownList
    DataSource: bsAccountTypes
    DisplayMember: AccountType
    ValueMember: AccountTypeID
    (DataBindings) -> Selected Value: bsCustomers - AccountTypeID
    MaskedTextBox   TxtAccountNumber Mask: 00-000000-00
    (DataBindings) -> Text: bsCustomers - AccountNumber
    ComboBox   CbxAccountStatus DropDownStyle: DropDownList
    (DataBindings) -> Text: bsCustomers - DateCreated
    Items:
    Active
    Closed
    suspended
    Button Close BtnClose Anchor: Bottom, Right 
     
     
  31. Double-click the Close button and 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
  32. Access the Central form, add a button and change its properties as follows:
    (Name): BtnCustomers
    Text: Customers...
  33. Double-click the Account Types button and implement its event as follows:
     
    Private Sub btnCustomers_Click(ByVal sender As Object, _
                                   ByVal e As System.EventArgs) _
                                   Handles btnCustomers.Click
        Dim Clients As Customers = New Customers
        Clients.ShowDialog()
    End Sub
  34. Execute the application and open the Employees form
  35. Create the following records:
  36. Close the forms and return to your programming environment
  37. To create a new form, on the main menu, click Project -> Add Windows Form...
  38. Set the Name to NewDeposit and click Add
  39. Design the form as follows:
     
    Yugo National Bank - Deposits Form Design
    Control Text Name Other Properties 
    Label Transaction Date:    
    DateTimePicker   DtpTransactionDate  
    Label Processed By:    
    MaskedTextBox   TxtEmployeeNumber Mask: 00000
    TextBox   TxtEmployeeName  
    Label Processed For:    
    MaskedTextBox   TxtAccountNumber Mask: 00-000000-00
    TextBox   TxtCustomerName  
    Label Currency Type:    
    ComboBox   CbxCurrencyTypes DropDownStyle: DropDownList
    Items:
    Cash
    Check
    Money Order
    Label Amount Deposited:    
    TextBox   TxtAmount TextAlign: Right
    Label  Notes    
    TextBox    TxtNotes Multiline: True
    ScrollBars: Vertical
    Button Submit BtnSubmit  
    Button Close BtnClose  
  40. Right-click the form and click View Code
  41. In the Class Name combo box, select (New Deposit Events)
  42. In the Method Name combo box, select Load and make the following changes:
     
    Imports System.Data.SqlClient
    
    Public Class NewDeposit
        Dim EmployeeID As Integer
        Dim CustomerID As Integer
    
        Private Sub NewDeposit_Load(ByVal sender As Object, _
                                    ByVal e As System.EventArgs) _
                                    Handles Me.Load
            EmployeeID = 0
            CustomerID = 0
        End Sub
    End Class
  43. In the Class Name combo box, select TxtEmployeeNumber
  44. In the Method Name combo box, select Leave and implement the even as follows:
     
    Private Sub TxtEmployeeNumber_Leave(ByVal sender As Object, _
                                        ByVal e As System.EventArgs) _
                                        Handles TxtEmployeeNumber.Leave
        If TxtEmployeeNumber.Text.Length = 0 Then
            MsgBox("You must specify the employee number " & _
                   "of the clerk who is processing the deposit.")
            Exit Sub
        Else
            Using Connect As SqlConnection = _
                   New SqlConnection("Data Source=(local);" & _
                                     "Database='YugoNationalBank1';" & _
                                     "Integrated Security=SSPI;")
    
                Dim strYNB As String = _
    		     "SELECT EmployeeID, FirstName, LastName " & _
                         "FROM Employees WHERE EmployeeNumber = '" & _
                         TxtEmployeeNumber.Text & "';"
                Dim Command As SqlCommand = New SqlCommand(strYNB, Connect)
    
                Connect.Open()
    
                Dim rdrEmployees As SqlDataReader = Command.ExecuteReader()
    
                While rdrEmployees.Read()
                    EmployeeID = CInt(rdrEmployees.GetSqlInt32(0))
                    TxtEmployeeName.Text = rdrEmployees.GetString(1) & " " & _
                                           rdrEmployees.GetString(2)
                End While
    
                If EmployeeID = 0 Then
                    MsgBox("The employee number you entered " & _
                           "is not recognized in our database.")
                    TxtEmployeeNumber.Text = ""
                End If
            End Using
        End If
    End Sub
  45. In the Class Name combo box, select TxtAccountNumber
  46. In the Method Name combo box, select Leave and implement the even as follows:
     
    Private Sub TxtAccountNumber_Leave(ByVal sender As Object, _
                                       ByVal e As System.EventArgs) _
                                       Handles TxtAccountNumber.Leave
        If TxtAccountNumber.Text.Length = 0 Then
            MsgBox("You must specify the account number " & _
                   "of the customer whose deposit you are entering.")
            Exit Sub
        Else
            Using Connect As SqlConnection = _
                    New SqlConnection("Data Source=(local);" & _
                        "Database='YugoNationalBank1';" & _
                        "Integrated Security=SSPI;")
    
                Dim strYNB As String = _
    		      "SELECT CustomerID, CustomerName FROM " & _
                          "Customers WHERE AccountNumber = '" & _
                          TxtAccountNumber.Text & "';"
                Dim Command As SqlCommand = _
                	 New SqlCommand(strYNB, Connect)
                Dim sdaYNB As SqlDataAdapter = New SqlDataAdapter
    
                sdaYNB.SelectCommand = Command
                Dim dsCustomers As DataSet = New DataSet("CustomersSet")
                sdaYNB.Fill(dsCustomers)
    
                Connect.Open()
    
                For Each Record As DataRow In dsCustomers.Tables(0).Rows
                    CustomerID = Record("CustomerID")
                    TxtCustomerName.Text = Record("CustomerName")
                    Exit For
                Next
    
                If CustomerID = 0 Then
                    MsgBox("The account number you entered " & _
                           "is not recognized in our database.")
                    TxtAccountNumber.Text = ""
                End If
            End Using
        End If
    End Sub
  47. In the Class Name combo box, select BtnSubmit
  48. 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 DateTransaction As DateTime = DateTime.Today
        Dim StrCurrencyType As String = "Unknown"
        Dim Amount As Double = 0.0
    
        If EmployeeID = 0 Then
            MsgBox("You must specify the employee number " & _
                   "of the clerk who is processing the deposit.")
            Exit Sub
        End If
    
        If CustomerID = 0 Then
            MsgBox("You must enter an account number " & _
                   "for the new customer.")
            Exit Sub
        End If
    
        StrCurrencyType = CbxCurrencyTypes.Text
    
        Try
            Amount = CDbl(TxtAmount.Text)
        Catch ex As Exception
            MsgBox("Invalid Amount.")
        End Try
    
        Using Connect As SqlConnection = _
    	      New SqlConnection("Data Source=(local);" & _
                                    "Database='YugoNationalBank1';" & _
             			"Integrated Security=SSPI;")
    
            Dim StrSQL As String = _
    	            "INSERT INTO AccountsTransactions(" & _
                        "EmployeeID, CustomerID, " & _
                        "TransactionDate, TransactionType, " & _
                        "CurrencyType, DepositAmount, Notes) " & _
                        "VALUES('" & CStr(EmployeeID) + "', '" & _
                        CStr(CustomerID) + "', '" & _
                        DtpTransactionDate.Value.ToString("d") & _
            	    "', 'Deposit', '" & CbxCurrencyTypes.Text & _
                  	    "', '" & CStr(Amount) & "', '" & TxtNotes.Text & "');"
    
            Dim cmdEmployees As SqlCommand = _
    	          New SqlCommand(StrSQL, Connect)
    
            Connect.Open()
            cmdEmployees.ExecuteNonQuery()
    
            DtpTransactionDate.Value = DateTime.Today
            TxtEmployeeNumber.Text = ""
            TxtEmployeeName.Text = ""
            TxtAccountNumber.Text = ""
            TxtCustomerName.Text = ""
            CbxCurrencyTypes.SelectedIndex = 0
            TxtAmount.Text = "0.00"
            TxtNotes.Text = ""
        End Using
    End Sub
  49. In the Class Name combo box, select BtnClose
  50. In the Method Name combo box, select Click and implement the even as follows:
     
    Private Sub BtnClose_Click(ByVal sender As System.Object, _
                               ByVal e As System.EventArgs) _
                               Handles BtnClose.Click
        Close()
    End Sub
  51. Access the Central form, add a button and change its properties as follows:
    (Name): BtnNewDeposit
    Text: New Deposit...
  52. Double-click the Account Types button and implement its event as follows:
     
    Private Sub BtnNewDeposit_Click(ByVal sender As System.Object, _
                                    ByVal e As System.EventArgs) _
                                    Handles BtnNewDeposit.Click
        Dim Deposit As NewDeposit = New NewDeposit
        Deposit.ShowDialog()
    End Sub
  53. To create a new form, on the main menu, click Project -> Add Windows Form...
  54. Set the Name to NewWithdrawal and click Add
  55. Design the form as follows:
     
    Yugo National Bank - New Withdrawal Form Design
    Control Text Name Other Properties 
    Label Transaction Date:    
    DateTimePicker   DtpTransactionDate  
    Label Processed By:    
    MaskedTextBox   TxtEmployeeNumber Mask: 00000
    TextBox   TxtEmployeeName  
    Label Processed For:    
    MaskedTextBox   TxtAccountNumber Mask: 00-000000-00
    TextBox   TxtCustomerName  
    Label Currency Type:    
    ComboBox   CbxCurrencyTypes DropDownStyle: DropDownList
    Items:
    Cash
    Check
    Money Order
    Label Amount Withdrawn:    
    TextBox   TxtAmount TextAlign: Right
    Label  Notes    
    TextBox    TxtNotes Multiline: True
    ScrollBars: Vertical
    Button Submit BtnSubmit  
    Button Close BtnClose  
  56. Right-click the form and click View Code
  57. In the Class Name combo box, select (New Deposit Events)
  58. In the Method Name combo box, select Load and make the following changes:
     
    Imports System.Data.SqlClient
    
    Public Class NewWithdrawal
        Dim EmployeeID As Integer
        Dim CustomerID As Integer
    
        Private Sub NewWithdrawal_Load(ByVal sender As Object, _
                                       ByVal e As System.EventArgs) _
                                       Handles Me.Load
            EmployeeID = 0
            CustomerID = 0
        End Sub
    End Class
  59. In the Class Name combo box, select Txt EmployeeNumber
  60. In the Method Name combo box, select Leave and implement the even as follows:
     
    Private Sub TxtEmployeeNumber_Leave(ByVal sender As Object, _
                                        ByVal e As System.EventArgs) _
                                        Handles TxtEmployeeNumber.Leave
        If TxtEmployeeNumber.Text.Length = 0 Then
             MsgBox("You must specify the employee number " & _
                    "of the clerk who is processing the transaction.")
             Exit Sub
        Else
            Using Connect As SqlConnection = _
                  New SqlConnection("Data Source=(local);" & _
                        "Database='YugoNationalBank1';" & _
                        "Integrated Security=SSPI;")
    
                Dim strYNB As String = _
    		"SELECT EmployeeID, FirstName, LastName " & _
                    "FROM Employees WHERE EmployeeNumber = '" & _
                    TxtEmployeeNumber.Text & "';"
                Dim Command As SqlCommand = New SqlCommand(strYNB, Connect)
    
                Connect.Open()
    
                Dim rdrEmployees As SqlDataReader = Command.ExecuteReader()
    
                While rdrEmployees.Read()
                        EmployeeID = rdrEmployees.GetSqlInt32(0)
                        TxtEmployeeName.Text = rdrEmployees.GetString(1) & " " & _
                                               rdrEmployees.GetString(2)
                End While
    
                If EmployeeID = 0 Then
                    MsgBox("The employee number you entered " & _
                           "is not recognized in our database.")
                    TxtEmployeeNumber.Text = ""
                End If
            End Using
        End If
    End Sub
  61. In the Class Name combo box, select Txt AccountNumber
  62. In the Method Name combo box, select Leave and implement the even as follows:
     
    Private Sub TxtAccountNumber_Leave(ByVal sender As Object, _
                                       ByVal e As System.EventArgs) _
                                       Handles TxtAccountNumber.Leave
        If TxtAccountNumber.Text.Length = 0 Then
            MsgBox("You must specify the account number " & _
                   "of the customer whose withdrawal you are processing.")
            Exit Sub
        Else
            Using Connect As SqlConnection = _
                    New SqlConnection("Data Source=(local);" & _
                                      "Database='YugoNationalBank1';" & _
                                      "Integrated Security=SSPI;")
    
                Dim strYNB As String = _
    		       "SELECT CustomerID, CustomerName FROM " & _
                           "Customers WHERE AccountNumber = '" & _
                           TxtAccountNumber.Text & "';"
                Dim Command As SqlCommand = New SqlCommand(strYNB, Connect)
                Dim sdaYNB As SqlDataAdapter = New SqlDataAdapter
    
                sdaYNB.SelectCommand = Command
                Dim dsCustomers As DataSet = New DataSet("CustomersSet")
                sdaYNB.Fill(dsCustomers)
    
                Connect.Open()
    
                For Each RecordCustomer As DataRow In dsCustomers.Tables(0).Rows
                    CustomerID = RecordCustomer("CustomerID")
                    TxtCustomerName.Text = RecordCustomer("CustomerName")
                    Exit For
                Next
    
                If CustomerID = 0 Then
                    MsgBox("The account number you entered " & _
                           "is not recognized in our database.")
                    TxtAccountNumber.Text = ""
                End If
            End Using
        End If
    End Sub
  63. In the Class Name combo box, select Btn Submit
  64. 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 DateTransaction As DateTime = DateTime.Today
            Dim Amount As Double = 0.0
    
            If CustomerID = 0 Then
                MsgBox("You must enter a valid account number " & _
                  "for the new customer.")
                Exit Sub
            End If
    
            Try
                Amount = CDbl(TxtAmount.Text)
            Catch ex As Exception
                MsgBox("Invalid Amount.")
                Exit Sub
            End Try
    
        Using Connect As SqlConnection = _
    	     New SqlConnection("Data Source=(local);" & _
            		      "Database='YugoNationalBank1';" & _
             		      "Integrated Security=SSPI;")
    
            Dim strCharges As String = _
                 "INSERT INTO AccountsTransactions(" & _
                 "CustomerID, " & _
                 "TransactionDate, TransactionType, " & _
                 "ChargeAmount, ChargeReason, Notes) " & _
                 "VALUES('" & CStr(CustomerID) & "', '" & _
                 DtpTransactionDate.Value.ToString("d") & _
                 "', 'Charge', '" & CStr(Amount) & "', '" & _
                 CbxChargeReasons.Text & "', '" & _
                 TxtNotes.Text & "');"
            Dim cmdCharges As SqlCommand = _
                New SqlCommand(strCharges, Connect)
    
            Connect.Open()
            cmdCharges.ExecuteNonQuery()
    
            DtpTransactionDate.Value = DateTime.Today
            TxtAccountNumber.Text = ""
            TxtCustomerName.Text = ""
            CbxChargeReasons.SelectedIndex = 0
            TxtAmount.Text = "0.00"
            TxtNotes.Text = ""
        End Using
    End Sub
  65. In the Class Name combo box, select Btn Close
  66. In the Method Name combo box, select Click and implement the even as follows:
     
    Private Sub BtnClose_Click(ByVal sender As System.Object, _
                               ByVal e As System.EventArgs) _
                               Handles BtnClose.Click
        Close()
    End Sub
  67. Access the Central form, add a button and change its properties as follows:
    (Name): BtnNewWithdrawal
    Text: New Withdrawal...
  68. Double-click the Account Types button and implement its event as follows:
     
    Private Sub BtnNewWithdrawal_Click(ByVal sender As System.Object, _
                                       ByVal e As System.EventArgs) _
                                       Handles BtnNewWithdrawal.Click
        Dim Withdraw As NewWithdrawal = New NewWithdrawal
        Withdraw.ShowDialog()
    End Sub
  69. To create a new form, on the main menu, click Project -> Add Windows Form...
  70. Set the Name to NewCharge and click Add
  71. Design the form as follows:
     
    Yugo National Bank - New Withdrawal Form Design
    Control Text Name Other Properties 
    Label Transaction Date:    
    DateTimePicker   DtpTransactionDate  
    Label Processed For:    
    MaskedTextBox   TxtAccountNumber Mask: 00-000000-00
    TextBox   TxtCustomerName  
    Label Charge Reason:    
    ComboBox   CbxChargeReason DropDownStyle: DropDownList
    Items:
    Overdraft
    Money Order
    Check Stopping
    Monthly Charge
    Label Amount Charged:    
    TextBox   TxtAmount TextAlign: Right
    Label  Notes    
    TextBox    TxtNotes Multiline: True
    ScrollBars: Vertical
    Button Submit BtnSubmit  
    Button Close BtnClose  
  72. Right-click the form and click View Code
  73. In the Class Name combo box, select (New Deposit Events)
  74. In the Method Name combo box, select Load and make the following changes:
     
    Imports System.Data.SqlClient
    
    Public Class NewCharge
        Dim CustomerID As Double
    
        Private Sub NewCharge_Load(ByVal sender As Object, _
                                   ByVal e As System.EventArgs) _
                                   Handles Me.Load
            CustomerID = 0
        End Sub
    End Class
  75. In the Class Name combo box, select Txt AccountNumber
  76. In the Method Name combo box, select Leave and implement the even as follows:
     
    Private Sub txtAccountNumber_Leave(ByVal sender As Object, _
                                       ByVal e As System.EventArgs) _
                                       Handles txtAccountNumber.Leave
        If txtAccountNumber.Text.Length = 0 Then
            MsgBox("You must specify the account number " & _
                   "of the customer whose withdrawal you are processing.")
            Exit Sub
        Else
            Using Connect As SqlConnection = _
                    New SqlConnection("Data Source=(local);" & _
                        "Database='YugoNationalBank1';" & _
                        "Integrated Security=SSPI;")
    
                Dim strYNB As String = _
    		      "SELECT CustomerID, CustomerName FROM " & _
                          "Customers WHERE AccountNumber = '" & _
                          txtAccountNumber.Text & "';"
                Dim Command As SqlCommand = New SqlCommand(strYNB, Connect)
                Dim sdaYNB As SqlDataAdapter = New SqlDataAdapter
    
                sdaYNB.SelectCommand = Command
                Dim dsCustomers As DataSet = New DataSet("CustomersSet")
                sdaYNB.Fill(dsCustomers)
    
                Connect.Open()
    
    	    For Each RecordCustomer as DataRow in dsCustomers.Tables(0).Rows)
                    CustomerID = RecordCustomer("CustomerID")
                    txtCustomerName.Text = RecordCustomer("CustomerName")
                    Exit For
                Next
    
                If CustomerID = 0 Then
                    MsgBox("The account number you entered " & _
                           "is not recognized in our database.")
                    txtAccountNumber.Text = ""
                End If
            End Using
        End If
    End Sub
  77. In the Class Name combo box, select Btn Submit
  78. 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 DateTransaction As DateTime = DateTime.Today
        Dim Amount As Double = 0.0
    
        If CustomerID = 0 Then
            MsgBox("You must enter a valid account number " & _
                   "for the new customer.")
            Exit Sub
        End If
    
        Try
            Amount = CDbl(TxtAmount.Text)
        Catch ex As Exception
            MsgBox("Invalid Amount.")
            Exit Sub
        End Try
    
        Using Connect As SqlConnection = _
     	    New SqlConnection("Data Source=(local);" & _
            		      "Database='YugoNationalBank1';" & _
             		      "Integrated Security=SSPI;")
    
            Dim strCharges As String = _
                       "INSERT INTO AccountsTransactions(" & _
                       "CustomerID, " & _
                       "TransactionDate, TransactionType, " & _
                       "ChargeAmount, ChargeReason, Notes) " & _
                       "VALUES('" & CustomerID + "', '" & _
                       DtpTransactionDate.Value.ToString("d") & _
                       "', 'Charge', '" & Amount + "', '" & _
                       CbxChargeReasons.Text & "', '" & _
                       TxtNotes.Text & "');"
            Dim cmdCharges As SqlCommand = _
            New SqlCommand(strCharges, Connect)
    
            Connect.Open()
            cmdCharges.ExecuteNonQuery()
    
            DtpTransactionDate.Value = DateTime.Today
            TxtAccountNumber.Text = ""
            TxtCustomerName.Text = ""
            CbxChargeReasons.SelectedIndex = 0
            TxtAmount.Text = "0.00"
            TxtNotes.Text = ""
        End Using
    End Sub
  79. In the Class Name combo box, select BtnClose
  80. In the Method Name combo box, select Click and implement the even as follows:
     
    Private Sub BtnClose_Click(ByVal sender As System.Object, _
                               ByVal e As System.EventArgs) _
                               Handles BtnClose.Click
        Close()
    End Sub
  81. Access the Central form, add a button and change its properties as follows:
    (Name): BtnNewCharge
    Text: New Charge...
  82. Double-click the Account Types button and implement its event as follows:
     
    Private Sub BtnNewCharge_Click(ByVal sender As System.Object, _
                                   ByVal e As System.EventArgs) _
                                   Handles BtnNewCharge.Click
        Dim Charge As NewCharge = New NewCharge
        Charge.ShowDialog()
    End Sub
  83. Execute the application
  84. Open the New Deposit form to create the first three transactions
  85. Then open the New Withdrawal form to create the withdrawal transactions
  86. Create the other transactions
  87. Close the forms and return to your programming environment
  88. To create a new view, in the Server Explorer, under YugoNationalBank1, right-click Views and click Add New View
  89. In the Add Table dialog box, double-click Customers and AccountTypes
  90. Click Close
  91. In the Diagram section, click the check boxes of CustomerID, CustomerName, AccountNumber, AccountType, DateCreated, and AccountStatus
     
    View: Customer Identification
  92.  Close the view
  93. When asked whether you want to save it, click Yes
  94. Set the Name to CustomerIdentification and click OK
  95. In the Data Sources window, right-click dsYugoNationalBank and click Configure DataSet With Wizard...
  96. Click the check box of Views to remove the check mark
  97. Click it again to put the check mark and click Finish
  98. To create a new view, in the Server Explorer, under YugoNationalBank1, right-click Views and click Add New View
  99. In the Add Table dialog box, double-click AccountsTransactions and Customers
  100. Click Close
  101. In the Diagram section, click the check boxes of AccountNumber, TransactionDate, TransactionType, CurrencyType, DepositAmount, WithdrawalAmount, ChargeAmount, ChargeReason, and Balance
     
    Transactions
  102. Close the view
  103. When asked whether you want to save it, click Yes
  104. Set the Name to AccountTransactions and click OK
  105. In the Data Sources window, right-click dsYugoNationalBank and click Configure DataSet With Wizard...
  106. Click the check box of View to remove the check mark
  107. Click it again to put the check mark
     
    Data Source Configuration Wizard
  108. Click Finish
  109. To create a new form, on the main menu, click Project -> Add Windows Form...
  110. Set the Name to AccountTransactions and click Add
  111. From the Data Sources window, drag AccountTransactions and drop it on the form
  112. Under the form, click accountTransactionsBindingNavigator and press Delete
  113. Under the form, click the objects and, using the Properties window, change their names as follows:
     
    Object Name
    accountTransactionsBindingSource BsAccountTransactions
    accountTypesTableAdapter TaAccountTransactions
  114. Design the form as follows:
     
    Yugo National Bank - Account Transactions
    Control Text Name Other Properties 
    Label Account Number:    
    MaskedTextBox   TxtAccountNumber Mask: 00-000000-00
    Button  Locate BtnLocate  
    Label  Customer Name:    
    TextBox   TxtCustomerName  
    Label Account Type:    
    TextBox   TxtAccountType  
    Label Account Status:    
    TextBox   TxtAccountStatus  
    Label  Date Created:    
    DateTimePicker   DtpDateCreated  
    DataGridView   DgvAccountProperties  
    Label  Total Deposits     
    TextBox    TxtTotalDeposits  Text: 0.00
    TextAlign: Right
    Label   Total Charges     
    TextBox     TxtTotalCharges  Text: 0.00
    TextAlign: Right 
    Button Close BtnClose   
    Label   Total Withdrawals    
    TextBox     TxtTotalWithdrawals  Text: 0.00
    TextAlign: Right 
    Label   Balance     
    TextBox     TxtBalance Text: 0.00
    TextAlign: Right 
  115. Right-click the form and click View Code
  116. Just above the Public Class line, import the System.Data.SqlClient namespace
     
    Imports System.Data.SqlClient
    
    Public Class AccountTransactions
    . . . No Change
  117. Locate the Load event and add the following line:
     
    Private Sub AccountTransactions_Load(ByVal sender As System.Object, _
                                         ByVal e As System.EventArgs) _
                                         Handles MyBase.Load
        'TODO: This line of code loads data into 
        ' the 'DsYugoNationalBank.AccountTransactions' table.
        ' You can move, or remove it, as needed.
        Me.TaAccountTransactions.Fill(Me.DsYugoNationalBank.AccountTransactions)
        BsAccountTransactions.Filter = "AccountNumber = '00-000000-00'"
    End Sub
  118. In the Class Name combo box, select BtnLocate
  119. In the Method Name combo box, select Click and implement the event the as follows:
     
    Private Sub BtnLocate_Click(ByVal sender As Object, _
                                ByVal e As System.EventArgs) _
                                Handles BtnLocate.Click
        Dim CustomerID As Integer = 0
    
        If TxtAccountNumber.Text.Length = 0 Then
            MsgBox("You must specify the account number " & _
                   "of the customer whose transactions you want to view.")
            Exit Sub
        Else
            Using Connect As SqlConnection = _
                 New SqlConnection("Data Source=(local);" & _
                  "Database='YugoNationalBank1';" & _
                  "Integrated Security=SSPI;")
    
                Dim StrYNB As String = _
    		   "SELECT * FROM " & _
                       "CustomerIdentification WHERE AccountNumber = '" & _
                       TxtAccountNumber.Text & "';"
                Dim Command As SqlCommand = New SqlCommand(StrYNB, Connect)
                Dim SdaYNB As SqlDataAdapter = New SqlDataAdapter
    
                SdaYNB.SelectCommand = Command
                Dim dsCustomers As DataSet = New DataSet("CustomersSet")
                SdaYNB.Fill(dsCustomers)
    
                Connect.Open()
    
                For Each Record As DataRow In dsCustomers.Tables(0).Rows
                    CustomerID = Record("CustomerID")
    
                    TxtCustomerName.Text = Record("CustomerName")
                    TxtAccountType.Text = Record("AccountType")
                    TxtAccountStatus.Text = Record("AccountStatus")
                    DtpDateCreated.Value = Record("DateCreated")
                Next
    
                BsAccountTransactions.Filter = _
                     "AccountNumber = '" & TxtAccountNumber.Text & "'"
            End Using
        End If
    
        If CustomerID <> 0 Then
            Dim Deposits As Double = 0.0, Withdraws As Double = 0.0
            Dim Charges As Double = 0.0, Balance As Double = 0.0
    
            Using Connect As SqlConnection = _
                 New SqlConnection("Data Source=(local);" & _
                     "Database='YugoNationalBank1';" & _
                     "Integrated Security=SSPI;")
    
                Dim StrYNB As String = _
                      "SELECT SUM(CAST(DepositAmount AS money)), " & _
                      "SUM(CAST(WithdrawalAmount AS money)), " & _
                      "SUM(CAST(ChargeAmount AS money)) FROM " & _
                      "AccountsTransactions WHERE CustomerID = '" & _
                      CStr(CustomerID) & "';"
                Dim Command As SqlCommand = New SqlCommand(StrYNB, Connect)
    
                Connect.Open()
                Dim rdrTransactions As SqlDataReader = _
    			Command.ExecuteReader
    
                While rdrTransactions.Read()
                    Try
                        Deposits = CDbl(rdrTransactions(0))
                    Catch ex As Exception
    
                    End Try
    
                    Try
                        Withdraws = CDbl(rdrTransactions(1))
                    Catch ex As Exception
    
                    End Try
    
                    Try
                        Charges = CDbl(rdrTransactions(2))
                    Catch ex As Exception
    
                    End Try
    
                    TxtTotalDeposits.Text = FormatNumber(Deposits)
                    TxtTotalWithdrawals.Text = FormatNumber(Withdraws)
                    TxtTotalCharges.Text = FormatNumber(Charges)
    
                    Balance = Deposits - (Withdraws + Charges)
                    TxtBalance.Text = FormatNumber(Balance)
                End While
            End Using
        End If
    End Sub
  120. In the Class Name combo box, select BtnClose
  121. In the Method Name combo box, select Click and implement the even as follows:
     
    Private Sub BtnClose_Click(ByVal sender As System.Object, _
                               ByVal e As System.EventArgs) _
                               Handles BtnClose.Click
        Close()
    End Sub
  122. Access the Central form, add a button and change its properties as follows:
    (Name): BtnAccountTransactions
    Text: View an Account's Transactions...
     
    Yugo National Bank
  123. Double-click the Account Types button and implement its event as follows:
     
    Private Sub BtnAccountTransactions_Click(ByVal sender As System.Object, _
                                             ByVal e As System.EventArgs) _
                                     Handles BtnAccountTransactions.Click
        Dim Transactions As AccountTransactions = New AccountTransactions
        Transactions.ShowDialog()
    End Sub
  124. Execute the application
  125. Open the Account's Transactions form, enter an account number and click Locate
     
    Yugo National Bank 
    Yugo National Bank 
  126. Close the forms and return to your programming environment
 

Practical LearningPractical Learning: Programmatically Creating a View

  1. In the Solution Explorer, right-click Central.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:
     
    Private Sub BtnClose_Click(ByVal sender As Object, _
                               ByVal e As System.EventArgs) _
                               Handles BtnClose.Click
        Using Connect As SqlConnection = _
             New SqlConnection("Data Source=(local);" & _
                 "Database='YugoNationalBank1';" & _
                 "Integrated Security=SSPI;")
    
            Dim StrTimesheet As String = _
                  "CREATE VIEW dbo.Timesheet " & _
                  "AS " & _
                  "SELECT EmployeeNumber, StartDate, " & _
                  "		TimesheetCode, Week1Monday, " & _
                  "		Week1Tuesday, Week1Wednesday, " & _
                  "		Week1Thursday, Week1Friday, " & _
                  "		Week1Saturday, Week1Sunday, " & _
                  "		Week2Monday, Week2Tuesday, " & _
                  "		Week2Wednesday, Week2Thursday, " & _
                  "		Week2Friday, Week2Saturday, " & _
                  "		Week2Sunday, Notes " & _
                  "FROM dbo.Timesheets;"
    
            Dim Command As SqlCommand = _
                 New SqlCommand(StrTimesheet, Connect)
            Connect.Open()
            Command.ExecuteNonQuery()
            MsgBox("A view named Timesheet has been created.")
        End Using
    
        Close()
    End Sub
  4. Execute the application and click the Close button
  5. Change the code of the Close event of the Central form as follows:
     
    Private Sub BtnClose_Click(ByVal sender As Object, _
                               ByVal e As System.EventArgs) _
                               Handles BtnClose.Click
        End
    End Sub
  6. Save all

Practical LearningPractical Learning: Performing Data Entry Using a View

  1. In the Solution Explorer, right-click Timesheet.vb and click View Code
  2. In the Class Name combo box, select BtnSubmit
  3. In the Method Name combo box, select Click and implement the event as follows:
     
    Private Sub BtnSubmit_Click(ByVal sender As Object, _
                                ByVal e As System.EventArgs) _
                                Handles BtnSubmit.Click
        Dim StrTimeSheet As String = ""
    
        ' If this is new record, then create a new time sheet
        If IsNewRecord = True Then
                strTimeSheet = "INSERT INTO dbo.Timesheet " & _
                               "VALUES('" & _
                               TxtEmployeeNumber.Text & "', '" & _
                               DtpStartDate.Value.ToString("MM/dd/yyyy") & "', '" & _
                               StrTimeSheetCode + "', '" & _
                               TxtWeek1Monday.Text & "', '" & _
                               TxtWeek1Tuesday.Text & "', '" & _
                               TxtWeek1Wednesday.Text & "', '" & _
                               TxtWeek1Thursday.Text & "', '" & _
                               TxtWeek1Friday.Text & "', '" & _
                               TxtWeek1Saturday.Text & "', '" & _
                               TxtWeek1Sunday.Text & "', '" & _
                               TxtWeek2Monday.Text & "', '" & _
                               TxtWeek2Tuesday.Text & "', '" & _
                               TxtWeek2Wednesday.Text & "', '" & _
                               TxtWeek2Thursday.Text & "', '" & _
                               TxtWeek2Friday.Text & "', '" & _
                               TxtWeek2Saturday.Text & "', '" & _
                               TxtWeek2Sunday.Text & "', '" & _
                               TxtNotes.Text & "');"
        End If
    
        ' If this is an existing record, then, only update it
        If IsNewRecord = False Then
                StrTimeSheet = "UPDATE dbo.Timesheets SET Week1Monday = '" & _
                               TxtWeek1Monday.Text & "', Week1Tuesday = '" & _
                               TxtWeek1Tuesday.Text & "', Week1Wednesday = '" & _
                               TxtWeek1Wednesday.Text & "', Week1Thursday = '" & _
                               TxtWeek1Thursday.Text & "', Week1Friday = '" & _
                               TxtWeek1Friday.Text & "', Week1Saturday = '" & _
                               TxtWeek1Saturday.Text & "', Week1Sunday = '" & _
                               TxtWeek1Sunday.Text & "', Week2Monday = '" & _
                               TxtWeek2Monday.Text & "', Week2Tuesday = '" & _
                               TxtWeek2Tuesday.Text & "', Week2Wednesday = '" & _
                               TxtWeek2Wednesday.Text & "', Week2Thursday = '" & _
                               TxtWeek2Thursday.Text & "', Week2Friday = '" & _
                               TxtWeek2Friday.Text & "', Week2Saturday = '" & _
                               TxtWeek2Saturday.Text & "', Week2Sunday = '" & _
                               TxtWeek2Sunday.Text & "', Notes = '" & TxtNotes.Text & _
                               "' WHERE TimeSheetCode = '" & StrTimeSheetCode & "';"
        End If
    
        If ValidTimeSheet = True Then
            Dim Connect As SqlConnection = _
                    New SqlConnection("Data Source=(local);" & _
                                      "Database='YugoNationalBank1';" & _
                                      "Integrated Security=true")
            Dim cmdTimeSheet As SqlCommand = _
                 New SqlCommand(StrTimeSheet, Connect)
    
            Connect.Open()
            cmdTimeSheet.ExecuteNonQuery()
            Connect.Close()
    
            MsgBox("Your time sheet has been submitted")
    
            ' Reset the timesheet
            TxtEmployeeNumber.Text = ""
            DtpStartDate.Value = DateTime.Today
    
            BtnReset_Click(sender, e)
        Else
    
            MsgBox("The time sheet is not valid" & vbCrLf & _
                   "either you didn't enter a valid employee number, " & _
                   "or you didn't select a valid start date\n" & _
                   "The time sheet will not be saved")
        End If
    End Sub
  4. In the Class Name combo box, select BtnClose
  5. In the Method Name combo box, select Click and implement the even as follows:
     
    Private Sub BtnClose_Click(ByVal sender As System.Object, _
                               ByVal e As System.EventArgs) _
                               Handles BtnClose.Click
        Close()
    End Sub
  6. Execute the application
  7. Open the employees timesheet and create a few entries
     
    Yugo National Bank
    Yugo National Bank
    Yugo National Bank
  8. Close the form and return to your programming environment

Practical LearningPractical Learning: Creating a Stored Procedure

  1. In the Server Explorer, expand the YugoNationalBank1 connection, right-click its Stored Procedures node and click Add New Stored Procedure
  2. Change the text in the Code Editor as follows:
     
    -- =============================================
    -- Author: 	  FunctionX
    -- Creation date: Monday, January 28, 2008
    -- Description:   This stored procedure assigns a 
    --                default password to each employee.
    -- =============================================
    
    CREATE PROCEDURE dbo.AssignDefaultPassword 
    
    AS
    BEGIN
        UPDATE dbo.Employees
        SET Password = 'Password1' FROM dbo.Employees;
    END
  3. To save the stored procedure, on the Standard toolbar, click the Save button

Practical LearningPractical Learning: Executing a Stored Procedure

  1. (You should open the Employees table from the Server Explorer and verify that the Password fields are empty or NULL, then close it).
    While the content of the stored procedure is still displaying in the Code Editor, right-click anywhere in the window and click Execute.
    In the Output window, you should receive various lines of code that indicate success:
     
    Running [dbo].[AssignDefaultPassword].
    
    (8 row(s) affected)
    (0 row(s) returned)
    @RETURN_VALUE = 0
    Finished running [dbo].[AssignDefaultPassword].
  2. (You should open the Employees table from the Server Explorer and verify that the Password fields now have Password1 each).
    Close the stored procedure tab

Practical LearningPractical Learning: Using Expressions and Functions

  1. In the Server Explorer, under the YugoNationalBank1 connection, right-click its Stored Procedures node and click Add New Stored Procedure
  2. Change the text in the Code Editor as follows:
     
    -- =============================================
    -- Author:      FunctionX
    -- Create date: Friday, May 25, 2007
    -- Description: This stored procedure creates a
    -- 		username for each employee.
    -- 		It also assigns an email to the employee.
    -- =============================================
    CREATE PROCEDURE dbo.CreateUsername 
    
    AS
    
    BEGIN
        UPDATE dbo.Employees
    	SET Username = LOWER(LEFT(FirstName, 1) + LEFT(LastName, 5))
    	FROM dbo.Employees;
        UPDATE dbo.Employees
    	SET EmailAddress = LOWER(LEFT(FirstName, 1) + 
    				 LEFT(LastName, 5)) +
    				'@yugonationalbank.com'
        FROM dbo.Employees;
    END
  3. To save the stored procedure, on the Standard toolbar, click the Save button
  4. To execute the stored procedure, in the Server Explorer and under the YugoNationalBank1 database, right-click CreateUsername and click execute
  5. Close the stored procedure tab
 

Home Copyright © 2008-2012 FunctionX