Home

Database Example Application: Georgetown Dry Cleaners

 

Introduction

This application is called Georgetown Dry Cleaners. It is a fictitious program used by a company that dry-cleans some clothes for local customers. The application is made of various forms for employees and customers. The Employees form is used to both enter information about the employees and to hold their information. The Customers form holds customers names and telephone numbers.

The pieces of information in the application are stored in as Microsoft SQL Server database.

 

Practical LearningPractical Learning: Creating the Application 

  1. Create a new Windows Application named GeorgetownDryCleaners3
  2. To create a new form, on the main menu, click Project -> Add Windows Form...
  3. Set the Name to Employees and press Enter
  4. Design the form as follows:
     
    Georgetown Cleaning Services: Employees
     
    Control Name Text Additional Properties
    Group Box Group Box   New Employee  
    Label Label   First Name:  
    TextBox Text Box TxtFirstName    
    Label Label   Last Name:  
    TextBox Text Box TxtLastName    
    Label Label   Employee #:  
    MaskedTextBox Text Box TxtEmployeeNumber   Mask: 00-000
    Label Label   Title:  
    TextBox Text Box TxtTitle    
    Label Label   Hourly Salary:  
    TextBox Text Box TxtHourlySalary   TextAlign: Right
    Button Button BtnSubmit Submit  
    DataGridView Data Grid View DgvEmployees    
    Button Button BtnClose Close  
  5. Save the form
  6. To create a new form, on the main menu, click Project -> Add Windows Form...
  7. Set the Name to Customers and press Enter
  8. Design the form as follows:
     
    Georgetown Cleaning Services: Customers
     
    Control Name Text Additional Properties
    Group Box Group Box   New Customer  
    Label Label   Customer Phone:  
    MaskedTextBox Text Box TxtCustomerPhone   Mask: (999) 000-0000
    Label Label   Customer Name:  
    TextBox Text Box TxtCustomerName    
    Button Button BtnSubmit Submit  
    DataGridView Data Grid View dgvCustomers    
    Button Button BtnClose Close  
  9. Save the form
  10. In the Solution Explorer, right-click Form1.vb and click Rename
  11. Type CleaningOrders.vb and press Enter twice
  12. Design the form as follows:
     
    Georgetown Cleaning Services: Cleaning Orders
     
    Control Name Text Additional Properties
    GroupBox GroupBox   Processed By  
    Label Label   Employee #:  
    MaskedTextBox Masked Text Box TxtEmployeeNumber   Mask: 00-000
    Button  Button BtnNewEmployee  New Employee...  
    Label Label   Employee Name:  
    TextBox TextBox TxtEmployeeName    
    GroupBox GroupBox   Processed For  
    Label Label   Customer Phone:  
    MaskedTextBox Masked Text Box TxtCustomerPhone   Mask: (999) 000-0000
    Button  Button BtnNewCustomer  New Cust...  
    Label Label   Customer Name:  
    TextBox TextBox TxtCustomerName    
    GroupBox GroupBox   Order Timing  
    Label Label   Date Left:  
    DateTimePicker DateTimePicker DtpDateLeft    
    Label Label   Time Left:  
    DateTimePicker Date Time Picker DtpTimeLeft   Format: Time
    Label Label   Date Expected:  
    DateTimePicker DateTimePicker DtpDateExpected    
    Label Label   Time Expected:  
    DateTimePicker DateTimePicker DtpTimeExpected   Format: Time
    Label Label   D&ate Picked Up:  
    DateTimePicker DateTimePicker DtpDatePickedUp    
    Label Label   Time Pic&kep Up:  
    DateTimePicker DateTimePicker DtpTimePickedUp    
    GroupBox GroupBox   Order Processing  
    Label Label   Item Type  
    Label Label   Unit Price  
    Label Label   Qty  
    Label Label   Sub Total  
    Label Label   Shirts  
    TextBox TextBox TxtUnitPriceShirts 1.25 TextAlign: Right
    TextBox TextBox TxtQuantityShirts 0 TextAlign: Right
    TextBox TextBox TxtSubTotalShirts 0.00 TextAlign: Right
    Label Label   Pants  
    TextBox TextBox TxtUnitPricePants 1.95 TextAlign: Right
    TextBox TextBox TxtQuantityPants   TextAlign: Right
    TextBox TextBox TxtSubTotalPants 0.00 TextAlign: Right
    ComboBox ComboBox CbxItem1Name None Items:
    None
    Women Suit
    Dress
    Regular Skirt
    Skirt With Hook
    Men Suit 2Pc
    Men Suit 3Pc
    Sweaters
    Silk Shirt
    Tie
    Coat
    Jacket
    Swede
    TextBox TextBox TxtUnitPriceItem1 0.00 TextAlign: Right
    TextBox TextBox TxtQuantityItem1 0 TextAlign: Right
    TextBox TextBox TxtSubTotalItem1 0.00 TextAlign: Right
    ComboBox ComboBox CbxItem2Name None Items:
    None
    Women Suit
    Dress
    Regular Skirt
    Skirt With Hook
    Men Suit 2Pc
    Men Suit 3Pc
    Sweaters
    Silk Shirt
    Tie
    Coat
    Jacket
    Swede
    TextBox TextBox TxtUnitPriceItem2 0.00 TextAlign: Right
    TextBox TextBox TxtQuantityItem2 0 TextAlign: Right
    TextBox TextBox TxtSubTotalItem2 0.00 TextAlign: Right
    ComboBox ComboBox CbxItem3Name None Items:
    None
    Women Suit
    Dress
    Regular Skirt
    Skirt With Hook
    Men Suit 2Pc
    Men Suit 3Pc
    Sweaters
    Silk Shirt
    Tie
    Coat
    Jacket
    Swede
    TextBox TextBox TxtUnitPriceItem3 0.00 TextAlign: Right
    TextBox TextBox TxtQuantityItem3 0 TextAlign: Right
    TextBox TextBox TxtSubTotalItem3 0.00 TextAlign: Right
    ComboBox ComboBox CbxItem4Name None Items:
    None
    Women Suit
    Dress
    Regular Skirt
    Skirt With Hook
    Men Suit 2Pc
    Men Suit 3Pc
    Sweaters
    Silk Shirt
    Tie
    Coat
    Jacket
    Swede
    TextBox TextBox TxtUnitPriceItem4 0.00 TextAlign: Right
    TextBox TextBox TxtQuantityItem4 0 TextAlign: Right
    TextBox TextBox TxtSubTotalItem4 0.00 TextAlign: Right
    GroupBox GroupBox   Order Summary  
    Label Label   Cleaning Total:  
    TextBox TextBox TxtCleaningTotal 0.00 TextAlign: Right
    Label Label   Tax Rate:  
    TextBox TextBox TxtTaxRate 7.75 TextAlign: Right
    Label Label   %  
    Label Label   Tax Amount:  
    TextBox TextBox TxtTaxAmount 0.00 TextAlign: Right
    Label Label   Net Total:  
    TextBox TextBox TxtNetPrice 0.00 TextAlign: Right
    Label Label   Order &Status:  
    ComboBox ComboBox CbxOrderStatus    
    Button Button BtnSave Save  
    GroupBox  Group Box   Cleaning Order Details   
    Label Label   &Receipt #:  
    TextBox TextBox TxtCleaningOrderID    
    Button Button BtnOpen Open  
    Label  Label   Notes  
    TextBox  TextBox TxtNotes    
    Button Button BtnNewCleaningOrder New Cleaning Order  
    Button Button BtnClose Close  
  13. Double-click the New Employee button and implement its event as follows:
     
    Imports System.Data.SqlClient
    
    Public Class CleaningOrders
    
        Private Sub BtnNewEmployee_Click(ByVal sender As System.Object, _
                                         ByVal e As System.EventArgs) _
                                         Handles BtnNewEmployee.Click
            Dim Clerks As Employees = New Employees
            Clerks.ShowDialog()
        End Sub
    End Class
  14. In the Class Name combo box, select BtnNewCustomer
  15. In the Method Name combo box, select Click and implement the event as follows:
     
    Private Sub BtnNewCustomer_Click(ByVal sender As Object, _
                                     ByVal e As System.EventArgs) _
                                     Handles BtnNewCustomer.Click
        Dim Clients As Customers = New Customers
        Clients.ShowDialog()
    End Sub
  16. In the Class Name combo box, select (CleaningOrders Events)
  17. In the Method Name combo box, select Load and implement the event as follows:
     
    Private Sub CleaningOrders_Load(ByVal sender As Object, _
                                   ByVal e As System.EventArgs) _
                                   Handles Me.Load
        Using Connect As SqlConnection = _
                 New SqlConnection("Data Source=(local); " & _
                   "Integrated Security='SSPI';")
    
            Dim strCreateDatabase As String = _
                "CREATE DATABASE GeorgetownDryCleaner1"
    
            Dim Command As SqlCommand = _
                New SqlCommand(strCreateDatabase, _
                       Connect)
    
            Connect.Open()
            Command.ExecuteNonQuery()
    
            MsgBox("A database named " & _
                 "GeorgetownDryCleaner1 has been created")
        End Using
    
        Using Connect As SqlConnection = _
             New SqlConnection("Data Source=(local); " & _
        		   "Database='GeorgetownDryCleaner1'; " & _
         		   "Integrated Security='SSPI';")
    
            Dim strCreateTable As String = _
                	"CREATE TABLE Employees( " & _
          		"EmployeeID int identity(1, 1) NOT NULL, " & _
          		"EmployeeNumber nchar(6), " & _
          		"FirstName varchar(32), " & _
          		"LastName varchar(32) NOT NULL, " & _
          		"FullName AS (([LastName]+', ')+[FirstName]), " & _
          		"Title varchar(80), " & _
          		"HourlySalary smallmoney, " & _
          		"CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID));"
    
            Dim Command As SqlCommand = _
                New SqlCommand(strCreateTable, Connect)
    
            Connect.Open()
            Command.ExecuteNonQuery()
            MsgBox("A table named Employees has been created")
        End Using
    
        Using Connect As SqlConnection = _
             New SqlConnection("Data Source=(local); " & _
        		           "Database='GeorgetownDryCleaner1'; " & _
                    	   "Integrated Security='SSPI';")
    
            Dim strCreateTable As String = _
    	        "CREATE TABLE Customers( " & _
            	"CustomerID int identity(1, 1) NOT NULL, " & _
             	"PhoneNumber varchar(20), " & _
             	"FullName varchar(80), " & _
             	"CONSTRAINT PK_Customer PRIMARY KEY (CustomerID));"
    
            Dim Command As SqlCommand = _
                New SqlCommand(strCreateTable, Connect)
    
            Connect.Open()
            Command.ExecuteNonQuery()
            MsgBox("A table named Customers has been created")
        End Using
    
        Using Connect As SqlConnection = _
             New SqlConnection("Data Source=(local); " & _
                    "Database='GeorgetownDryCleaner1'; " & _
                    "Integrated Security='SSPI';")
    
            Dim strCreateTable As String = _
                	"CREATE TABLE CleaningOrders( " & _
             "CleaningOrderID int identity(1001, 1) NOT NULL, " & _
             "EmployeeNumber nchar(6) NOT NULL, " & _
             "CustomerNumber varchar(20) NOT NULL, " & _
             "DateLeft smalldatetime, " & _
             "TimeLeft smalldatetime, " & _
             "DateExpected smalldatetime, " & _
             "TimeExpected smalldatetime, " & _
             "OrderStatus varchar(50), " & _
             "DatePickedUp smalldatetime, " & _
             "TimePickedUp smalldatetime, " & _
             "UnitPriceShirts smallmoney, " & _
             "QuantityShirts smallmoney, " & _
             "SubTotalShirts smallmoney, " & _
             "UnitPricePants smallmoney, " & _
             "QuantityPants smallmoney, " & _
             "SubTotalPants smallmoney, " & _
             "Item1Name varchar(50), " & _
             "UnitPriceItem1 smallmoney, " & _
             "QuantityItem1 smallmoney, " & _
             "SubTotalItem1 smallmoney, " & _
             "Item2Name varchar(50), " & _
             "UnitPriceItem2 smallmoney, " & _
             "QuantityItem2 smallmoney, " & _
             "SubTotalItem2 smallmoney, " & _
             "Item3Name varchar(50), " & _
             "UnitPriceItem3 smallmoney, " & _
             "QuantityItem3 smallmoney, " & _
             "SubTotalItem3 smallmoney, " & _
             "Item4Name varchar(50), " & _
             "UnitPriceItem4 smallmoney, " & _
             "QuantityItem4 smallmoney, " & _
             "SubTotalItem4 smallmoney, " & _
             "CleaningTotal smallmoney, " & _
             "TaxRate decimal(6,2), " & _
             "TaxAmount smallmoney, " & _
             "NetPrice smallmoney, " & _
             "Notes ntext);"
    
            Dim Command As SqlCommand = _
                New SqlCommand(strCreateTable, Connect)
    
            Connect.Open()
            Command.ExecuteNonQuery()
            MsgBox("A table named CleaningOrders has been created")
        End Using
    End Sub
  18. Execute the application to create the database and its tables
  19. Close the form and return to your programming environment
  20. In the source file, delete the whole content of the Load event and return to the form
  21. In the Solution Explorer, right-click Employees.vb and click View Code
  22. Just above the Public Class line, import the System.Data.SqlClient namespace
  23. Under the Public Class line, create a procedure as follows:
     
    Imports System.Data.SqlClient
    
    Public Class Employees
    
        Friend Sub ShowEmployees()
    	using Connect As SqlConnection = _
    		new SqlConnection("Data Source=(local);" & _
    			"Database='GeorgetownDryCleaner1';" & _
    			"Integrated Security=SSPI;")
    
                Dim strEmployees As String = _
    	   	"SELECT * FROM Employees;"
                Dim Command As SqlCommand = _
                    New SqlCommand(strEmployees, Connect)
                Dim daEmployees As SqlDataAdapter = New SqlDataAdapter()
    
                daEmployees.SelectCommand = Command
                Dim dsEmployees As DataSet = New DataSet("EmployeesSet")
                daEmployees.Fill(dsEmployees)
    
                Connect.Open()
    
                dgvEmployees.DataSource = dsEmployees
                dgvEmployees.DataMember = dsEmployees.Tables(0).TableName
            End Using
        End Sub
    End Class
  24. In the Class Name combo box, select (Employees Events)
  25. In the Method Name combo box, select Load and implement the event as follows:
     
    Private Sub Employees_Load(ByVal sender As Object, _
                               ByVal e As System.EventArgs) _
                               Handles Me.Load
        ShowEmployees()
    End Sub
  26. In the Class Name combo box, select BtnSubmit
  27. 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
        Using Connect As SqlConnection = _
             New SqlConnection("Data Source=(local);" & _
    	        	   "Database='GeorgetownDryCleaner1';" & _
            		   "Integrated Security=SSPI;")
    
            Dim strEmployees As String = _
    		    "INSERT INTO Employees(EmployeeNumber, " & _
                        "FirstName, LastName, Title, HourlySalary) " & _
                        "VALUES('" & TxtEmployeeNumber.Text & _
                        "', '" & TxtFirstName.Text & "', '" & _
                        TxtLastName.Text & "', '" & TxtTitle.Text & _
                        "', '" & TxtHourlySalary.Text & "');"
            Dim Command As SqlCommand = _
               New SqlCommand(strEmployees, _
                      Connect)
    
            Connect.Open()
            Command.ExecuteNonQuery()
    
            TxtEmployeeNumber.Text = ""
            TxtFirstName.Text = ""
            TxtLastName.Text = ""
            TxtTitle.Text = ""
            TxtHourlySalary.Text = "0.00"
    
            ShowEmployees()
        End Using
    End Sub
  28. In the Class Name combo box, select BtnClose
  29. 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
        Close()
    End Sub
  30. In the Solution Explorer, right-click Customers.vb and click View Code
  31. Just above the Public Class line, import the System.Data.SqlClient namespace
  32. Under the Public Class line, create a procedure as follows:
  33. In the Class Name combo box, select (Employees Events)
  34. In the Method Name combo box, select Load and implement the event as follows:
     
    Imports System.Data.SqlClient
    
    Public Class Customers
        Friend Sub ShowCustomers()
            Using Connect As SqlConnection = _
         		New SqlConnection("Data Source=(local);" & _
          			  	  "Database='GeorgetownDryCleaner1';" & _
          			 	  "Integrated Security=SSPI;")
    
                Dim strCustomers As String = "SELECT * FROM Customers;"
                Dim Command As SqlCommand = _
                    New SqlCommand(strCustomers, Connect)
                Dim sdaCustomers As SqlDataAdapter = New SqlDataAdapter
    
                sdaCustomers.SelectCommand = Command
                Dim DsCustomers As DataSet = New DataSet("CustomersSet")
                sdaCustomers.Fill(DsCustomers)
    
                Connect.Open()
    
                dgvCustomers.DataSource = dsCustomers
                dgvCustomers.DataMember = dsCustomers.Tables(0).TableName
            End Using
        End Sub
    End Class
  35. In the Class Name combo box, select (Customers Events)
  36. In the Method Name combo box, select Load and implement the event as follows:
     
    Private Sub Customers_Load(ByVal sender As Object, _
                               ByVal e As System.EventArgs) _
                               Handles Me.Load
        ShowCustomers()
    End Sub
  37. In the Class Name combo box, select Btn Submit
  38. In the Method Name combo box, select Click and implement the event as follows:
     
    Private Sub BtnSubmit_Click(ByVal sender As System.Object, _
                                ByVal e As System.EventArgs) _
                                Handles BtnSubmit.Click
        Using Connect As SqlConnection = _
             New SqlConnection("Data Source=(local);" & _
                 "Database='GeorgetownDryCleaner1';" & _
                 "Integrated Security=SSPI;")
    
            Dim strCustomers As String = _
    		    "INSERT INTO Customers(PhoneNumber, " & _
                        "FullName) " & _
                        "VALUES('" & TxtCustomerPhone.Text & _
                        "', '" & TxtCustomerName.Text & "');"
            Dim Command As SqlCommand = _
                New SqlCommand(strCustomers, _
    	                   Connect)
    
            Connect.Open()
            Command.ExecuteNonQuery()
    
            TxtCustomerPhone.Text = ""
            TxtCustomerName.Text = ""
    
            ShowCustomers()
        End Using
    End Sub 
  39. In the Class Name combo box, select Btn Close
  40. 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
        Close()
    End Sub
  41. In the Solution Explorer, right-click CleaningOrders and click View Code
  42. In the Class Name combo box, select TxtEmployeeNumber
  43. 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='GeorgetownDryCleaner1';" & _
         			   "Integrated Security=SSPI;")
    
            Dim strEmployees As String = _
         		"SELECT FullName FROM Employees WHERE EmployeeNumber = '" & _
      		TxtEmployeeNumber.Text & "';"
            Dim Command As SqlCommand = _
                 New SqlCommand(strEmployees, Connect)
            Dim daEmployees As SqlDataAdapter = New SqlDataAdapter
    
            daEmployees.SelectCommand = Command
            Dim dsEmployees As DataSet = New DataSet("EmployeesSet")
            daEmployees.Fill(dsEmployees)
    
            Connect.Open()
    
            For Each rowEmployee As DataRow In dsEmployees.Tables(0).Rows
                For Each colEmployee As DataColumn In dsEmployees.Tables(0).Columns
                    TxtEmployeeName.Text = rowEmployee(colEmployee).ToString()
                Next
                Exit For
            Next
        End Using
    End Sub
  44. In the Class Name combo box, select TxtCustomerPhone
  45. In the Method Name combo box, select Leave and implement the event as follows:
     
    Private Sub TxtCustomerPhone_Leave(ByVal sender As Object, _
                                       ByVal e As System.EventArgs) _
                                       Handles TxtCustomerPhone.Leave
        Using Connect As SqlConnection = _
    	 New SqlConnection("Data Source=(local);" & _
         			   "Database='GeorgetownDryCleaner1';" & _
         			   "Integrated Security=SSPI;")
    
            Dim strCustomers As String = _
                 "SELECT FullName FROM Customers WHERE PhoneNumber = '" & _
            	     TxtCustomerPhone.Text & "';"
            Dim Command As SqlCommand = _
    		 New SqlCommand(strCustomers, Connect)
            Dim sdaCustomers As SqlDataAdapter = New SqlDataAdapter
    
            sdaCustomers.SelectCommand = Command
            Dim DsCustomers As DataSet = New DataSet("CustomersSet")
            sdaCustomers.Fill(DsCustomers)
    
            Connect.Open()
    
            For Each rowCustomer As DataRow In DsCustomers.Tables(0).Rows
                For Each colCustomer As DataColumn In DsCustomers.Tables(0).Columns
                     TxtCustomerName.Text = rowCustomer(colCustomer)
                Next
                Exit For
            Next
        End Using
    End Sub
  46. In the Class Name combo box, select BtnNewCleaningOrder
  47. In the Method Name combo box, select Click and implement the event as follows:
     
    Private Sub BtnNewCleaningOrder_Click(ByVal sender As Object, _
                                          ByVal e As System.EventArgs) _
                                          Handles BtnNewCleaningOrder.Click
            TxtEmployeeNumber.Text = ""
            TxtEmployeeName.Text = ""
            TxtCustomerPhone.Text = ""
            TxtCustomerName.Text = ""
    
            TxtCleaningOrderID.Text = ""
    
            DtpDateLeft.Value = DateTime.Today
            DtpTimeLeft.Value = DateTime.Today
            DtpDateExpected.Value = DateTime.Today
            DtpTimeExpected.Value = DateTime.Today
    
            CbxOrderStatus.Text = "Not Yet Ready"
            DtpDatePickedUp.Value = DateTime.Today
            DtpTimePickedUp.Value = DateTime.Today
    
            TxtUnitPriceShirts.Text = "1.25"
            TxtQuantityShirts.Text = "0"
            TxtSubTotalShirts.Text = "0.00"
    
            TxtUnitPricePants.Text = "1.95"
            TxtQuantityPants.Text = "0"
            TxtSubTotalPants.Text = "0.00"
            CbxItemName1.Text = "None"
            TxtUnitPriceItem1.Text = "0.00"
            TxtQuantityItem1.Text = "0"
            TxtSubTotalItem1.Text = "0.00"
            CbxItemName2.Text = "None"
            TxtUnitPriceItem2.Text = "0.00"
            TxtQuantityItem2.Text = "0"
            TxtSubTotalItem2.Text = "0.00"
            CbxItemName3.Text = "None"
            TxtUnitPriceItem3.Text = "0.00"
            TxtQuantityItem3.Text = "0"
            TxtSubTotalItem3.Text = "0.00"
            CbxItemName4.Text = "None"
            TxtUnitPriceItem4.Text = "0.00"
            TxtQuantityItem4.Text = "0"
            TxtSubTotalItem4.Text = "0.00"
    
            TxtCleaningTotal.Text = "0.00"
            TxtTaxRate.Text = "7.75"
            TxtTaxAmount.Text = "0.00"
            TxtCleaningTotal.Text = "0.00"
            TxtNotes.Text = ""
    
            TxtEmployeeNumber.Focus()
    End Sub
  48. Change to Load event as follows:
     
    Private Sub OrderCleaning_Load(ByVal sender As Object, _
                                   ByVal e As System.EventArgs) _
                                   Handles Me.Load
        BtnNewCleaningOrder_Click(sender, e)
    End Sub
  49. In the Class Name combo box, select DtpTimeLeft
  50. In the Method Name combo box, select ValueChanged and implement the event as follows:
     
    Private Sub DtpTimeLeft_ValueChanged(ByVal sender As Object, _
                                         ByVal e As System.EventArgs) _
                                         Handles DtpTimeLeft.ValueChanged
        Dim DateLeft As DateTime = DtpDateLeft.Value
        Dim TimeLeft As DateTime = DtpTimeLeft.Value
    
        Dim Time9AM As DateTime = New DateTime(TimeLeft.Year, _
                                               TimeLeft.Month, _
                                               TimeLeft.Day, 9, 0, 0)
    
        ' If the customer leaves clothes before 9AM...
        If TimeLeft <= Time9AM Then
            ' ... then they should be ready the same day after 5PM
            DtpDateExpected.Value = DateLeft
            DtpTimeExpected.Value = New DateTime(DateLeft.Year, _
                                                 DateLeft.Month, _
                                                 DateLeft.Day, 17, 0, 0)
        Else
            ' If the clothes were left after 9AM,
            ' then they will be available
            ' the following business morning at 8AM
            ' If the following day is Sunday,
            ' then they will be ready the following Monday
            If DateLeft.DayOfWeek = DayOfWeek.Saturday Then
                DtpDateExpected.Value = DateLeft.AddDays(2D)
                DtpTimeExpected.Value = New DateTime(DateLeft.Year, _
                                                     DateLeft.Month, _
                                               DateLeft.Day + 2, 8, 0, 0)
            Else
                DtpDateExpected.Value = New DateTime(DateLeft.Year, _
                                                     DateLeft.Month, _
                                                     DateLeft.Day + 1)
                DtpTimeExpected.Value = New DateTime(DateLeft.Year, _
                                                     DateLeft.Month, _
             				DateLeft.Day + 1, 8, 0, 0)
            End If
        End If
    End Sub
  51. In the Class Name combo box, select BtnSave
  52. In the Method Name combo box, select Click and implement the event as follows:
     
    Private Sub BtnSave_Click(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles BtnSave.Click
        Dim strCommand As String
    
        Using Connect As SqlConnection = _
                New SqlConnection("Data Source=(local);" & _
             "Database='GeorgetownDryCleaner1';" & _
             "Integrated Security=SSPI;")
    
            ' If the Receipt Number is empty, it appears that
            ' the user/clerk wants to create a new cleaning order
            If TxtCleaningOrderID.Text.Length = 0 Then
                strCommand = "INSERT INTO CleaningOrders( " & _
            	         "EmployeeNumber, CustomerNumber, DateLeft, " & _
    	                 "TimeLeft, DateExpected, TimeExpected, " & _
                   		 "OrderStatus, DatePickedUp, " & _
                  		 "TimePickedUp, UnitPriceShirts, " & _
                  		 "QuantityShirts, SubTotalShirts, " & _
                  		 "UnitPricePants, QuantityPants, " & _
                  		 "SubTotalPants, Item1Name, " & _
                  		 "UnitPriceItem1, QuantityItem1, " & _
                  		 "SubTotalItem1, Item2Name, " & _
                  		 "UnitPriceItem2, QuantityItem2, " & _
                  		 "SubTotalItem2, Item3Name, " & _
                  		 "UnitPriceItem3, QuantityItem3, " & _
                  		 "SubTotalItem3, Item4Name, " & _
                  		 "UnitPriceItem4, QuantityItem4, " & _
                  		 "SubTotalItem4, CleaningTotal, " & _
                  		 "TaxRate, TaxAmount, NetPrice, Notes) " & _
                  		 "VALUES('" & TxtEmployeeNumber.Text & "', '" & _
                  		 TxtCustomerPhone.Text & "', '" & _
                  		 DtpDateLeft.Value.ToString("d") & "', '" & _
                 		 DtpTimeLeft.Value.ToString("t") & "', '" & _
                  		 DtpDateExpected.Value.ToString("d") & "', '" & _
                  		 DtpTimeExpected.Value.ToString("t") & "', '" & _
                  		 CbxOrderStatus.Text & "', '" & _
                  		 DtpDatePickedUp.Value.ToString("d") & "', '" & _
                  		 DtpDatePickedUp.Value.ToString("t") & "', '" & _
                  		 TxtUnitPriceShirts.Text & "', '" & _
                 		 TxtQuantityShirts.Text & "', '" & _
                  		 TxtSubTotalShirts.Text & "', '" & _
                  		 TxtUnitPricePants.Text & "', '" & _
                  		 TxtQuantityPants.Text & "', '" & _
                  		 TxtSubTotalPants.Text & "', '" & _
                  		 CbxItemName1.Text & "', '" & _
                  		 TxtUnitPriceItem1.Text & "', '" & _
                  		 TxtQuantityItem1.Text & "', '" & _
                  		 TxtSubTotalItem1.Text & "', '" & _
                  		 CbxItemName2.Text & "', '" & _
                  		 TxtUnitPriceItem2.Text & "', '" & _
                  		 TxtQuantityItem2.Text & "', '" & _
                  		 TxtSubTotalItem2.Text & "', '" & _
                  		 CbxItemName3.Text & "', '" & _
                  		 TxtUnitPriceItem3.Text & "', '" & _
                  		 TxtQuantityItem3.Text & "', '" & _
                  		 TxtSubTotalItem3.Text & "', '" & _
                  		 CbxItemName4.Text & "', '" & _
                  		 TxtUnitPriceItem4.Text & "', '" & _
                  		 TxtQuantityItem4.Text & "', '" & _
                  		 TxtSubTotalItem4.Text & "', '" & _
                  		 TxtCleaningTotal.Text & "', '" & _
                  		 TxtTaxRate.Text & "', '" & _
                  		 TxtTaxAmount.Text & "', '" & _
                  		 TxtNetPrice.Text & "', '" & TxtNotes.Text & "');"
                Else ' Since there is a receipt number, update/edit the cleaning 
                    strCommand = "UPDATE CleaningOrders " & _
                   "SET EmployeeNumber = '" & _
                   TxtEmployeeNumber.Text & "', " & _
                  " CustomerNumber = '" & TxtCustomerPhone.Text & "', " & _
                 " DateLeft = '" & _
                 DtpDateLeft.Value.ToString("d") & "', " & _
                 " TimeLeft = '" & _
                 DtpTimeLeft.Value.ToString("t") & "', " & _
                 " DateExpected = '" & _
                 DtpDateExpected.Value.ToString("d") & "', " & _
                 " TimeExpected = '" & _
                 DtpTimeExpected.Value.ToString("t") & "', " & _
                 " OrderStatus = '" & _
                 CbxOrderStatus.Text & "', " & _
                 " DatePickedUp = '" & _
                 DtpDatePickedUp.Value.ToString("d") & "', " & _
                 " TimePickedUp = '" & _
                 DtpDatePickedUp.Value.ToString("t") & "', " & _
                 " UnitPriceShirts = '" & TxtUnitPriceShirts.Text & "', " & _
                 " QuantityShirts = '" & TxtQuantityShirts.Text & "', " & _
                 " SubTotalShirts = '" & TxtSubTotalShirts.Text & "', " & _
                 " UnitPricePants = '" & TxtUnitPricePants.Text & "', " & _
                 " QuantityPants = '" & TxtQuantityPants.Text & "', " & _
                 " SubTotalPants = '" & TxtSubTotalPants.Text & "', " & _
                 " Item1Name = '" & _
                 CbxItemName1.Text & "', " & _
                 " UnitPriceItem1 = '" & TxtUnitPriceItem1.Text & "', " & _
                 " QuantityItem1 = '" & TxtQuantityItem1.Text & "', " & _
                 " SubTotalItem1 = '" & TxtSubTotalItem1.Text & "', " & _
                 " Item2Name = '" & _
                 CbxItemName2.Text & "', " & _
                 " UnitPriceItem2 = '" & TxtUnitPriceItem2.Text & "', " & _
                 " QuantityItem2 = '" & TxtQuantityItem2.Text & "', " & _
                 " SubTotalItem2 = '" & TxtSubTotalItem2.Text & "', " & _
                 " Item3Name = '" & _
                 CbxItemName3.Text & "', " & _
                 " UnitPriceItem3 = '" & TxtUnitPriceItem3.Text & "', " & _
                 " QuantityItem3 = '" & TxtQuantityItem3.Text & "', " & _
                 " SubTotalItem3 = '" & TxtSubTotalItem3.Text & "', " & _
                 " Item4Name = '" & _
                 CbxItemName4.Text & "', " & _
                 " UnitPriceItem4 = '" & TxtUnitPriceItem4.Text & "', " & _
                 " QuantityItem4 = '" & TxtQuantityItem4.Text & "', " & _
                 " SubTotalItem4 = '" & TxtSubTotalItem4.Text & "', " & _
                 " CleaningTotal = '" & TxtCleaningTotal.Text & "', " & _
                 " TaxRate = '" & TxtTaxRate.Text & "', " & _
                 " TaxAmount = '" & TxtTaxAmount.Text & "', " & _
                 " NetPrice = '" & TxtNetPrice.Text & "', " & _
                 " Notes = '" & TxtNotes.Text & "' " & _
                 " WHERE CleaningOrderID = '" & TxtCleaningOrderID.Text & "';"
            End If
    
            Dim cmdCleaningOrders As SqlCommand = _
                     New SqlCommand(strCommand, _
    				Connect)
    
            Connect.Open()
            cmdCleaningOrders.ExecuteNonQuery()
    
            BtnNewCleaningOrder_Click(sender, e)
        End Using
    End Sub
  53. In the Class Name combo box, select BtnOpen
  54. In the Method Name combo box, select Click and implement the event as follows:
     
    Private Sub BtnOpen_Click(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles BtnOpen.Click
        Dim i As Integer
    
        If TxtCleaningOrderID.Text.Length = 0 Then
            MsgBox("To open a cleaning order, " & _
                   "enter its receipt number and click Open.")
            Exit Sub
        End If
    
        Using Connect As SqlConnection = _
          New SqlConnection("Data Source=(local);" & _
                 		"Database='GeorgetownDryCleaner1';" & _
             		"Integrated Security=SSPI;")
    
            Dim strCleaningOrders As String = _
                  "SELECT EmployeeNumber, CustomerNumber, DateLeft, " & _
                  "TimeLeft, DateExpected, TimeExpected, " & _
                  "OrderStatus, DatePickedUp, " & _
                  "TimePickedUp, UnitPriceShirts, " & _
                  "QuantityShirts, SubTotalShirts, " & _
                  "UnitPricePants, QuantityPants, " & _
                  "SubTotalPants, Item1Name, " & _
                  "UnitPriceItem1, QuantityItem1, " & _
                  "SubTotalItem1, Item2Name, " & _
                  "UnitPriceItem2, QuantityItem2, " & _
                  "SubTotalItem2, Item3Name, " & _
                  "UnitPriceItem3, QuantityItem3, " & _
                  "SubTotalItem3, Item4Name, " & _
                  "UnitPriceItem4, QuantityItem4, " & _
                  "SubTotalItem4, CleaningTotal, " & _
                  "TaxRate, TaxAmount, NetPrice, Notes " & _
                  "FROM CleaningOrders WHERE CleaningOrderID = '" & _
                  TxtCleaningOrderID.Text & "';"
    
            Dim cmdCleaningOrders As SqlCommand = _
                  New SqlCommand(strCleaningOrders, Connect)
            Dim sdaCleaningOrders As SqlDataAdapter = New SqlDataAdapter
    
            sdaCleaningOrders.SelectCommand = cmdCleaningOrders
            Dim DsCleaningOrders As DataSet = New DataSet("CleaningOrdersSet")
            sdaCleaningOrders.Fill(DsCleaningOrders)
    
            Connect.Open()
    
            For i = 0 To DsCleaningOrders.Tables(0).Rows.Count - 1
                Dim record As DataRow = DsCleaningOrders.Tables(0).Rows(i)
    
                TxtEmployeeNumber.Text = record(0)
                TxtCustomerPhone.Text = record(1)
    
                DtpDateLeft.Value = CDate(record(2))
                DtpTimeLeft.Value = CDate(record(3))
                DtpDateExpected.Value = CDate(record(4))
                DtpTimeExpected.Value = CDate(record(5))
    
                CbxOrderStatus.Text = record(6)
    
                DtpDatePickedUp.Value = CDate(record(7))
                DtpTimePickedUp.Value = CDate(record(8))
    
                TxtUnitPriceShirts.Text = record(9)
                TxtQuantityShirts.Text = record(10)
                TxtSubTotalShirts.Text = CDbl(record(11).ToString()).ToString("F")
    
                TxtUnitPricePants.Text = CDbl(record(12).ToString()).ToString("F")
                TxtQuantityPants.Text = record(13).ToString()
                TxtSubTotalPants.Text = CDbl(record(14).ToString()).ToString("F")
    
                CbxItemName1.Text = record(15).ToString()
                TxtUnitPriceItem1.Text = CDbl(record(16).ToString()).ToString("F")
                TxtQuantityItem1.Text = record(17).ToString()
                TxtSubTotalItem1.Text = CDbl(record(18).ToString()).ToString("F")
    
                CbxItemName2.Text = record(19).ToString()
                TxtUnitPriceItem2.Text = CDbl(record(20).ToString()).ToString("F")
                TxtQuantityItem2.Text = record(21).ToString()
                TxtSubTotalItem2.Text = CDbl(record(22).ToString()).ToString("F")
    
                CbxItemName3.Text = record(23).ToString()
                TxtUnitPriceItem3.Text = CDbl(record(24).ToString()).ToString("F")
                TxtQuantityItem3.Text = record(25).ToString()
                TxtSubTotalItem3.Text = CDbl(record(26).ToString()).ToString("F")
    
                CbxItemName4.Text = record(27).ToString()
                TxtUnitPriceItem4.Text = CDbl(record(28).ToString()).ToString("F")
                TxtQuantityItem4.Text = record(29).ToString()
                TxtSubTotalItem4.Text = CDbl(record(30).ToString()).ToString("F")
    
                TxtCleaningTotal.Text = CDbl(record(31).ToString()).ToString("F")
                TxtTaxRate.Text = CDbl(record(32).ToString()).ToString("F")
                TxtTaxAmount.Text = CDbl(record(33).ToString()).ToString("F")
                TxtCleaningTotal.Text = CDbl(record(34).ToString()).ToString("F")
    
                TxtNotes.Text = record(35).ToString()
    
                TxtEmployeeNumber_Leave(sender, e)
                TxtCustomerPhone_Leave(sender, e)
            Next
        End Using
    End Sub
  55. Under the above End Sub line, implement the following event:
     
    Private Sub ControlsLeave(ByVal sender As Object, _
                              ByVal e As EventArgs) _
                              Handles TxtUnitPriceItem1.Leave, _
                                      TxtUnitPriceItem2.Leave, _
                                      TxtUnitPriceItem3.Leave, _
                                      TxtUnitPriceItem4.Leave, _
                                      TxtUnitPriceShirts.Leave, _
                                      TxtUnitPricePants.Leave, _
                                      TxtQuantityItem1.Leave, _
                                      TxtQuantityItem2.Leave, _
                                      TxtQuantityItem3.Leave, _
                                      TxtQuantityItem4.Leave, _
                                      TxtQuantityShirts.Leave, _
                                      TxtQuantityPants.Leave, _
                                      TxtTaxRate.Leave
        Dim UnitPriceShirts As Double, UnitPricePants As Double
        Dim UnitPriceItem1, UnitPriceItem2 As Double
        Dim UnitPriceItem3 As Double, UnitPriceItem4 As Double
        Dim SubTotalShirts As Double, SubTotalPants As Double
        Dim SubTotalItem1 As Double, SubTotalItem2 As Double
        Dim SubTotalItem3 As Double, SubTotalItem4 As Double
        Dim QuantityShirts As Integer, QuantityPants As Integer
        Dim QuantityItem1 As Integer, QuantityItem2 As Integer
        Dim QuantityItem3 As Integer, QuantityItem4 As Integer
        Dim CleaningTotal As Double, TaxRate As Double
        Dim TaxAmount As Double, NetPrice As Double
    
        ' Retrieve the unit price of this item
        ' Just in case the user types an invalid value,
        ' we are using a try...catch
        Try
            UnitPriceShirts = CDbl(TxtUnitPriceShirts.Text)
        Catch ex As Exception
            MsgBox("The value you entered for the price of " & _
                   "shirts is not valid" & _
                   vbCrLf & "Please try again")
            Exit Sub
        End Try
    
        ' Retrieve the number of this item
        ' Just in case the user types an invalid value,
        ' we are using a try...catch
        Try
            QuantityShirts = CInt(TxtQuantityShirts.Text)
        Catch ex As Exception
            MsgBox("The value you entered for the number of " & _
                   "shirts is not valid" & _
                   vbCrLf & "Please try again")
            Exit Sub
        End Try
    
        Try
            UnitPricePants = CDbl(TxtUnitPricePants.Text)
        Catch ex As Exception
            MsgBox("The value you entered for the price of " & _
                   "pants is not valid" & _
                    vbCrLf & "Please try again")
            Exit Sub
        End Try
    
        Try
            QuantityPants = CInt(TxtQuantityPants.Text)
        Catch ex As Exception
            MsgBox("The value you entered for the number of " & _
                   "pants is not valid" & _
                   vbCrLf & "Please try again")
            Exit Sub
        End Try
    
            If (CbxItemName1.Text = "None") Or _
                (CbxItemName1.Text = "") Then
                QuantityItem1 = 0
                UnitPriceItem1 = 0.0
            Else
                Try
                    UnitPriceItem1 = CDbl(TxtUnitPriceItem1.Text)
                Catch ex As Exception
                    MsgBox("The value you entered for the price is not valid" & _
                                    vbCrLf & "Please try again")
                    Exit Sub
                End Try
    
                Try
                    QuantityItem1 = CInt(TxtQuantityItem1.Text)
                Catch ex As Exception
                    MsgBox("The value you entered is not valid" & _
                                    vbCrLf & "Please try again")
                    Exit Sub
                End Try
            End If
    
            If (CbxItemName2.Text = "None") Or _
                (CbxItemName2.Text = "") Then
                QuantityItem2 = 0
                UnitPriceItem2 = 0.0
            Else
                Try
                    UnitPriceItem2 = CDbl(TxtUnitPriceItem2.Text)
                Catch ex As Exception
                    MsgBox("The value you entered for " & _
                        "the price is not valid" & _
                                    vbCrLf & "Please try again")
                    Exit Sub
                End Try
    
                Try
                    QuantityItem2 = CInt(TxtQuantityItem2.Text)
                Catch ex As Exception
                    MsgBox("The value you entered is not valid" & _
                                    vbCrLf & "Please try again")
                    Exit Sub
                End Try
            End If
    
            If (CbxItemName3.Text = "None") Or _
            (CbxItemName3.Text = "") Then
                QuantityItem3 = 0
                UnitPriceItem3 = 0.0
            Else
                Try
                    UnitPriceItem3 = CDbl(TxtUnitPriceItem3.Text)
                Catch ex As Exception
                    MsgBox("The value you entered for the " & _
                        "price is not valid" & _
                                    vbCrLf & "Please try again")
                    Exit Sub
                End Try
    
                Try
                    QuantityItem3 = CInt(TxtQuantityItem3.Text)
                Catch ex As Exception
                    MsgBox("The value you entered is not valid" & _
                                    vbCrLf & "Please try again")
                    Exit Sub
                End Try
            End If
    
            If (CbxItemName4.Text = "None") Or (CbxItemName4.Text = "") Then
                QuantityItem4 = 0
                UnitPriceItem4 = 0.0
            Else
                Try
                    UnitPriceItem4 = CDbl(TxtUnitPriceItem4.Text)
                Catch ex As Exception
                    MsgBox("The value you entered for the price is not valid" & _
                            vbCrLf & "Please try again")
                    Exit Sub
                End Try
    
                Try
                    QuantityItem4 = CInt(TxtQuantityItem4.Text)
                Catch ex As Exception
                    MsgBox("The value you entered is not valid" & _
                            vbCrLf & "Please try again")
                    Exit Sub
                End Try
            End If
    
            ' Calculate the sub-total for this item
            SubTotalShirts = QuantityShirts * UnitPriceShirts
            SubTotalPants = QuantityPants * UnitPricePants
            SubTotalItem1 = QuantityItem1 * UnitPriceItem1
            SubTotalItem2 = QuantityItem2 * UnitPriceItem2
            SubTotalItem3 = QuantityItem3 * UnitPriceItem3
            SubTotalItem4 = QuantityItem4 * UnitPriceItem4
    
            ' Calculate the total based on sub-totals
            CleaningTotal = SubTotalShirts + SubTotalPants + SubTotalItem1 + _
                            SubTotalItem2 + SubTotalItem3 + SubTotalItem4
    
            TaxRate = CDbl(TxtTaxRate.Text)
            ' Calculate the amount owed for the taxes
            TaxAmount = CleaningTotal * TaxRate / 100
            ' Add the tax amount to the total order
            NetPrice = CleaningTotal + TaxAmount
    
            ' Display the sub-total in the corresponding text box
            TxtSubTotalShirts.Text = FormatNumber(SubTotalShirts)
            TxtSubTotalPants.Text = FormatNumber(SubTotalPants)
            TxtSubTotalItem1.Text = FormatNumber(SubTotalItem1)
            TxtSubTotalItem2.Text = FormatNumber(SubTotalItem2)
            TxtSubTotalItem3.Text = FormatNumber(SubTotalItem3)
            TxtSubTotalItem4.Text = FormatNumber(SubTotalItem4)
    
            TxtCleaningTotal.Text = FormatNumber(CleaningTotal)
            TxtTaxAmount.Text = FormatNumber(TaxAmount)
            TxtNetPrice.Text = FormatNumber(NetPrice)
    End Sub
  56. In the Class Name combo box, select BtnClose
  57. 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
        End
    End Sub
  58. Execute the application
  59. Create a few employees. Here are a few examples:
     
    Georgetown Cleaning Services - Employees
  60. Create a few customers. Here are some examples:
     
    Georgetown Cleaning Services - Customers
  61. Create a few cleaning orders. Here are examples:
     
    Georgetown Cleaning Services - Cleaning Orders
      
    Georgetown Cleaning Services - Cleaning Orders
  62. Close the form and return to your programming environment
  63. Execute the application again and open a few previously saved cleaning orders
  64. After opening an order, update it. Here are examples:
     
    Georgetown Cleaning Services - Cleaning Orders
      
    Georgetown Cleaning Services - Cleaning Orders
  65. Close the form and return to your programming environment
 

Home Copyright © 2008-2012 FunctionX