ADO.NET Examples: Georgetown Cleaning Services

	Georgetown Cleaning Services

The Data View in ADO

A view is a technique of creating a query, that is, a means of isolating a few records based on data from an existing table, a query, or another view. To create a view in Microsoft Access, you use the ADO library with the following SQL syntax:

CREATE VIEW ViewName
AS
SELECT Statement

The creation of a view starts with the CREATE VIEW expression followed by a name. After the name of the view, use the AS keyword to indicate that you are ready to define the view.

Because a view is like a query, it can be defined using a SELECT statement, following the same rules we applied for data sorting or filtering. Here is an example that creates a view:

Private Sub cmdCreateRegistration_Click()
	Dim conDatabase As ADODB.Connection
	Dim SQL As String

    	Set conDatabase = Application.CurrentProject.Connection
    
    	SQL = "CREATE VIEW StudentsIdentification " & _
              "AS SELECT FirstName, LastName FROM Students"

	conDatabase.Execute SQL
    
	conDatabase.Close
    	Set conDatabase = Nothing
End Sub

After creating the view, it is internally available to all objects of your database like a query but, because Microsoft Access doesn't have a Views section, you cannot see the view in the Database window.

Practical Learning: Starting the Application

Start Microsoft Visual Studio .NET or Visual Basic .NET and create a Windows Forms Application named GCS4

Design the form as follows:

Control	Name	Text	Additional Properties
GroupBox		Order Identification
Label		Customer Name:
TextBox	txtCustomerName
Label		Customer Phone:
TextBox	txtCustomerPhone
Label		Date Left:
DateTimePicker	dtpDateLeft		Format: Custom Custom Format: dddd MMM dd, yyyy
Label		Time Left:
DateTimePicker	dtpTimeLeft		Format: Time
Label		Date Expected:
DateTimePicker	dtpDateExpected		Format: Custom Custom Format: dddd MMM dd, yyyy
Label		Time Expected:
DateTimePicker	dtpTimeExpected		Format: Time
GroupBox		Order Processing
Label		Item Type
Label		Unit Price
Label		Qty
Label		Sub Total
Label		Shirts
TextBox	txtShirtsUnitPrice	0.95	TextAlign: Right
TextBox	txtShirtsQuantity	0	TextAlign: Right
Button	btnCalcShirts		Image: SubTotal.ico
TextBox	txtShirtsSubTotal	0.00	TextAlign: Right
Label		Pants
TextBox	txtPantsUnitPrice	1.75	TextAlign: Right
TextBox	txtPantsQuantity		TextAlign: Right
Button	btnCalcPants		Image: SubTotal.ico
TextBox	txtPantsSubTotal	0.00	TextAlign: Right
ComboBox	cboItem1	None
TextBox	txtItem1UnitPrice	0.00	TextAlign: Right
TextBox	txtItem1Quantity	0	TextAlign: Right
Button	btnCalcItem1		Image: SubTotal.ico
TextBox	txtItem1SubTotal	0.00	TextAlign: Right
ComboBox	cboItem2	None
TextBox	txtItem2UnitPrice	0.00	TextAlign: Right
TextBox	txtItem2Quantity	0	TextAlign: Right
Button	btnCalcItem2		Image: SubTotal.ico
TextBox	txtItem2SubTotal	0.00	TextAlign: Right
ComboBox	cboItem3	None
TextBox	txtItem3UnitPrice	0.00	TextAlign: Right
TextBox	txtItem3Quantity	0	TextAlign: Right
Button	btnCalcItem3		Image: SubTotal.ico
TextBox	txtItem3SubTotal	0.00	TextAlign: Right
ComboBox	cboItem4	None
TextBox	txtItem4UnitPrice	0.00	TextAlign: Right
TextBox	txtItem4Quantity	0	TextAlign: Right
Button	btnCalcItem4		Image: SubTotal.ico
TextBox	txtItem4SubTotal	0.00	TextAlign: Right
GroupBox		Order Summary
Label		Cleaning Total:
TextBox	txtOrderTotal	0.00	TextAlign: Right
Label		Tax Rate:
TextBox	txtTaxRate	5.75	TextAlign: Right
Label		%
Label		Tax Amount:
TextBox	txtTaxAmount	0.00	TextAlign: Right
Label		Order Total:
TextBox	txtOrderTotal	0.00	TextAlign: Right
Button	btnReset	Reset
Button	btnSave	Save
Label		Enter Receipt Number:
TextBox	txtReceiptNumber	0
Button	btnOpen	Open
Button	btnClose	Close

