Home

Georgetown Cleaning Services

 

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. Display the form then, 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. Double-click the Open button and implement its event as follows:
     
    System::Void btnOpen_Click(System::Object *  sender, System::EventArgs *  e)
    {
    	 if( this->txtReceiptNumber->Text->Equals(S"") )
    	 {
    		 MessageBox::Show(S"Please enter a receipt number");
    		 this->txtReceiptNumber->Focus();
    		 return;
    	 }
    
    	 int iReceiptNumber = this->txtReceiptNumber->Text->ToInt32(0);
    
    	 this->oleDbDataAdapter1->Fill(this->dsCleaningOrders1);
    this->dvwCleaningOrder->RowFilter = String::Concat(S"CleaningOrderID = '", iReceiptNumber.ToString(), "'");
    
    	 this->txtCustomerName->DataBindings->Clear();
    	 this->txtCustomerPhone->DataBindings->Clear();
    	 this->dtpDateLeft->DataBindings->Clear();
    	 this->dtpTimeLeft->DataBindings->Clear();
    	 this->dtpDateExpected->DataBindings->Clear();
    	 this->dtpTimeExpected->DataBindings->Clear();
    	 this->txtShirtsUnitPrice->DataBindings->Clear();
    	 this->txtShirtsQuantity->DataBindings->Clear(); 
    this->txtShirtsSubTotal->DataBindings->Clear();
    this->txtPantsUnitPrice->DataBindings->Clear();
    this->txtPantsQuantity->DataBindings->Clear();
    this->txtPantsSubTotal->DataBindings->Clear(); 
    this->cboItem1->DataBindings->Clear(); 
    this->txtItem1UnitPrice->DataBindings->Clear(); 
     this->txtItem1Quantity->DataBindings->Clear(); 
     this->txtItem1SubTotal->DataBindings->Clear(); 
     this->cboItem2->DataBindings->Clear(); 
     this->txtItem2UnitPrice->DataBindings->Clear(); 
     this->txtItem2Quantity->DataBindings->Clear(); 
     this->txtItem2SubTotal->DataBindings->Clear(); 
     this->cboItem3->DataBindings->Clear(); 
     this->txtItem3UnitPrice->DataBindings->Clear(); 
     this->txtItem3Quantity->DataBindings->Clear(); 
     this->txtItem3SubTotal->DataBindings->Clear(); 
     this->cboItem4->DataBindings->Clear(); 
     this->txtItem4UnitPrice->DataBindings->Clear(); 
     this->txtItem4Quantity->DataBindings->Clear(); 
     this->txtItem4SubTotal->DataBindings->Clear(); 
     this->txtCleaningTotal->DataBindings->Clear(); 
     this->txtTaxRate->DataBindings->Clear(); 
     this->txtTaxAmount->DataBindings->Clear(); 
     this->txtOrderTotal->DataBindings->Clear();
    
    	 this->txtCustomerName->DataBindings->Add(S"Text", this->dvwCleaningOrder, "CustomerName");
    	 this->txtCustomerPhone->DataBindings->Add(S"Text", this->dvwCleaningOrder, "CustomerPhone");
    	 this->dtpDateLeft->DataBindings->Add("Value", this->dvwCleaningOrder, "DateLeft");
    	 this->dtpTimeLeft->DataBindings->Add("Value", this->dvwCleaningOrder, "TimeLeft");
    	 this->dtpDateExpected->DataBindings->Add("Value", this->dvwCleaningOrder, "DateExpected");
    	 this->dtpTimeExpected->DataBindings->Add("Value", this->dvwCleaningOrder, "TimeExpected");
    	 this->txtShirtsUnitPrice->DataBindings->Add(S"Text", this->dvwCleaningOrder, "ShirtsUnitPrice");
    	 this->txtShirtsQuantity->DataBindings->Add(S"Text", this->dvwCleaningOrder, "ShirtsQuantity");
    	 this->txtShirtsSubTotal->DataBindings->Add(S"Text", this->dvwCleaningOrder, "ShirtsSubTotal");
    	 this->txtPantsUnitPrice->DataBindings->Add(S"Text", this->dvwCleaningOrder, "PantsUnitPrice");
    	 this->txtPantsQuantity->DataBindings->Add(S"Text", this->dvwCleaningOrder, "PantsQuantity");
    	 this->txtPantsSubTotal->DataBindings->Add(S"Text", this->dvwCleaningOrder, "PantsSubTotal");
    	 this->cboItem1->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item1Name");
    	 this->txtItem1UnitPrice->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item1UnitPrice");
    	 this->txtItem1Quantity->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item1Quantity");
    	 this->txtItem1SubTotal->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item1SubTotal");
    	 this->cboItem2->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item2Name");
    	 this->txtItem2UnitPrice->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item2UnitPrice");
    	 this->txtItem2Quantity->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item2Quantity");
    	 this->txtItem2SubTotal->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item2SubTotal");
    	 this->cboItem3->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item3Name");
    	 this->txtItem3UnitPrice->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item3UnitPrice");
    	 this->txtItem3Quantity->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item3Quantity");
    	 this->txtItem3SubTotal->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item3SubTotal");
    	 this->cboItem4->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item4Name");
    	 this->txtItem4UnitPrice->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item4UnitPrice");
    	 this->txtItem4Quantity->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item4Quantity");
    	 this->txtItem4SubTotal->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"Item4SubTotal");
    	 this->txtCleaningTotal->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"CleaningTotal");
    	 this->txtTaxRate->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"TaxRate");
    	 this->txtTaxAmount->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"TaxAmount");
    	 this->txtOrderTotal->DataBindings->Add(S"Text", this->dvwCleaningOrder, S"OrderTotal");
    
    	 IsNewOrder = false;
    }
  4. In the Class View, right-click Form1 -> Add -> Add Function...
  5. Set the characteristics as follows:
    Return Type: void
    Function Name: UpdateCleaningOrder
    Access: private
  6. Click Finish and implement the event as follows:
     
    // This method is used to update an existing record if the user changes any of its values
    void UpdateCleaningOrder(void)
    {
    	 // 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 )
    	 {
    	 String *strUpdate = String::Concat(S"UPDATE CleaningOrders ",
                           S"SET CustomerName = '",  this->txtCustomerName->Text, S"', ",
    		S"CustomerPhone = '",  this->txtCustomerPhone->Text, S"', ",
    		S"DateLeft = '", this->dtpDateLeft->Value.ToString(), S"', ",
    		S"TimeLeft = '", this->dtpTimeLeft->Value.ToString(), S"', ",
    		S"DateExpected = '", this->dtpDateExpected->Value.ToString(), S"', ",
    		S"TimeExpected = '", this->dtpTimeExpected->Value.ToString(), S"', ",
    		S"ShirtsUnitPrice = '", this->txtShirtsUnitPrice->Text, S"', ",
    		S"ShirtsQuantity = '", this->txtShirtsQuantity->Text, S"', ",
    		S"ShirtsSubTotal = '", this->txtShirtsSubTotal->Text, S"', ",
    		S"PantsUnitPrice = '", this->txtPantsUnitPrice->Text, S"', ",
    		S"PantsQuantity = '", this->txtPantsQuantity->Text, S"', ",
    		S"PantsSubTotal = '", this->txtPantsSubTotal->Text, S"', ",
    		S"Item1Name = '", this->cboItem1->Text, S"', ",
    		S"Item1UnitPrice = '", this->txtItem1UnitPrice->Text, S"', ",
    		S"Item1Quantity = '", this->txtItem1Quantity->Text, S"', ",
    		S"Item1SubTotal = '", this->txtItem1SubTotal->Text, S"', ",
    		S"Item2Name = '", this->cboItem2->Text, S"', ",
    		S"Item2UnitPrice = '", this->txtItem2UnitPrice->Text, S"', ",
    		S"Item2Quantity = '", this->txtItem2Quantity->Text, S"', ",
    		S"Item2SubTotal = '", this->txtItem2SubTotal->Text, S"', ",
    		S"Item3Name = '", this->cboItem3->Text, S"', ",
    		S"Item3UnitPrice = '", this->txtItem3UnitPrice->Text, S"', ",
    		S"Item3Quantity = '", this->txtItem3Quantity->Text, S"', ",
    		S"Item3SubTotal = '", this->txtItem3SubTotal->Text, S"', ",
    		S"Item4Name = '", this->cboItem4->Text, S"', ",
    		S"Item4UnitPrice = '", this->txtItem4UnitPrice->Text, S"', ",
    		S"Item4Quantity = '", this->txtItem4Quantity->Text, S"', ",
    		S"Item4SubTotal = '", this->txtItem4SubTotal->Text, S"', ",
    		S"CleaningTotal = '", this->txtCleaningTotal->Text, S"', ",
    		S"TaxRate = '", this->txtTaxRate->Text, S"', ",
    		S"TaxAmount = '", this->txtTaxAmount->Text, S"', ",
    		S"OrderTotal = '", this->txtOrderTotal->Text, S"' ",
    		S"WHERE CleaningOrderID = ",
    		this->txtReceiptNumber->Text, S";");
    
    	 OleDbCommand    *cmdDatabase = new OleDbCommand(strUpdate, oleDbConnection1);
    
    	 oleDbConnection1->Open();
    
    	 cmdDatabase->ExecuteNonQuery();
    	 oleDbConnection1->Close();
    	 }
    }
  7. Return to the form and click the Customer Name text box
  8. In the Properties window, click the Events button and double-click Leave
  9. Implement its event as follows:
     
    System::Void txtCustomerName_Leave(System::Object *  sender, System::EventArgs *  e)
    {
    	 if( IsNewOrder == false )
    	 {
    		UpdateCleaningOrder();
    	 }
    }
  10. Return to the form and click the Customer Phone text box
  11. In the Events section of the Properties window, double-click Leave and implement its event as follows:
     
    System::Void txtCustomerPhone_Leave(System::Object *  sender, System::EventArgs *  e)
    {
    	 if( IsNewOrder == false )
    	 {
    		UpdateCleaningOrder();
    	 }
    }
  12. Return to the form and double-click the Date Left date time picker control
  13. Implement its ValueChanged event as follows:
     
    System::Void dtpDateLeft_ValueChanged(System::Object *  sender, System::EventArgs *  e)
    {
    	 DateTime dateLeft = this->dtpDateLeft->Value;
    	 DateTime timeLeft = this->dtpTimeLeft->Value;
    	 DateTime time9AM  = DateTime(timeLeft.Year, timeLeft.Month, timeLeft.Day, 9, 0, 0);
    
    	 // If the customer leaves clothes before 9AM...
    	 if( timeLeft <= time9AM )
    	 {
    		 // ... then they should be ready the same day after 5PM
    		 this->dtpDateExpected->Value = dateLeft;
     this->dtpTimeExpected->Value = 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
    	 this->dtpDateExpected->Value = DateTime(dateLeft.Year, dateLeft.Month, dateLeft.Day + 1); 
     this->dtpTimeExpected->Value = DateTime(dateLeft.Year, dateLeft.Month, dateLeft.Day + 1, 8, 0, 0);
    	 }
    	
    	 if( IsNewOrder == false )
    	 {
    		UpdateCleaningOrder();
    	 }
    }
  14. Return to the form and double-click the Time Left date time picker control
  15. Implement its ValueChanged event as follows:
     
    System::Void dtpTimeLeft_ValueChanged(System::Object *  sender, System::EventArgs *  e)
    {
    	 DateTime dateLeft = this->dtpDateLeft->Value;
    	 DateTime timeLeft = this->dtpTimeLeft->Value;
    	 DateTime time9AM  = DateTime(timeLeft.Year, timeLeft.Month, timeLeft.Day, 9, 0, 0);
    
    	 // If the customer leaves clothes before 9AM...
    	 if( timeLeft <= time9AM )
    	 {
    		 // ... then they should be ready the same day after 5PM
    		 this->dtpDateExpected->Value = dateLeft;
     this->dtpTimeExpected->Value = 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
     this->dtpDateExpected->Value = DateTime(dateLeft.Year, dateLeft.Month, dateLeft.Day + 1); 
    this->dtpTimeExpected->Value = DateTime(dateLeft.Year, dateLeft.Month, dateLeft.Day + 1, 8, 0, 0);
    	 }
    	
    	 if( IsNewOrder == false )
    	 {
    		 UpdateCleaningOrder();
    	 }
    }
  16. Return to the form and double-click the Date Expected date time picker control
  17. Implement its ValueChanged event as follows:
     
    System::Void dtpDateExpected_ValueChanged(System::Object *  sender, System::EventArgs *  e)
    {
    	 if( IsNewOrder == false )
    	 	 UpdateCleaningOrder();
    }
  18. Return to the form and double-click the Time Expected date time picker control
  19. Implement its ValueChanged event as follows:
     
    System::Void dtpTimeExpected_ValueChanged(System::Object *  sender, System::EventArgs *  e)
    {
    	 if( IsNewOrder == false )
    	 	UpdateCleaningOrder();
    }
  20. Return to the form and click the Unit Price text box corresponding to the Shirts
  21. In the Events section of the Properties window, double-click Leave and implement its event as follows:
     
    System::Void txtShirtsUnitPrice_Leave(System::Object *  sender, System::EventArgs *  e)
    {
    	 CalculateCleaningOrder();
    
    	 if( IsNewOrder == false )
    		 UpdateCleaningOrder();
    }
  22. Return to the form and click the Qty text box corresponding to the Shirts
  23. In the Events section of the Properties window, double-click Leave and implement its event as follows:
     
    System::Void txtShirtsQuantity_Leave(System::Object *  sender, System::EventArgs *  e)
    {
    	 CalculateCleaningOrder();
    
    	 if( IsNewOrder == false )
    		 UpdateCleaningOrder();
    }
  24. Return to the form and click the Unit Price text box corresponding to the Pants
  25. In the Events section of the Properties window, double-click Leave and implement its event as follows:
     
    System::Void txtPantsUnitPrice_Leave(System::Object *  sender, System::EventArgs *  e)
    {
    	 CalculateCleaningOrder();
    
    	 if( IsNewOrder == false )
    		 UpdateCleaningOrder();
    }
  26. Return to the form and click the Qty text box corresponding to the Pants
  27. In the Events section of the Properties window, double-click Leave and implement its event as follows:
     
    System::Void txtPantsQuantity_Leave(System::Object *  sender, System::EventArgs *  e)
    {
    	 CalculateCleaningOrder();
    
    	 if( IsNewOrder == false )
    		 UpdateCleaningOrder();
    }
  28. Return to the form and double-click the first combo box under Pants
  29. Implement its event as follows:
     
    System::Void cboItem1_SelectedIndexChanged(System::Object *  sender, System::EventArgs *  e)
    {
    	 if( this->cboItem1->Text->Equals(S"None") )
    	 {
    		 this->txtItem1UnitPrice->Text = S"0.00";
    		 this->txtItem1Quantity->Text  = S"0";
    		 this->txtItem1SubTotal->Text  = S"0.00";
    	 }
    
    	 CalculateCleaningOrder();
    
    	 if( IsNewOrder == false )
    		 UpdateCleaningOrder();
    }
  30. Return to the form and click the Unit Price text box corresponding to the first combo box
  31. In the Events section of the Properties window, double-click Leave and implement its event as follows:
     
    System::Void txtItem1UnitPrice_Leave(System::Object *  sender, System::EventArgs *  e)
    {
    	 CalculateCleaningOrder();
    
    	 if( IsNewOrder == false )
    		 UpdateCleaningOrder();
    }
  32. Return to the form and click the Qty text box corresponding to the first combo box
  33. In the Events section of the Properties window, double-click Leave and implement its event as follows:
     
    System::Void txtItem1Quantity_Leave(System::Object *  sender, System::EventArgs *  e)
    {
    	 CalculateCleaningOrder();
    
    	 if( IsNewOrder == false )
    		 UpdateCleaningOrder();
    }
  34. Return to the form and double-click the second combo box under Pants
  35. Implement its event as follows:
     
    System::Void cboItem2_SelectedIndexChanged(System::Object *  sender, System::EventArgs *  e)
    {
    	 if( this->cboItem2->Text->Equals(S"None") )
    	 {
    		 this->txtItem2UnitPrice->Text = S"0.00";
    		 this->txtItem2Quantity->Text  = S"0";
    		 this->txtItem2SubTotal->Text  = S"0.00";
    	 }
    
    	 CalculateCleaningOrder();
    
    	 if( IsNewOrder == false )
    		 UpdateCleaningOrder();
    }
  36. Return to the form and click the Unit Price text box corresponding to the second combo box
  37. In the Events section of the Properties window, double-click Leave and implement its event as follows:
     
    System::Void txtItem2UnitPrice_Leave(System::Object *  sender, System::EventArgs *  e)
    {
    	 CalculateCleaningOrder();
    
    	 if( IsNewOrder == false )
    		 UpdateCleaningOrder();
    }
  38. Return to the form and click the Qty text box corresponding to the second combo box
  39. In the Events section of the Properties window, double-click Leave and implement its event as follows:
     
    System::Void txtItem2Quantity_Leave(System::Object *  sender, System::EventArgs *  e)
    {
    	 CalculateCleaningOrder();
    
    	 if( IsNewOrder == false )
    		 UpdateCleaningOrder();
    }
  40. Return to the form and double-click the third combo box under Pants
  41. Implement its event as follows:
     
    System::Void cboItem3_SelectedIndexChanged(System::Object *  sender, System::EventArgs *  e)
    {
    	 if( this->cboItem3->Text->Equals(S"None") )
    	 {
    		 this->txtItem3UnitPrice->Text = S"0.00";
    		 this->txtItem3Quantity->Text  = S"0";
    		 this->txtItem3SubTotal->Text  = S"0.00";
    	 }
    
    	 CalculateCleaningOrder();
    
    	 if( IsNewOrder == false )
    		 UpdateCleaningOrder();
    }
  42. Return to the form and click the Unit Price text box corresponding to the third combo box
  43. In the Events section of the Properties window, double-click Leave and implement its event as follows:
     
    System::Void txtItem3UnitPrice_Leave(System::Object *  sender, System::EventArgs *  e)
    {
    	 CalculateCleaningOrder();
    
    	 if( IsNewOrder == false )
    		 UpdateCleaningOrder();
    }
  44. Return to the form and click the Qty text box corresponding to the third combo box
  45. In the Events section of the Properties window, double-click Leave and implement its event as follows:
     
    System::Void txtItem3Quantity_Leave(System::Object *  sender, System::EventArgs *  e)
    {
    	 CalculateCleaningOrder();
    
    	 if( IsNewOrder == false )
    		 UpdateCleaningOrder();
    }
  46. Return to the form and double-click the fourth combo box under Pants
  47. Implement its event as follows:
     
    System::Void cboItem4_SelectedIndexChanged(System::Object *  sender, System::EventArgs *  e)
    {
    	 if( this->cboItem4->Text->Equals(S"None") )
    	 {
    		 this->txtItem4UnitPrice->Text = S"0.00";
    		 this->txtItem4Quantity->Text  = S"0";
    		 this->txtItem4SubTotal->Text  = S"0.00";
    	 }
    
    	 CalculateCleaningOrder();
    
    	 if( IsNewOrder == false )
    		 UpdateCleaningOrder();
    }
  48. Return to the form and click the Unit Price text box corresponding to the fourth combo box
  49. In the Events section of the Properties window, double-click Leave and implement its event as follows:
     
    System::Void txtItem4UnitPrice_Leave(System::Object *  sender, System::EventArgs *  e)
    {
    	 CalculateCleaningOrder();
    
    	 if( IsNewOrder == false )
    		 UpdateCleaningOrder();
    }
  50. Return to the form and click the Qty text box corresponding to the fourth combo box
  51. In the Events section of the Properties window, double-click Leave and implement its event as follows:
     
    System::Void txtItem4Quantity_Leave(System::Object *  sender, System::EventArgs *  e)
    {
    	 CalculateCleaningOrder();
    
    	 if( IsNewOrder == false )
    		 UpdateCleaningOrder();
    }
  52. Return to the form and click the Tax Rate text box
  53. In the Events section of the Properties window, double-click Leave and implement its event as follows:
     
    System::Void txtTaxRate_Leave(System::Object *  sender, System::EventArgs *  e)
    {
    	 CalculateCleaningOrder();
    
    	 if( IsNewOrder == false )
    		 UpdateCleaningOrder();
    }
  54. Display the form and double-click the Close button
  55. Implement its event as follows:
     
    System::Void btnClose_Click(System::Object *  sender, System::EventArgs *  e)
    {
    	 Close();
    }
  56. Execute the application and create a few cleaning orders
     
    Georgetown Cleaning Services - Cleaning Order
    Georgetown Cleaning Services - Cleaning Order
  57. Close the form
  58. Execute the application again and try opening a cleaning order whose receipt number is 1001, then 1002, then 1003
  59. Close the form

 
 

Previous Copyright © 2005-2010 FunctionX, Inc.