Relationships and Data Integrity

CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
Department VARCHAR(50))
GO

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

CREATE TABLE StaffMembers (
EmployeeID  INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
FullName VARCHAR(50),
DepartmentID INT FOREIGN KEY REFERENCES Departments(DepartmentID))
GO

CREATE TABLE StaffMembers (
EmployeeID  INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
FullName VARCHAR(50),
DepartmentID INT FOREIGN KEY(DepartmentID) REFERENCES Departments(DepartmentID))
GO

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

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

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# and create a new Windows Forms Application named BCR4
5. To create a new form, on the main menu, click Project -> Add Windows Form...
6. In the Add New Item dialog box, make sure that Windows Form is selected and set the Name to Employees
7. Click Open
8. In Server Explorer, under the BCR database, expand the Tables node.
   Drag Employees and drop it on the form
9. To create a data set, on the main menu, click Data -> Generate Dataset...
10. Accept the New radio button. Change the name to dsBCR and click OK 
11. 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

12. Double-click an unoccupied area of the form and implement the event as follows:
     

    private void Employees_Load(object sender, System.EventArgs e) { this.sqlDataAdapter1.Fill(this.dsBCR1); }

13. Return to the form and click the DataGrid
14. In the Properties window, click the Events button and double-click the right field to CurrentCellChanged
15. Implement the event as follows:
     

    private void dataGrid1_CurrentCellChanged(object sender, System.EventArgs e) { this.sqlDataAdapter1.Update(this.dsBCR1); }

16. Return to the form and double-click the Close button
17. Implement its event as follows:
     

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

18. Save all
19. To create a new form, on the main menu, click Project -> Add Windows Form...
20. In the Add New Item dialog box, set the Name to Customers
21. 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

22. 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
23. 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 Home Phone label
24. To create a data set, on the main menu, click Data -> Generate Dataset...
25. Accept the Existing radio button with the dsBCR name and click OK
26. 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

27. Double-click the Load button of the form and implement the event as follows:
     

    private void btnLoad_Click(object sender, System.EventArgs e) { this.sqlDataAdapter1.Fill(this.dsBCR1); }

28. Return to the form. Double-click the First button and implement its Click event as follows:
     

    private void btnFirst_Click(object sender, System.EventArgs e) { this.pgeCustomers.BindingContext[this.dsBCR1, "Customers"].Position = 0; this.sqlDataAdapter1.Update(this.dsBCR1); }

29. Return to the form. Double-click the Previous button and implement its Click event as follows:
     

    private void btnPrevious_Click(object sender, System.EventArgs e) { this.pgeCustomers.BindingContext[this.dsBCR1, "Customers"].Position = this.pgeCustomers.BindingContext[this.dsBCR1, "Customers"].Position - 1; this.sqlDataAdapter1.Update(this.dsBCR1); }

30. Return to the form. Double-click the Next button and implement its Click event as follows:
     

    private void btnNext_Click(object sender, System.EventArgs e) { this.pgeCustomers.BindingContext[this.dsBCR1, "Customers"].Position = this.pgeCustomers.BindingContext[this.dsBCR1, "Customers"].Position + 1; this.sqlDataAdapter1.Update(this.dsBCR1); }

31. Return to the form. Double-click the Last button and implement its Click event as follows:
     

    private void btnLast_Click(object sender, System.EventArgs e) { this.pgeCustomers.BindingContext[this.dsBCR1, "Customers"].Position = this.pgeCustomers.BindingContext[this.dsBCR1, "Customers"].Count - 1; this.sqlDataAdapter1.Update(this.dsBCR1); }

32. Return to the form and double-click the Close button
33. Implement its event as follows:
     

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

