Borland C++ Builder Database Examples: College Park Auto Repair

	College Park Auto-Shop

Introduction

The Borland C++ Builder (and Delphi) programming environment provides a great level of support for database creation and maintenance. To start, it ships with Paradox that you can use to create the structure of one or more tables. Equipped with such a table, you can then create the necessary form(s) for friendlier data entry and/or analysis.

In this exercise, we will review all the needed steps to create a database that can be used to process customers repair requests for a car repair business.

Table Creation

To start a database that would be supported by Paradox, you can first create a folder in which you would store your files, just as you would create a folder for a regular project. Because of the way Paradox works, you would also create an alias for your database. This is usually not a requirement because you can safely use any of the already available aliases. Still, creating an alias is particularly easy as long as you rightly follow the necessary steps.

Once you know that alias you would use to identify your database, you can create its table(s) as you see fit.

Practical Learning: Creating a Table

Open Windows Explorer or My Computer
If you don't have it yet, create a folder named Programs
Inside of the Programs folder, create a sub-folder named College Park Auto Shop
To start Database Desktop, on the Taskbar, click Start -> (All) Programs -> Borland C++ Builder 6 -> Database Desktop
On the main menu of Database Desktop, click Tools -> Alias Manager...
In the Alias Manager, click New
Change the Name to CPAS
Accept the Driver Type as STANDARD
In the path, enter the complete path to the College Park Auto Shop folder you created
Click OK
You should receive a message asking you whether to update the new list of aliases
Click Yes
On the main menu of Database Desktop (or Corel Paradox), click File -> New -> Table... (if you are using Corel Paradox, click Blank Table)
In the Create Table dialog box, accept Paradox 7 (or Paradox 7 - 10) and click OK
In the first box under Field Name, type WorkOrderID and press Tab
Click the box under Type and press the Space bar (if you are using Corel Paradox, click the box under Type and click the arrow of the combo box)
Click +

Complete the table with other fields as follows:

Field Name	Type	Size
WorkOrderID	+
CustomerName	A	50
Address	A	50
City	A	30
State	A	30
ZIPCode	A	10
Make	A	30
Model	A	40
CarYear	S
ProbDesc	M	240
PartName1	A	50
UnitPrice1	A	12
Quantity1	S
SubTotal1	A	12
PartName2	A	50
UnitPrice2	A	12
Quantity2	S
SubTotal2	A	12
PartName3	A	50
UnitPrice3	A	12
Quantity3	S
SubTotal3	A	12
PartName4	A	50
UnitPrice4	A	12
Quantity4	S
SubTotal4	A	12
PartName5	A	50
UnitPrice5	A	12
Quantity5	S
SubTotal5	A	12
JobPerformed1	A	50
JobPrice1	A	12
JobPerformed2	A	50
JobPrice2	A	12
JobPerformed3	A	50
JobPrice3	A	12
JobPerformed4	A	50
JobPrice4	A	12
TotalParts	A	12
TotalLabor	A	12
TaxRate	N
TaxAmount	A	12
OrderTotal	A	12
Recommendations	M	240

To save the table, click the Save As... button (if you are using Corel Paradox, click the Create button)
Set the File Name to WorkOrders
Locate the Programs\College Park Auto Shop folder you created and display it in the Save combo box
Click Save
Return to the Database Desktop
On the main menu of the Database Desktop, click File -> Open -> Table...
Locate the College Park Auto Shop folder you created and display it in the Look In combo box. Select WorkOrders.db and click Open
To put the table into edit mode, on the main menu of Database Desktop, click Table -> Edit Data

Create the following work order:

CustomerName	Gertrude Monay
Address	1488 East Acacia Rd
City	Silver Spring
State	MD
ZIPCode	20906
Make	Dodge
Model	Neon
CarYear	1998
PartName1	A/C Compressor Clutch
UnitPrice1	125.85
Quantity1	1
SubTotal1	125.85
PartName2	Condenser Fan
UnitPrice2	70.95
Quantity2	1
SubTotal2	70.95
JobPerformed1	Changed the compressor clutch
JobPrice1	54.50

Close the table and close the Database Desktop

Database Creation

Once you have a Paradox table, you can use it in a Borland C++ Builder project. You can work from scratch by starting a brand new form. Fortunately, C++ Builder provides a wizard you can use to locate a table and add it to a form. The form itself would be created for you can it would be equipped to handle all the necessary operations of a database table without your having to write a single line of code.