Click each combo box. Access its Items property and fill it up as follows:
Click OK and save All
On the Toolbox, click Data, click OleDbDataAdapter and click an unoccupied area of the form
In the first page of the Data Adapter Configuration Wizard, click Next
In the second page of the wizard, click the arrow of the combo box. If you see a string that contains ACCESS and gcs6, click it, and go to the next point.
If you don't see it in the list, click New Connection...
1. In the Data Link Properties, click the Provider tab and click Microsoft Jet 4.0 OLE DB Provider
2. Click Next
3. In the 1 text box, select the ellipsis button, locate and select the GCS6 database you created above
4. In the 2 section, clear the user name and make sure the Blank Password check box is marked
5. Click Test Connection:
6. Click OK twice
Click Next
Accept the Use SQL Statement option and click Next
Click Query Builder...
In the Add Table dialog box, make sure CleaningOrders is selected in the Tables tab. Click Add and click Close
Click the *(All Columns) check box and click OK
Click Next
Click Finish
Click Don't Include Password
On the main menu, click Data . Generate Dataset...
In the Generate Dataset dialog box, click the New radio button
Set the name to dsCleaningOrders
Click OK
Right-click the form and click View Code

Declare a Boolean variable named IsNewOrder

Public Class Form1
    Inherits System.Windows.Forms.Form

    Dim IsNewOrder As Boolean

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

In the Method Name combo box, select Load and implement the event as follows:

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        IsNewOrder = True
        Me.OleDbDataAdapter1.Fill(Me.DsCleaningOrders1)
End Sub

In the Class Name combo box, select btnReset

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
        IsNewOrder = True

        Me.txtCustomerName.Text = ""
        Me.txtCustomerPhone.Text = ""
        Me.dtpDateLeft.Value = DateTime.Today
        Me.dtpTimeLeft.Value = DateTime.Now
        Me.dtpDateExpected.Value = DateTime.Today
        Me.dtpTimeExpected.Value = DateTime.Now

        Me.txtShirtsUnitPrice.Text = "0.95"
        Me.txtShirtsQuantity.Text = "0"
        Me.txtShirtsSubTotal.Text = "0.00"
        Me.txtPantsUnitPrice.Text = "1.95"
        Me.txtPantsQuantity.Text = "0"
        Me.txtPantsSubTotal.Text = "0.00"

        Me.cboItem1.SelectedIndex = 0
        Me.txtItem1UnitPrice.Text = "0.00"
        Me.txtItem1Quantity.Text = "0"
        Me.txtItem1SubTotal.Text = "0.00"

        Me.cboItem2.SelectedIndex = 0
        Me.txtItem2UnitPrice.Text = "0.00"
        Me.txtItem2Quantity.Text = "0"
        Me.txtItem2SubTotal.Text = "0.00"

        Me.cboItem3.SelectedIndex = 0
        Me.txtItem3UnitPrice.Text = "0.00"
        Me.txtItem3Quantity.Text = "0"
        Me.txtItem3SubTotal.Text = "0.00"

        Me.cboItem4.SelectedIndex = 0
        Me.txtItem4UnitPrice.Text = "0.00"
        Me.txtItem4Quantity.Text = "0"
        Me.txtItem4SubTotal.Text = "0.00"

        Me.txtCleaningTotal.Text = "0.00"
        Me.txtTaxRate.Text = "5.75"
        Me.txtTaxAmount.Text = "0.00"
        Me.txtOrderTotal.Text = "0.00"
        Me.txtCustomerName.Focus()
End Sub

Under the above End Sub line, create the following method:

