ADO.NET Data Binding: Database Example Application: College Park Auto-Repair

	Database Example Application: College Park Auto-Repair

Introduction

When reading the records of a table, as mentioned already, the data reader reads one record at a time and moves to the next. Before moving to the next record, you can access the values stored in the current record. To help with this, the columns of the table being read are stored in a collection and each column can be referred to with a numeric index. The first column has an index of 1. The second column has an index of 2, and so on. To retrieve the actual data stored in a column, you may need to know the type of information the column is holding so you can read it accurately.

Practical Learning: Using a Data Reader

Start Microsoft Visual C# and create a new Windows Application named CollegeParkAutoRepair4
In the Solution Explorer, right-click Form1.cs and click Rename
Type RepairOrders.cs and press Enter twice

Design the form as follows:

Control	Name	Text	Additional Properties
GroupBox		Order Identification
Label		Customer Name:
TextBox	txtCustomerName
Label		Address:
TextBox	txtAddress
Label		City:
TextBox	txtCity
Label		State:
TextBox	txtState
Label		ZIP Code:
TextBox	txtZIPCode
Label		Make/Model:
TextBox	txtMake
TextBox	txtModel
Label		Year:
TextBox	txtYear
Label		Problem Description:
TextBox	txtProblemDescription		Scrollbars: Vertical Multiline: True
GroupBox		Parts Used
Label		Part Name
Label		Unit Price
Label		Qty
Label		Sub Total
TextBox	txtPart1Name
TextBox	txtUnitPrice1	0.00	TextAlign: Right
TextBox	txtQuantity1	0	TextAlign: Right
TextBox	txtSubTotal1	0.00	TextAlign: Right
TextBox	txtPart2Name
TextBox	txtUnitPrice2	0.00	TextAlign: Right
TextBox	txtQuantity2	0	TextAlign: Right
TextBox	txtSubTotal2	0.00	TextAlign: Right
TextBox	txtPart3Name
TextBox	txtUnitPrice3	0.00	TextAlign: Right
TextBox	txtQuantity3	0	TextAlign: Right
TextBox	txtSubTotal3	0.00	TextAlign: Right
TextBox	txtPart4Name
TextBox	txtUnitPrice4	0.00	TextAlign: Right
TextBox	txtQuantity4	0	TextAlign: Right
TextBox	txtSubTotal4	0.00	TextAlign: Right
TextBox	txtPart5Name
TextBox	txtUnitPrice5	0.00	TextAlign: Right
TextBox	txtQuantity5	0	TextAlign: Right
TextBox	txtSubTotal5	0.00	TextAlign: Right
GroupBox		Jobs Performed
Label		Job Description
Label		Price
TextBox	txtJobDescription1
TextBox	txtJobPrice1	0.00	TextAlign: Right
TextBox	txtJobDescription2
TextBox	txtJobPrice2	0.00	TextAlign: Right
TextBox	txtJobDescription3
TextBox	txtJobPrice3	0.00	TextAlign: Right
TextBox	txtJobDescription4
TextBox	txtJobPrice4	0.00	TextAlign: Right
TextBox	txtJobDescription5
TextBox	txtJobPrice5	0.00	TextAlign: Right
GroupBox		Order Summary
Label		Total Parts:
TextBox	txtTotalParts	0.00	TextAlign: Right
Label		Total Labor:
TextBox	txtTotalLabor	0.00	TextAlign: Right
Label		Tax Rate:
TextBox	txtTaxRate	7.75	TextAlign: Right
Label		%
Label		Tax Amount:
TextBox	txtTaxAmount	0.00	TextAlign: Right
Label		Total Order:
TextBox	txtTotalOrder	0.00	TextAlign: Right
Label		Recommendations:
TextBox	txtRecommendations		Scrollbars: Vertical Multiline: True
Button	btnSave	Save
Label		Receipt #:
TextBox	txtReceiptNumber
Button	btnOpen	Open
Button	btnNewRepairOrder	New Repair Order
Button	btnClose	Close