If you have a long table whose fields you want to create a form, sometimes you should design the form from scratch because the wizard may not do such a good job. In fact, if you use the Form Wizard for a table with many fields, you would still end up redesigning the form. For this reason, we will design our from from the beginning.

Practical Learning: Creating the Database

Start Borland C++ Builder with its default form
Change the form's Caption with College Park Auto-Shop
Change its Name to frmWorkOrders and set its ShowHint property to true
To save the project, on the Standard toolbar, click the Save All button
Locate the Programs folder and display it in the Save In combo box
Click the Create New Folder button
Type CPAS1 and press Enter twice to display it in the Save In combo box
Change Unit1.cpp to WorkOrders and press Enter
Change the Project file to CPAS and press Enter
To add a panel, on the Standard tab of the Component Palette, double-click the Panel control
While the new panel is still selected, in the Object Inspector, set its Align property to alTop
Click Caption and press Delete to remove the caption
To add a new label, on the Standard tab of the Component Palette, click the Label and click the top panel
In the Object Inspector, click Caption and type College Park Auto-Shop
Click the gray box on the right side of the Font field
Click the ellipsis button to open the Font dialog
Change the Font to Times New Roman
In the Font Style combo box, click Bold
In the Font Size combo box, select 24
Click the arrow of the Color combo box and select Blue
Click OK
On the Object Inspector, click the Name field and type lblMainTitle
Double-click the box on the right side of the Transparent field. Instead of false, it should now display true
On the form, click the College Park Auto-Shop label to select it
On the main menu, click Edit -> Copy
Click the top panel on which the College Park label is positioned
On the main menu, click Edit -> Paste
As the new label is still selected, click the + on the Font field
Under the Font field, click Color to reveal its combo box and select clGray
Click the � on the Font field
Click the Name field and type lblTitleShadow
Click the arrow on the top section of the Object Inspector and select lblMainTitle
Set its Top property to 8
Click the arrow on the top section of the Object Inspector and select lblTitleShadow
Set its Top property to 11
Right-click on the group of those big labels. One of them will be selected. On the context menu, click Bring To Front. If the blue label does not come in front, right-click again and click Bring To Front until the blue label is on top of the white label
On the Component Palette, click BDE, then click Table and click the form
In the Object Inspector, change the following properties:
Database Name: CPAS
Name: tblWorkOrders
TableName: WorkOrders.DB
On the Component Palette, click Data Access, then click DataSource and click the form
In the Object Inspector, change the following properties:
DataSet: tblWorkOrders
Name: dsWorkOrders

Complete the design of the form as follows:

Control	Caption	Name	DataSource	DataField
Label	Work Order ID:
DBEdit		edtWorkOrderID	dsWorkOrders	WorkOrderID
Label	Customer Name:
DBEdit		edtCustomerName	dsWorkOrders	CustomerName
Label	Address:
DBEdit		edtAddress	dsWorkOrders	Address
Label	City
DBEdit		edtCity	dsWorkOrders	City
Label	State:
DBEdit		edtZIPCode	dsWorkOrders	ZIPCode
Label	Make/Model:
DBEdit		edtMake	dsWorkOrders	Make
DBEdit		edtModel	dsWorkOrders	Model
Label	Year:
DBEdit		edtCarYear	dsWorkOrders	CarYear
Label	Problem Description:
DBMemo		mmoProbDesc	dsWorkOrders	ProblemDescription
GroupBox	Parts Used
Label	Part Name
Label	Unit Price
Label	Qty
Label	Sub-Total
DBEdit		edtPartName1	dsWorkOrders	PartName1
DBEdit		edtUnitPrice1	dsWorkOrders	UnitPrice1
DBEdit		edtQuantity1	dsWorkOrders	Quantity1
DBEdit		edtSubTotal1	dsWorkOrders	SubTotal1
DBEdit		edtPartName2	dsWorkOrders	PartName2
DBEdit		edtUnitPrice2	dsWorkOrders	UnitPrice2
DBEdit		edtQuantity2	dsWorkOrders	Quantity2
DBEdit		edtSubTotal2	dsWorkOrders	SubTotal2
DBEdit		edtPartName3	dsWorkOrders	PartName3
DBEdit		edtUnitPrice3	dsWorkOrders	UnitPrice3
DBEdit		edtQuantity3	dsWorkOrders	Quantity3
DBEdit		edtSubTotal3	dsWorkOrders	SubTotal3
DBEdit		edtPartName4	dsWorkOrders	PartName4
DBEdit		edtUnitPrice4	dsWorkOrders	UnitPrice4
DBEdit		edtQuantity4	dsWorkOrders	Quantity4
DBEdit		edtSubTotal4	dsWorkOrders	SubTotal4
DBEdit		edtPartName5	dsWorkOrders	PartName5
DBEdit		edtUnitPrice5	dsWorkOrders	UnitPrice5
DBEdit		edtQuantity5	dsWorkOrders	Quantity5
DBEdit		edtSubTotal5	dsWorkOrders	SubTotal5
GroupBox	Jobs Performed
Label	Job Price
DBEdit		edtJobPerformed1	dsWorkOrders	JobPerformed1
DBEdit		edtJobPrice1	dsWorkOrders	JobPrice1
DBEdit		edtJobPerformed2	dsWorkOrders	JobPerformed2
DBEdit		edtJobPrice2	dsWorkOrders	JobPrice2
DBEdit		edtJobPerformed3	dsWorkOrders	JobPerformed3
DBEdit		edtJobPrice3	dsWorkOrders	JobPrice3
DBEdit		edtJobPerformed4	dsWorkOrders	JobPerformed4
DBEdit		edtJobPrice3	dsWorkOrders	JobPrice4
GroupBox	Order Summary
Label	Total Parts:
DBEdit		edtTotalParts	dsWorkOrders	TotalParts
Label	Total Labor:
DBEdit		edtTotalLabor	dsWorkOrders	TotalLabor
Label	Tax Rate:
DBEdit		edtTaxRate	dsWorkOrders	TaxRate
Label	Tax Amount:
DBEdit		edtTaxAmount	dsWorkOrders	TaxAmount
Label	Order Total:
DBEdit		edtOrderTotal	dsWorkOrders	OrderTotal
GroupBox	Recommendations to Customer
DBMemo		mmoRecommendations
DBNavigator
BitBtn

To save the project, on the Standard toolbar, click Save All
In Class Explorer, right-click TfrmWorkOrders and click New Method...
Set the Method Name to CalculateOrder, its Function Result to void, and check __fastcall

Click OK and implement the method as follows:

//---------------------------------------------------------------------------
void __fastcall TfrmWorkOrders::CalculateOrder()
{
    //TODO: Add your source code here
    double part1UnitPrice, part1SubTotal, part2UnitPrice, part2SubTotal,
		    part3UnitPrice, part3SubTotal, part4UnitPrice, part4SubTotal,
		part5UnitPrice, part5SubTotal, totalParts;
	 int    part1Quantity = 0, part2Quantity = 0, part3Quantity = 0,
		    part4Quantity = 0, part5Quantity = 0;
	 double job1Price = 0.00, job2Price = 0.00, job3Price = 0.00,
		    job4Price = 0.00, job5Price = 0.00;
	 double totalLabor;
	 double taxRate, taxAmount, totalOrder;

	 // Don't charge a part unless it is clearly identified
	 if( this->edtUnitPrice1->Text == "" )
	 {
		 this->edtUnitPrice1->Text = "0.00";
		 this->edtQuantity1->Text   = "0";
		 this->edtSubTotal1->Text  = "0.00";
		 part1UnitPrice = 0.00;
	 }
	 else
	 {
		 try {
			 part1UnitPrice = this->edtUnitPrice1->Text.ToDouble();
		 }
		 catch(EConvertError *)
		 {
			 ShowMessage("Invalid unit price");
			 this->edtUnitPrice1->Text = "0.00";
			 this->edtUnitPrice1->SetFocus();
		 }

		 try {
			 part1Quantity = this->edtQuantity1->Text.ToInt();
		 }
		 catch(EConvertError *)
		 {
			 ShowMessage("Invalid Quantity");
			 this->edtQuantity1->Text = "0";
			 this->edtQuantity1->SetFocus();
		 }
	 }

	 if( this->edtPartName2->Text == "" )
	 {
		 this->edtUnitPrice2->Text = "0.00";
		 this->edtQuantity2->Text  = "0";
		 this->edtSubTotal2->Text  = "0.00";
		 part2UnitPrice = 0.00;
	 }
	 else
	 {
		 try {
			 part2UnitPrice = this->edtUnitPrice2->Text.ToDouble();
		 }
		 catch(EConvertError *)
		 {
			 ShowMessage("Invalid Unit Price");
			 this->edtUnitPrice2->Text = "0.00";
			 this->edtUnitPrice2->SetFocus();
		 }

		 try {
			 part2Quantity = this->edtQuantity2->Text.ToInt();
		 }
		 catch(EConvertError *)
		 {
			 ShowMessage("Invalid Quantity");
			 this->edtQuantity2->Text = "0";
			 this->edtQuantity2->SetFocus();
		 }
	 }

	 if( this->edtPartName3->Text == "" )
	 {
		 this->edtUnitPrice3->Text = "0.00";
		 this->edtQuantity3->Text  = "0";
		 this->edtSubTotal3->Text  = "0.00";
		 part3UnitPrice = 0.00;
	 }
	 else
	 {
		 try {
			 part3UnitPrice = this->edtUnitPrice3->Text.ToDouble();
		 }
		 catch(EConvertError *)
		 {
			 ShowMessage("Invalid Unit Price");
			 this->edtUnitPrice3->Text = "0.00";
			 this->edtUnitPrice3->SetFocus();
		 }

		 try {
			 part3Quantity = this->edtQuantity3->Text.ToInt();
		 }
		 catch(EConvertError *)
		 {
			 ShowMessage("Invalid Quantity");
			 this->edtQuantity3->Text = "0";
			 this->edtQuantity3->SetFocus();
		 }
	 }

	 if( this->edtPartName4->Text == "" )
	 {
		 this->edtUnitPrice4->Text = "0.00";
		 this->edtQuantity4->Text  = "0";
		 this->edtSubTotal4->Text  = "0.00";
		 part4UnitPrice = 0.00;
	 }
	 else
	 {
		 try {
			 part4UnitPrice = this->edtUnitPrice4->Text.ToDouble();
		 }
		 catch(EConvertError *)
		 {
			 ShowMessage("Invalid Unit Price");
			 this->edtUnitPrice4->Text = "0.00";
			 this->edtUnitPrice4->SetFocus();
		 }

		 try {
			 part4Quantity = this->edtQuantity4->Text.ToInt();
		 }
		 catch(EConvertError *)
		 {
			 ShowMessage("Invalid Quantity");
			 this->edtQuantity4->Text = "0";
			 this->edtQuantity4->SetFocus();
		 }
	 }

	 if( this->edtPartName5->Text == "" )
	 {
		 this->edtUnitPrice5->Text = "0.00";
		 this->edtQuantity5->Text  = "0";
		 this->edtSubTotal5->Text  = "0.00";
		 part5UnitPrice = 0.00;
	 }
	 else
	 {
		 try {
			 part5UnitPrice = this->edtUnitPrice5->Text.ToDouble();
		 }
		 catch(EConvertError *)
		 {
			 ShowMessage("Invalid Unit Price");
			 this->edtUnitPrice5->Text = "0.00";
			 this->edtUnitPrice5->SetFocus();
		 }

		 try {
			 part5Quantity = this->edtQuantity5->Text.ToInt();
		 }
		 catch(EConvertError *)
		 {
			 ShowMessage("Invalid Quantity");
			 this->edtQuantity5->Text = "0";
			 this->edtQuantity5->SetFocus();
		 }
	 }

	 // Don't bill the customer for a job that is not specified
	 if( this->edtJobPerformed1->Text == "" )
	 {
		 this->edtJobPrice1->Text = "0.00";
		 job1Price = 0.00;
	 }
	 else
	 {
		 try {
			 job1Price = this->edtJobPrice1->Text.ToDouble();
		 }
		 catch(EConvertError *)
		 {
			 ShowMessage("Invalid Job Price");
			 this->edtJobPrice1->Text = "0.00";
			 this->edtJobPrice1->SetFocus();
		 }
	 }

	 if( this->edtJobPerformed2->Text == "" )
	 {
		 this->edtJobPrice2->Text = "0.00";
		 job2Price = 0.00;
	 }
	 else
	 {
		 try {
			 job2Price = this->edtJobPrice2->Text.ToDouble();
		 }
		 catch(EConvertError *)
		 {
			 ShowMessage("Invalid Job Price");
			 this->edtJobPrice2->Text = "0.00";
			 this->edtJobPrice2->SetFocus();
		 }
	 }

	 if( this->edtJobPerformed3->Text == "" )
	 {
		 this->edtJobPrice3->Text = "0.00";
		 job3Price = 0.00;
	 }
	 else
	 {
		 try {
			 job3Price = this->edtJobPrice3->Text.ToDouble();
		 }
		 catch(EConvertError *)
		 {
			 ShowMessage("Invalid Job Price");
			 this->edtJobPrice3->Text = "0.00";
			 this->edtJobPrice3->SetFocus();
		 }
	 }

	 if( this->edtJobPerformed4->Text == "" )
	 {
		 this->edtJobPrice4->Text = "0.00";
		 job4Price = 0.00;
	 }
	 else
	 {
		 try {
			 job4Price = this->edtJobPrice4->Text.ToDouble();
		 }
		 catch(EConvertError *)
		 {
			 ShowMessage("Invalid Job Price");
			 this->edtJobPrice4->Text = "0.00";
			 this->edtJobPrice4->SetFocus();
		 }
	 }

	 part1SubTotal = part1UnitPrice * part1Quantity;
	 part2SubTotal = part2UnitPrice * part2Quantity;
	 part3SubTotal = part3UnitPrice * part3Quantity;
	 part4SubTotal = part4UnitPrice * part4Quantity;
	 part5SubTotal = part5UnitPrice * part5Quantity;

	 this->edtSubTotal1->Text  = FloatToStrF(part1SubTotal, ffFixed, 8, 2);
	 this->edtSubTotal2->Text = FloatToStrF(part2SubTotal, ffFixed, 8, 2);
	 this->edtSubTotal3->Text = FloatToStrF(part3SubTotal, ffFixed, 8, 2);
	 this->edtSubTotal4->Text = FloatToStrF(part4SubTotal, ffFixed, 8, 2);
	 this->edtSubTotal5->Text = FloatToStrF(part5SubTotal, ffFixed, 8, 2);

	 totalParts    = part1SubTotal + part2SubTotal + part3SubTotal +
			 part4SubTotal + part5SubTotal;

	 totalLabor    = job1Price + job2Price + job3Price +
		             job4Price + job5Price;

	 try {
		 taxRate = this->edtTaxRate->Text.ToDouble();
	 }
	 catch(EConvertError *)
	 {
		 ShowMessage("Invalid Tax Rate");
		 this->edtTaxRate->Text = "7.75";
		 this->edtTaxRate->SetFocus();
	 }

	 double totalPartsAndLabor = totalParts + totalLabor;
	 taxAmount  = totalPartsAndLabor * taxRate / 100;
	 totalOrder = totalPartsAndLabor + taxAmount;

	 this->edtTotalParts->Text = FloatToStrF(totalParts, ffFixed, 8, 2);
	 this->edtTotalLabor->Text = FloatToStrF(totalLabor, ffFixed, 8, 2);
	 this->edtTaxAmount->Text  = FloatToStrF(taxAmount,  ffFixed, 8, 2);
	 this->edtOrderTotal->Text = FloatToStrF(totalOrder, ffFixed, 8, 2);
}
//---------------------------------------------------------------------------

Return to the form and click the Qty control corresponding to the first part used
In the Object Inspector, click the Events tab and double-click the event of the OnExit field

Implement it as follows:

//---------------------------------------------------------------------------
void __fastcall TfrmWorkOrders::edtQuantity1Exit(TObject *Sender)
{
    CalculateOrder();
}
//---------------------------------------------------------------------------

Return to the form. Click each Qty and, in the Events tab of the Object Inspector, select edtQuantity1Exit in the combo box
In the Jobs Performed section, click each control under Job Price and, in the Object Inspector, select edtQuantity1Exit in the OnExit event
Do the same for the edtTaxRate edit control
Execute the application to test it
On the form, click edtWorkOrders and, in the Object Inspector, set its Active property to true
Update the first order as follows:
Click the Post Edit button
Close the form and return to your programming environment


Home	Copyright © 2005-2012, FunctionX, Inc.