Private Sub CalculateCleaningOrder()
        Dim unitPriceShirts As Double = 0.95
        Dim unitPricePants As Double = 1.75
        Dim unitPrice1 As Double = 0.0
        Dim unitPrice2 As Double = 0.0
        Dim unitPrice3 As Double = 0.0
        Dim unitPrice4 As Double = 0.0

        Dim qtyShirts As Integer = 1
        Dim qtyPants As Integer = 1
        Dim quantity1 As Integer = 1
        Dim quantity2 As Integer = 1
        Dim quantity3 As Integer = 1
        Dim quantity4 As Integer = 1

        Dim subTotalShirts As Double = 0
        Dim subTotalPants As Double = 0
        Dim subTotal1 As Double = 0
        Dim subTotal2 As Double = 0
        Dim subTotal3 As Double = 0
        Dim subTotal4 As Double

        Dim cleaningTotal As Double = 0.0
        Dim taxRate As Double = 5.75
        Dim taxAmount As Double = 0.0
        Dim orderTotal As Double = 0.0

        ' 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(Me.txtShirtsUnitPrice.Text)
        Catch exc As FormatException
            MsgBox("The value you entered for the price of shirts is not valid" _
     & vbCrLf & "Please try again")
        End Try

        ' Retrieve the number of this item
        ' Just in case the user types an invalid value, we are using a try...catch
        Try
            qtyShirts = CInt(Me.txtShirtsQuantity.Text)
        Catch exc As FormatException
            MsgBox("The value you entered for the number of shirts is not valid" _
     & vbCrLf & "Please try again")
        End Try

        ' Calculate the sub-total for this item
        subTotalShirts = unitPriceShirts * qtyShirts

        ' Display the sub-total in the corresponding text box
        Me.txtShirtsSubTotal.Text = subTotalShirts.ToString("F")

        Try
            unitPricePants = CDbl(Me.txtPantsUnitPrice.Text)
        Catch exc As FormatException
	msgbox("The value you entered for the price of pants is not valid" _
					& vbcrlf & "Please try again")
        End Try

        Try
            qtyPants = CInt(Me.txtPantsQuantity.Text)
        Catch exc As FormatException
	msgbox("The value you entered for the number of pants is not valid" _
					& vbcrlf & "Please try again")
        End Try

        subTotalPants = unitPricePants * qtyPants
        Me.txtPantsSubTotal.Text = subTotalPants.ToString("F")

        Try
            unitPrice1 = CDbl(Me.txtItem1UnitPrice.Text)
        Catch exc As FormatException
            MsgBox("The value you entered for the price is not valid" _
     & vbCrLf & "Please try again")
        End Try

        Try
            quantity1 = CInt(Me.txtItem1Quantity.Text)
        Catch exc As FormatException
            MsgBox("The value you entered is not valid" _
    & vbCrLf & "Please try again")
        End Try

        subTotal1 = unitPrice1 * quantity1
        Me.txtItem1SubTotal.Text = subTotal1.ToString("F")

        Try
            unitPrice2 = CDbl(Me.txtItem2UnitPrice.Text)
        Catch exc As FormatException
	msgbox("The value you entered for the price is not valid" _
			& vbcrlf & "Please try again")
        End Try

        Try
            quantity2 = CInt(Me.txtItem2Quantity.Text)
        Catch exc As FormatException
	msgbox("The value you entered is not valid" _
			& vbcrlf & "Please try again")
        End Try

        subTotal2 = quantity2 * unitPrice2
        Me.txtItem2SubTotal.Text = subTotal2.ToString("F")

        Try
            quantity3 = CInt(Me.txtItem3Quantity.Text)
        Catch exc As FormatException
		msgbox("The value you entered is not valid" _
				& vbcrlf & "Please try again")
        End Try

        Try
            unitPrice3 = CDbl(Me.txtItem3UnitPrice.Text)
        Catch exc As FormatException
            MsgBox("The value you entered for the price is not valid" _
    & vbCrLf & "Please try again")
        End Try

        subTotal3 = quantity3 * unitPrice3
        Me.txtItem3SubTotal.Text = subTotal3.ToString("F")

        Try
            unitPrice4 = CDbl(Me.txtItem4UnitPrice.Text)
        Catch exc As FormatException
            msgbox("The value you entered for the price is not valid" _
              & vbcrlf & "Please try again")
        End Try

        Try
            quantity4 = CInt(Me.txtItem4Quantity.Text)
        Catch exc As FormatException
            msgbox("The value you entered is not valid" _
              & vbcrlf & "Please try again")
        End Try

        subTotal4 = quantity4 * unitPrice4
        Me.txtItem4SubTotal.Text = subTotal4.ToString("F")

        ' Calculate the total
        cleaningTotal = subTotalShirts + subTotalPants + subTotal1 + _
   subTotal2 + subTotal3 + subTotal4

        ' Retrieve the value of the tax rate
        Try
            taxRate = CDbl(Me.txtTaxRate.Text)
        Catch exc As FormatException
            msgbox("The tax rate you entered is invalid" _
              & vbcrlf & "Please try again")
        End Try

        ' Calculate the amount owed for the taxes
        taxAmount = cleaningTotal * taxRate / 100
        ' Add the tax amount to the total order
        orderTotal = cleaningTotal + taxAmount

        ' Display the values of the order summary
        Me.txtCleaningTotal.Text = cleaningTotal.ToString("F")
        Me.txtTaxAmount.Text = taxAmount.ToString("F")
        Me.txtOrderTotal.Text = orderTotal.ToString("F")
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
        If IsNewOrder = True Then
            Dim strInsert As String = "INSERT INTO CleaningOrders(" & _
             "CustomerName, CustomerPhone, DateLeft, " & _
             "TimeLeft, DateExpected, TimeExpected, " & _
             "ShirtsUnitPrice, ShirtsQuantity, " & _
             "ShirtsSubTotal, PantsUnitPrice, " & _
             "PantsQuantity, PantsSubTotal, Item1Name, " & _
             "Item1UnitPrice, Item1Quantity, " & _
             "Item1SubTotal, Item2Name, Item2UnitPrice, " & _
             "Item2Quantity, Item2SubTotal, Item3Name, " & _
             "Item3UnitPrice, Item3Quantity, " & _
             "Item3SubTotal, Item4Name, Item4UnitPrice, " & _
            "Item4Quantity, Item4SubTotal, CleaningTotal, " & _
             "TaxRate, TaxAmount, OrderTotal) VALUES(" & _
             "'" & txtCustomerName.Text & _
             "', '" & txtCustomerPhone.Text & _
             "', '" & dtpDateLeft.Value & _
             "', '" & dtpTimeLeft.Value & _
             "', '" & dtpDateExpected.Value & _
             "', '" & dtpTimeExpected.Value & _
             "', '" & txtShirtsUnitPrice.Text & _
             "', '" & txtShirtsQuantity.Text & _
             "', '" & txtShirtsSubTotal.Text & _
             "', '" & txtPantsUnitPrice.Text & _
             "', '" & txtPantsQuantity.Text & _
             "', '" & txtPantsSubTotal.Text & _
             "', '" & cboItem1.Text & _
             "', '" & txtItem1UnitPrice.Text & _
             "', '" & txtItem1Quantity.Text & _
             "', '" & txtItem1SubTotal.Text & _
             "', '" & cboItem2.Text & _
             "', '" & txtItem2UnitPrice.Text & _
             "', '" & txtItem2Quantity.Text & _
             "', '" & txtItem2SubTotal.Text & _
             "', '" & cboItem3.Text & _
             "', '" & txtItem3UnitPrice.Text & _
             "', '" & txtItem3Quantity.Text & _
             "', '" & txtItem3SubTotal.Text & _
             "', '" & cboItem4.Text & _
             "', '" & txtItem4UnitPrice.Text & _
             "', '" & txtItem4Quantity.Text & _
             "', '" & txtItem4SubTotal.Text & _
             "', '" & txtCleaningTotal.Text & _
             "', '" & txtTaxRate.Text & _
             "', '" & txtTaxAmount.Text & _
             "', '" & txtOrderTotal.Text & "');"

            Dim cmdDatabase As System.Data.OleDb.OleDbCommand = New System.Data.OleDb.OleDbCommand(strInsert, OleDbConnection1)

            OleDbConnection1.Open()

            cmdDatabase.ExecuteNonQuery()
            OleDbConnection1.Close()

            Me.btnReset_Click(sender, e)
        End If
