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 Basic .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 0 
  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 becomes 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 Basic .NET and create a new Windows Application named BCR4
  5. To create a new form, on the main menu, click Project -> Add Windows Form...
  6. In the New File dialog box, make sure Windows Form is selected. Set its Name to Employees and click Open
  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 its Load event as follows:
      
    Private Sub Employees_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
		Handles MyBase.Load
        SqlDataAdapter1.Fill(DsBCR1)
End Sub
  12. In the Class Name combo box, select dataGrid1
  13. In the Method Name combo box, select CurrentCellChanged and implement the event as follows:
      
    Private Sub dataGrid1_CurrentCellChanged(ByVal sender As Object, ByVal e As System.EventArgs) _
			Handles dataGrid1.CurrentCellChanged
        SqlDataAdapter1.Update(DsBCR1)
End Sub
  14. In the Class Name combo box, select btnClose
  15. In the Method Name combo box, select Click and implement its event as follows:
      
    Private Sub btnClose_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Close()
End Sub
  16. Save all
  17. To create a new form, on the main menu, click Project -> Add Windows Form...
  18. In the New File dialog box, set its Name to Customers
  19. 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  
  20. On the form click the Customers tab and click an empty area of its body. For example, click the area just below the Notes label
  21. 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
  22. To create a data set, on the main menu, click Data -> Generate Dataset...
  23. Accept the Existing radio button with the dsBCR name and click OK
  24. 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
  25. Double-click the Load button of the form and implement the event as follows:
      
    Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click
        SqlDataAdapter1.Fill(DsBCR1)
End Sub
  26. In the Class Name combo box, select btnFirst
  27. In the Method Name combo box, select Click and implement its event as follows:
      
    Private Sub btnFirst_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        pgeCustomers.BindingContext(DsBCR1, "Customers").Position = 0
        SqlDataAdapter1.Update(DsBCR1)
End Sub
  28. In the Class Name combo box, select btnPrevious
  29. In the Method Name combo box, select Click and implement its Click event as follows:
      
    Private Sub btnPrevious_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
        pgeCustomers.BindingContext(DsBCR1, "Customers").Position = _
	   pgeCustomers.BindingContext(DsBCR1, "Customers").Position - 1
        SqlDataAdapter1.Update(DsBCR1)
End Sub
  30. In the Class Name combo box, select btnNext
  31. In the Method Name combo box, select Click and implement its Click event as follows:
      
    Private Sub btnNext_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnNext.Click
        pgeCustomers.BindingContext(DsBCR1, "Customers").Position = _
	   pgeCustomers.BindingContext(DsBCR1, "Customers").Position + 1
        SqlDataAdapter1.Update(DsBCR1)
End Sub
  32. In the Class Name combo box, select btnLast
  33. In the Method Name combo box, select Click and implement its Click event as follows:
      
    Private Sub btnLast_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLast.Click
        pgeCustomers.BindingContext(DsBCR1, "Customers").Position = _
	   pgeCustomers.BindingContext(DsBCR1, "Customers").Count - 1
        SqlDataAdapter1.Update(DsBCR1)
End Sub
  34. In the Class Name combo box, select btnClose
  35. In the Method Name combo box, select Click and implement its event as follows:
      
    Private Sub btnClose_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Close()
End Sub
  36. Save all
  37. In the Class Name combo box, select btnReset
  38. In the Method Name combo box, select Click and implement its Click event as follows:
      
    Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        txtNCDrvLicNbr.Text = ""
        txtNCDLClass.Text = "C"
        dtpNCDateIssued.Value = DateTime.Today
        dtpNCExpDate.Value = DateTime.Today
        txtNCFullName.Text = ""
        chkNCOrganDonor.Checked = False
        txtNCAddress.Text = ""
        txtNCCity.Text = ""
        txtNCState.Text = "MD"
        txtNCZIPCode.Text = ""
        txtNCCountry.Text = "USA"
        txtNCHomePhone.Text = "(000) 000-0000"

        txtNCDrvLicNbr.Focus()
