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

Still in Microsoft Access, to create a new form, on the main menu, click Insert . Form
In the New Form dialog box, double-click Design View
While in Design View, double-click the button at the intersection of the rulers to access the Properties window
In the Properties window, click Event and double-click On Load

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

Close Microsoft Visual Basic and return to Microsoft Access
Switch the the form to Form View to create the view
After viewing the message box that states that a data view has been created, click OK
Close the form
When asked whether you want to save the form, (make sure you) click No
Close Microsoft Access
Start Microsoft Visual Studio .NET or Visual C# 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 private Boolean variable named IsNewOrder

bool IsNewOrder;

Return to the form, double-click an unoccupied area of the form to generate its Load event and implement it as follows:

private void Form1_Load(object sender, System.EventArgs e)
{
	IsNewOrder = true;
	this.oleDbDataAdapter1.Fill(this.dsCleaningOrders1);
}

Return to the form, double-click the Reset button and implement its Click event as follows:

private void btnReset_Click(object sender, System.EventArgs e)
		{
			IsNewOrder = true;

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

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

			this.cboItem1.SelectedIndex = 0;
			this.txtItem1UnitPrice.Text = "0.00";
			this.txtItem1Quantity.Text  = "0";
			this.txtItem1SubTotal.Text  = "0.00";

			this.cboItem2.SelectedIndex = 0;
			this.txtItem2UnitPrice.Text = "0.00";
			this.txtItem2Quantity.Text  = "0";
			this.txtItem2SubTotal.Text  = "0.00";

			this.cboItem3.SelectedIndex = 0;
			this.txtItem3UnitPrice.Text = "0.00";
			this.txtItem3Quantity.Text  = "0";
			this.txtItem3SubTotal.Text  = "0.00";

			this.cboItem4.SelectedIndex = 0;
			this.txtItem4UnitPrice.Text = "0.00";
			this.txtItem4Quantity.Text  = "0";
			this.txtItem4SubTotal.Text  = "0.00";

			this.txtCleaningTotal.Text = "0.00";
			this.txtTaxRate.Text = "5.75";
			this.txtTaxAmount.Text = "0.00";
			this.txtOrderTotal.Text = "0.00";
			this.txtReceiptNumber.Text = "0";
				this.txtCustomerName.Focus();
		}

In the Class View, expand GCS3 followed by GCS3 and Form1
Right-click Form1 -> Add -> Add Method...
Set the characteristics as follows:
Method Access: internal
Return Type: void
Function Name: CalculateCleaningOrder

Click Finish and implement the method as follows:

internal void CalculateCleaningOrder()
{
	decimal unitPriceShirts = 0.95M, unitPricePants = 1.75M, unitPrice1 = 0.00M,
		unitPrice2 = 0.00M, unitPrice3 = 0.00M, unitPrice4 = 0.00M;
	int qtyShirts = 1, qtyPants = 1, quantity1 = 1,
		quantity2 = 1, quantity3 = 1, quantity4 = 1;
	decimal subTotalShirts = 0, subTotalPants = 0, subTotal1 = 0,
		subTotal2 = 0, subTotal3 = 0, subTotal4;

	decimal cleaningTotal = 0.00M, taxRate = 5.75M,
		taxAmount = 0.00M, orderTotal = 0.00M;

	// Retrieve the unit price of this item
	// Just in case the user types an invalid value, we are using a try...catch
	try 
	{
		unitPriceShirts = decimal.Parse(this.txtShirtsUnitPrice.Text);
	}
	catch(FormatException)
	{
	MessageBox.Show("The value you entered for the price of shirts is not valid" +
					"\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  = int.Parse(this.txtShirtsQuantity.Text);
	}
	catch(FormatException)
	{
	MessageBox.Show("The value you entered for the number of shirts is not valid" +
					"\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("F");

	try 
	{
		unitPricePants = decimal.Parse(this.txtPantsUnitPrice.Text);
	}
	catch(FormatException)
	{
	MessageBox.Show("The value you entered for the price of pants is not valid" +
					"\nPlease try again");
	}

	try 
	{
		qtyPants  = int.Parse(this.txtPantsQuantity.Text);
	}
	catch(FormatException)
	{
	MessageBox.Show("The value you entered for the number of pants is not valid" +
					"\nPlease try again");
	}

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

	try 
	{
		unitPrice1 = decimal.Parse(this.txtItem1UnitPrice.Text);
	}
	catch(FormatException)
	{
	MessageBox.Show("The value you entered for the price is not valid" +
					"\nPlease try again");
	}

	try 
	{
		quantity1  = int.Parse(this.txtItem1Quantity.Text);
	}
	catch(FormatException)
	{
		MessageBox.Show("The value you entered is not valid" +
				"\nPlease try again");
	}

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

	try 
	{
		unitPrice2 = decimal.Parse(this.txtItem2UnitPrice.Text);
	}
	catch(FormatException)
	{
	MessageBox.Show("The value you entered for the price is not valid" +
			"\nPlease try again");
	}

	try 
	{
		quantity2  = int.Parse(this.txtItem2Quantity.Text);
	}
	catch(FormatException)
	{
	MessageBox.Show("The value you entered is not valid" +
			"\nPlease try again");
	}

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

	try 
	{
		quantity3  = int.Parse(this.txtItem3Quantity.Text);
	}
	catch(FormatException)
	{
		MessageBox.Show("The value you entered is not valid" +
				"\nPlease try again");
	}

	try 
	{
		unitPrice3 = decimal.Parse(this.txtItem3UnitPrice.Text);
	}
	catch(FormatException)
	{
		MessageBox.Show("The value you entered for the price is not valid" +
				"\nPlease try again");
	}

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

	try 
	{
		unitPrice4 = decimal.Parse(this.txtItem4UnitPrice.Text);
	}
	catch(FormatException)
	{
		MessageBox.Show("The value you entered for the price is not valid" +
				"\nPlease try again");
	}
	try 
	{
		quantity4  = int.Parse(this.txtItem4Quantity.Text);
	}
	catch(FormatException)
	{
		MessageBox.Show("The value you entered is not valid" +
				"\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 = decimal.Parse(this.txtTaxRate.Text);
	}
	catch(FormatException)
	{
		MessageBox.Show("The tax rate you entered is invalid" +
				"\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("F");
	this.txtTaxAmount.Text  = taxAmount.ToString("F");
	this.txtOrderTotal.Text   = orderTotal.ToString("F");
}

Return to the form and double-click the Save button
In the top section of the file, under the other using lines, type:

using System.Data.OleDb;

Implement its event as follows:

private void btnSave_Click(object sender, System.EventArgs e)
{
	if( IsNewOrder == true )
	{
		string strInsert = "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 + "');";

OleDbCommand cmdDatabase = new OleDbCommand(strInsert, oleDbConnection1);

		oleDbConnection1.Open();

		cmdDatabase.ExecuteNonQuery();
		oleDbConnection1.Close();

		this.btnReset_Click(sender, e);
	}
}

Save all