End Sub

Save all

The Data View in ADO.NET

In a database environment, a query is a list of values created from another list. For example, a query can be created from a table by isolating records that follow a criterion. The .NET Framework supports queries through the DataView class. With a DataView object, you can create a selected list of records and you can then perform all types of regular operations of a database object, including:

Sorting Records
Filtering Records
Creating a new record
Updating one or more existing records

To create a DataView object, you can first declare a pointer to DataView. If you are working in Microsoft Visual Studio, in the Data section of the Toolbox, you can click the DataView button and click a container such as a form. After creating a DataView object, to perform a desired operation on it, you can use the SQL.

Practical Learning: Using a Data View

In the Data section of the Toolbox, click DataView and click the form
In the Properties window, change its values as follows:
(Name): dvwCleaningOrder
Table: DsCleaningOrders1.CleaningOrders

On the form, double-click the Open button and implement the event as follows:

Private Sub btnOpen_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnOpen.Click
        If Me.txtReceiptNumber.Text = "" Then
            MsgBox("Please enter a receipt number")
            Me.txtReceiptNumber.Focus()
            Return
        End If

        Dim iReceiptNumber As Integer = CInt(Me.txtReceiptNumber.Text)

        Me.OleDbDataAdapter1.Fill(Me.DsCleaningOrders1)
        Me.dvwCleaningOrder.RowFilter = "CleaningOrderID = '" & iReceiptNumber.ToString() & "'"

        Me.txtCustomerName.DataBindings.Clear()
        Me.txtCustomerPhone.DataBindings.Clear()
        Me.dtpDateLeft.DataBindings.Clear()
        Me.dtpTimeLeft.DataBindings.Clear()
        Me.dtpDateExpected.DataBindings.Clear()
        Me.dtpTimeExpected.DataBindings.Clear()
        Me.txtShirtsUnitPrice.DataBindings.Clear()
        Me.txtShirtsQuantity.DataBindings.Clear()
        Me.txtShirtsSubTotal.DataBindings.Clear()
        Me.txtPantsUnitPrice.DataBindings.Clear()
        Me.txtPantsQuantity.DataBindings.Clear()
        Me.txtPantsSubTotal.DataBindings.Clear()
        Me.cboItem1.DataBindings.Clear()
        Me.txtItem1UnitPrice.DataBindings.Clear()
        Me.txtItem1Quantity.DataBindings.Clear()
        Me.txtItem1SubTotal.DataBindings.Clear()
        Me.cboItem2.DataBindings.Clear()
        Me.txtItem2UnitPrice.DataBindings.Clear()
        Me.txtItem2Quantity.DataBindings.Clear()
        Me.txtItem2SubTotal.DataBindings.Clear()
        Me.cboItem3.DataBindings.Clear()
        Me.txtItem3UnitPrice.DataBindings.Clear()
        Me.txtItem3Quantity.DataBindings.Clear()
        Me.txtItem3SubTotal.DataBindings.Clear()
        Me.cboItem4.DataBindings.Clear()
        Me.txtItem4UnitPrice.DataBindings.Clear()
        Me.txtItem4Quantity.DataBindings.Clear()
        Me.txtItem4SubTotal.DataBindings.Clear()
        Me.txtCleaningTotal.DataBindings.Clear()
        Me.txtTaxRate.DataBindings.Clear()
        Me.txtTaxAmount.DataBindings.Clear()
        Me.txtOrderTotal.DataBindings.Clear()

        Me.txtCustomerName.DataBindings.Add("Text", Me.dvwCleaningOrder, "CustomerName")
        Me.txtCustomerPhone.DataBindings.Add("Text", Me.dvwCleaningOrder, "CustomerPhone")
        Me.dtpDateLeft.DataBindings.Add("Value", Me.dvwCleaningOrder, "DateLeft")
        Me.dtpTimeLeft.DataBindings.Add("Value", Me.dvwCleaningOrder, "TimeLeft")
        Me.dtpDateExpected.DataBindings.Add("Value", Me.dvwCleaningOrder, "DateExpected")
        Me.dtpTimeExpected.DataBindings.Add("Value", Me.dvwCleaningOrder, "TimeExpected")
        Me.txtShirtsUnitPrice.DataBindings.Add("Text", Me.dvwCleaningOrder, "ShirtsUnitPrice")
        Me.txtShirtsQuantity.DataBindings.Add("Text", Me.dvwCleaningOrder, "ShirtsQuantity")
        Me.txtShirtsSubTotal.DataBindings.Add("Text", Me.dvwCleaningOrder, "ShirtsSubTotal")
        Me.txtPantsUnitPrice.DataBindings.Add("Text", Me.dvwCleaningOrder, "PantsUnitPrice")
        Me.txtPantsQuantity.DataBindings.Add("Text", Me.dvwCleaningOrder, "PantsQuantity")
        Me.txtPantsSubTotal.DataBindings.Add("Text", Me.dvwCleaningOrder, "PantsSubTotal")
        Me.cboItem1.DataBindings.Add("Text", Me.dvwCleaningOrder, "Item1Name")
        Me.txtItem1UnitPrice.DataBindings.Add("Text", Me.dvwCleaningOrder, "Item1UnitPrice")
        Me.txtItem1Quantity.DataBindings.Add("Text", Me.dvwCleaningOrder, "Item1Quantity")
        Me.txtItem1SubTotal.DataBindings.Add("Text", Me.dvwCleaningOrder, "Item1SubTotal")
        Me.cboItem2.DataBindings.Add("Text", Me.dvwCleaningOrder, "Item2Name")
        Me.txtItem2UnitPrice.DataBindings.Add("Text", Me.dvwCleaningOrder, "Item2UnitPrice")
        Me.txtItem2Quantity.DataBindings.Add("Text", Me.dvwCleaningOrder, "Item2Quantity")
        Me.txtItem2SubTotal.DataBindings.Add("Text", Me.dvwCleaningOrder, "Item2SubTotal")
        Me.cboItem3.DataBindings.Add("Text", Me.dvwCleaningOrder, "Item3Name")
        Me.txtItem3UnitPrice.DataBindings.Add("Text", Me.dvwCleaningOrder, "Item3UnitPrice")
        Me.txtItem3Quantity.DataBindings.Add("Text", Me.dvwCleaningOrder, "Item3Quantity")
        Me.txtItem3SubTotal.DataBindings.Add("Text", Me.dvwCleaningOrder, "Item3SubTotal")
        Me.cboItem4.DataBindings.Add("Text", Me.dvwCleaningOrder, "Item4Name")
        Me.txtItem4UnitPrice.DataBindings.Add("Text", Me.dvwCleaningOrder, "Item4UnitPrice")
        Me.txtItem4Quantity.DataBindings.Add("Text", Me.dvwCleaningOrder, "Item4Quantity")
        Me.txtItem4SubTotal.DataBindings.Add("Text", Me.dvwCleaningOrder, "Item4SubTotal")
        Me.txtCleaningTotal.DataBindings.Add("Text", Me.dvwCleaningOrder, "CleaningTotal")
        Me.txtTaxRate.DataBindings.Add("Text", Me.dvwCleaningOrder, "TaxRate")
        Me.txtTaxAmount.DataBindings.Add("Text", Me.dvwCleaningOrder, "TaxAmount")
        Me.txtOrderTotal.DataBindings.Add("Text", Me.dvwCleaningOrder, "OrderTotal")

        IsNewOrder = False
    End Sub

