Home

Relationships and Data Integrity

 

The Keys to a Good Relationship

 

Relational Databases

Imagine you are asked to create an application that allows a company to rent cars to potential customers. You may start by creating a list of cars that would be made available to customers. The list may include the type (make, model, and year) of the car and other pieces of information such as options (CD, AC, DVD, etc) that would be of interest to some of the customers. Here is an example of such a list:

Car
Make Dodge
Model Neon
Year 2004
HasCDPlayer Yes
HasDVDPlayer No

When a new customer comes to the store to rent a car, the company clerk would need some of the customer's personal information to keep track of who has the car. The information about the customer may include her name, address, driver's license number, etc. The list of information about the customer may appear as follows:

Customer
Name Janice Lalas
Driver's License Number L-793-475-904
Address 1402 Lamer Lane
HomePhone  

Once this information is collected, the clerk can present the available cars and their rental rates to the customer so she can select the type of car she wants. The clerk would also be interested to know how long the customer intends to keep the car. After using the car, the customer would bring it back to the store so the car can be made available to other customers.

When creating this application, you may be tempted to enter information about the car in the same list that includes the customer's information:

Customer
Name Janice Lalas
Driver's License Number L-793-475-904
Address 1402 Lamer Lane
HomePhone  
Car Rented  
Make Dodge
Model Neon
Year 2004
HasCDPlayer Yes
HasDVDPlayer No

The problem here is that, when a different customer comes to rent the same car, the clerk would have to enter the same pieces of information. Experience shows that when the same information is manually entered over and over again, it could be different from one list to another as human beings are capable of making mistakes. The solution is to create separate lists: one list for the customers, another list for the cars. When a customer comes to rent a car, the clerk can select the customer's information from one list, select the information about the car from another list, and then process a rental order. This technique tremendously reduces the likelihood of making mistakes because information about each item, whether a customer or a car, is created only once and then made available to other lists that would need that information: this is the foundation of relational databases.

A relational database is an application in which information flows from one list to another. Microsoft SQL Server, like most database environments, is a relational database system: It allows you to create tables and link them so they can exchange information among themselves.

Practical Learning Practical Learning: Starting a Relational Database Application

  1. Start Microsoft Visual Studio .NET or Visual C++ .NET
  2. Display the Server Explorer. Expand the Servers node, followed by the name of the computer, followed by SQL Servers, followed by the name of the server
  3. Right-click the server and click New Database
  4. Set the New Data Name to BCR (BCR stands for Bethesda Car Rental) and accept to use Windows NT Integrated Security
  5. Click OK

The Primary Key

The transactions among various objects of a database should make sure information of one object is accessible to another object. The objects that hold information, as we have mentioned already, are the tables.

To manage the flow of information from one table (A) to another table (B), the table that holds the information, A, must make it available to other tables, such as B. There are two issues that must be dealt with:

  1. Each record that a table (A) holds should/must be unique among all the other records of the same table (A). For example, if a clerk creates a list of cars on a table, you should make sure that, even when two cars are the exact same type (a car rental company can purchase two to four of the same car at the same time), each car must be uniquely identified so that, when one of them has been rented, the company should know with certainty what car is out and what car is available.
  2. A table (A) that holds information must make it available to other tables (such as B). Two tables must not serve the same purpose. For example, you should not have two lists of cars that hold information about the cars that can be rented (you can have different lists of cars; for example, one list may contain the cars that have just been purchased but are not yet registered to be rented, and another list that contains the cars made available for renting but the cars that are available to be rented should be in only one list: this reduces confusion).

To solve the first problem of uniquely identifying records inside of a table, in Lesson 15, we saw that you could create one column or a group of columns used as the primary key. In a relational database, which is the case for most of the databases you will be creating in SQL Server, each table should have at least one primary key.

To specify a primary key on a table, as we have already done in the past, you create one column as the PRIMARY KEY constraint and there can be only one PRIMARY KEY constraint on a table. To do this in Enterprise Manager, create a column and specify its data type. Then, on the toolbar, click the Set Primary Key button .

To create a primary column using SQL, on the right side of the column definition, type PRIMARY KEY (remember, SQL is case-insensitive). Here is an example:

CREATE TABLE Departments (

DepartmentID INT PRIMARY KEY IDENTITY(1,1) NOT NULL,

Department VARCHAR(50))

GO

Practical Learning Practical Learning: Creating Tables and their Primary Keys

  1. In the Server Explorer, expand the BCR node. Right-click the Tables node and click New Table
  2. Specify the first Column Name as RentalRateID, set its Data Type to int
  3. To make it the Primary Key constraint, while the field is still selected, on the toolbar, click the Set Primary Key button 
  4. While the field is still selected, in the bottom section of the Design Table, set the Identity field to Yes and accept the Identity Seed and the Identity Increment to 1 each
  5. Create the rest of the table as follows:
     
    Column Name Data Type Length Allow Nulls
    RentalRateID int 4  
    Category varchar   Unchecked
    DailyRate varchar 10  
    WeeklyRate varchar 10  
    MonthlyRate varchar 10  
    WeekendRate varchar 10  
  6. Save the table as RentalRates and close it
  7. In the Server Explorer, expand the Tables node under BCR and double-click RentalRates
  8. Fill it up as follows:
     
    Category DailyRate WeeklyRate MonthlyRate WeekendRate
    Economy  32.95 29.75 22.95 19.95
    Compact 39.95 34.75 24.95 29.95
    Standard 45.95 39.75 35.95 34.95
    Full Size 49.95 42.75 35.95 38.95
    Mini Van 55.95 50.95 45.95 42.95
    SUV 55.95 50.95 45.95 42.95
    Truck 42.95 35.75 30.95 30.95
    Van 69.95 59.75 50.75 49.95
  9. Close the RentalRates table
  10. In the Server Explorer and under BCR, right-click the Tables node and click New Table...
  11. Fill the new table as follows:
     
    Column Name Data Type Length Allow Nulls Properties
    EmployeeID (Primary Key) int     Identity: Yes
    EmployeeNumber char 6    
    FirstName varchar 32    
    LastName varchar 32 Unchecked  
    FullName varchar     Formula: LastName + ', ' + FirstName
    Title varchar      
    Username varchar      
    WorkPhone varchar 20    
    Extension char 2    
    HourlySalary varchar 10    
  12. Close the table
  13. When asked whether you want to save it, click Yes
  14. Type Employees and press Enter
  15. In the Tables node of BCR in the Server Explorer, double-click Employees and create a few employees as follows:
     
    EmployeeNumber FirstName LastName Title HourlySalary
    22-082 Daniel Ferguson Regional Manager 28.82
    46-288 Joseph Pearlman Sales Representative 14.58
    27-196 Paula Catalane Assistant Manager 22.64
    66-286 Barthelemy Lundquist Sales Representative 12.88
    27-284 Ellen Brooks Sales Representative 15.75 
  16. Close the table
  17. In the Server Explorer and under BCR, right-click the Tables node and click New Table...
  18. Fill the new table as follows:
     
    Column Name Data Type Length Allow Nulls Properties
    CustomerID (Primary Key) int     Identity: Yes
    DrvLicNbr varchar   Unchecked  
    DrvLicClass varchar      
    DateIssued datetime      
    DateExpired datetime      
    FullName varchar      
    Address varchar      
    City varchar      
    State varchar      
    ZIPCode varchar 20    
    Country varchar     Default Value: 'USA'
    HomePhone varchar 20    
    OrganDonor bit      
  19. Save the table as Customers and close it
  20. In the Tables node of BCR in the Server Explorer, double-click Customers and create a few customers as follows:
     
    DrvLicNbr DrvLicClass DateIssued DateExpired FullName OrganDonor
    646-856-734-P C 1/5/2002 1/5/2007 Amy Larsson 0
    793-405-719-D C 4/27/2004 4/27/2009 Anne DeCarlo 1
    294-80-2759 C 11/4/2004 11/4/2009 Chrissie Yuen 0
    731-249-759-S M 7/24/2003 7/24/2008 Scott Salomons 0
    308-45-7642 C 4/10/2003 4/10/2008 Mary Herness
  21. Close the table
 

