Home

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

  1. Start Microsoft Visual Studio .NET or Visual Basic .NET and create a Windows Forms Application named GCS4
  2. Design the form as follows:
     
    Control Name Text Additional Properties
    GroupBox GroupBox   Order Identification  
    Label Label   Customer Name:  
    TextBox TextBox txtCustomerName    
    Label Label   Customer Phone:  
    TextBox TextBox txtCustomerPhone    
    Label Label   Date Left:  
    DateTimePicker DateTimePicker dtpDateLeft   Format: Custom
    Custom Format: dddd MMM dd, yyyy
    Label Label   Time Left:  
    DateTimePicker DateTimePicker dtpTimeLeft   Format: Time
    Label Label   Date Expected:  
    DateTimePicker DateTimePicker dtpDateExpected   Format: Custom
    Custom Format: dddd MMM dd, yyyy
    Label     Time Expected:  
    DateTimePicker DateTimePicker dtpTimeExpected   Format: Time
    GroupBox GroupBox   Order Processing  
    Label Label   Item Type  
    Label Label   Unit Price  
    Label Label   Qty  
    Label Label   Sub Total  
    Label Label   Shirts  
    TextBox TextBox txtShirtsUnitPrice 0.95 TextAlign: Right
    TextBox TextBox txtShirtsQuantity 0 TextAlign: Right
    Button Button btnCalcShirts   Image: SubTotal.ico
    TextBox TextBox txtShirtsSubTotal 0.00 TextAlign: Right
    Label Label   Pants  
    TextBox TextBox txtPantsUnitPrice 1.75 TextAlign: Right
    TextBox TextBox txtPantsQuantity   TextAlign: Right
    Button Button btnCalcPants   Image: SubTotal.ico
    TextBox TextBox txtPantsSubTotal 0.00 TextAlign: Right
    ComboBox ComboBox cboItem1 None  
    TextBox TextBox txtItem1UnitPrice 0.00 TextAlign: Right
    TextBox TextBox txtItem1Quantity 0 TextAlign: Right
    Button Button btnCalcItem1   Image: SubTotal.ico
    TextBox TextBox txtItem1SubTotal 0.00 TextAlign: Right
    ComboBox ComboBox cboItem2 None  
    TextBox TextBox txtItem2UnitPrice 0.00 TextAlign: Right
    TextBox TextBox txtItem2Quantity 0 TextAlign: Right
    Button Button btnCalcItem2   Image: SubTotal.ico
    TextBox TextBox txtItem2SubTotal 0.00 TextAlign: Right
    ComboBox ComboBox cboItem3 None  
    TextBox TextBox txtItem3UnitPrice 0.00 TextAlign: Right
    TextBox TextBox txtItem3Quantity 0 TextAlign: Right
    Button Button btnCalcItem3   Image: SubTotal.ico
    TextBox TextBox txtItem3SubTotal 0.00 TextAlign: Right
    ComboBox ComboBox cboItem4 None  
    TextBox TextBox txtItem4UnitPrice 0.00 TextAlign: Right
    TextBox TextBox txtItem4Quantity 0 TextAlign: Right
    Button Button btnCalcItem4   Image: SubTotal.ico
    TextBox TextBox txtItem4SubTotal 0.00 TextAlign: Right
    GroupBox GroupBox   Order Summary  
    Label Label   Cleaning Total:  
    TextBox TextBox txtOrderTotal 0.00 TextAlign: Right
    Label Label   Tax Rate:  
    TextBox TextBox txtTaxRate 5.75 TextAlign: Right
    Label Label   %  
    Label Label   Tax Amount:  
    TextBox TextBox txtTaxAmount 0.00 TextAlign: Right
    Label Label   Order Total:  
    TextBox TextBox txtOrderTotal 0.00 TextAlign: Right
    Button Button btnReset Reset  
    Button Button btnSave Save  
    Label Label   Enter Receipt Number:  
    TextBox TextBox txtReceiptNumber 0  
    Button Button btnOpen Open  
    Button Button btnClose Close  
  3. Click each combo box. Access its Items property and fill it up as follows:
     
  4. Click OK and save All
  5. On the Toolbox, click Data, click OleDbDataAdapter and click an unoccupied area of the form
  6. In the first page of the Data Adapter Configuration Wizard, click Next
  7. 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
       
  8. Click Next
  9. Accept the Use SQL Statement option and click Next
  10. Click Query Builder...
  11. In the Add Table dialog box, make sure CleaningOrders is selected in the Tables tab. Click Add and click Close
  12. Click the *(All Columns) check box and click OK
  13. Click Next
     
    Data Adapter Configuration Wizard
  14. Click Finish
  15. Click Don't Include Password
  16. On the main menu, click Data . Generate Dataset...
  17. In the Generate Dataset dialog box, click the New radio button
  18. Set the name to dsCleaningOrders
     
  19. Click OK
  20. Right-click the form and click View Code
  21. Declare a Boolean variable named IsNewOrder
     
    Public Class Form1
        Inherits System.Windows.Forms.Form
    
        Dim IsNewOrder As Boolean
  22. In the Class Name combo box, select (Form1 Events)
  23. 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
  24. In the Class Name combo box, select btnReset
  25. 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
  26. 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
  27. In the Class Name combo box, select btnSave
  28. 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
  29. 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 Practical Learning: Using a Data View

 
  1. In the Data section of the Toolbox, click DataView and click the form
  2. In the Properties window, change its values as follows:
    (Name): dvwCleaningOrder
    Table: DsCleaningOrders1.CleaningOrders
  3. 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
  4. 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
  5. In the Class Name combo box, select txtCustomerName
  6. 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
  7. In the Class Name combo box, select txtCustomerPhone
  8. 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
  9. In the Class Name combo box, select dtpDateLeft
  10. 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
  11. In the Class Name combo box, select dtpTimeLeft
  12. 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
  13. In the Class Name combo box, select dtpDateExpected
  14. 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
  15. In the Class Name combo box, select dtpTimeExpected
  16. 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
  17. In the Class Name combo box, select txtShirtsUnitPrice
  18. 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
  19. In the Class Name combo box, select txtShirtsQuantity 
  20. 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
  21. In the Class Name combo box, select txtPantsUnitPrice 
  22. 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
  23. In the Class Name combo box, select txtPantsQuantity
  24. 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
  25. In the Class Name combo box, select cboItem1
  26. 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
  27. In the Class Name combo box, select txtItem1UnitPrice
  28. 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
  29. In the Class Name combo box, select txtItem1Quantity
  30. 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
  31. In the Class Name combo box, select cboItem2
  32. 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
  33. In the Class Name combo box, select txtItem2UnitPrice
  34. 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
  35. In the Class Name combo box, select txtItem2Quantity
  36. 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
  37. In the Class Name combo box, select cboItem3
  38. 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
  39. In the Class Name combo box, select txtItem3UnitPrice
  40. 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
  41. In the Class Name combo box, select txtItem3Quantity
  42. 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
  43. In the Class Name combo box, select cboItem4
  44. 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
  45. In the Class Name combo box, select txtItem4UnitPrice
  46. 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
  47. In the Class Name combo box, select txtItem4Quantity
  48. 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
  49. In the Class Name combo box, select txtTaxRate
  50. 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
  51. In the Class Name combo box, select btnClose
  52. 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
  53. Execute the application and create a few cleaning orders
     
    Georgetown Cleaning Services - Cleaning Order
    Georgetown Cleaning Services - Cleaning Order
  54. Close the form
  55. Execute the application again and try opening a cleaning order whose receipt number is 1001, then 1002, then 1003
  56. Change some values of an order
  57. Close the form

 
 

Previous Copyright © 2005-2012 FunctionX