Under the above End Sub line, create the following method:

' This method is used to update an existing record if the user changes any of its values
    Private Sub UpdateCleaningOrder()
        ' Find out if this is a new, usually started as blank, order
        ' If it is, trust that the user will save it by clicking the Save button
        ' If it's not, then, if the user changed the string that was in the
        ' Customer Name text box, update the current record
        If IsNewOrder = False Then
            Dim strUpdate As String = "UPDATE CleaningOrders " & _
                                "SET CustomerName = '" & Me.txtCustomerName.Text & "', " & _
            "CustomerPhone = '" & Me.txtCustomerPhone.Text & "', " & _
            "DateLeft = '" & Me.dtpDateLeft.Value.ToString() & "', " & _
            "TimeLeft = '" & Me.dtpTimeLeft.Value.ToString() & "', " & _
           "DateExpected = '" & Me.dtpDateExpected.Value.ToString() & "', " & _
           "TimeExpected = '" & Me.dtpTimeExpected.Value.ToString() & "', " & _
           "ShirtsUnitPrice = '" & Me.txtShirtsUnitPrice.Text & "', " & _
           "ShirtsQuantity = '" & Me.txtShirtsQuantity.Text & "', " & _
           "ShirtsSubTotal = '" & Me.txtShirtsSubTotal.Text & "', " & _
           "PantsUnitPrice = '" & Me.txtPantsUnitPrice.Text & "', " & _
           "PantsQuantity = '" & Me.txtPantsQuantity.Text & "', " & _
           "PantsSubTotal = '" & Me.txtPantsSubTotal.Text & "', " & _
           "Item1Name = '" & Me.cboItem1.Text & "', " & _
           "Item1UnitPrice = '" & Me.txtItem1UnitPrice.Text & "', " & _
           "Item1Quantity = '" & Me.txtItem1Quantity.Text & "', " & _
           "Item1SubTotal = '" & Me.txtItem1SubTotal.Text & "', " & _
           "Item2Name = '" & Me.cboItem2.Text & "', " & _
           "Item2UnitPrice = '" & Me.txtItem2UnitPrice.Text & "', " & _
           "Item2Quantity = '" & Me.txtItem2Quantity.Text & "', " & _
           "Item2SubTotal = '" & Me.txtItem2SubTotal.Text & "', " & _
           "Item3Name = '" & Me.cboItem3.Text & "', " & _
           "Item3UnitPrice = '" & Me.txtItem3UnitPrice.Text & "', " & _
           "Item3Quantity = '" & Me.txtItem3Quantity.Text & "', " & _
           "Item3SubTotal = '" & Me.txtItem3SubTotal.Text & "', " & _
           "Item4Name = '" & Me.cboItem4.Text & "', " & _
           "Item4UnitPrice = '" & Me.txtItem4UnitPrice.Text & "', " & _
           "Item4Quantity = '" & Me.txtItem4Quantity.Text & "', " & _
           "Item4SubTotal = '" & Me.txtItem4SubTotal.Text & "', " & _
           "CleaningTotal = '" & Me.txtCleaningTotal.Text & "', " & _
           "TaxRate = '" & Me.txtTaxRate.Text & "', " & _
           "TaxAmount = '" & Me.txtTaxAmount.Text & "', " & _
           "OrderTotal = '" & Me.txtOrderTotal.Text & "' " & _
           "WHERE CleaningOrderID = '" & _
           Me.txtReceiptNumber.Text & "';"

            Dim cmdDatabase As System.Data.OleDb.OleDbCommand = New _
   System.Data.OleDb.OleDbCommand(strUpdate, OleDbConnection1)

            OleDbConnection1.Open()

            cmdDatabase.ExecuteNonQuery()
            OleDbConnection1.Close()
        End If