End Sub
  39. In the Class Name combo box, select btnCreate
  40. In the Method Name combo box, select Click and implement its event as follows:
      
    Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        Dim strDrvLicNbr As String = txtNCDrvLicNbr.Text
        Dim strFullName As String = txtNCFullName.Text

        If strDrvLicNbr = "" Then
            MsgBox("You must provide the customer's driver's license number")
            Exit Sub
        End If
        If strFullName = "" Then
            MsgBox("You must provide the customer's name")
            Exit Sub
        End If

        Dim strInsert As String = "INSERT INTO Customers(DrvLicNbr, DrvLicClass, " & _
                                        "DateIssued, DateExpired, FullName, Addre& _ss, " & _
    "City, State, ZIPCode, Country, HomePhone, " & _
    "OrganDonor) VALUES('" & _
    txtNCDrvLicNbr.Text & "', '" & txtNCDLClass.Text & _
    "', '" & dtpNCDateIssued.Value & "', '" & _
    dtpNCExpDate.Value & "', '" & txtNCFullName.Text & _
    "', '" & txtNCAddress.Text & "', '" & _
    txtNCCity.Text & "', '" & txtNCState.Text & "', '" & _
    txtNCZIPCode.Text & "', '" & txtNCCountry.Text & "', '" & _
    txtNCHomePhone.Text & "', '" & chkNCOrganDonor.Checked & "')"

        Dim cmdNewCustomer As System.Data.SqlClient.SqlCommand = _
        New System.Data.SqlClient.SqlCommand(strInsert, SqlConnection1)

        SqlConnection1.Open()

        cmdNewCustomer.ExecuteNonQuery()
        SqlConnection1.Close()

        btnReset_Click(sender, e)
End Sub
  41. Save all
  42. To create a new form, on the main menu, click Project -> Add Windows Form...
  43. In the New File dialog box, set its Name to RentalRates and click Open
  44. In Server Explorer, in the Tables node of the BCR database, drag RentalRates and drop it on the form
  45. To create a data set, on the main menu, click Data -> Generate Dataset...
  46. Accept the Existing radio button with the dsBCR name and click OK
  47. 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
  48. Right-click the form and click View Code
  49. In the Class Name combo box, select (RentalRates Events)
  50. In the Method Name combo box, select Load and implement the event as follows:
      
    Private Sub RentalRates_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        SqlDataAdapter1.Fill(DsBCR1)
End Sub
  51. Save all
  52. To create a new form, on the main menu, click Project -> Add Windows Form...
  53. In the New File dialog box, click Windows Form and set its Name to Cars
  54. 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  
  55. On the form click the Cars Details tab and click an empty area of its body. For example, click the area just below the Notes label
  56. 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
  57. Drag the selected tables and drop them inside the Cars Details tab page. For example, drop them just below the Notes label
  58. To create a data set, on the main menu, click Data -> Generate Dataset...
  59. Accept the Existing radio button with the dsBCR name:
     
  60. Click OK
  61. 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
  62. In the Windows Forms section of the Toolbox, click the OpenFileDialog button and click the form
  63. Set its Filter as Picture Files (*.bmp;*.gif;*.jpeg;*jpg)|*.bmp;*.gif;*.jpeg;*jpg
  64. On the form, click the Car Details tab and double-click the Picture button
  65. Implement its event as follows:
      
    Private Sub btnPicture_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPicture.Click
        If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
            txtPicture.Text = OpenFileDialog1.FileName
            pctCar.Image = Bitmap.FromFile(OpenFileDialog1.FileName)
        End If
End Sub
  66. In the Class Name combo box, select btnNCPicture
  67. In the Method Name combo box, select Click and implement its event as follows:
      
    Private Sub btnNCPicture_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnNCPicture.Click
        If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
            txtNCPicture.Text = OpenFileDialog1.FileName
            pctNCar.Image = Bitmap.FromFile(OpenFileDialog1.FileName)
        End If
End Sub
  68. In the Class Name combo box, select btnLoad
  69. In the Method Name combo box, select Click and implement the event as follows:
      
    Private Sub btnLoad_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLoad.Click
        SqlDataAdapter2.Fill(DsBCR1)
        SqlDataAdapter1.Fill(DsBCR1)
        pctCar.Image = Bitmap.FromFile(txtPicture.Text)
