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. Still in Microsoft Access, to create a new form, on the main menu, click Insert -> Form
  2. In the New Form dialog box, double-click Design View
  3. While in Design View, double-click the button at the intersection of the rulers to access the Properties window
  4. In the Properties window, click Event and double-click On Load
  5. Click its ellipsis button and implement the event as follows:
     
    Private Sub Form_Load()
        Dim conDatabase As ADODB.Connection
        Dim strSQL As String
    
            Set conDatabase = Application.CurrentProject.Connection
        
            strSQL = "CREATE VIEW CleaningOrder " & _
                     "AS SELECT CustomerName, CustomerPhone, DateLeft, " & _
                     "TimeLeft, DateExpected, TimeExpected, " & _
                     "ShirtsUnitPrice, ShirtsQuantity, ShirtsSubTotal, " & _
                     "PantsUnitPrice, PantsQuantity, Item1Name, " & _
                     "Item1UnitPrice, Item1Quantity, Item1SubTotal, " & _
                     "Item2Name, Item2UnitPrice, Item2Quantity, " & _
                     "Item2SubTotal, Item3Name, Item3UnitPrice, " & _
                     "Item3Quantity, Item3SubTotal, Item4Name, " & _
                     "Item4UnitPrice, Item4Quantity, Item4SubTotal, " & _
                     "CleaningTotal, TaxRate, TaxAmount, " & _
                     "OrderTotal FROM CleaningOrders;"
    
        conDatabase.Execute strSQL
        MsgBox "A data view named CleaningOrder has been created"
        
        conDatabase.Close
        Set conDatabase = Nothing
    End Sub
  6. Close Microsoft Visual Basic and return to Microsoft Access
  7. Switch the the form to Form View to create the view
  8. After viewing the message box that states that a data view has been created, click OK
  9. Close the form
  10. When asked whether you want to save the form, (make sure you) click No
  11. Close Microsoft Access
  12. Start Microsoft Visual Studio .NET or Visual C++ .NET and create a Windows Forms Application named GCS4
  13. 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  
  14. Click each combo box. Access its Items property and fill it up as follows:
     
  15. Click OK and save All
  16. On the Toolbox, click Data, click OleDbDataAdapter and click an unoccupied area of the form
  17. In the first page of the Data Adapter Configuration Wizard, click Next
  18. 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
       
  19. Click Next
  20. Accept the Use SQL Statement option and click Next
  21. Click Query Builder...
  22. In the Add Table dialog box, make sure CleaningOrders is selected in the Tables tab. Click Add and click Close
  23. Click the *(All Columns) check box and click OK
  24. Click Next
     
    Data Adapter Configuration Wizard
  25. Click Finish
  26. Click Don't Include Password
  27. On the main menu, click Data -> Generate Dataset...
  28. In the Generate Dataset dialog box, click the New radio button
  29. Set the name to dsCleaningOrders
     
  30. Click OK
  31. Right-click the form and click View Code
  32. Declare a private Boolean variable named IsNewOrder
     
    bool IsNewOrder;
  33. Return to the form, double-click an unoccupied area of the form to generate its Load event and implement it as follows:
     
    System::Void Form1_Load(System::Object *  sender, System::EventArgs *  e)
    {
    	 IsNewOrder = true;
    	 this->oleDbDataAdapter1->Fill(this->dsCleaningOrders1);
    }
  34. Return to the form, double-click the Reset button and implement its Click event as follows:
     
    private: System::Void btnReset_Click(System::Object *  sender, System::EventArgs *  e)
    {
    	 IsNewOrder = true;
    
    	 this->txtCustomerName->Text = S"";
    	 this->txtCustomerPhone->Text = S"";
    	 this->dtpDateLeft->Value = DateTime::Today;
    	 this->dtpTimeLeft->Value = DateTime::Now;
    	 this->dtpDateExpected->Value = DateTime::Today;
    	 this->dtpTimeExpected->Value = DateTime::Now;
    
    	 this->txtShirtsUnitPrice->Text = S"0.95";
    	 this->txtShirtsQuantity->Text = S"0";
    	 this->txtShirtsSubTotal->Text = S"0.00";
    	 this->txtPantsUnitPrice->Text = S"1.95";
    	 this->txtPantsQuantity->Text  = S"0";
    	 this->txtPantsSubTotal->Text  = S"0.00";
    
    	 this->cboItem1->SelectedIndex = 0;
    	 this->txtItem1UnitPrice->Text = S"0.00";
    	 this->txtItem1Quantity->Text  = S"0";
    	 this->txtItem1SubTotal->Text  = S"0.00";
    
    	 this->cboItem2->SelectedIndex = 0;
    	 this->txtItem2UnitPrice->Text = S"0.00";
    	 this->txtItem2Quantity->Text  = S"0";
    	 this->txtItem2SubTotal->Text  = S"0.00";
    
    	 this->cboItem3->SelectedIndex = 0;
    	 this->txtItem3UnitPrice->Text = S"0.00";
    	 this->txtItem3Quantity->Text  = S"0";
    	 this->txtItem3SubTotal->Text  = S"0.00";
    
    	 this->cboItem4->SelectedIndex = 0;
    	 this->txtItem4UnitPrice->Text = S"0.00";
    	 this->txtItem4Quantity->Text  = S"0";
    	 this->txtItem4SubTotal->Text  = S"0.00";
    
    	 this->txtCleaningTotal->Text = S"0.00";
    	 this->txtTaxRate->Text = S"5.75";
    	 this->txtTaxAmount->Text = S"0.00";
    	 this->txtOrderTotal->Text = S"0.00";
             this->txtReceiptNumber->Text = S"0"
    	 this->txtCustomerName->Focus();
    }
  35. In the Class View, expand GCS3 followed by GCS3 and Form1
  36. Right-click Form1 -> Add -> Add Function...
  37. Set the characteristics as follows:
    Return Type: void
    Function Name: CalculateCleaningOrder
    Access: private
  38. Click Finish and implement the method as follows:
     
    void CalculateCleaningOrder(void)
    {
    	double unitPriceShirts = 0.95, unitPricePants = 1.75, unitPrice1 = 0.00,
    		   unitPrice2 = 0.00, unitPrice3 = 0.00, unitPrice4 = 0.00;
    	 int qtyShirts = 1, qtyPants = 1, quantity1 = 1,
    	     quantity2 = 1, quantity3 = 1, quantity4 = 1;
    	double subTotalShirts = 0, subTotalPants = 0, subTotal1 = 0,
    		   subTotal2 = 0, subTotal3 = 0, subTotal4;
    
    	double cleaningTotal = 0.00, taxRate = 5.75,
    		   taxAmount = 0.00, orderTotal = 0.00;
    
    	 // Retrieve the unit price of this item
    	 // Just in case the user types an invalid value, we are using a try...catch
    	 try {
    		 unitPriceShirts = this->txtShirtsUnitPrice->Text->ToDouble(0);
    	 }
    	 catch(FormatException *)
    	 {
    	MessageBox::Show(S"The value you entered for the price of shirts is not valid"
    			              S"\nPlease try again");
    	 }
    
    	 // Retrieve the number of this item
    	 // Just in case the user types an invalid value, we are using a try...catch
    	 try {
    		 qtyShirts  = this->txtShirtsQuantity->Text->ToInt16(0);
    	 }
    	 catch(FormatException *)
    	 {
    	 MessageBox::Show(S"The value you entered for the number of shirts is not valid"
    			              S"\nPlease try again");
    	 }
    
    	 // Calculate the sub-total for this item
    	 subTotalShirts  = unitPriceShirts * qtyShirts;
    
    	 // Display the sub-total in the corresponding text box
    	 this->txtShirtsSubTotal->Text = subTotalShirts.ToString(S"F");
    
    	try {
    		unitPricePants = this->txtPantsUnitPrice->Text->ToDouble(0);
    	}
    	catch(FormatException *)
    	{
    	MessageBox::Show(S"The value you entered for the price of pants is not valid"
    			             S"\nPlease try again");
    	}
    
    	try {
    		qtyPants  = this->txtPantsQuantity->Text->ToInt16(0);
    	}
    	catch(FormatException *)
    	{
    	MessageBox::Show(S"The value you entered for the number of pants is not valid"
    			             S"\nPlease try again");
    	}
    
    	subTotalPants  = unitPricePants * qtyPants;
    	this->txtPantsSubTotal->Text = subTotalPants.ToString("F");
    
    	try {
    		unitPrice1 = this->txtItem1UnitPrice->Text->ToDouble(0);
    	}
    	catch(FormatException *)
    	{
    		MessageBox::Show(S"The value you entered for the price is not valid"
    			             S"\nPlease try again");
    	}
    
    	try {
    		quantity1  = this->txtItem1Quantity->Text->ToInt16(0);
    	}
    	catch(FormatException *)
    	{
    		MessageBox::Show(S"The value you entered is not valid"
    			             S"\nPlease try again");
    	}
    
    	subTotal1  = unitPrice1 * quantity1;
    	this->txtItem1SubTotal->Text = subTotal1.ToString("F");
    
    	try {
    		unitPrice2 = this->txtItem2UnitPrice->Text->ToDouble(0);
    	}
    	catch(FormatException *)
    	{
    		MessageBox::Show(S"The value you entered for the price is not valid"
    			             S"\nPlease try again");
    	}
    
    	try {
    		quantity2  = this->txtItem2Quantity->Text->ToInt16(0);
    	}
    	catch(FormatException *)
    	{
    		MessageBox::Show(S"The value you entered is not valid"
    			             S"\nPlease try again");
    	}
    
    	subTotal2  = quantity2 * unitPrice2;
    	this->txtItem2SubTotal->Text = subTotal2.ToString("F");
    
    	try {
    		quantity3  = this->txtItem3Quantity->Text->ToInt16(0);
    	}
    	catch(FormatException *)
    	{
    		MessageBox::Show(S"The value you entered is not valid"
    			             S"\nPlease try again");
    	}
    
    	try {
    		unitPrice3 = this->txtItem3UnitPrice->Text->ToDouble(0);
    	}
    	catch(FormatException *)
    	{
    		MessageBox::Show(S"The value you entered for the price is not valid"
    			             S"\nPlease try again");
    	}
    
    	subTotal3  = quantity3 * unitPrice3;
    	this->txtItem3SubTotal->Text = subTotal3.ToString("F");
    
    	try {
    		unitPrice4 = this->txtItem4UnitPrice->Text->ToDouble(0);
    	}
    	catch(FormatException *)
    	{
    		MessageBox::Show(S"The value you entered for the price is not valid"
    			             S"\nPlease try again");
    	}
    	try {
    		quantity4  = this->txtItem4Quantity->Text->ToInt16(0);
    	}
    	catch(FormatException *)
    	{
    		MessageBox::Show(S"The value you entered is not valid"
    			             S"\nPlease try again");
    	}
    
    	subTotal4  = quantity4 * unitPrice4;
    	this->txtItem4SubTotal->Text = subTotal4.ToString("F");
    
    	// Calculate the total
    	cleaningTotal = subTotalShirts + subTotalPants + subTotal1 +
    		         subTotal2 + subTotal3 + subTotal4;
    
    	// Retrieve the value of the tax rate
    	try {
    		taxRate = this->txtTaxRate->Text->ToDouble(0);
    	}
    	catch(FormatException *)
    	{
    		MessageBox::Show(S"The tax rate you entered is invalid"
    			             S"\nPlease try again");
    	}
    
    	// 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
    	this->txtCleaningTotal->Text = cleaningTotal.ToString(S"F");
    	this->txtTaxAmount->Text  = taxAmount.ToString(S"F");
    	this->txtOrderTotal->Text   = orderTotal.ToString(S"F");
    }
  39. Return to the form and double-click the Save button
  40. In the top section of the file, under the other using namespace lines, type:

    using namespace System::Data::OleDb;
  41. Implement the event as follows:
     
    System::Void btnSave_Click(System::Object *  sender, System::EventArgs *  e)
    {
    	 if( IsNewOrder == true )
    	 {
    	 String *strInsert = String::Concat(S"INSERT INTO CleaningOrders(",
                                    S"CustomerName, CustomerPhone, DateLeft, ",
    				S"TimeLeft, DateExpected, TimeExpected, ",
    				S"ShirtsUnitPrice, ShirtsQuantity, ",
    				S"ShirtsSubTotal, PantsUnitPrice, ",
    				S"PantsQuantity, PantsSubTotal, Item1Name, ",
    				S"Item1UnitPrice, Item1Quantity, ",
    				S"Item1SubTotal, Item2Name, Item2UnitPrice, ",
    				S"Item2Quantity, Item2SubTotal, Item3Name, ",
                                    S"Item3UnitPrice, Item3Quantity, ",
    				S"Item3SubTotal, Item4Name, Item4UnitPrice, ",
    				S"Item4Quantity, Item4SubTotal, CleaningTotal, ",
    				S"TaxRate, TaxAmount, OrderTotal) VALUES(",
                                    S"'",    txtCustomerName->Text,
    				S"', '", txtCustomerPhone->Text,
    				S"', '", dtpDateLeft->Value,
    				S"', '", dtpTimeLeft->Value,
    				S"', '", dtpDateExpected->Value,
    				S"', '", dtpTimeExpected->Value,
    				S"', '", txtShirtsUnitPrice->Text,
    				S"', '", txtShirtsQuantity->Text,
    				S"', '", txtShirtsSubTotal->Text,
    				S"', '", txtPantsUnitPrice->Text,
    				S"', '", txtPantsQuantity->Text,
    				S"', '", txtPantsSubTotal->Text,
    				S"', '", cboItem1->Text,
    				S"', '", txtItem1UnitPrice->Text,
    				S"', '", txtItem1Quantity->Text,
    				S"', '", txtItem1SubTotal->Text,
    				S"', '", cboItem2->Text,
    				S"', '", txtItem2UnitPrice->Text,
    				S"', '", txtItem2Quantity->Text,
    				S"', '", txtItem2SubTotal->Text,
    				S"', '", cboItem3->Text,
    				S"', '", txtItem3UnitPrice->Text,
    				S"', '", txtItem3Quantity->Text,
    				S"', '", txtItem3SubTotal->Text,
    				S"', '", cboItem4->Text,
    				S"', '", txtItem4UnitPrice->Text,
    				S"', '", txtItem4Quantity->Text,
    				S"', '", txtItem4SubTotal->Text,
    				S"', '", txtCleaningTotal->Text,
    				S"', '", txtTaxRate->Text,
    				S"', '", txtTaxAmount->Text,
    				S"', '", txtOrderTotal->Text, S"');");
    
    OleDbCommand    *cmdDatabase = new OleDbCommand(strInsert, oleDbConnection1);
    
    	 oleDbConnection1->Open();
    
    	 cmdDatabase->ExecuteNonQuery();
    	 oleDbConnection1->Close();
    
    	 this->btnReset_Click(sender, e);
    	}
    }
  42. Save all

 
 

Previous Copyright © 2005-2010 FunctionX, Inc. Next