End Sub

In the Class Name combo box, select txtCustomerName

In the Method Name combo box, select Leave and implement the event as follows:

Private Sub txtCustomerName_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtCustomerName.Leave
        If IsNewOrder = False Then UpdateCleaningOrder()
End Sub

In the Class Name combo box, select txtCustomerPhone

In the Method Name combo box, select Leave and implement its event as follows:

Private Sub txtCustomerPhone_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtCustomerPhone.Leave
        If IsNewOrder = False Then UpdateCleaningOrder()
End Sub

In the Class Name combo box, select dtpDateLeft

In the Method Name combo box, select ValueChanged and implement it as follows:

Private Sub dtpDateLeft_ValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dtpDateLeft.ValueChanged
        Dim dateLeft As New DateTime
        Dim timeLeft As New DateTime
        Dim time9AM As New DateTime

        dateLeft = Me.dtpDateLeft.Value
        timeLeft = Me.dtpTimeLeft.Value
        time9AM = 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
            Me.dtpDateExpected.Value = dateLeft
            Me.dtpTimeExpected.Value = New DateTime(dateLeft.Year, dateLeft.Month, dateLeft.Day, 17, 0, 0)
        Else
            ' If the clothese were left after 9AM, they will be availablethe following morning at 8AM
            Me.dtpDateExpected.Value = New DateTime(dateLeft.Year, dateLeft.Month, dateLeft.Day + 1)
            Me.dtpTimeExpected.Value = New DateTime(dateLeft.Year, dateLeft.Month, dateLeft.Day + 1, 8, 0, 0)
        End If

        If IsNewOrder = False Then UpdateCleaningOrder()