End Sub
  70. In the Class Name combo box, select btnFirst
  71. In the Method Name combo box, select Click and implement its Click event as follows:
      
    Private Sub btnFirst_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        pgeCars.BindingContext(DsBCR1, "Cars").Position = 0
        pctCar.Image = Bitmap.FromFile(txtPicture.Text)
        SqlDataAdapter1.Update(DsBCR1)
End Sub
  72. In the Class Name combo box, select btnPrevious
  73. In the Method Name combo box, select Click and implement its Click event as follows:
      
    Private Sub btnPrevious_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
        pgeCars.BindingContext(DsBCR1, "Cars").Position = _
	   pgeCars.BindingContext(DsBCR1, "Cars").Position - 1
        pctCar.Image = Bitmap.FromFile(txtPicture.Text)
        SqlDataAdapter1.Update(DsBCR1)
End Sub
  74. In the Class Name combo box, select btnNext
  75. In the Method Name combo box, select Click and implement its Click event as follows:
      
    Private Sub btnNext_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnNext.Click
        pgeCars.BindingContext(DsBCR1, "Cars").Position = _
	   pgeCars.BindingContext(DsBCR1, "Cars").Position + 1
        pctCar.Image = Bitmap.FromFile(txtPicture.Text)
        SqlDataAdapter1.Update(DsBCR1)
End Sub
  76. In the Class Name combo box, select btnLast
  77. In the Method Name combo box, select Click and implement its Click event as follows:
      
    Private Sub btnLast_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLast.Click
        pgeCars.BindingContext(DsBCR1, "Cars").Position = _
	   pgeCars.BindingContext(DsBCR1, "Cars").Count - 1
        pctCar.Image = Bitmap.FromFile(txtPicture.Text)
        SqlDataAdapter1.Update(DsBCR1)
End Sub
  78. In the Class Name combo box, select btnClose
  79. In the Method Name combo box, select Click and implement its event as follows:
      
    Private Sub btnClose_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Close()
End Sub
  80. In the Class Name combo box, select btnReset
  81. In the Method Name combo box, select Click and implement its Click event as follows:
      
    Private Sub btnReset_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnReset.Click
        txtNCTagNumber.Text = ""
        txtNCMake.Text = ""
        txtNCModel.Text = ""
        txtNCYear.Text = ""
        cboNCRentalRateID.SelectedIndex = -1
        chkNCK7Player.Checked = False
        chkNCDVDPlayer.Checked = False
        chkNCCDPlayer.Checked = False
        chkNCAvailable.Checked = False
        txtNCPicture.Text = "C:\\Programs\\Unknown.bmp"
        pctNCar.Image = Bitmap.FromFile(txtNCPicture.Text)
        txtNCNotes.Text = ""

        txtNCTagNumber.Focus()
End Sub
  82. In the Class Name combo box, select btnReset
  83. In the Method Name combo box, select Click and implement its Click event as follows:
      
    Private Sub btnCreate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        Dim strInsert As String = "INSERT INTO Cars(TagNumber, Make, Model, " & _
                                  "CarYear, RentalRateID, HasK7Player, " & _
    "HasCDPlayer, HasDVDPlayer, Picture, " & _
    "Available, Notes) VALUES('" & _
    txtNCTagNumber.Text & "', '" & _
    txtNCMake.Text & "', '" & txtNCModel.Text & _
    "', '" & txtNCYear.Text & "', '" & _
    cboNCRentalRateID.SelectedIndex & "', '" & _
    chkNCCDPlayer.Checked & "', '" & _
    chkNCDVDPlayer.Checked & "', '" & _
    chkNCDVDPlayer.Checked & "', '" & _
    txtNCPicture.Text & "', '" & _
    chkNCAvailable.Checked & "', '" & _
    txtNCNotes.Text & "')"

        Dim cmdNewCar As System.Data.SqlClient.SqlCommand = New _
        System.Data.SqlClient.SqlCommand(strInsert, SqlConnection1)

        SqlConnection1.Open()

        cmdNewCar.ExecuteNonQuery()
        SqlConnection1.Close()

        btnReset_Click(sender, e)