Double-click an unoccupied area of the form and, to create the database, implement the event as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace CollegeParkAutoRepair4
{
    public partial class RepairOrders : Form
    {
	public RepairOrders()
    	{
	    InitializeComponent();
	}

	void CreateDatabase()
	{
	    using (SqlConnection conCPAR =
		new SqlConnection("Data Source=(local); " +
				  "Integrated Security='SSPI';"))
	    {
		string strCreateDatabase =
		    "CREATE DATABASE CollegeParkAutoRepair1";

		SqlCommand cmdCPAR = new SqlCommand(strCreateDatabase,
						    conCPAR);

		conCPAR.Open();
		cmdCPAR.ExecuteNonQuery();

		MessageBox.Show("A database named " +
		    	"CollegeParkAutoRepair1 has been created");
	    }

	    using (SqlConnection conCPAR =
		new SqlConnection("Data Source=(local); " +
			"Database='CollegeParkAutoRepair1'; " +
			"Integrated Security='SSPI';"))
	    {
		string strCreateTable = "CREATE TABLE RepairOrders( " +
		    "RepairOrderID int identity(100001, 1) NOT NULL, " +
		    "CustomerName varchar(80) NOT NULL, " +
		    "Address varchar(100) NOT NULL, " +
		    "City varchar(50), State varchar(50), " +
		    "ZIPCode varchar(50), CarMake varchar(50), " +
		    "CarModel varchar(50), CarYear smallint, " +
		    "ProblemDescription text, Part1Name varchar(80), " +
		    "Part1UnitPrice money, " +
		    "Part1Quantity tinyint, " +
		    "Part1SubTotal money, " +
		    "Part2Name varchar(80), " +
		    "Part2UnitPrice money, " +
		    "Part2Quantity tinyint, " +
		    "Part2SubTotal money, " +
		    "Part3Name varchar(80), " +
		    "Part3UnitPrice money, " +
		    "Part3Quantity tinyint, " +
		    "Part3SubTotal money, " +
		    "Part4Name varchar(80), " +
		    "Part4UnitPrice money, " +
		    "Part4Quantity tinyint, " +
		    "Part4SubTotal money, " +
		    "Part5Name varchar(80), " +
		    "Part5UnitPrice money, " +
		    "Part5Quantity tinyint, " +
		    "Part5SubTotal money, " +
		    "Job1Description varchar(80), " +
		    "Job1Price money, " +
		    "Job2Description varchar(80), " +
		    "Job2Price money, " +
		    "Job3Description varchar(80), " +
		    "Job3Price money, " +
		    "Job4Description varchar(80), " +
		    "Job4Price money, " +
		    "Job5Description varchar(80), " +
		    "Job5Price money, " +
		    "TotalParts money, " +
		    "TotalLabor money, " +
		    "TaxRate decimal(6,2), " +
		    "TaxAmount money, " +
		    "TotalOrder money, " +
		    "Recommendations text, " +
		    "CONSTRAINT PK_RepairOrders PRIMARY KEY (RepairOrderID));";

		SqlCommand cmdCPAR = new SqlCommand(strCreateTable, conCPAR);

		conCPAR.Open();
	    	cmdCPAR.ExecuteNonQuery();
	        MessageBox.Show("A table named RepairOrders has been created");
	    }
    	}

	private void RepairOrders_Load(object sender, EventArgs e)
	{
	    CreateDatabase();
    	}
    }
}

Execute the application to create the database and its table
Close the form and return to your programming environment

On the form, double-click the New Repair Order button and implement its event as follows:

private void btnNewRepairOrder_Click(object sender, EventArgs e)
{
    // This code is used to reset the form
    txtReceiptNumber.Text = "";

    txtCustomerName.Text = "";
    txtAddress.Text = ""; txtCity.Text = "";
    txtState.Text = ""; txtZIPCode.Text = "";

    txtMake.Text = ""; txtModel.Text = "";
    txtCarYear.Text = ""; txtProblemDescription.Text = "";

    txtPart1Name.Text = ""; txtUnitPrice1.Text = "0.00";
    txtQuantity1.Text = "0"; txtSubTotal1.Text = "0.00";

    txtPart2Name.Text = ""; txtUnitPrice2.Text = "0.00";
    txtQuantity2.Text = "0"; txtSubTotal2.Text = "0.00";

    txtPart3Name.Text = ""; txtUnitPrice3.Text = "0.00";
    txtQuantity3.Text = "0"; txtSubTotal3.Text = "0.00";

    txtPart4Name.Text = ""; txtUnitPrice4.Text = "0.00";
    txtQuantity4.Text = "0"; txtSubTotal4.Text = "0.00";

    txtPart5Name.Text = ""; txtUnitPrice5.Text = "0.00";
    txtQuantity5.Text = "0"; txtSubTotal5.Text = "0.00";

    txtJobDescription1.Text = ""; txtJobPrice1.Text = "0.00";
    txtJobDescription2.Text = ""; txtJobPrice2.Text = "0.00";
    txtJobDescription3.Text = ""; txtJobPrice3.Text = "0.00";
    txtJobDescription4.Text = ""; txtJobPrice4.Text = "0.00";
    txtJobDescription5.Text = ""; txtJobPrice5.Text = "0.00";

    txtRecommendations.Text = ""; txtTotalParts.Text = "0.00";
    txtTotalLabor.Text = "0.00"; txtTaxRate.Text = "7.75";
    txtTaxAmount.Text = "0.00"; txtTotalLabor.Text = "0.00";

    txtCustomerName.Focus();
}

Change to Load event as follows:

private void RepairOrders_Load(object sender, EventArgs e)
{
    btnNewRepairOrder_Click(sender, e);
}

Return to the form and click the first unit price text box
Press and hold Shift
Click the other unit price text boxes
Click each text box under the Qty label
Click each text box under Price (in the Jobs Performed group box)
Click the Tax Rate text box
Release Shift
In the Properties window, click the Events button and double-click Leave

Implement the event as follows:

internal void CalculateOrder()
{
    double UnitPrice1 = 0.00, UnitPrice2 = 0.00,
	   UnitPrice3 = 0.00, UnitPrice4 = 0.00,
	   UnitPrice5 = 0.00,
	   SubTotal1, SubTotal2, SubTotal3,
	   SubTotal4, SubTotal5, TotalParts;
    int Quantity1 = 0, Quantity2 = 0, Quantity3 = 0,
	Quantity4 = 0, Quantity5 = 0;
    double JobPrice1 = 0.00, JobPrice2 = 0.00, JobPrice3 = 0.00,
	   JobPrice4 = 0.00, JobPrice5 = 0.00, TotalLabor = 0.00;
    double TaxAmount, TotalOrder, TaxRate = 0.00;

    // Don't charge a part unless it is clearly identified
    if( txtPart1Name.Text == "")
    {
	txtUnitPrice1.Text = "0.00";
	txtQuantity1.Text = "0";
	txtSubTotal1.Text = "0.00";
	UnitPrice1 = 0.00;
    }
    else
    {
	try
        {
	    UnitPrice1 = double.Parse(this.txtUnitPrice1.Text);
    	}
	catch (FormatException)
	{
	    MessageBox.Show("Invalid Unit Price");
	    txtUnitPrice1.Text = "0.00";
	    txtUnitPrice1.Focus();
	}

	try
	{
	    Quantity1 = int.Parse(this.txtQuantity1.Text);
	}
	catch (FormatException)
	{
	    MessageBox.Show("Invalid Quantity");
	    txtQuantity1.Text = "0";
	    txtQuantity1.Focus();
	}
    }

    if (this.txtPart2Name.Text == "")
    {
	txtUnitPrice2.Text = "0.00";
	txtQuantity2.Text = "0";
	txtSubTotal2.Text = "0.00";
	UnitPrice2 = 0.00;
    }
    else
    {
	try
    	{
	    UnitPrice2 = double.Parse(this.txtUnitPrice2.Text);
	}
	catch (FormatException)
	{
	    MessageBox.Show("Invalid Unit Price");
	    txtUnitPrice2.Text = "0.00";
	    txtUnitPrice2.Focus();
	}

	try
	{
	    Quantity2 = int.Parse(this.txtQuantity2.Text);
	}
	catch (FormatException)
	{
	    MessageBox.Show("Invalid Quantity");
	    txtQuantity2.Text = "0";
	    txtQuantity2.Focus();
	}
    }

    if (this.txtPart3Name.Text == "")
    {
	txtUnitPrice3.Text = "0.00";
	txtQuantity3.Text = "0";
	txtSubTotal3.Text = "0.00";
	UnitPrice3 = 0.00;
    }
    else
    {
	try
	{
	    UnitPrice3 = double.Parse(this.txtUnitPrice3.Text);
	}
	catch (FormatException)
	{
	    MessageBox.Show("Invalid Unit Price");
	    txtUnitPrice3.Text = "0.00";
	    txtUnitPrice3.Focus();
	}

	try
	{
	    Quantity3 = int.Parse(this.txtQuantity3.Text);
	}
	catch (FormatException)
	{
	    MessageBox.Show("Invalid Quantity");
	    txtQuantity3.Text = "0";
	    txtQuantity3.Focus();
	}
    }

    if (this.txtPart4Name.Text == "")
    {
	txtUnitPrice4.Text = "0.00";
	txtQuantity4.Text = "0";
	txtSubTotal4.Text = "0.00";
	UnitPrice4 = 0.00;
    }
    else
    {
	try
	{
	    UnitPrice4 = double.Parse(this.txtUnitPrice4.Text);
	}
	catch (FormatException)
	{
	    MessageBox.Show("Invalid Unit Price");
	    txtUnitPrice4.Text = "0.00";
	    txtUnitPrice4.Focus();
	}

	try
	{
	    Quantity4 = int.Parse(this.txtQuantity4.Text);
	}
	catch (FormatException)
	{
	    MessageBox.Show("Invalid Quantity");
	    txtQuantity4.Text = "0";
	    txtQuantity4.Focus();
	}
    }

    if (txtPart5Name.Text == "")
    {
	txtUnitPrice5.Text = "0.00";
	txtQuantity5.Text = "0";
	txtSubTotal5.Text = "0.00";
	UnitPrice5 = 0.00;
    }
    else
    {
	try
	{
	    UnitPrice5 = double.Parse(this.txtUnitPrice5.Text);
	}
	catch (FormatException)
	{
	    MessageBox.Show("Invalid Unit Price");
	    txtUnitPrice5.Text = "0.00";
	    txtUnitPrice5.Focus();
	}

	try
	{
	    Quantity5 = int.Parse(this.txtQuantity5.Text);
	}
	catch (FormatException)
	{
	    MessageBox.Show("Invalid Quantity");
	    txtQuantity5.Text = "0";
	    txtQuantity5.Focus();
	}
    }

    // Don't bill the customer for a job that is not specified
    if( txtJobDescription1.Text == "" )
    {
	txtJobPrice1.Text = "0.00";
	JobPrice1 = 0.00;
    }
    else
    {
	try
	{
	    JobPrice1 = double.Parse(this.txtJobPrice1.Text);
	}
	catch (FormatException)
	{
	    MessageBox.Show("Invalid Job Price");
	    txtJobPrice1.Text = "0.00";
	    txtJobPrice1.Focus();
	}
    }

    if (txtJobDescription2.Text == "")
    {
	txtJobPrice2.Text = "0.00";
	JobPrice2 = 0.00;
    }
    else
    {
	try
	{
	    JobPrice2 = double.Parse(this.txtJobPrice2.Text);
	}
	catch (FormatException)
	{
	    MessageBox.Show("Invalid Job Price");
	    txtJobPrice2.Text = "0.00";
	    txtJobPrice2.Focus();
	}
    }

    if (txtJobDescription3.Text == "")
    {
	txtJobPrice3.Text = "0.00";
	JobPrice3 = 0.00;
    }
    else
    {
	try
	{
	    JobPrice3 = double.Parse(this.txtJobPrice3.Text);
	}
	catch (FormatException)
	{
	    MessageBox.Show("Invalid Job Price");
	    txtJobPrice3.Text = "0.00";
	    txtJobPrice3.Focus();
	}
    }

    if (txtJobDescription4.Text == "")
    {
	txtJobPrice4.Text = "0.00";
	JobPrice4 = 0.00;
    }
    else
    {
	try
	{
	    JobPrice4 = double.Parse(this.txtJobPrice4.Text);
	}
	catch (FormatException)
	{
	    MessageBox.Show("Invalid Job Price");
	    txtJobPrice4.Text = "0.00";
	    txtJobPrice4.Focus();
	}
    }

    if (txtJobDescription5.Text == "")
    {
	txtJobPrice5.Text = "0.00";
	JobPrice5 = 0.00;
    }
    else
    {
	try
	{
	    JobPrice5 = double.Parse(this.txtJobPrice5.Text);
	}
	catch (FormatException)
	{
	    MessageBox.Show("Invalid Job Price");
	    txtJobPrice5.Text = "0.00";
	    txtJobPrice5.Focus();
	}
    }

    SubTotal1 = UnitPrice1 * Quantity1;
    SubTotal2 = UnitPrice2 * Quantity2;
    SubTotal3 = UnitPrice3 * Quantity3;
    SubTotal4 = UnitPrice4 * Quantity4;
    SubTotal5 = UnitPrice5 * Quantity5;

    txtSubTotal1.Text = SubTotal1.ToString("F");
    txtSubTotal2.Text = SubTotal2.ToString("F");
    txtSubTotal3.Text = SubTotal3.ToString("F");
    txtSubTotal4.Text = SubTotal4.ToString("F");
    txtSubTotal5.Text = SubTotal5.ToString("F");

    TotalParts = SubTotal1 + SubTotal2 + SubTotal3 +
		 SubTotal4 + SubTotal5;

    TotalLabor = JobPrice1 + JobPrice2 + JobPrice3 +
		 JobPrice4 + JobPrice5;

    try
    {
	TaxRate = double.Parse(this.txtTaxRate.Text);
    }
    catch (FormatException)
    {
	MessageBox.Show("Invalid Tax Rate");
	txtTaxRate.Text = "7.75";
	txtTaxRate.Focus();
    }

    double TotalPartsAndLabor = TotalParts + TotalLabor;
    TaxAmount = TotalPartsAndLabor * TaxRate / 100;
    TotalOrder = TotalPartsAndLabor + TaxAmount;

    txtTotalParts.Text = TotalParts.ToString("F");
    txtTotalLabor.Text = TotalLabor.ToString("F");
    txtTaxAmount.Text = TaxAmount.ToString("F");
    txtTotalOrder.Text = TotalOrder.ToString("F");
}