End Sub

In the Class Name combo box, select dtpTimeLeft

In the Method Name combo box, select ValueChanged and implement it as follows:

Private Sub dtpTimeLeft_ValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dtpTimeLeft.ValueChanged
        Dim dateLeft As New DateTime
        Dim timeLeft As New DateTime
        Dim time9AM As New DateTime

        dateLeft = Me.dtpDateLeft.Value
        timeLeft = Me.dtpTimeLeft.Value
        time9AM = 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
            Me.dtpDateExpected.Value = dateLeft
            Me.dtpTimeExpected.Value = New DateTime(dateLeft.Year, dateLeft.Month, dateLeft.Day, 17, 0, 0)
        Else
            ' If the clothese were left after 9AM, they will be availablethe following morning at 8AM
            Me.dtpDateExpected.Value = New DateTime(dateLeft.Year, dateLeft.Month, dateLeft.Day + 1)
            Me.dtpTimeExpected.Value = New DateTime(dateLeft.Year, dateLeft.Month, dateLeft.Day + 1, 8, 0, 0)
        End If

        If IsNewOrder = False Then UpdateCleaningOrder()
End Sub

In the Class Name combo box, select dtpDateExpected

In the Method Name combo box, select ValueChanged and implement its event as follows:

Private Sub dtpDateExpected_ValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dtpDateExpected.ValueChanged
        If IsNewOrder = False Then UpdateCleaningOrder()
End Sub

In the Class Name combo box, select dtpTimeExpected

In the Method Name combo box, select ValueChanged and implement its event as follows:

Private Sub dtpTimeExpected_ValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dtpTimeExpected.ValueChanged
        If IsNewOrder = False Then UpdateCleaningOrder()
End Sub

In the Class Name combo box, select txtShirtsUnitPrice

In the Method Name combo box, select Leave and implement its event as follows:

Private Sub txtShirtsUnitPrice_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtShirtsUnitPrice.Leave
        CalculateCleaningOrder()

        If IsNewOrder = False Then UpdateCleaningOrder()
End Sub

In the Class Name combo box, select txtShirtsQuantity

In the Method Name combo box, select Leave and implement its event as follows:

Private Sub txtShirtsQuantity_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtShirtsQuantity.Leave
        CalculateCleaningOrder()

        If IsNewOrder = False Then UpdateCleaningOrder()
End Sub

In the Class Name combo box, select txtPantsUnitPrice

In the Method Name combo box, select Leave and implement its event as follows:

Private Sub txtPantsUnitPrice_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtPantsUnitPrice.Leave
        CalculateCleaningOrder()

        If IsNewOrder = False Then UpdateCleaningOrder()
End Sub

In the Class Name combo box, select txtPantsQuantity

In the Method Name combo box, select Leave and implement its event as follows:

Private Sub txtPantsQuantity_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtPantsQuantity.Leave
        CalculateCleaningOrder()

        If IsNewOrder = False Then UpdateCleaningOrder()
End Sub

In the Class Name combo box, select cboItem1

In the Method Name combo box, select Leave and implement its event as follows:

Private Sub cboItem1_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboItem1.Leave
        If Me.cboItem1.Text = "None" Then
            Me.txtItem1UnitPrice.Text = "0.00"
            Me.txtItem1Quantity.Text = "0"
            Me.txtItem1SubTotal.Text = "0.00"
        End If

        CalculateCleaningOrder()

        If IsNewOrder = False Then UpdateCleaningOrder()