End Sub
  84. Save all
  85. To create a new form, on the main menu, click Project -> Add Windows Form...
  86. In the New File dialog box, set its Name to RentalOrders
  87. In Server Explorer, in the Tables node of the BCR database, click Cars
  88. Press and hold Ctrl
  89. Click Customers, Employees, and RentalOrders
  90. Release Ctrl
  91. Drag the selected tables and drop them on the form
  92. To create a data set, on the main menu, click Data -> Generate Dataset...
     
  93. Accept the Existing radio button with the dsBCR name and click OK
  94. 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    
  95. 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
  96. Right-click the form and click View Code
  97. In the Class Name combo box, select (RentalOrders Events)
  98. In the Method Name combo box, select Load and implement the event as follows:
      
    Private Sub RentalOrders_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        SqlDataAdapter4.Fill(DsBCR1)
        SqlDataAdapter3.Fill(DsBCR1)
        SqlDataAdapter2.Fill(DsBCR1)
        SqlDataAdapter1.Fill(DsBCR1)
End Sub
  99. In the Class Name combo box, select cboCarID
  100. In the Method Name combo box, select SelectedIndexChanged and implement the event as follows:
      
    Private Sub cboCarID_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboCarID.SelectedIndexChanged
        Dim strTagNumber As String = "000000"
        Dim strMake As String = "Not Found"
        Dim strModel As String = "Not Found"
        Dim strYear As String = "0"
        Dim i As Integer

        ' Get a reference to the records of the Cars table
        Dim rows As DataRowCollection = DsBCR1.Tables("Cars").Rows
        ' Check each record one by one
        For i = 0 To rows.Count - 1 Step 1
            ' Get the current tag number
            strTagNumber = CStr(rows.Item(i).Item("TagNumber"))

            ' If the current tag matches the one selected by the user...
            If strTagNumber = cboCarID.Text Then

                ' Retrieve its corresponding make, model, and year
                strMake = CStr(rows.Item(i).Item("Make"))
                strModel = CStr(rows.Item(i).Item("Model"))
                strYear = CStr(rows.Item(i).Item("CarYear"))
            End If
        Next

        ' Display the make, model, and year of the selected car
        txtMake.Text = strMake
        txtModel.Text = strModel
        txtCarYear.Text = strYear
End Sub
  101. In the Class Name combo box, select cboCustomerID
  102. In the Method Name combo box, select SelectedIndexChanged and implement the event as follows:
      
    Private Sub cboCustomerID_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboCustomerID.SelectedIndexChanged
        Dim strDrvLicNbr As String = "000000"
        Dim strCustName As String = "Not Found"
        Dim strCustAddress As String = ""
        Dim strCustCity As String = ""
        Dim strCustState As String = ""
        Dim strCustZIPCode As String = ""
        Dim strCountry As String = ""
        Dim i As Integer

        ' Get a reference to the records of the Customers table
        Dim rows As DataRowCollection = DsBCR1.Tables("Customers").Rows
        ' Check each record one by one
        For i = 0 To rows.Count - 1 Step 1

            ' Get the current tag number
            strDrvLicNbr = CStr(rows(i).Item("DrvLicNbr"))

            ' If the current driver's license number matches the one selected by the user...
            If strDrvLicNbr = cboCustomerID.Text Then
                ' Retrieve its corresponding information
                strCustName = CStr(rows.Item(i).Item("FullName"))
                strCustAddress = CStr(rows.Item(i).Item("Address"))
                strCustCity = CStr(rows.Item(i).Item("City"))
                strCustState = CStr(rows.Item(i).Item("State"))
                strCustZIPCode = CStr(rows.Item(i).Item("ZIPCode"))
                strCountry = CStr(rows.Item(i).Item("Country"))
                ' Since you found a match, STOP!!!
            End If
        Next

        ' Display the details of the customer
        txtCustName.Text = strCustName
        txtCustAddress.Text = strCustAddress
        txtCustCity.Text = strCustCity
        txtCustState.Text = strCustState
        txtCustZIPCode.Text = strCustZIPCode
        txtCustCountry.Text = strCountry
End Sub
  103. In the Class Name combo box, select btnRateApplie
  104. In the Method Name combo box, select Click and implement the event as follows:
      
    Private Sub btnRateApplied_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRateApplied.Click
        Dim frmRates As RentalRates = New RentalRates
        frmRates.Show()