34. Save all
35. Return to the form and click the New Customer tab
36. In the New Customer page, double-click the Reset button and implement its Click event as follows:
     

    private void btnReset_Click(object sender, System.EventArgs e) { this.txtNCDrvLicNbr.Text = ""; this.txtNCDLClass.Text = "C"; this.dtpNCDateIssued.Value = DateTime.Today; this.dtpNCExpDate.Value = DateTime.Today; this.txtNCFullName.Text = ""; this.chkNCOrganDonor.Checked = false; this.txtNCAddress.Text = ""; this.txtNCCity.Text = ""; this.txtNCState.Text = "MD"; this.txtNCZIPCode.Text = ""; this.txtNCCountry.Text = "USA"; this.txtNCHomePhone.Text = "(000) 000-0000"; this.txtNCDrvLicNbr.Focus(); }

37. Return to the form and double-click the Create button
38. Implement its Click event as follows:
     

    private void btnCreate_Click(object sender, System.EventArgs e) { string strDrvLicNbr = this.txtNCDrvLicNbr.Text; string strFullName = this.txtNCFullName.Text; if( strDrvLicNbr == "" ) { MessageBox.Show("You must provide the customer's driver's license number"); return; } if( strFullName == "" ) { MessageBox.Show("You must provide the customer's name"); return; } string strInsert = "INSERT INTO Customers(DrvLicNbr, DrvLicClass, " + "DateIssued, DateExpired, FullName, Address, " + "City, State, ZIPCode, Country, HomePhone, " + "OrganDonor) VALUES('" + this.txtNCDrvLicNbr.Text + "', '" + txtNCDLClass.Text + "', '" + dtpNCDateIssued.Value + "', '" + dtpNCExpDate.Value + "', '" + txtNCFullName.Text + "', '" + txtNCAddress.Text + "', '" + txtNCCity.Text + "', '" + txtNCState.Text + "', '" + txtNCZIPCode.Text + "', '" + txtNCCountry.Text + "', '" + txtNCHomePhone.Text + "', '" + this.chkNCOrganDonor.Checked + "');"; System.Data.SqlClient.SqlCommand cmdNewCustomer = new System.Data.SqlClient.SqlCommand(strInsert, this.sqlConnection1); sqlConnection1.Open(); cmdNewCustomer.ExecuteNonQuery(); sqlConnection1.Close(); this.btnReset_Click(sender, e); }

39. Save all
40. To create a new form, on the main menu, click Project -> Add Windows Form...
41. In the Add New Item dialog box, set the Name to RentalRates
42. In Server Explorer, in the Tables node of the BCR database, drag RentalRates and drop it on the form
43. To create a data set, on the main menu, click Data -> Generate Dataset...
44. Accept the Existing radio button with the dsBCR name and click OK
45. 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

46. 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 void RentalRates_Load(object sender, System.EventArgs e) { this.sqlDataAdapter1.Fill(this.dsBCR1); }

47. Save all
48. To create a new form, on the main menu, click Project -> Add Windows Form...
49. In the Add New Item dialog box, set the Name to Cars
50. 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

51. 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
52. 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
53. Drag the selected tables and drop them inside the Cars Details tab page. For example, drop them just below the Notes label
54. To create a data set, on the main menu, click Data -> Generate Dataset...
55. Accept the Existing radio button with the dsBCR name. Click the Cars check box and make sure both check boxes are selected:
     
    
56. Click OK
57. 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

58. In the Windows Forms section of the Toolbox, click the OpenFileDialog button and click the form
59. Set its Filter as Picture Files (*.bmp;*.gif;*.jpeg;*jpg)|*.bmp;*.gif;*.jpeg;*jpg
60. On the form, click the Car Details tab and double-click the Picture button
61. Implement its event as follows:
     

    private void btnPicture_Click(object sender, System.EventArgs e) { if( openFileDialog1.ShowDialog() == DialogResult.OK ) { this.txtPicture.Text = openFileDialog1.FileName; this.pctCar.Image = Bitmap.FromFile(openFileDialog1.FileName); } }