Foreign Keys

In our introduction to relationships, we illustrated a table that could be used to enter a customer's information and the car he wants to rent:

Customer
Name Janice Lalas
Driver's License Number L-793-475-904
Address 1402 Lamer Lane
HomePhone  
Car Rented  
Make Dodge
Model Neon
Year 2004
HasCDPlayer Yes
HasDVDPlayer No

We also mentioned that it was not effective to put these two categories of information, namely the customer and the car, in the same list. The reason is that the same customer may come at different times to rent different types of cars and the same car is regularly rented by different customers. To reduce the likelihood of mistakes, you should separate these two categories, each in its own list:

Customers
Name
Driver's License Number
Address
Home Phone
Cars
Make
Model
Year
HasCDPlayer
HasDVDPlayer

This time, if you keep these two lists separate, when it is time to rent a car to a customer, you can use another list that allows the clerk to select the name of the customer, followed by the car she wants to rent:

Customers
Name
Driver's License Number
Address
Home Phone
Rental Orders
Customer
Car Rented
Rental Rate
Cars
Make
Model
Year
HasCDPlayer
HasDVDPlayer

To make this scenario work, there must be a column in the Rental Order list that represents the customers: this would be the Customer column in our illustration. The column that belongs to the Rental Order list but is used to represent the customers is called a foreign key. This column behaves like an ambassador who is not a citizen of the country where he works but instead represents his native country.

Because a foreign key is used to represent a table other than the one where it resides, the table that the foreign key represents must have a primary key that would insure the uniqueness of records in the original table. The table that holds the necessary values and that has the primary key can be referred to as the parent table. In our above illustration, the Customers table is the parent. The table that holds the foreign key is referred to as the child table, which is the case for the Rental Orders list of our illustration.

To create a foreign key, you can start by adding the necessary column in the table that will need or use it. There are rules and suggestions you should or must follow. As a suggestion, the name of the column used as the foreign key should be the same as the primary key of the table it represents. As a rule, the data type of the primary key of the parent table must be the same as the data type of the foreign key.

If you are working with SQL code, to create a foreign key, when creating the table, before the closing comma of the name of a column (if the column is not the last in the table) or the closing parenthesis of the table (if the column is the last), you can type REFERENCES followed by the name of the parent table with parentheses. In the parentheses of the name of the parent table, enter the name of the primary key column. Here is an example:

CREATE TABLE Departments (

DepartmentID INT PRIMARY KEY IDENTITY(1,1) NOT NULL,

Department VARCHAR(50))

GO



CREATE TABLE StaffMembers (

EmployeeID  INT PRIMARY KEY IDENTITY(1,1) NOT NULL,

FullName VARCHAR(50),

DepartmentID INT REFERENCES Departments(DepartmentID))

GO

When you create a table like this, the interpreter would know that, in the StaffMembers table, the DepartmentID column is a foreign key. If you want to explicitly indicate that the column is a foreign key, you can type FOREIGN KEY before specifying it with REFERENCES. Here is an example:

CREATE TABLE StaffMembers (

EmployeeID  INT PRIMARY KEY IDENTITY(1,1) NOT NULL,

FullName VARCHAR(50),

DepartmentID INT FOREIGN KEY REFERENCES Departments(DepartmentID))

GO

You can also specify the name of the primary key of the parent table in parentheses you would include following FOREIGN KEY. This can be done as follows:

CREATE TABLE StaffMembers (

EmployeeID  INT PRIMARY KEY IDENTITY(1,1) NOT NULL,

FullName VARCHAR(50),

DepartmentID INT FOREIGN KEY(DepartmentID) REFERENCES Departments(DepartmentID))

GO

If you are planning to reference the foreign key as an object somewhere in your code, you can give it an explicit name. To do this, when creating a table, type CONSTRAINT followed by the name of the foreign key constraint. Here is an example:

CREATE TABLE StaffMembers (

EmployeeID  INT PRIMARY KEY IDENTITY(1,1) NOT NULL,

FullName VARCHAR(50),

DepartmentID INT CONSTRAINT FK_Department 

		FOREIGN KEY(DepartmentID)

		REFERENCES Departments(DepartmentID))

GO

If you are working from the SQL Server Enterprise Manager or from the Server Explorer, to specify that a column is used as a foreign key, you can start by creating a column that has the same name and the same data type as the primary key of the parent table. Then, you can right-click anywhere in the table and click Relationships... This would open the Properties dialog box. To actually create a foreign key, in the Properties dialog box, you can click New. You would be asked to select the primary key from the parent table and the foreign from the child table. A name would be generated by the relationship. Once you are satisfied, you can click Close.

 

Practical Learning Practical Learning: Creating Foreign Keys

  1. In the Server Explorer, under the BCR node, right-click Tables and click New Table
  2. Create the columns of the table as follows:
     
    Column Name Data Type Length Allow Nulls Properties
    CarID (Primary Key) int     Identity: Yes
    TagNumber  varchar      
    Make  varchar      
    Model varchar      
    CarYear smallint      
    RentalRateID int      
    HasK7Player bit      
    HasCDPlayer bit      
    HasDVDPlayer bit      
    Picture varchar 120   Default Value: 'C:\Programs\Unknown.bmp'
    Available bit      
    Notes text      
  3. Save the table as Cars and close it
  4. Double-click Cars and create a few cars as follows:
     
    TagNumber Make Model CarYear RentalRateID HasK7Player HasCDPlayer HasDVDPlayer Available
    294-759 Mercury Grand Marquis 2002 4 1 0 0 1
    M294668 Lincoln Navigator 2004 6 0 1 1 1
    962-048 Hyundai Elantra 2002 2 0 1 0 0
    348759 Hyundai Accent 2003 1 0 0 0 0
  5. To create a new table, right-click the Tables node of BCR and click New Table
  6. Fill it up as follows:
     
    Column Name Data Type Length Allow Nulls Properties
    RentalOrderID (Primary Key) int     Identity: Yes
    EmployeeID int   Unchecked  
    CustomerID int   Unchecked  
    CarID int   Unchecked  
    CarCondition varchar      
    TankLevel varchar      
    Mileage int      
    StartDate datetime      
    EndDate datetime      
    NumberOfDays smallint     Default Value: 0
    RateApplied decimal     Default Value: 20.00
    Scale: 4
    SubTotal decimal     Default Value: 0.00
    Scale: 4
    TaxRate decimal     Scale: 2
    Default Value: 5.75
    TaxAmount decimal     Default Value: 0.00
    Scale: 4
    RentTotal decimal     Default Value: 20.00
    Scale: 4
    Notes text      
  7. Save the table as RentalOrders and close it
  8. Make sure the Cars table displays in design.
    Right-click somewhere in the table and click Relationships... 
  9. In the Relationships property page, click the New button
  10. In the Primary Key Table combo box, select RentalRates
  11. In the first combo box of the grid under RentalRates, select RentalRateID
  12. In the Foreign Key Table combo box, select Cars (it should be selected already)
    Click the grid under Cars to reveal a combo box. In the combo box, select RentalRateID
      
  13. Click Close
  14. Close the table. When asked to save, click Yes and Yes
 