End Sub
  105. In the Class Name combo box, select btnReset
  106. In the Method Name combo box, select Click and implement the event as follows:
      
    Private Sub btnReset_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnReset.Click
        cboEmployeeID.SelectedIndex = -1
        txtRentalOrderID.Text = ""
        cboCarID.SelectedIndex = -1
        txtMake.Text = ""
        txtModel.Text = ""
        txtCarYear.Text = ""
        cboCarCondition.SelectedIndex = 0
        cboCustomerID.SelectedIndex = -1
        txtCustName.Text = ""
        txtCustAddress.Text = ""
        txtCustCity.Text = ""
        txtCustState.Text = ""
        txtCustZIPCode.Text = ""
        txtCustCountry.Text = "USA"
        cboTankLevel.SelectedIndex = 0
        txtRateApplied.Text = "24.95"
        txtMileage.Text = "0"
        txtSubTotal.Text = "0.00"
        dtpStartDate.Value = DateTime.Today
        txtTaxRate.Text = "7.75"
        Dim ts As TimeSpan = New TimeSpan(1, 0, 0, 0)
        dtpEndDate.Value = DateTime.Today.Add(ts)
        txtTaxAmount.Text = "0.00"
        txtNumberOfDays.Text = "1"
        txtOrderTotal.Text = "0.00"
        cboEmployeeID.Focus()
End Sub
  107. In the Class Name combo box, select dtpEndDate
  108. In the Method Name combo box, select ValueChangedand implement the event as follows:
      
    Private Sub dtpEndDate_ValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dtpEndDate.ValueChanged
        Dim startDate As DateTime = dtpStartDate.Value
        Dim endDate As DateTime = dtpEndDate.Value
        Dim diff As TimeSpan = endDate.Subtract(startDate)
        txtNumberOfDays.Text = CStr(diff.Days + 1)
End Sub
  109. In the Class Name combo box, select txtRateApplied
  110. In the Method Name combo box, select Leave implement the event as follows:
      
    Private Sub btnRateApplied_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRateApplied.Leave
        Dim rateApplied As Double = CDbl(txtRateApplied.Text)
        Dim numberOfDays As Integer = CInt(txtNumberOfDays.Text)
        Dim subTotal As Double = rateApplied * numberOfDays
        txtSubTotal.Text = subTotal.ToString("F")
        Dim taxRate As Double = CDbl(txtTaxRate.Text)
        Dim taxAmount As Double = subTotal * taxRate / 100
        txtTaxAmount.Text = taxAmount.ToString("F")
        Dim orderTotal As Double = subTotal + taxAmount
        txtOrderTotal.Text = orderTotal.ToString("F")
End Sub
  111. In the Class Name combo box, select btnNewOrder
  112. In the Method Name combo box, select Click implement the event as follows:
      
    Private Sub btnNewOrder_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnNewOrder.Click
        If btnNewOrder.Text = "New Rental Order" Then
            btnNewOrder.Text = "Save"
            btnReset_Click(sender, e)
        Else
            Dim strInsert As String = "INSERT INTO RentalOrders(" & _
                                            "EmployeeID, CustomerID, CarID, " & _
              "CarCondition, TankLevel, Mileage, " & _
              "StartDate, EndDate, NumberOfDays, " & _
              "RateApplied, SubTotal, TaxRate, TaxAmount, " & _
              "RentTotal, Notes) VALUES('" & _
              cboEmployeeID.SelectedIndex.ToString() & "', '" & _
              cboCustomerID.SelectedIndex.ToString() & "', '" & _
              cboCarID.SelectedIndex.ToString() & "', '" & _
              cboCarCondition.Text & "', '" & _
              cboTankLevel.Text & "', '" & txtMileage.Text & _
              "', '" & dtpStartDate.Value & "', '" & _
              dtpEndDate.Value & "', '" & txtNumberOfDays.Text & _
              "', '" & txtRateApplied.Text & "', '" & _
              txtSubTotal.Text & "', '" & _
              txtTaxRate.Text & "', '" & _
              txtTaxAmount.Text & "', '" & _
              txtOrderTotal.Text & "', '" & _
              txtNotes.Text & "')"

            Dim cmdNewOrder As System.Data.SqlClient.SqlCommand = New _
            System.Data.SqlClient.SqlCommand(strInsert, SqlConnection1)

            SqlConnection1.Open()

            cmdNewOrder.ExecuteNonQuery()
            SqlConnection1.Close()

            btnReset_Click(sender, e)
            btnNewOrder.Text = "New Rental Order"
        End If