private void txtUnitPrice1_Leave(object sender, EventArgs e)
{
    CalculateOrder();
}

Return to the form and double-click the Save button

Implement its event as follows:

private void btnSave_Click(object sender, EventArgs e)
{
    string strCommand = "";

    using (SqlConnection cnnRepairOrders =
	new SqlConnection("Data Source=(local);" +
			  "Database='CollegeParkAutoRepair1';" +
		 	  "Integrated Security=SSPI;"))
    {
	// If the Receipt Number is empty, it appears that
	// the user/clerk wants to create a new cleaning order
	if( txtReceiptNumber.Text == "" )
	{
	    strCommand = "INSERT INTO RepairOrders( " +
		"CustomerName, Address, City, " +
		"State, ZIPCode, CarMake, " +
		"CarModel, CarYear, ProblemDescription, " +
		"Part1Name, Part1UnitPrice, Part1Quantity, " +
		"Part1SubTotal, Part2Name, Part2UnitPrice, " +
		"Part2Quantity, Part2SubTotal, Part3Name, " +
		"Part3UnitPrice, Part3Quantity, Part3SubTotal, " +
		"Part4Name, Part4UnitPrice, Part4Quantity, " +
		"Part4SubTotal, Part5Name, Part5UnitPrice, " +
		"Part5Quantity, Part5SubTotal, Job1Description, " +
		"Job1Price, Job2Description, Job2Price, " +
		"Job3Description, Job3Price, Job4Description, " +
		"Job4Price, Job5Description, Job5Price, " +
		"Recommendations, TotalParts, TotalLabor, " +
		"TaxRate, TaxAmount, TotalOrder) " +
		"VALUES('" + txtCustomerName.Text + "', '" +
		txtAddress.Text + "', '" + txtCity.Text +
		"', '" + txtState.Text + "', '" +
		txtZIPCode.Text + "', '" + txtMake.Text +
		"', '" + txtModel.Text + "', '" +
		txtCarYear.Text + "', '" +
		txtProblemDescription.Text + "', '" +
	txtPart1Name.Text + "', '" + txtUnitPrice1.Text + "', '" +
	txtQuantity1.Text + "', '" + txtSubTotal1.Text + "', '" +
	txtPart2Name.Text + "', '" + txtUnitPrice2.Text + "', '" +
	txtQuantity2.Text + "', '" + txtSubTotal2.Text + "', '" +
	txtPart3Name.Text + "', '" + txtUnitPrice3.Text + "', '" +
	txtQuantity3.Text + "', '" + txtSubTotal3.Text + "', '" +
	txtPart4Name.Text + "', '" + txtUnitPrice4.Text + "', '" +
	txtQuantity4.Text + "', '" + txtSubTotal4.Text + "', '" +
	txtPart5Name.Text + "', '" + txtUnitPrice5.Text + "', '" +
	txtQuantity5.Text + "', '" + txtSubTotal5.Text + "', '" +
	txtJobDescription1.Text + "', '" + txtJobPrice1.Text + "', '" +
	txtJobDescription2.Text + "', '" + txtJobPrice2.Text + "', '" +
	txtJobDescription3.Text + "', '" + txtJobPrice3.Text + "', '" +
	txtJobDescription4.Text + "', '" + txtJobPrice4.Text + "', '" +
	txtJobDescription5.Text + "', '" + txtJobPrice5.Text + "', '" +
	txtRecommendations.Text + "', '" + txtTotalParts.Text + "', '" +
	txtTotalLabor.Text + "', '" + txtTaxRate.Text + "', '" +
	txtTaxAmount.Text + "', '" + txtTotalOrder.Text + "');";
	}
	else // Since there is a receipt number, update/edit the cleaning order
	{
	    strCommand = "UPDATE CleaningOrders " +
		"SET CustomerName = '" + txtCustomerName.Text + "', " +
		"    Address = '" + txtAddress.Text + "', " +
		"    City = '" + txtCity.Text + "', " +
		"    State = '" + txtState.Text + "', " +
		"    ZIPCode = '" + txtZIPCode.Text + "', " +
		"    CarMake = '" + txtMake.Text + "', " +
		"    CarModel = '" + txtModel.Text + "', " +
		"    CarYear = '" + txtCarYear.Text + "', " +
	" ProblemDescription = '" + txtProblemDescription.Text + "', " +
		"    Part1Name = '" + txtPart1Name.Text + "', " +
		"    Part1UnitPrice = '" + txtUnitPrice1.Text + "', " +
		"    Part1Quantity = '" + txtQuantity1.Text + "', " +
		"    Part1SubTotal = '" + txtSubTotal1.Text + "', " +
		"    Part2Name = '" + txtPart2Name.Text + "', " +
		"    Part2UnitPrice = '" + txtUnitPrice2.Text + "', " +
		"    Part2Quantity = '" + txtQuantity2.Text + "', " +
		"    Part2SubTotal = '" + txtSubTotal2.Text + "', " +
		"    Part3Name = '" + txtPart3Name.Text + "', " +
		"    Part3UnitPrice = '" + txtUnitPrice3.Text + "', " +
		"    Part3Quantity = '" + txtQuantity3.Text + "', " +
		"    Part3SubTotal = '" + txtSubTotal3.Text + "', " +
		"    Part4Name = '" + txtPart4Name.Text + "', " +
		"    Part4UnitPrice = '" + txtUnitPrice4.Text + "', " +
		"    Part4Quantity = '" + txtQuantity4.Text + "', " +
		"    Part4SubTotal = '" + txtSubTotal4.Text + "', " +
		"    Part5Name = '" + txtPart5Name.Text + "', " +
		"    Part5UnitPrice = '" + txtUnitPrice5.Text + "', " +
		"    Part5Quantity = '" + txtQuantity5.Text + "', " +
		"    Part5SubTotal = '" + txtSubTotal5.Text + "', " +
		"    Job1Description = '" + txtJobDescription1.Text + "', " +
		"    Job1Price = '" + txtJobDescription1.Text + "', " +
		"    Job2Description = '" + txtJobDescription2.Text + "', " +
		"    Job2Price = '" + txtJobDescription2.Text + "', " +
		"    Job3Description = '" + txtJobDescription3.Text + "', " +
		"    Job3Price = '" + txtJobDescription3.Text + "', " +
		"    Job4Description = '" + txtJobDescription4.Text + "', " +
		"    Job4Price = '" + txtJobDescription4.Text + "', " +
		"    Job5Description = '" + txtJobDescription5.Text + "', " +
		"    Job5Price = '" + txtJobDescription5.Text + "', " +
		"    TotalParts = '" + txtTotalParts.Text + "', " +
		"    TotalLabor = '" + txtTotalLabor.Text + "', " +
		"    TaxRate = '" + txtTaxRate.Text + "', " +
		"    TaxAmount = '" + txtTaxAmount.Text + "', " +
		"    TotalOrder = '" + txtTotalOrder.Text + "' " +
		"    WHERE RepairOrderID = '" + txtReceiptNumber.Text + "';";
	}

	SqlCommand cmdCleaningOrders = new SqlCommand(strCommand,
						      cnnRepairOrders);

	cnnRepairOrders.Open();
	cmdCleaningOrders.ExecuteNonQuery();

	btnNewRepair_Click(sender, e);
    }
}

