Database Example Application: Georgetown Dry Cleaners

	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 Learning: Creating the Application

Create a new Windows Application named GeorgetownDryCleaners3
To create a new form, on the main menu, click Project -> Add Windows Form...
Set the Name to Employees and press Enter

Design the form as follows:

Control	Name	Text	Additional Properties
Group Box		New Employee
Label		First Name:
TextBox	TxtFirstName
Label		Last Name:
TextBox	TxtLastName
Label		Employee #:
MaskedTextBox	TxtEmployeeNumber		Mask: 00-000
Label		Title:
TextBox	TxtTitle
Label		Hourly Salary:
TextBox	TxtHourlySalary		TextAlign: Right
Button	BtnSubmit	Submit
DataGridView	DgvEmployees
Button	BtnClose	Close

Save the form
To create a new form, on the main menu, click Project -> Add Windows Form...
Set the Name to Customers and press Enter

Design the form as follows:

Control	Name	Text	Additional Properties
Group Box		New Customer
Label		Customer Phone:
MaskedTextBox	TxtCustomerPhone		Mask: (999) 000-0000
Label		Customer Name:
TextBox	TxtCustomerName
Button	BtnSubmit	Submit
DataGridView	dgvCustomers
Button	BtnClose	Close

Save the form
In the Solution Explorer, right-click Form1.vb and click Rename
Type CleaningOrders.vb and press Enter twice

Design the form as follows:

Georgetown Cleaning Services: Cleaning Orders

Control	Name	Text	Additional Properties
GroupBox		Processed By
Label		Employee #:
MaskedTextBox	TxtEmployeeNumber		Mask: 00-000
Button	BtnNewEmployee	New Employee...
Label		Employee Name:
TextBox	TxtEmployeeName
GroupBox		Processed For
Label		Customer Phone:
MaskedTextBox	TxtCustomerPhone		Mask: (999) 000-0000
Button	BtnNewCustomer	New Cust...
Label		Customer Name:
TextBox	TxtCustomerName
GroupBox		Order Timing
Label		Date Left:
DateTimePicker	DtpDateLeft
Label		Time Left:
DateTimePicker	DtpTimeLeft		Format: Time
Label		Date Expected:
DateTimePicker	DtpDateExpected
Label		Time Expected:
DateTimePicker	DtpTimeExpected		Format: Time
Label		D&ate Picked Up:
DateTimePicker	DtpDatePickedUp
Label		Time Pic&kep Up:
DateTimePicker	DtpTimePickedUp
GroupBox		Order Processing
Label		Item Type
Label		Unit Price
Label		Qty
Label		Sub Total
Label		Shirts
TextBox	TxtUnitPriceShirts	1.25	TextAlign: Right
TextBox	TxtQuantityShirts	0	TextAlign: Right
TextBox	TxtSubTotalShirts	0.00	TextAlign: Right
Label		Pants
TextBox	TxtUnitPricePants	1.95	TextAlign: Right
TextBox	TxtQuantityPants		TextAlign: Right
TextBox	TxtSubTotalPants	0.00	TextAlign: Right
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	TxtUnitPriceItem1	0.00	TextAlign: Right
TextBox	TxtQuantityItem1	0	TextAlign: Right
TextBox	TxtSubTotalItem1	0.00	TextAlign: Right
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	TxtUnitPriceItem2	0.00	TextAlign: Right
TextBox	TxtQuantityItem2	0	TextAlign: Right
TextBox	TxtSubTotalItem2	0.00	TextAlign: Right
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	TxtUnitPriceItem3	0.00	TextAlign: Right
TextBox	TxtQuantityItem3	0	TextAlign: Right
TextBox	TxtSubTotalItem3	0.00	TextAlign: Right
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	TxtUnitPriceItem4	0.00	TextAlign: Right
TextBox	TxtQuantityItem4	0	TextAlign: Right
TextBox	TxtSubTotalItem4	0.00	TextAlign: Right
GroupBox		Order Summary
Label		Cleaning Total:
TextBox	TxtCleaningTotal	0.00	TextAlign: Right
Label		Tax Rate:
TextBox	TxtTaxRate	7.75	TextAlign: Right
Label		%
Label		Tax Amount:
TextBox	TxtTaxAmount	0.00	TextAlign: Right
Label		Net Total:
TextBox	TxtNetPrice	0.00	TextAlign: Right
Label		Order &Status:
ComboBox	CbxOrderStatus
Button	BtnSave	Save
GroupBox		Cleaning Order Details
Label		&Receipt #:
TextBox	TxtCleaningOrderID
Button	BtnOpen	Open
Label		Notes
TextBox	TxtNotes
Button	BtnNewCleaningOrder	New Cleaning Order
Button	BtnClose	Close

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

In the Class Name combo box, select BtnNewCustomer

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

In the Class Name combo box, select (CleaningOrders Events)

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

Execute the application to create the database and its tables
Close the form and return to your programming environment
In the source file, delete the whole content of the Load event and return to the form
In the Solution Explorer, right-click Employees.vb and click View Code
Just above the Public Class line, import the System.Data.SqlClient namespace

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

In the Class Name combo box, select (Employees Events)

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

In the Class Name combo box, select BtnSubmit

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

In the Class Name combo box, select BtnClose

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

In the Solution Explorer, right-click Customers.vb and click View Code
Just above the Public Class line, import the System.Data.SqlClient namespace
Under the Public Class line, create a procedure as follows:
In the Class Name combo box, select (Employees Events)

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

In the Class Name combo box, select (Customers Events)

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

In the Class Name combo box, select Btn Submit

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

In the Class Name combo box, select Btn Close

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

In the Solution Explorer, right-click CleaningOrders and click View Code
In the Class Name combo box, select TxtEmployeeNumber

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

In the Class Name combo box, select TxtCustomerPhone

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

In the Class Name combo box, select BtnNewCleaningOrder

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

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

In the Class Name combo box, select DtpTimeLeft

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

In the Class Name combo box, select BtnSave

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

In the Class Name combo box, select BtnOpen

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

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

In the Class Name combo box, select BtnClose

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

Execute the application
Create a few employees. Here are a few examples:
Create a few customers. Here are some examples:
Create a few cleaning orders. Here are examples:
Close the form and return to your programming environment
Execute the application again and open a few previously saved cleaning orders
After opening an order, update it. Here are examples:
Close the form and return to your programming environment


Home	Copyright © 2008-2016, FunctionX, Inc.