End Sub
  113. 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
  114. Double-click the Rental Orders button and implement its Click event as follows:
      
    Private Sub btnRentalOrders_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRentalOrders.Click
        Dim frmOrders As RentalOrders = New RentalOrders
        frmOrders.Show()
End Sub
  115. In the Class Name combo box, select btnCars
  116. In the Method Name combo box, select Click and implement its event as follows:
      
    Private Sub btnCars_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCars.Click
        Dim frmCars As Cars = New Cars
        frmCars.ShowDialog()
    End Sub
  117. In the Class Name combo box, select btnCustomers
  118. In the Method Name combo box, select Click and implement its event as follows:
      
    Private Sub btnCustomers_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCustomers.Click
        Dim frmCust As Customers = New Customers
        frmCust.ShowDialog()
End Sub
  119. In the Class Name combo box, select btnEmployees
  120. In the Method Name combo box, select Click and implement its event as follows:
      
    Private Sub btnEmployees_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnEmployees.Click
        Dim frmEmpl As Employees = New Employees
        frmEmpl.Show()
End Sub
  121. In the Class Name combo box, select btnClose
  122. In the Method Name combo box, select Click and implement its event as follows:
      
    Private Sub btnClose_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnClose.Click
        End
End Sub
  123. Execute the application
  124. Create a few rental orders and save them
     
  125. Close the form and return to your programming environment
  126. In the Class Name combo box, select btnFind
  127. In the Method Name combo box, select Click and implement its event as follows:
      
    Private Sub btnFind_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnFind.Click
        Dim strReceiptNumber As String = txtRentalOrderID.Text

        If strReceiptNumber = "" Then
            MsgBox("You must provide a receipt number to look for a rental order")
            Exit Sub
        End If

        Dim strFindOrder As String = "SELECT * FROM RentalOrders WHERE RentalOrderID = '" & _
         CInt(strReceiptNumber) & "'"

        Dim conDatabase As System.Data.SqlClient.SqlConnection = New _
    System.Data.SqlClient.SqlConnection("Data Source=(local);Database='BCR';Integrated Security=yes")
        Dim cmdDatabase As System.Data.SqlClient.SqlCommand = New _
            System.Data.SqlClient.SqlCommand(strFindOrder, conDatabase)

        conDatabase.Open()

        Dim rdrRentalOrder As System.Data.SqlClient.SqlDataReader
        rdrRentalOrder = cmdDatabase.ExecuteReader()

        While rdrRentalOrder.Read()
            cboEmployeeID.SelectedIndex = CInt(rdrRentalOrder.GetSqlInt32(1).ToString()) - 1
            cboCustomerID.SelectedIndex = CInt(rdrRentalOrder.GetSqlInt32(2).ToString()) - 1
            cboCarID.SelectedIndex = CInt(rdrRentalOrder.GetSqlInt32(3).ToString()) - 1
            cboCarCondition.Text = rdrRentalOrder.GetString(4)
            cboTankLevel.Text = rdrRentalOrder.GetString(5)
            txtMileage.Text = rdrRentalOrder.GetInt32(6).ToString()
            dtpStartDate.Value = rdrRentalOrder.GetDateTime(7)
            dtpEndDate.Value = rdrRentalOrder.GetDateTime(8)
            txtNumberOfDays.Text = rdrRentalOrder.GetInt16(9).ToString()
            txtRateApplied.Text = rdrRentalOrder.GetDecimal(10).ToString()
            txtSubTotal.Text = rdrRentalOrder.GetDecimal(11).ToString()
            txtTaxRate.Text = rdrRentalOrder.GetDecimal(12).ToString()
            txtTaxAmount.Text = rdrRentalOrder.GetDecimal(13).ToString()
            txtOrderTotal.Text = rdrRentalOrder.GetDecimal(14).ToString()
            txtNotes.Text = rdrRentalOrder.GetString(15)
        End While

        rdrRentalOrder.Close()
        conDatabase.Close()
End Sub
  128. Update the employees work phone and extensions as follows:
     
  129. Process a few rental orders
 
Previous Copyright © 2005-2016, FunctionX Next