End Sub

In the Class Name combo box, select txtItem1UnitPrice

In the Method Name combo box, select Leave and implement its event as follows:

Private Sub txtItem1UnitPrice_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtItem1UnitPrice.Leave
        CalculateCleaningOrder()

        If IsNewOrder = False Then UpdateCleaningOrder()
End Sub

In the Class Name combo box, select txtItem1Quantity

In the Method Name combo box, select Leave and implement its event as follows:

Private Sub txtItem1Quantity_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtItem1Quantity.Leave
        CalculateCleaningOrder()

        If IsNewOrder = False Then UpdateCleaningOrder()
End Sub

In the Class Name combo box, select cboItem2

In the Method Name combo box, select Leave and implement its event as follows:

Private Sub cboItem2_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboItem2.Leave
        If Me.cboItem2.Text = "None" Then
            Me.txtItem2UnitPrice.Text = "0.00"
            Me.txtItem2Quantity.Text = "0"
            Me.txtItem2SubTotal.Text = "0.00"
        End If

        CalculateCleaningOrder()

        If IsNewOrder = False Then UpdateCleaningOrder()
End Sub

In the Class Name combo box, select txtItem2UnitPrice

In the Method Name combo box, select Leave and implement its event as follows:

Private Sub txtItem2UnitPrice_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtItem2UnitPrice.Leave
        CalculateCleaningOrder()

        If IsNewOrder = False Then UpdateCleaningOrder()
End Sub

In the Class Name combo box, select txtItem2Quantity

In the Method Name combo box, select Leave and implement its event as follows:

Private Sub txtItem2Quantity_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtItem2Quantity.Leave
        CalculateCleaningOrder()

        If IsNewOrder = False Then UpdateCleaningOrder()
End Sub

In the Class Name combo box, select cboItem3

In the Method Name combo box, select Leave and implement its event as follows:

Private Sub cboItem3_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboItem3.Leave
        If Me.cboItem3.Text = "None" Then
            Me.txtItem3UnitPrice.Text = "0.00"
            Me.txtItem3Quantity.Text = "0"
            Me.txtItem3SubTotal.Text = "0.00"
        End If

        CalculateCleaningOrder()

        If IsNewOrder = False Then UpdateCleaningOrder()
End Sub

In the Class Name combo box, select txtItem3UnitPrice

In the Method Name combo box, select Leave and implement its event as follows:

Private Sub txtItem3UnitPrice_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtItem3UnitPrice.Leave
        CalculateCleaningOrder()

        If IsNewOrder = False Then UpdateCleaningOrder()
End Sub

In the Class Name combo box, select txtItem3Quantity

In the Method Name combo box, select Leave and implement its event as follows:

Private Sub txtItem3Quantity_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtItem3Quantity.Leave
        CalculateCleaningOrder()

        If IsNewOrder = False Then UpdateCleaningOrder()
End Sub

In the Class Name combo box, select cboItem4

In the Method Name combo box, select Leave and implement its event as follows:

Private Sub cboItem4_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboItem4.Leave
        If Me.cboItem4.Text = "None" Then
            Me.txtItem4UnitPrice.Text = "0.00"
            Me.txtItem4Quantity.Text = "0"
            Me.txtItem4SubTotal.Text = "0.00"
        End If

        CalculateCleaningOrder()

        If IsNewOrder = False Then UpdateCleaningOrder()
End Sub

In the Class Name combo box, select txtItem4UnitPrice

In the Method Name combo box, select Leave and implement its event as follows:

Private Sub txtItem4UnitPrice_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtItem4UnitPrice.Leave
        CalculateCleaningOrder()

        If IsNewOrder = False Then UpdateCleaningOrder()
End Sub

In the Class Name combo box, select txtItem4Quantity

In the Method Name combo box, select Leave and implement its event as follows:

Private Sub txtItem4Quantity_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtItem4Quantity.Leave
        CalculateCleaningOrder()

        If IsNewOrder = False Then UpdateCleaningOrder()
End Sub

In the Class Name combo box, select txtTaxRate

In the Method Name combo box, select Leave and implement its event as follows:

Private Sub txtTaxRate_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtTaxRate.Leave
        CalculateCleaningOrder()

        If IsNewOrder = False Then UpdateCleaningOrder()
End Sub

In the Class Name combo box, select btnClose

In the Method Name combo box, select Click and implement its 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 and create a few cleaning orders
Close the form
Execute the application again and try opening a cleaning order whose receipt number is 1001, then 1002, then 1003
Change some values of an order
Close the form