62. Return to the form and click the New Car tab
63. In the New Car section, double-click the Picture button and implement its Click event as follows:
     

    private void btnNCPicture_Click(object sender, System.EventArgs e) { if( openFileDialog1.ShowDialog() == DialogResult.OK ) { this.txtNCPicture.Text = openFileDialog1.FileName; this.pctNCar.Image = Bitmap.FromFile(openFileDialog1.FileName); } }

64. Return to the form. Double-click the Load button and implement the event as follows:
     

    private void btnLoad_Click(object sender, System.EventArgs e) { this.sqlDataAdapter2.Fill(this.dsBCR1); this.sqlDataAdapter1.Fill(this.dsBCR1); this.pctCar.Image = Bitmap.FromFile(this.txtPicture.Text); }

65. Return to the form and click the Car Details. Double-click the First button and implement its Click event as follows:
     

    private void btnFirst_Click(object sender, System.EventArgs e) { this.pgeCars.BindingContext[this.dsBCR1, "Cars"].Position = 0; this.pctCar.Image = Bitmap.FromFile(this.txtPicture.Text); this.sqlDataAdapter1.Update(this.dsBCR1); }

66. Return to the form. Double-click the Previous button and implement its Click event as follows:
     

    private void btnPrevious_Click(object sender, System.EventArgs e) { this.pgeCars.BindingContext[this.dsBCR1, "Cars"].Position = this.pgeCars.BindingContext[this.dsBCR1, "Cars"].Position - 1; this.pctCar.Image = Bitmap.FromFile(this.txtPicture.Text); this.sqlDataAdapter1.Update(this.dsBCR1); }

67. Return to the form. Double-click the Next button and implement its Click event as follows:
     

    private void btnNext_Click(object sender, System.EventArgs e) { this.pgeCars.BindingContext[this.dsBCR1, "Cars"].Position = this.pgeCars.BindingContext[this.dsBCR1, "Cars"].Position + 1; this.pctCar.Image = Bitmap.FromFile(this.txtPicture.Text); this.sqlDataAdapter1.Update(this.dsBCR1); }

68. Return to the form. Double-click the Last button and implement its Click event as follows:
     

    private void btnLast_Click(object sender, System.EventArgs e) { this.pgeCars.BindingContext[this.dsBCR1, "Cars"].Position = this.pgeCars.BindingContext[this.dsBCR1, "Cars"].Count - 1; this.pctCar.Image = Bitmap.FromFile(this.txtPicture.Text); this.sqlDataAdapter1.Update(this.dsBCR1); }

69. Return to the form and double-click the Close button
70. Implement its event as follows:
     

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

71. Return to the form and click the New Car tab
72. In the New Car page, double-click the Reset button and implement its Click event as follows:
     

    private void btnReset_Click(object sender, System.EventArgs e) { this.txtNCTagNumber.Text = ""; this.txtNCMake.Text = ""; this.txtNCModel.Text = ""; this.txtNCYear.Text = ""; this.cboNCCategoryID.SelectedIndex = -1; this.chkNCK7Player.Checked = false; this.chkNCDVDPlayer.Checked = false; this.chkNCCDPlayer.Checked = false; this.chkNCAvailable.Checked = false; this.txtNCPicture.Text = "C:\\Programs\\Unknown.bmp"; this.pctNCar.Image = Bitmap.FromFile(this.txtNCPicture.Text); this.txtNCNotes.Text = ""; this.txtNCTagNumber.Focus(); }

73. Return to the form and double-click the Create button
74. Implement its Click event as follows:
     

    private void btnCreate_Click(object sender, System.EventArgs e) { string strInsert = String.Concat("INSERT INTO Cars(TagNumber, Make, Model, ", "CarYear, RentalRateID, HasK7Player, ", "HasCDPlayer, HasDVDPlayer, Picture, ", "Available, Notes) VALUES('", this.txtNCTagNumber.Text, "', '", txtNCMake.Text, "', '", txtNCModel.Text, "', '", txtNCYear.Text, "', '", cboNCCategoryID.SelectedIndex, "', '", chkNCCDPlayer.Checked, "', '", chkNCDVDPlayer.Checked, "', '", chkNCDVDPlayer.Checked, "', '", txtNCPicture.Text, "', '", chkNCAvailable.Checked, "', '", txtNCNotes.Text, "');"); System.Data.SqlClient.SqlCommand cmdNewCar = new System.Data.SqlClient.SqlCommand(strInsert, this.sqlConnection1); sqlConnection1.Open(); cmdNewCar.ExecuteNonQuery(); sqlConnection1.Close(); this.btnReset_Click(sender, e); }