Diagrams

If you have created the relationships of your tables using SQL code, you only have a vague idea of the flow of information among them. A diagram is a window that visually displays the relationships among tables of a database. A diagram also allows you to troubleshoot a relation when necessary. The Diagram window provides all the tools you need to create, view, change, delete or maintain relationships.

To create a diagram, if you are working from the SQL Server Enterprise Manager, in the node of the database, you can right-click the Diagrams node and click New Database Diagram. This would launch the Create Database Diagram Wizard that you can follow to select the necessary tables.

Once in the Diagram window, each table displays as a rectangle with dividing lines like a spreadsheet:

To effectively work on the tables, you should make sure you can identify each. If the view makes it difficult, you can zoom it. To do this, you can right-click a white area of the window, position the mouse on Zoom, and select a percentage. You can also click the Zoom button on the toolbar. Each table is equipped with a title bar on top that displays its name. If you forgot to add a table, you can right-click a white area in the window and click Add Table... This would display the list of tables of the same database, allowing you to select the desired table(s) before clicking Add and then Close. If you had added a table by mistake or you don't want to include an existing table in the diagram, to remove a table, you can right-click the table and click Remove Table From Diagram. To move a table, you drag its title bar.

To create a relationship between two tables, you can right-click anywhere in the diagram and click Relationships... You would then proceed as we did in the section on Foreign Keys. To create or indicate a foreign key, you can drag the necessary column from a parent table to a child table.

Once a relationship between two tables exists, there is a line that joins them. You can then manage the relationship. To identify a relationship, you can position the mouse on the line that joins the table:

To delete a relationship, you can right-click it and click Delete Relationship From Database.

Practical Learning Practical Learning: Creating Foreign Keys

  1. In the Server Explorer, under the BCR node, right-click Database Diagrams and click New Diagram...
  2. In the Add Table dialog box, click each table and click Add
     
  3. When all tables have been added, click Close on the Add Table dialog box
  4. Position the tables as follows:
     
  5. Click the gray box on the left of the EmployeeID field in the Employees table.
    Click and drag that box then drop it on the EmployeeID field of the RentalOrders table:
     
  6. On the Create Relationship dialog box, make sure EmployeeID is selected for the Employees Primary Key Table and that EmployeeID is selected for the RentalOrders Foreign Key Table
     
  7. Click OK
  8. To save the diagram, on the toolbar, click the Save button
  9. Type BCRMap as the name of the diagram and press Enter
  10. When notified that some tables need to be saved, click Yes
 

Relationships and Data Integrity

As mentioned in previous sections, relationships allow information to flow from one list, the parent table, to another list, the child table. When maintaining records, sometimes a piece of information may become obsolete. An employee may decide to change or to delete it from the parent table. This would cause the relation information in the child table to become orphan. When this happens, you need to take appropriate action. Referential integrity is the ability to take care of necessary details when data from a table gets changed or deleted.

When a piece of information is changed in a parent table, you need to make sure that the change is replicated to the related child table. If you are creating or troubleshooting a table using the Design Table from the SQL Server Enterprise Manager or from Server Explorer, after displaying the Create Relationship or the Property Pages, you can click the Cascade Update Related Fields check box.

If a piece of information is deleted in a parent, the records of the child table(s) that depended on it should be notified. If you are working from a table in the Design Table from the SQL Server Enterprise Manager or from Server Explorer, after displaying the Create Relationship or the Property Pages, you can click the Cascade Delete Related Records check box.

 

Practical Learning Practical Learning: Insuring Referential Integrity

  1. The Diagram window should still be displaying
    Right-click the line between RentalRates and Cars and click Properties (SQL Server) or Property Pages (Visual Studio .NET)
  2. In the Relationship tab of the Property Pages, make sure the FK_Cars_RentalRates item is selected in the Selected Relationship combo box.
    In the bottom section, click the Cascade Update Relation Fields and the Cascade Delete Related Records check boxes
     
  3. Click Close
  4. In the same way, right-click the line between the Employees and the RentalOrders tables and click Properties. Click both bottom check boxes and click Close
  5. Drag CarID from the Cars table and drop it on CarID from the RentalOrders table
  6. In the Create Relationship dialog box, click both bottom check boxes
     
  7. Click OK
  8. Drag CustomerID from the Customers table and drop on CustomerID from the RentalOrders table
  9. In the Create Relationship dialog box, click both bottom check boxes
     
  10. Click OK
  11. To save the diagram, on the toolbar, click the save button
  12. After reading the message box, click Yes
     
  13. Close the Diagram window and any table that is opened

Windows Applications and Database Relationships

When creating an application that uses a database with related tables, to allow the user to select information from parent tables when using forms from child tables, you would configure list-based controls to get their data from the parent tables.

 