Return to the form and double-click the Open button

Implement its event as follows:

private void btnOpen_Click(object sender, EventArgs e)
{
    string strReceiptNumber = txtReceiptNumber.Text;

    if (strReceiptNumber.Length == 0 )
    {
	MessageBox.Show("You open a repair order, " +
			"enter its receipt number and click Open.");
	return;
    }

    using (SqlConnection conDatabase = new
	SqlConnection("Data Source=(local);" +
		      "Database='CollegeParkAutoRepair1';" +
		      "Integrated Security=yes"))
    {
	string strFindRepair =
	    "SELECT * FROM RepairOrders WHERE RepairOrderID = '" +
	    strReceiptNumber + "'";
	SqlCommand cmdDatabase = new
	SqlCommand(strFindRepair, conDatabase);

	conDatabase.Open();

	SqlDataReader rdrRepairOrder;
	rdrRepairOrder = cmdDatabase.ExecuteReader();

	while (rdrRepairOrder.Read())
	{
	    txtCustomerName.Text = rdrRepairOrder.GetString(1);
	    txtAddress.Text = rdrRepairOrder.GetString(2);
	    txtCity.Text = rdrRepairOrder.GetString(3);
	    txtState.Text = rdrRepairOrder.GetString(4);
	    txtZIPCode.Text = rdrRepairOrder.GetString(5);
	    txtMake.Text = rdrRepairOrder.GetString(6);
	    txtModel.Text = rdrRepairOrder.GetString(7);
	    txtCarYear.Text = rdrRepairOrder.GetSqlInt16(8).ToString();
	    txtProblemDescription.Text = rdrRepairOrder.GetString(9);

	    txtPart1Name.Text = rdrRepairOrder.GetString(10);
	    txtUnitPrice1.Text = rdrRepairOrder.GetSqlMoney(11).ToString();
	    txtQuantity1.Text = rdrRepairOrder.GetSqlByte(12).ToString();
	    txtSubTotal1.Text = rdrRepairOrder.GetSqlMoney(13).ToString();

	    txtPart2Name.Text = rdrRepairOrder.GetString(14);
	    txtUnitPrice2.Text = rdrRepairOrder.GetSqlMoney(15).ToString();
	    txtQuantity2.Text = rdrRepairOrder.GetSqlByte(16).ToString();
	    txtSubTotal2.Text = rdrRepairOrder.GetSqlMoney(17).ToString();

	    txtPart3Name.Text = rdrRepairOrder.GetString(18);
	    txtUnitPrice3.Text = rdrRepairOrder.GetSqlMoney(19).ToString();
	    txtQuantity3.Text = rdrRepairOrder.GetSqlByte(20).ToString();
	    txtSubTotal3.Text = rdrRepairOrder.GetSqlMoney(21).ToString();

	    txtPart4Name.Text = rdrRepairOrder.GetString(22);
	    txtUnitPrice4.Text = rdrRepairOrder.GetSqlMoney(23).ToString();
	    txtQuantity4.Text = rdrRepairOrder.GetSqlByte(24).ToString();
	    txtSubTotal4.Text = rdrRepairOrder.GetSqlMoney(25).ToString();

	    txtPart5Name.Text = rdrRepairOrder.GetString(26);
	    txtUnitPrice5.Text = rdrRepairOrder.GetSqlMoney(27).ToString();
	    txtQuantity5.Text = rdrRepairOrder.GetSqlByte(28).ToString();
	    txtSubTotal5.Text = rdrRepairOrder.GetSqlMoney(29).ToString();

	    txtJobDescription1.Text = rdrRepairOrder.GetString(30);
	    txtJobPrice1.Text = rdrRepairOrder.GetSqlMoney(31).ToString();
	    txtJobDescription2.Text = rdrRepairOrder.GetString(32);
	    txtJobPrice2.Text = rdrRepairOrder.GetSqlMoney(33).ToString();
	    txtJobDescription3.Text = rdrRepairOrder.GetString(34);
	    txtJobPrice3.Text = rdrRepairOrder.GetSqlMoney(35).ToString();
	    txtJobDescription4.Text = rdrRepairOrder.GetString(36);
	    txtJobPrice4.Text = rdrRepairOrder.GetSqlMoney(37).ToString();
	    txtJobDescription5.Text = rdrRepairOrder.GetString(38);
	    txtJobPrice5.Text = rdrRepairOrder.GetSqlMoney(39).ToString();

	    txtTotalParts.Text = rdrRepairOrder.GetSqlMoney(40).ToString();
	    txtTotalLabor.Text = rdrRepairOrder.GetSqlMoney(41).ToString();
	    txtTaxRate.Text = rdrRepairOrder.GetSqlDecimal(42).ToString();
	    txtTaxAmount.Text = rdrRepairOrder.GetSqlMoney(43).ToString();
	    txtTotalOrder.Text = rdrRepairOrder.GetSqlMoney(44).ToString();

	    txtRecommendations.Text = rdrRepairOrder.GetString(45);
	}

	rdrRepairOrder.Close();
    }
}

Return to the form and double-click the Close button

Implement its event as follows:

private void btnClose_Click(object sender, EventArgs e)
{
    Close();
}

Execute the application
Create a few repair orders. Here are two examples:
Close the form and return to your programming environment
Execute the application again and open a few previously saved cleaning orders

Published on Thursday 10 January 2008


Home	Copyright © 2007 FunctionX, Inc.