75. Save all
76. To create a new form, on the main menu, click Project -> Add Windows Form...
77. In the Add New Item dialog box, set the Name to RentalOrders
78. Click Open
79. In Server Explorer, in the Tables node of the BCR database, click Cars
80. Press and hold Ctrl
81. Click Customers, Employees, and RentalOrders
82. Release Ctrl
83. Drag the selected tables and drop them on the form
84. To create a data set, on the main menu, click Data . Generate Dataset...
     
    
85. Accept the Existing radio button with the dsBCR name and click OK
86. 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

87. 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

88. Generate the Load event of the form and implement it as follows:
     

    private void RentalOrders_Load(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); }

89. Return to the form and double-click the Car Selected combo box
90. In the top section of the file, among the other using lines, type using System.Data;
91. Implement its SelectedIndexChanged event as follows:
     

    private void cboCarID_SelectedIndexChanged(object sender, System.EventArgs e) { string strTagNumber = "000000"; string strMake = "Not Found"; string strModel = "Not Found"; string strYear = "0"; // Get a reference to the records of the Cars table DataRowCollection colRows = this.dsBCR1.Tables["Cars"].Rows; // Check each record one by one for(int i = 0; i < colRows.Count; i++) { // Get the current tag number strTagNumber = (string)colRows[i]["TagNumber"]; // If the current tag matches the one selected by the user... if( strTagNumber == this.cboCarID.Text ) { // Retrieve its corresponding make, model, and year strMake = (string)colRows[i]["Make"]; strModel = (string)colRows[i]["Model"]; strYear = (string)colRows[i]["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; }

92. Return to the form and double-click the Customer combo box
93. Implement its SelectedIndexChanged event as follows:
     

    private void cboCustomerID_SelectedIndexChanged(object sender, System.EventArgs e) { string strDrvLicNbr = "000000"; string strCustName = "Not Found"; string strCustAddress = ""; string strCustCity = ""; string strCustState = ""; string strCustZIPCode = ""; string strCountry = ""; // Get a reference to the records of the Customers table DataRowCollection colRows = this.dsBCR1.Tables["Customers"].Rows; // Check each record one by one for(int i = 0; i < colRows.Count; i++) { // Get the current tag number strDrvLicNbr = (string)colRows[i]["DrvLicNbr"]; // If the current driver's license number matches the one selected by the user... if( strDrvLicNbr == this.cboCustomerID.Text ) { // Retrieve its corresponding information strCustName = (string)colRows[i]["FullName"]; strCustAddress = (string)colRows[i]["Address"]; strCustCity = (string)colRows[i]["City"]; strCustState = (string)colRows[i]["State"]; strCustZIPCode = (string)colRows[i]["ZIPCode"]; strCountry = (string)colRows[i]["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; }

94. Return to the form. Double-click the Rate Applied button and implement the event as follows:
     

    private void btnRateApplied_Click(object sender, System.EventArgs e) { RentalRates frmRates = new RentalRates(); frmRates.Show(); }

95. Double-click the Reset/Start New button and implement its Click event as follows:
     

    private void btnReset_Click(object sender, System.EventArgs e) { this.cboEmployeeID.SelectedIndex = -1; this.txtRentalOrderID.Text = ""; this.cboCarID.SelectedIndex = -1; this.txtMake.Text = ""; this.txtModel.Text = ""; this.txtCarYear.Text = ""; this.cboCarCondition.SelectedIndex = 0; this.cboCustomerID.SelectedIndex = -1; this.txtCustName.Text = ""; this.txtCustAddress.Text = ""; this.txtCustCity.Text = ""; this.txtCustState.Text = ""; this.txtCustZIPCode.Text = ""; this.txtCustCountry.Text = "USA"; this.cboTankLevel.SelectedIndex = 0; this.txtRateApplied.Text = "24.95"; this.txtMileage.Text = "0"; this.txtSubTotal.Text = "0.00"; this.dtpStartDate.Value = DateTime.Today; this.txtTaxRate.Text = "7.75"; TimeSpan ts = new TimeSpan(1, 0, 0, 0); this.dtpEndDate.Value = DateTime.Today.Add(ts); this.txtTaxAmount.Text = "0.00"; this.txtNumberOfDays.Text = "1"; this.txtOrderTotal.Text = "0.00"; this.cboEmployeeID.Focus(); }

96. Return to the form and double-click the End Date control
97. Implement its ValueChanged event as follows:
     

    private void dtpEndDate_ValueChanged(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(); }

98. Return to the form and click the Rate Applied text box (not the button)
99. In the Events section of the Properties window, double-click the right box to Leave and implement its event as follows:
     

    private void txtRateApplied_Leave(object sender, System.EventArgs e) { double rateApplied = double.Parse(this.txtRateApplied.Text); int numberOfDays = int.Parse(this.txtNumberOfDays.Text); double subTotal = rateApplied * numberOfDays; this.txtSubTotal.Text = subTotal.ToString("F"); double taxRate = double.Parse(this.txtTaxRate.Text); double taxAmount = subTotal * taxRate / 100; this.txtTaxAmount.Text = taxAmount.ToString("F"); double orderTotal = subTotal + taxAmount; this.txtOrderTotal.Text = orderTotal.ToString("F"); }

100. Return to the form. Double-click the New Rental Order button on the form and implement its Click event as follows:
      

     private void btnNewOrder_Click(object sender, System.EventArgs e) { if( this.btnNewOrder.Text == "New Rental Order" ) { this.btnNewOrder.Text = "Save"; this.btnReset_Click(sender, e); } else { string strInsert = String.Concat("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.ToDecimal(0), "', '", txtSubTotal.Text.ToDecimal(0), "', '", txtTaxRate.Text.ToDecimal(0), "', '", txtTaxAmount.Text.ToDecimal(0), "', '", txtOrderTotal.Text.ToDecimal(0), "', '", txtNotes.Text, "');"); 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 = "New Rental Order"; } }

101. 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

102. On the form, double-click the Rental Orders button and implement its Click event as follows:
      

     private void btnRentalOrders_Click(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:
      

     private void btnCars_Click(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:
      

     private void btnCustomers_Click(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:
      

     private void btnEmployees_Click(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:
      

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

107. Execute the application
108. Create a few rental orders and save them
      
     
109. Return to the Rental Orders form. Double-click the Find button on the form and implement its Click event as follows:
      

     private void btnFind_Click(object sender, System.EventArgs e) { string strReceiptNumber = this.txtRentalOrderID.Text; if( strReceiptNumber == "" ) { MessageBox.Show("You must provide a receipt number to look for a rental order"); return; } string strFindOrder = String.Concat("SELECT * FROM RentalOrders WHERE RentalOrderID = '", strReceiptNumber, "'"); System.Data.SqlClient.SqlConnection conDatabase = new System.Data.SqlClient.SqlConnection("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 = int.Parse((rdrRentalOrder.GetSqlInt32(1) - 1).ToString()); this.cboCustomerID.SelectedIndex = int.Parse((rdrRentalOrder.GetSqlInt32(2) - 1).ToString()); this.cboCarID.SelectedIndex = int.Parse((rdrRentalOrder.GetSqlInt32(3) - 1).ToString()); 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