Home

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 Practical Learning: Creating a Table

  1. Open Windows Explorer or My Computer
  2. If you don't have it yet, create a folder named Programs
  3. Inside of the Programs folder, create a sub-folder named College Park Auto Shop
     
  4. To start Database Desktop, on the Taskbar, click Start -> (All) Programs -> Borland C++ Builder 6 -> Database Desktop
  5. On the main menu of Database Desktop, click Tools -> Alias Manager...
  6. In the Alias Manager, click New
  7. Change the Name to CPAS
  8. Accept the Driver Type as STANDARD
  9. In the path, enter the complete path to the College Park Auto Shop folder you created
     
  10. Click OK
    You should receive a message asking you whether to update the new list of aliases
     
  11. Click Yes
  12. On the main menu of Database Desktop (or Corel Paradox), click File -> New -> Table... (if you are using Corel Paradox, click Blank Table)
  13. In the Create Table dialog box, accept Paradox 7 (or Paradox 7 - 10) and click OK
  14. In the first box under Field Name, type WorkOrderID and press Tab
  15. 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)
  16. Click +
  17. 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
  18. To save the table, click the Save As... button (if you are using Corel Paradox, click the Create button)
  19. Set the File Name to WorkOrders
  20. Locate the Programs\College Park Auto Shop folder you created and display it in the Save combo box
     
  21. Click Save
  22. Return to the Database Desktop
  23. On the main menu of the Database Desktop, click File -> Open -> Table...
  24. Locate the College Park Auto Shop folder you created and display it in the Look In combo box. Select WorkOrders.db and click Open
  25. To put the table into edit mode, on the main menu of Database Desktop, click Table -> Edit Data
  26. 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
  27. 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 Practical Learning: Creating the Database

  1. Start Borland C++ Builder with its default form
  2. Change the form's Caption with College Park Auto-Shop
  3. Change its Name to frmWorkOrders and set its ShowHint property to true
  4. To save the project, on the Standard toolbar, click the Save All button
  5. Locate the Programs folder and display it in the Save In combo box
  6. Click the Create New Folder button
  7. Type CPAS1 and press Enter twice to display it in the Save In combo box
  8. Change Unit1.cpp to WorkOrders and press Enter
  9. Change the Project file to CPAS and press Enter
  10. To add a panel, on the Standard tab of the Component Palette, double-click the Panel control Panel
  11. While the new panel is still selected, in the Object Inspector, set its Align property to alTop
  12. Click Caption and press Delete to remove the caption
  13. To add a new label, on the Standard tab of the Component Palette, click the Label and click the top panel
  14. In the Object Inspector, click Caption and type College Park Auto-Shop
  15. Click the gray box on the right side of the Font field
  16. Click the ellipsis button to open the Font dialog
  17. Change the Font to Times New Roman
  18. In the Font Style combo box, click Bold
  19. In the Font Size combo box, select 24
  20. Click the arrow of the Color combo box and select Blue
     
  21. Click OK
  22. On the Object Inspector, click the Name field and type lblMainTitle
  23. Double-click the box on the right side of the Transparent field. Instead of false, it should now display true
  24. On the form, click the College Park Auto-Shop label to select it
  25. On the main menu, click Edit -> Copy
  26. Click the top panel on which the College Park label is positioned
  27. On the main menu, click Edit -> Paste
  28. As the new label is still selected, click the + on the Font field
  29. Under the Font field, click Color to reveal its combo box and select clGray
  30. Click the on the Font field
  31. Click the Name field and type lblTitleShadow
  32. Click the arrow on the top section of the Object Inspector and select lblMainTitle
  33. Set its Top property to 8
  34. Click the arrow on the top section of the Object Inspector and select lblTitleShadow
  35. Set its Top property to 11
  36. 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
  37. On the Component Palette, click BDE, then click Table and click the form
  38. In the Object Inspector, change the following properties:
    Database Name: CPAS
    Name: tblWorkOrders
    TableName: WorkOrders.DB
  39. On the Component Palette, click Data Access, then click DataSource and click the form
  40. In the Object Inspector, change the following properties:
    DataSet: tblWorkOrders
    Name: dsWorkOrders
  41. 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
  42. To save the project, on the Standard toolbar, click Save All
  43. In Class Explorer, right-click TfrmWorkOrders and click New Method...
  44. Set the Method Name to CalculateOrder, its Function Result to void, and check __fastcall
     
  45. 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);
    }
    //---------------------------------------------------------------------------
  46. Return to the form and click the Qty control corresponding to the first part used
  47. In the Object Inspector, click the Events tab and double-click the event of the OnExit field
  48. Implement it as follows:
     
    //---------------------------------------------------------------------------
    void __fastcall TfrmWorkOrders::edtQuantity1Exit(TObject *Sender)
    {
        CalculateOrder();
    }
    //---------------------------------------------------------------------------
  49. Return to the form. Click each Qty and, in the Events tab of the Object Inspector, select edtQuantity1Exit in the combo box
  50. In the Jobs Performed section, click each control under Job Price and, in the Object Inspector, select edtQuantity1Exit in the OnExit event
  51. Do the same for the edtTaxRate edit control
  52. Execute the application to test it
  53. On the form, click edtWorkOrders and, in the Object Inspector, set its Active property to true
  54. Update the first order as follows:
     
  55. Click the Post Edit button Post Edit
  56. Close the form and return to your programming environment
 

Home Copyright 2005-2007 FunctionX, Inc.