Practical Learning Practical Learning: Creating the Windows Application

  1. Open Windows Explorer or My Computer and create a folder named Programs on the C: drive
  2. Open Paint (Start -> Programs -> Accessories -> Paint) and paste the following picture:
     
  3. Save it as Unknown.bmp in the C:\Programs folder
  4. Return to Microsoft Visual C++ .NET and create a new Windows Forms Application named BCR4
  5. To create a new form, on the main menu, click Project -> Add New File...
  6. In the New File dialog box, click Windows Form and set its Name to Employees
  7. In Server Explorer, under the BCR database, expand the Tables node.
    Drag Employees and drop it on the form
  8. To create a data set, on the main menu, click Data -> Generate Dataset...
  9. Accept the New radio button. Change the name to dsBCR and click OK 
  10. Design the form as follows:
     
    Control Name Text/CaptionText Other Properties
    DataGrid   Employees Records Anchor:Top, Bottom, Left, Right
    Auto Format: Colorful 2
    DataSource: dsBCR1
    Button btnClose Close Anchor:Bottom, Right
  11. Double-click an unoccupied area of the form and implement the event as follows:
     
    private: System::Void Employees_Load(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 this->sqlDataAdapter1->Fill(this->dsBCR1);
    
    }
  12. Return to the form and click the DataGrid
  13. In the Properties window, click the Events button and double-click the right field to CurrentCellChanged
  14. Implement the event as follows:
     
    private: System::Void dataGrid1_CurrentCellChanged(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 this->sqlDataAdapter1->Update(this->dsBCR1);
    
    }
  15. Return to the form and double-click the Close button
  16. Implement its event as follows:
     
    private: System::Void btnClose_Click(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 Close();
    
    }
  17. Save all
  18. To create a new form, on the main menu, click Project -> Add New File...
  19. In the New File dialog box, click Windows Form and set its Name to Customers
  20. Design the form as follows:
     
     
    Control Name Text Other Properties
    TabPage pgeCustomers Customers  
    Label   Customer ID:  
    TextBox txtCustomerID    
    Label   Drv License #:  
    TextBox txtDrvLicNbr    
    Label   Class:  
    TextBox txtDLClass    
    Label   Date Issued:  
    DateTimePicker dtpDateIssued   Format: Short
    Label   Exp. Date:  
    DateTimePicker dtpExpDate   Format: Short
    Label   Full Name:  
    TextBox txtFullName    
    Label   Address:  
    TextBox txtAddress    
    Label   City:  
    TextBox txtCity    
    Label   State:  
    TextBox txtState    
    Label   ZIP Code:  
    TextBox txtZIPCode    
    Label   Country:  
    TextBox txtCountry    
    Label   Home Phone:  
    TextBox txtHomePhone    
    CheckBox chkOrganDonor Organ Donor? CheckAlign: MiddleRight
    Button btnFirst First  
    Button btnPrevious Previous  
    Button btnNext Next  
    Button btnLast Last  
    Button btnLoad Load  
    Button btnClose Close  
    Control Name Text Other Properties
    TabControl tabCustomers    
    TabPage pgeCustomers Customers  
    Label   Drv License #:  
    TextBox txtNCDrvLicNbr    
    Label   Class:  
    TextBox txtNCDLClass    
    Label   Date Issued:  
    DateTimePicker dtpNCDateIssued   Format: Short
    Label   Exp. Date:  
    DateTimePicker dtpNCExpDate   Format: Short
    Label   Full Name:  
    TextBox txtNCFullName    
    Label   Address:  
    TextBox txtNCAddress    
    Label   City:  
    TextBox txtNCCity    
    Label   State:  
    TextBox txtNCState    
    Label   ZIP Code:  
    TextBox txtNCZIPCode    
    Label   Country:  
    TextBox txtNCCountry    
    Label   Home Phone:  
    TextBox txtNCHomePhone    
    CheckBox chkNCOrganDonor Organ Donor? CheckAlign: MiddleRight
    Button btnReset Reset  
    Button btnCreate Create  
  21. On the form click the Customers tab and click an empty area of its box. For example, click the area just below the Notes label
  22. In Server Explorer, from the Tables node of the BCR database, drag Customers and drop it inside the Customers tab page. For example, drop it just below the Notes label
  23. To create a data set, on the main menu, click Data -> Generate Dataset...
  24. Accept the Existing radio button with the dsBCR name and click OK
  25. Bind the controls using the following table:
     
    Control DataBinding
    Type Value: dsBCR1 - Customers.
    txtCustomerID Text CustomerID
    txtDrvLicNbr Text DrvLicNbr
    txtDLClass Text DrvLicClass 
    dtpDateIssued Value DateIssued 
    dtpExpDate Value DateExpired
    txtFullName Text FullName
    txtAddress Text Address
    txtCity Text City
    txtState Text State
    txtZIPCode Text ZIPCode
    txtCountry Text Country
    txtHomePhone Text HomePhone
    chkOrganDonor Checked OrganDonor
  26. Double-click the Load button of the form and implement the event as follows:
     
    System::Void btnLoad_Click(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 this->sqlDataAdapter1->Fill(this->dsBCR1);
    
    }
  27. Return to the form. Double-click the First button and implement its Click event as follows:
     
    System::Void btnFirst_Click(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 this->pgeCustomers->BindingContext->get_Item(this->dsBCR1, S"Customers")->Position = 0;
    
    	 this->sqlDataAdapter1->Update(this->dsBCR1);
    
    }
  28. Return to the form. Double-click the Previous button and implement its Click event as follows:
     
    private: System::Void btnPrevious_Click(System::Object *  sender, System::EventArgs *  e)
    
    {
    
     this->pgeCustomers->BindingContext->get_Item(this->dsBCR1, S"Customers")->Position = 
    
     this->pgeCustomers->BindingContext->get_Item(this->dsBCR1, S"Customers")->Position - 1;
    
    	 this->sqlDataAdapter1->Update(this->dsBCR1);
    
    }
  29. Return to the form. Double-click the Next button and implement its Click event as follows:
     
    private: System::Void btnNext_Click(System::Object *  sender, System::EventArgs *  e)
    
    {
    
     this->pgeCustomers->BindingContext->get_Item(this->dsBCR1, S"Customers")->Position = 
    
    this->pgeCustomers->BindingContext->get_Item(this->dsBCR1, S"Customers")->Position + 1;
    
    	 this->sqlDataAdapter1->Update(this->dsBCR1);
    
    }
  30. Return to the form. Double-click the Last button and implement its Click event as follows:
     
    private: System::Void btnLast_Click(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    this->pgeCustomers->BindingContext->get_Item(this->dsBCR1, S"Customers")->Position = 
    
     this->pgeCustomers->BindingContext->get_Item(this->dsBCR1, S"Customers")->Count - 1;
    
    	 this->sqlDataAdapter1->Update(this->dsBCR1);
    
    }
  31. Return to the form and double-click the Close button
  32. Implement its event as follows:
     
    private: System::Void btnClose_Click(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 Close();
    
    }
  33. Save all
  34. Return to the form and click the New Customer tab
  35. In the New Customer page, double-click the Reset button and implement its Click event as follows:
     
    private: System::Void btnReset_Click(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 this->txtNCDrvLicNbr->Text = S"";
    
    	 this->txtNCDLClass->Text = S"C";
    
    	 this->dtpNCDateIssued->Value = DateTime::Today;
    
    	 this->dtpNCExpDate->Value    = DateTime::Today;
    
    	 this->txtNCFullName->Text = S"";
    
    	 this->chkNCOrganDonor->Checked = false;
    
    	 this->txtNCAddress->Text = S"";
    
    	 this->txtNCCity->Text = S"";
    
    	 this->txtNCState->Text = S"MD";
    
    	 this->txtNCZIPCode->Text = S"";
    
    	 this->txtNCCountry->Text = S"USA";
    
    	 this->txtNCHomePhone->Text = S"(000) 000-0000";
    
    
    
    	 this->txtNCDrvLicNbr->Focus();
    
    }
  36. Return to the form and double-click the Create button
  37. Implement its Click event as follows:
     
    System::Void btnCreate_Click(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 String *strDrvLicNbr = this->txtNCDrvLicNbr->Text;
    
    	 String *strFullName = this->txtNCFullName->Text;
    
    
    
    	 if( strDrvLicNbr->Equals(S"") )
    
    	 {
    
    	MessageBox::Show(S"You must provide the customer's driver's license number");
    
    		 return;
    
    	 }
    
    	 if( strFullName->Equals(S"") )
    
    	 {
    
    		 MessageBox::Show(S"You must provide the customer's name");
    
    		 return;
    
    	 }
    
    
    
    	 String *strInsert = String::Concat(S"INSERT INTO Customers(DrvLicNbr, DrvLicClass, "
    
    		                           S"DateIssued, DateExpired, FullName, Address, "
    
    				S"City, State, ZIPCode, Country, HomePhone, "
    
    				S"OrganDonor) VALUES('",
    
    				this->txtNCDrvLicNbr->Text, S"', '", txtNCDLClass->Text,
    
    				S"', '", dtpNCDateIssued->Value, S"', '", 
    
    				dtpNCExpDate->Value, S"', '", txtNCFullName->Text,
    
    				S"', '", txtNCAddress->Text, S"', '", 
    
    				txtNCCity->Text, S"', '", txtNCState->Text, S"', '", 
    
    				txtNCZIPCode->Text, S"', '", txtNCCountry->Text, S"', '", 
    
    		txtNCHomePhone->Text, S"', '", this->chkNCOrganDonor->Checked, S"');");
    
    
    
    	 System::Data::SqlClient::SqlCommand    *cmdNewCustomer = 
    
    		new System::Data::SqlClient::SqlCommand(strInsert, this->sqlConnection1);
    
    
    
    	 sqlConnection1->Open();
    
    
    
    	 cmdNewCustomer->ExecuteNonQuery();
    
    	 sqlConnection1->Close();
    
    
    
    	 this->btnReset_Click(sender, e);
    
    }
  38. Save all
  39. To create a new form, on the main menu, click Project -> Add New File...
  40. In the New File dialog box, click Windows Form and set its Name to RentalRates
  41. In Server Explorer, in the Tables node of the BCR database, drag RentalRates and drop it on the form
  42. To create a data set, on the main menu, click Data -> Generate Dataset...
  43. Accept the Existing radio button with the dsBCR name and click OK
  44. Design the form as follows:
     
    Control Text/CaptionText Other Properties
    DataGrid Employees Records Auto Format: Colorful 2
    DataSource: dsBCR1
    Dock: Fill
    Form Bethesda Car Rental FormBorderStyle: SizableToolWindow
    ShowInTaskbar: False
    StartPosition: Manual
    TopMost: True
  45. In the combo box above the Properties window, select RentalRates. In the Events section of the Properties window, double-click to the right of Load and implement the event as follows:
     
    private: System::Void RentalRates_Load(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 this->sqlDataAdapter1->Fill(this->dsBCR1);
    
    }
  46. Save all
  47. To create a new form, on the main menu, click Project -> Add New File...
  48. In the New File dialog box, click Windows Form and set its Name to Cars
  49. Design the form as follows:
     
    Control Name Text Other Properties
    TabControl tabCars    
    TabPage pgeCars Cars Details  
    Label   Tag #:  
    TextBox txtTagNumber    
    Label   Car ID:  
    TextBox txtCarID    
    Label   Make:  
    TextBox txtMake    
    Label   Model:  
    TextBox txtModel    
    Label   Year  
    TextBox txtYear:    
    Label   Category:  
    ComboBox cboRentalRateID   DropDownStyle: DropDownList
    CheckBox chkK7Player Cassette Player CheckAlign: MiddleRight
    CheckBox chkDVDPlayer DVD Player CheckAlign: MiddleRight
    CheckBox chkCDPlayer CD Player CheckAlign: MiddleRight
    CheckBox chkAvailable Available CheckAlign: MiddleRight
    PictureBox pctCar    
    Button btnPicture Picture  
    TextBox txtPicture C:\Programs\Unknown.bmp  
    Label   Notes:  
    TextBox txtNotes   Multiline: True
    ScrollBars: Vertical
    Button btnFirst First  
    Button btnPrevious Previous  
    Button btnNext Next  
    Button btnLast Last  
    Button btnLoad Load  
    Button btnClose Close  
     
    Control Name Text Other Properties
    TabPage pgeNewCar New Car  
    Label   Tag #:  
    TextBox txtNCTagNumber    
    Label   Make:  
    TextBox txtNCMake    
    Label   Model:  
    TextBox txtNCModel    
    Label   Year  
    TextBox txtNCYear:    
    Label   Category:  
    ComboBox cboNCRentalRateID   DropDownStyle: DropDownList
    CheckBox chkNCK7Player Cassette Player CheckAlign: MiddleRight
    CheckBox chkNCDVDPlayer DVD Player CheckAlign: MiddleRight
    CheckBox chkNCCDPlayer CD Player CheckAlign: MiddleRight
    CheckBox chkNCAvailable Available CheckAlign: MiddleRight
    PictureBox pctNCar    
    Button btnNCPicture Picture  
    TextBox txtNCPicture    
    Label   Notes:  
    TextBox txtNCNotes   Multiline: True
    ScrollBars: Vertical
    Button btnReset Reset  
    Button btnCreate Create  
  50. On the form click the Cars Details tab and click an empty area of its box. For example, click the area just below the Notes label
  51. In Server Explorer, from the Tables node of the BCR database, click Cars to select it. Press and hold Ctrl, then click RentalRates to select both and release Ctrl
  52. Drag the selected tables and drop them inside the Cars Details tab page. For example, drop them just below the Notes label
  53. To create a data set, on the main menu, click Data -> Generate Dataset...
  54. Accept the Existing radio button with the dsBCR name. Click the Cars check box and make sure both check boxes are selected:
     
  55. Click OK
  56. Bind the controls using the following table:
     
    Control DataBindings Type Selection:
    dsBCR1-Cars.
    Others
    txtTagNumber Text TagNumber  
    txtCarID Text CarID  
    txtMake Text Make  
    txtModel Text Model  
    txtYear Text CarYear  
    cboRentalRateID SelectedValue RentalRateID DataSource: dsBCR1.RentalRates
    DisplayMember: Category
    ValueMember: RentalRateID
    chkK7Player Checked HasK7Player  
    chkDVDPlayer Checked HasDVDPlayer  
    chkCDPlayer Checked HasCDPlayer  
    chkAvailable Checked IsAvailable  
    txtPicture Text Picture  
    txtNotes Text Notes  
    cboNCRentalRateID SelectedValue RentalRateID DataSource: dsBCR1.RentalRates
    DisplayMember: Category
    ValueMember: RentalRateID
  57. In the Windows Forms section of the Toolbox, click the OpenFileDialog button and click the form
  58. Set its Filter as Picture Files (*.bmp;*.gif;*.jpeg;*jpg)|*.bmp;*.gif;*.jpeg;*jpg
  59. On the form, click the Car Details tab and double-click the Picture button
  60. Implement its event as follows:
     
    private: System::Void btnPicture_Click(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 if( openFileDialog1->ShowDialog() == DialogResult::OK )
    
    	 {
    
    		 this->txtPicture->Text = openFileDialog1->FileName;
    
    		 this->pctCar->Image = Bitmap::FromFile(openFileDialog1->FileName);
    
    	 }
    
    }
  61. Return to the form and click the New Car tab
  62. In the New Car section, double-click the Picture button and implement its Click event as follows:
     
    private: System::Void btnNCPicture_Click(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 if( openFileDialog1->ShowDialog() == DialogResult::OK )
    
    	 {
    
    		 this->txtNCPicture->Text = openFileDialog1->FileName;
    
    		 this->pctNCar->Image = Bitmap::FromFile(openFileDialog1->FileName);
    
    	 }
    
    }
  63. Return to the form. Double-click the Load button and implement the event as follows:
     
    System::Void btnLoad_Click(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 this->sqlDataAdapter2->Fill(this->dsBCR1);
    
    	 this->sqlDataAdapter1->Fill(this->dsBCR1);
    
    	 this->pctCar->Image = Bitmap::FromFile(this->txtPicture->Text);
    
    }
  64. Return to the form and click the Car Details. Double-click the First button and implement its Click event as follows:
     
    System::Void btnFirst_Click(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 this->pgeCars->BindingContext->get_Item(this->dsBCR1, S"Cars")->Position = 0;
    
    	 this->pctCar->Image = Bitmap::FromFile(this->txtPicture->Text);
    
    	 this->sqlDataAdapter1->Update(this->dsBCR1);
    
    }
  65. Return to the form. Double-click the Previous button and implement its Click event as follows:
     
    System::Void btnPrevious_Click(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 this->pgeCars->BindingContext->get_Item(this->dsBCR1, S"Cars")->Position = 
    
    		 this->pgeCars->BindingContext->get_Item(this->dsBCR1, S"Cars")->Position - 1;
    
    	 this->pctCar->Image = Bitmap::FromFile(this->txtPicture->Text);
    
    	 this->sqlDataAdapter1->Update(this->dsBCR1);
    
    }
  66. Return to the form. Double-click the Next button and implement its Click event as follows:
     
    System::Void btnNext_Click(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 this->pgeCars->BindingContext->get_Item(this->dsBCR1, S"Cars")->Position = 
    
    		 this->pgeCars->BindingContext->get_Item(this->dsBCR1, S"Cars")->Position + 1;
    
    	 this->pctCar->Image = Bitmap::FromFile(this->txtPicture->Text);
    
    	 this->sqlDataAdapter1->Update(this->dsBCR1);
    
    } 
  67. Return to the form. Double-click the Last button and implement its Click event as follows:
     
    System::Void btnLast_Click(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 this->pgeCars->BindingContext->get_Item(this->dsBCR1, S"Cars")->Position = 
    
    		 this->pgeCars->BindingContext->get_Item(this->dsBCR1, S"Cars")->Count - 1;
    
    	 this->pctCar->Image = Bitmap::FromFile(this->txtPicture->Text);
    
    	 this->sqlDataAdapter1->Update(this->dsBCR1);
    
    }
  68. Return to the form and double-click the Close button
  69. Implement its event as follows:
     
    private: System::Void btnClose_Click(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 Close();
    
    }
  70. Return to the form and click the New Car tab
  71. In the New Car page, double-click the Reset button and implement its Click event as follows:
     
    System::Void btnReset_Click(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 this->txtNCTagNumber->Text = S"";
    
    	 this->txtNCMake->Text = S"";
    
    	 this->txtNCModel->Text = S"";
    
    	 this->txtNCYear->Text = S"";
    
    	 this->cboNCCategoryID->SelectedIndex = -1;
    
    	 this->chkNCK7Player->Checked = false;
    
    	 this->chkNCDVDPlayer->Checked = false;
    
    	 this->chkNCCDPlayer->Checked = false;
    
    	 this->chkNCAvailable->Checked = false;
    
    	 this->txtNCPicture->Text = S"C:\\Programs\\Unknown.bmp";
    
    	 this->pctNCar->Image = Bitmap::FromFile(this->txtNCPicture->Text);
    
    	 this->txtNCNotes->Text = S"";
    
    
    
    	 this->txtNCTagNumber->Focus();
    
    }
  72. Return to the form and double-click the Create button
  73. Implement its Click event as follows:
     
    System::Void btnCreate_Click(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 String *strInsert = String::Concat(S"INSERT INTO Cars(TagNumber, Make, Model, "
    
    		                                S"CarYear, RentalRateID, HasK7Player, "
    
    				S"HasCDPlayer, HasDVDPlayer, Picture, "
    
    				S"Available, Notes) VALUES('",
    
    				this->txtNCTagNumber->Text, S"', '",
    
    				txtNCMake->Text, S"', '", txtNCModel->Text,
    
    				S"', '", txtNCYear->Text, S"', '",
    
    				cboNCCategoryID->SelectedIndex, S"', '",
    
    				chkNCCDPlayer->Checked, S"', '", 
    
    				chkNCDVDPlayer->Checked, S"', '",
    
    				chkNCDVDPlayer->Checked, S"', '", 
    
    				txtNCPicture->Text, S"', '",
    
    				chkNCAvailable->Checked, S"', '", 
    
    				txtNCNotes->Text, S"');");
    
    
    
    	 System::Data::SqlClient::SqlCommand    *cmdNewCar = new 
    
    		System::Data::SqlClient::SqlCommand(strInsert, this->sqlConnection1);
    
    
    
    	 sqlConnection1->Open();
    
    
    
    	 cmdNewCar->ExecuteNonQuery();
    
    	 sqlConnection1->Close();
    
    
    
    	 this->btnReset_Click(sender, e);
    
    }
  74. Save all
  75. To create a new form, on the main menu, click Project -> Add New File...
  76. In the New File dialog box, click Windows Form and set its Name to RentalOrders
  77. In Server Explorer, in the Tables node of the BCR database, click Cars
  78. Press and hold Ctrl
  79. Click Customers, Employees, and RentalOrders
  80. Release Ctrl
  81. Drag the selected tables and drop them on the form
  82. To create a data set, on the main menu, click Data -> Generate Dataset...
     
  83. Accept the Existing radio button with the dsBCR name and click OK
  84. Design the form as follows:
     
    Control Name Text/CaptionText Items Other Properties
    GroupBox   Order Identification    
    Label   Processed By:    
    ComboBox cboEmployeeID      
    Label   Receipt #:    
    TextBox txtRentalOrderID      
    Button btnFindReceipt Find    
    GroupBox   Car Selected    
    ComboBox cboCarID     DropDownStyle: DropDownList
    Label   Make:    
    TextBox txtMake      
    Label   Model:    
    TextBox txtModel      
    Label   Year:    
    TextBox txtCarYear      
    Label   Car Condition:    
    ComboBox cboCarCondition   Excellent
    Good
    Drivable
    Needs Repair
     
    GroupBox   Customer    
    ComboBox cboCustomerID     DropDownStyle: DropDownList
    Label   Name:    
    TextBox txtCustName      
    Label   Address:    
    TextBox txtCustAddress      
    TextBox txtCustCity      
    TextBox txtCustState MD    
    TextBox txtCustZIPCode      
    TextBox txtCustCountry USA    
    GroupBox   Order Evaluation    
    Label   Tank Level:    
    ComboBox cboTankLevel   Full Tank
    3/4 Full
    Half
    1/4 Full
    Empty
     
    Button btnRateApplied Rate Applied   FlatStyle: Popup
    TextBox txtRateApplied 24.95   TextAlign: Right
    Label   Mileage:    
    TextBox txtMileage 0   TextAlign: Right
    Label   Sub Total    
    TextBox txtSubTotal 0.00   TextAlign: Right
    Label   Start Date:    
    DateTimePicker dtpStartDate     Format: Custom
    CustomFormat: ddd dd MMM yyyy
    Label   Tax Rate:    
    TextBox txtTaxRate 5.75   TextAlign: Right
    Label   %    
    Label   End Date:    
    DateTimePicker dtpEndDate     Format: Custom
    CustomFormat: ddd dd MMM yyyy
    Label   Tax Amount:    
    TextBox txtTaxAmount 0.00   TextAlign: Right
    Label   Number of Days:    
    TextBox txtNumberOfDays 0   TextAlign: Right
    Label   Order Total:    
    TextBox txtOrderTotal 0.00   TextAlign: Right
    GroupBox   Management    
    Button btnNewOrder New Rental Order    
    Button btnReset Reset/Start New    
    Button btnClose Close    
  85. Bind the controls using the following table:
     
    Control DataBindings Type Selection:
    dsBCR1 -RentalOrders.
    Others
    cboEmployeeID SelectedValue EmployeeID DataSource: dsBCR1.Employees
    DisplayMember: FullName
    ValueMember: EmployeeID
    cboCarID SelectedValue CarID DataSource: dsBCR1.Cars
    DisplayMember: TagNumber
    ValueMember: CarID
    cboCustomerID SelectedValue CustomerID DataSource: dsBCR1.Customers
    DisplayMember: DrvLicNbr
    ValueMember: CustomerID
  86. Generate the Load event of the form and implement it as follows:
     
    private: System::Void RentalOrders_Load(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 this->sqlDataAdapter4->Fill(this->dsBCR1);
    
    	 this->sqlDataAdapter3->Fill(this->dsBCR1);
    
    	 this->sqlDataAdapter2->Fill(this->dsBCR1);
    
    	 this->sqlDataAdapter1->Fill(this->dsBCR1);
    
    }
  87. Return to the form and double-click the Car Selected combo box
  88. Implement its SelectedIndexChanged event as follows:
     
    System::Void cboCarID_SelectedIndexChanged(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    		 String *strTagNumber = S"000000";
    
    		 String *strMake      = S"Not Found";
    
    		 String *strModel     = S"Not Found";
    
    		 String *strYear      = S"0";
    
    
    
    		 // Get a reference to the records of the Cars table
    
    		 DataRowCollection *rows = this->dsBCR1->Tables->Item[S"Cars"]->Rows;
    
    		 // Check each record one by one
    
    		 for(int i = 0; i < rows->Count; i++)
    
    		 {
    
    			 // Get the current tag number
    
    		strTagNumber = dynamic_cast<String *>(rows->Item[i]->Item[S"TagNumber"]);
    
    
    
    			 // If the current tag matches the one selected by the user...
    
    			 if( strTagNumber->Equals(this->cboCarID->Text) )
    
    			 {
    
    				 // Retrieve its corresponding make, model, and year
    
    			 strMake  = dynamic_cast<String *>(rows->Item[i]->Item[S"Make"]);
    
    			 strModel = dynamic_cast<String *>(rows->Item[i]->Item[S"Model"]);
    
    			 strYear  = dynamic_cast<String *>(rows->Item[i]->Item[S"CarYear"]);
    
    			 // Since you found a match, STOP!!!
    
    				 break;
    
    			 }
    
    		 }
    
    		 
    
    		 // Display the make, model, and year of the selected car
    
    		 this->txtMake->Text = strMake;	
    
    		 this->txtModel->Text = strModel;
    
    		 this->txtCarYear->Text = strYear;
    
    }
  89. Return to the form and double-click the Customer combo box
  90. Implement its SelectedIndexChanged event as follows:
     
    System::Void cboCustomerID_SelectedIndexChanged(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 String *strDrvLicNbr   = S"000000";
    
    	 String *strCustName    = S"Not Found";
    
    	 String *strCustAddress = S"";
    
    	 String *strCustCity    = S"";
    
    	 String *strCustState   = S"";
    
    	 String *strCustZIPCode = S"";
    
    	 String *strCountry     = S"";
    
    
    
    	 // Get a reference to the records of the Customers table
    
    	 DataRowCollection *rows = this->dsBCR1->Tables->Item[S"Customers"]->Rows;
    
    	 // Check each record one by one
    
    	 for(int i = 0; i < rows->Count; i++)
    
    	 {
    
    		 // Get the current tag number
    
    		strDrvLicNbr = dynamic_cast<String *>(rows->Item[i]->Item[S"DrvLicNbr"]);
    
    
    
    		 // If the current driver's license number matches the one selected by the user...
    
    		 if( strDrvLicNbr->Equals(this->cboCustomerID->Text) )
    
    		 {
    
    			 // Retrieve its corresponding information
    
    		 strCustName    = dynamic_cast<String *>(rows->Item[i]->Item[S"FullName"]);
    
    		 strCustAddress = dynamic_cast<String *>(rows->Item[i]->Item[S"Address"]);
    
    		 strCustCity    = dynamic_cast<String *>(rows->Item[i]->Item[S"City"]);
    
    		 strCustState   = dynamic_cast<String *>(rows->Item[i]->Item[S"State"]);
    
    		 strCustZIPCode = dynamic_cast<String *>(rows->Item[i]->Item[S"ZIPCode"]);
    
    		 strCountry     = dynamic_cast<String *>(rows->Item[i]->Item[S"Country"]);
    
    			 // Since you found a match, STOP!!!
    
    				 break;
    
    		 }
    
    	 }
    
    		 
    
    	 // Display the details of the customer
    
    	 this->txtCustName->Text    = strCustName;	
    
    	 this->txtCustAddress->Text = strCustAddress;
    
    	 this->txtCustCity->Text    = strCustCity;
    
    	 this->txtCustState->Text   = strCustState;
    
    	 this->txtCustZIPCode->Text = strCustZIPCode;
    
    	 this->txtCustCountry->Text = strCountry;
    
    }
  91. Return to the form. Double-click the Rate Applied button and, in the top section of the file, just under the #pragma once line, type:
     
    #include "RentalRates.h"
  92. Implement the event as follows:
     
    System::Void btnRateApplied_Click(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 RentalRates *frmRates = new RentalRates();
    
    	 frmRates->Show();
    
    }
  93. Double-click the Reset/Start New button and implement its Click event as follows:
     
    System::Void btnReset_Click(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 this->cboEmployeeID->SelectedIndex = -1;
    
    	 this->txtRentalOrderID->Text = S"";
    
    	 this->cboCarID->SelectedIndex = -1;
    
    	 this->txtMake->Text = S"";
    
    	 this->txtModel->Text = S"";
    
    	 this->txtCarYear->Text = S"";
    
    	 this->cboCarCondition->SelectedIndex = 0;
    
    	 this->cboCustomerID->SelectedIndex = -1;
    
    	 this->txtCustName->Text = S"";
    
    	 this->txtCustAddress->Text = S"";
    
    	 this->txtCustCity->Text = S"";
    
    	 this->txtCustState->Text = S"";
    
    	 this->txtCustZIPCode->Text = S"";
    
    	 this->txtCustCountry->Text = S"USA";
    
    	 this->cboTankLevel->SelectedIndex = 0;
    
    	 this->txtRateApplied->Text = S"24.95";
    
    	 this->txtMileage->Text = S"0";
    
    	 this->txtSubTotal->Text = S"0.00";
    
    	 this->dtpStartDate->Value = DateTime::Today;
    
    	 this->txtTaxRate->Text = S"7.75";
    
    	 TimeSpan ts(1, 0, 0, 0);
    
    	 this->dtpEndDate->Value = DateTime::Today.Add(ts);
    
    	 this->txtTaxAmount->Text = S"0.00";
    
    	 this->txtNumberOfDays->Text = S"1";
    
    	 this->txtOrderTotal->Text = S"0.00";
    
    	 this->cboEmployeeID->Focus();
    
    }
  94. Return to the form and double-click the End Date control
  95. Implement its ValueChanged event as follows:
     
    private: System::Void dtpEndDate_ValueChanged(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 DateTime startDate = this->dtpStartDate->Value;
    
    	 DateTime endDate   = this->dtpEndDate->Value;
    
    	 TimeSpan diff      = endDate.Subtract(startDate);
    
    	 this->txtNumberOfDays->Text = (diff.Days + 1).ToString();
    
    }
  96. Return to the form and click the Rate Applied text box (not the button)
  97. In the Events section of the Properties window, double-click the right box to Leave and implement its event as follows:
     
    System::Void txtRateApplied_Leave(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 double rateApplied = this->txtRateApplied->Text->ToDouble(0);
    
    	 int numberOfDays   = this->txtNumberOfDays->Text->ToInt16(0);
    
    	 double subTotal = rateApplied * numberOfDays;
    
    	 this->txtSubTotal->Text = subTotal.ToString(S"F");
    
    	 double taxRate = this->txtTaxRate->Text->ToDouble(0);
    
    	 double taxAmount = subTotal * taxRate / 100;
    
    	 this->txtTaxAmount->Text = taxAmount.ToString(S"F");
    
    	 double orderTotal = subTotal + taxAmount;
    
    	 this->txtOrderTotal->Text = orderTotal.ToString(S"F");
    
    }
  98. Return to the form. Double-click the New Rental Order button on the form and implement its Click event as follows:
     
    System::Void btnNewOrder_Click(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 if( this->btnNewOrder->Text->Equals(S"New Rental Order") )
    
    	 {
    
    		 this->btnNewOrder->Text = S"Save";
    
    		 this->btnReset_Click(sender, e);
    
    	 }
    
    	 else
    
    	 {
    
    	 String *strInsert = String::Concat(S"INSERT INTO RentalOrders("
    
    		                            S"EmployeeID, CustomerID, CarID, "
    
    				S"CarCondition, TankLevel, Mileage, "
    
    				S"StartDate, EndDate, NumberOfDays, "
    
    				S"RateApplied, SubTotal, TaxRate, TaxAmount, "
    
    				S"RentTotal, Notes) VALUES('",
    
    			cboEmployeeID->SelectedIndex.ToString(), S"', '",
    
    			cboCustomerID->SelectedIndex.ToString(), S"', '",
    
    				cboCarID->SelectedIndex.ToString(), S"', '",
    
    				cboCarCondition->Text, S"', '",
    
    				cboTankLevel->Text,  S"', '", txtMileage->Text,
    
    				S"', '", dtpStartDate->Value, S"', '",
    
    			dtpEndDate->Value, S"', '", txtNumberOfDays->Text,
    
    			S"', '", txtRateApplied->Text->ToDecimal(0), S"', '",
    
    				txtSubTotal->Text->ToDecimal(0), S"', '",
    
    				txtTaxRate->Text->ToDecimal(0), S"', '",
    
    				txtTaxAmount->Text->ToDecimal(0), S"', '",
    
    				txtOrderTotal->Text->ToDecimal(0), S"', '",
    
    				txtNotes->Text, S"');");
    
    
    
    	 System::Data::SqlClient::SqlCommand    *cmdNewOrder = new 
    
    	System::Data::SqlClient::SqlCommand(strInsert, this->sqlConnection1);
    
    
    
    		 sqlConnection1->Open();
    
    
    
    	 	cmdNewOrder->ExecuteNonQuery();
    
    		sqlConnection1->Close();
    
    
    
    		 this->btnReset_Click(sender, e);
    
    		 this->btnNewOrder->Text = S"New Rental Order";
    
    	 }
    
    }
  99. Display the first form and change its design as follows:
     
    Control Name Text
    Button btnRentalOrders Rental Orders
    Button btnCars Cars
    Button btnCustomers Customers
    Button btnEmployees Employees
    Button btnClose Close
  100. Right-click the form and click View Code
  101. In the top section of the file, include the header file of each of the other forms:
     
    #pragma once
    
    
    
    #include "RentalOrders.h"
    
    #include "Cars.h"
    
    #include "Customers.h"
    
    #include "Employees.h"
  102. Return to the form. Double-click the Rental Orders button and implement its Click event as follows:
     
    System::Void btnRentalOrders_Click(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 RentalOrders *frmOrders = new RentalOrders();
    
    	 frmOrders->Show();
    
    }
  103. Return to the form. Double-click the Cars button and implement its Click event as follows:
     
    System::Void btnCars_Click(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 Cars *frmCars = new Cars();
    
    	 frmCars->ShowDialog();
    
    }
  104. Return to the form. Double-click the Customers button and implement its Click event as follows:
     
    System::Void btnCustomers_Click(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 Customers *frmCust = new Customers();
    
    	 frmCust->ShowDialog();
    
    }
  105. Return to the form. Double-click the Employees button and implement its Click event as follows:
     
    System::Void btnEmployees_Click(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 Employees *frmEmpl = new Employees();
    
    	 frmEmpl->Show();
    
    }
  106. Return to the form. Double-click the Close button and implement its Click event as follows:
     
    System::Void btnClose_Click(System::Object *  sender, System::EventArgs *  e)
    
    {
    
    	 Close();
    
    }
  107. Execute the application
  108. Create a few rental orders and save them
     
  109. Return to the form. Double-click the Find button on the form and implement its Click event as follows:
     
    System::Void btnFind_Click(System::Object *  sender, System::EventArgs *  e)
    
    {			 
    
    	 String *strReceiptNumber = this->txtRentalOrderID->Text;
    
    
    
    	 if( strReceiptNumber->Equals(S"") )
    
    	 {
    
    		 MessageBox::Show(S"You must provide a receipt number to look for a rental order");
    
    		 return;
    
    	 }
    
    
    
     String *strFindOrder = String::Concat(S"SELECT * FROM RentalOrders WHERE RentalOrderID = '",
    
    		 strReceiptNumber, S"'");
    
    			 
    
     	 System::Data::SqlClient::SqlConnection *conDatabase = new 
    
    System::Data::SqlClient::SqlConnection(S"Data Source=(local);Database='BCR';Integrated Security=yes");
    
    	 System::Data::SqlClient::SqlCommand    *cmdDatabase = new 
    
    		 System::Data::SqlClient::SqlCommand(strFindOrder, conDatabase);
    
    
    
    	 conDatabase->Open();
    
    
    
    	 System::Data::SqlClient::SqlDataReader *rdrRentalOrder;
    
    	 rdrRentalOrder = cmdDatabase->ExecuteReader();
    
    
    
    	 while(rdrRentalOrder->Read())
    
    	 {
    
     this->cboEmployeeID->SelectedIndex = (rdrRentalOrder->GetSqlInt32(1) - 1).ToString()->ToInt32(0);
    
     this->cboCustomerID->SelectedIndex = (rdrRentalOrder->GetSqlInt32(2) - 1).ToString()->ToInt32(0);
    
     this->cboCarID->SelectedIndex      = (rdrRentalOrder->GetSqlInt32(3) - 1).ToString()->ToInt32(0);
    
    		 this->cboCarCondition->Text        = rdrRentalOrder->GetString(4);
    
    		 this->cboTankLevel->Text           = rdrRentalOrder->GetString(5);
    
    		 this->txtMileage->Text             = rdrRentalOrder->GetInt32(6).ToString();
    
    		 this->dtpStartDate->Value          = rdrRentalOrder->GetDateTime(7);
    
    		 this->dtpEndDate->Value            = rdrRentalOrder->GetDateTime(8);
    
    		 this->txtNumberOfDays->Text        = rdrRentalOrder->GetInt16(9).ToString();
    
    		 this->txtRateApplied->Text         = rdrRentalOrder->GetDecimal(10).ToString();
    
    		 this->txtSubTotal->Text            = rdrRentalOrder->GetDecimal(11).ToString();
    
    		 this->txtTaxRate->Text           = rdrRentalOrder->GetDecimal(12).ToString();
    
    		 this->txtTaxAmount->Text           = rdrRentalOrder->GetDecimal(13).ToString();
    
    		 this->txtOrderTotal->Text          = rdrRentalOrder->GetDecimal(14).ToString();
    
    		 this->txtNotes->Text               = rdrRentalOrder->GetString(15);
    
    	 }
    
    
    
    	 rdrRentalOrder->Close();
    
    	 conDatabase->Close();
    
    }
  110. Execute the application
  111. Update the employees work phone and extensions as follows:
     
  112. Process a few rental orders
 

Previous Copyright 2005 FunctionX, Inc. Next