 |
Example Database Application:
College Park Auto Repair |
|
|
|
A car repair shop is a business where people bring their
cars to have them serviced. Before working on the car,the company would get
some basic information about the customer (such as name) and the car (such
as what is wrong with the car).
|
We are going to create a graphical application that can
assist a car repair shop manage its business.
|
 Practical
Learning: Introducing the Application
|
|
- Start Microsoft Visual Studio
- To start a new project, on the main menu, click File -> New ->
Project...
- Select the language or environment you will use. We will use
Visual Basic
- Make sure Windows Forms Application is selected.
Set the Name
to CollegeParkAutoRepair1
- Click OK
- In the Solution Explorer, right-click Form1.cs and click Rename
- Type CollegeParkAutoRepair.vb and press Enter
- Double-click the body of the form to generate its Load event
- Implement the event as follows:
Imports System.Data.SqlClient
Public Class CollegeParkAutoRepair
Private Sub CreateDatabase()
' Specify the name of your server. Ours is named EXPRESSION
Dim strServerName As String = "EXPRESSION"
Using cntCPAR As SqlConnection =
New SqlConnection("Data Source=" & strServerName & ";" &
"Integrated Security=Yes")
Dim cmdCPAR As SqlCommand =
New SqlCommand("IF EXISTS (" &
"SELECT name " &
"FROM sys.databases " &
"WHERE name = N'CollegeParkAutoRepair1')" &
"DROP DATABASE CollegeParkAutoRepair1; " &
"CREATE DATABASE CollegeParkAutoRepair1;", cntCPAR)
cntCPAR.Open()
cmdCPAR.ExecuteNonQuery()
End Using
Using cntCPAR As SqlConnection =
New SqlConnection("Data Source=" & strServerName & ";" &
"Database='CollegeParkAutoRepair1';" &
"Integrated Security=Yes")
Dim cmdCPAR As SqlCommand =
New SqlCommand("CREATE SCHEMA Repairs;", cntCPAR)
cntCPAR.Open()
cmdCPAR.ExecuteNonQuery()
End Using
MsgBox("The CollegeParkAutoRepair1 database has been created.",
MsgBoxStyle.OkOnly Or MsgBoxStyle.Information,
"College Park Auto Repair")
End Sub
Private Sub CollegeParkAutoRepair_Load(sender As System.Object,
e As System.EventArgs) Handles MyBase.Load
CreateDatabase()
End Sub
End Class
- Execute the application
- When the message box displays, click OK
- Close the form and return to your programming environment
A repair order is the core operation of this business.
A customer has to bring a car to the shop. An employee must first create a
repair order. To do this, the employee must get some basic information
about the customer such as the name, the car make, the model, the year,
and the problem with the car. The system must create a repair order that
also has a receipt number.
|
Practical
Learning: Creating a Repair Order
|
|
- Change the document as follows:
Imports System.Data.SqlClient
Public Class CollegeParkAutoRepair
Private Sub CreateRepairOrders()
' Specify the name of your server. Ours is named EXPRESSION
Dim strServerName As String = "EXPRESSION"
Using cntCPAR As SqlConnection =
New SqlConnection("Data Source=" & strServerName & ";" &
"Database='CollegeParkAutoRepair1';" &
"Integrated Security=Yes")
Dim cmdCeilInn As SqlCommand =
New SqlCommand("CREATE TABLE Repairs.Orders" &
"(" &
" ReceiptNumber int not null, " &
" DateReceived nvarchar(50), " &
" TimeReceived nvarchar(20), " &
" DateCompleted nvarchar(50), " &
" TimeCompleted nvarchar(20), " &
" DatePickedUp nvarchar(50), " &
" TimePickedUp nvarchar(20), " &
" CustomerName nvarchar(50), " &
" Address nvarchar(60), " &
" City nvarchar(40), " &
" State nvarchar(40), " &
" ZIPCode nvarchar(20), " &
" Make nvarchar(50), " &
" Model nvarchar(50), " &
" CarYear int, " &
" ProblemDescription nvarchar(max), " &
" TaxRate decimal(6, 2), " &
" Recommendations nvarchar(max), " &
" Constraint PK_RepairOrders Primary Key (ReceiptNumber)" &
");",
cntCPAR)
cntCPAR.Open()
cmdCeilInn.ExecuteNonQuery()
End Using
MsgBox("A table for repair orders has been created.",
MsgBoxStyle.OkOnly Or MsgBoxStyle.Information,
"College Park Auto Repair")
End Sub
Private Sub CollegeParkAutoRepair_Load(sender As System.Object,
e As System.EventArgs) Handles MyBase.Load
CreateRepairOrders()
End Sub
End Class
- Execute the application
- When the message box displays, click OK
- Close the form and return to your programming environment
Most of the time, to repair a car, a technician must
used one
or parts. In some cases, the company may have easy-to-find parts in place.
In most other cases, the parts must be ordered. Either way, the company must
keep track of the parts that were used to repair the car because the
customer will have to pay for them (in some cases, especially in small
companies, the technician would ask the customer to purchase the part(s)
first; in some other cases, the company would ask the customer to approve
using or purchasing a part; this would be added to the customer's invoice
who would then pay for the part(s)).
To keep track of the parts that were used to repair a
car, we will create a table for parts used. The record of each part would
hold a receipt number. That way, with the magic of relational databases,
we will be able to know what repair order used this or that part.
|
Practical
Learning: Keeping Track or Parts Used
|
|
- Change the document as follows:
Imports System.Data.SqlClient
Public Class CollegeParkAutoRepair
Private Sub CreatePartsUsed()
' Specify the name of your server. Ours is named EXPRESSION
Dim strServerName As String = "EXPRESSION"
Using cntCPAR As SqlConnection =
New SqlConnection("Data Source=" & strServerName & ";" &
"Database='CollegeParkAutoRepair1';" &
"Integrated Security=Yes")
Dim cmdCeilInn As SqlCommand =
New SqlCommand("CREATE TABLE Repairs.PartsUsed" &
"(" &
" PartUsedID int identity(1, 1) not null, " &
" ReceiptNumber int, " &
" PartName nvarchar(50), " &
" UnitPrice money, " &
" Quantity int, " &
" Constraint PK_PartsUsed Primary Key (PartUsedID)" &
");",
cntCPAR)
cntCPAR.Open()
cmdCeilInn.ExecuteNonQuery()
End Using
MsgBox("A table has been created to keep track of parts used in a repair order.",
MsgBoxStyle.OkOnly Or MsgBoxStyle.Information,
"College Park Auto Repair")
End Sub
Private Sub CollegeParkAutoRepair_Load(sender As System.Object,
e As System.EventArgs) Handles MyBase.Load
CreatePartsUsed()
End Sub
End Class
- Execute the application
- When the message box displays, click OK
- Close the form and return to your programming environment
Another important part of a car repair invoice is to
know what was done to fix the vehicle. To inform the customer of this, a
list of jobs performed should be included in the invoice.
To keep track of the jobs that were performe to repair
a car, we will create a table for them. The record of each job would have
a receipt number. When we access the record of a job, we would know what
repair order it is associated with.
|
Practical
Learning: Keeping Track or Jobs Performed
|
|
- Change the document as follows:
Imports System.Data.SqlClient
Public Class CollegeParkAutoRepair
Private Sub CreateJobsPerformed()
' Specify the name of your server. Ours is named EXPRESSION
Dim strServerName As String = "EXPRESSION"
Using cntCPAR As SqlConnection =
New SqlConnection("Data Source=" & strServerName & ";" &
"Database='CollegeParkAutoRepair1';" &
"Integrated Security=Yes")
Dim cmdCeilInn As SqlCommand =
New SqlCommand("CREATE TABLE Repairs.JobsPerformed" &
"(" &
" JobPerformedID int identity(1, 1) not null, " &
" ReceiptNumber int, " &
" JobPerformed nvarchar(100), " &
" JobPrice money, " &
" Constraint PK_JobsPerformed Primary Key (JobPerformedID)" &
");",
cntCPAR)
cntCPAR.Open()
cmdCeilInn.ExecuteNonQuery()
End Using
MsgBox("A table has been created to keep track of job performed for a repair order.",
MsgBoxStyle.OkOnly Or MsgBoxStyle.Information,
"College Park Auto Repair")
End Sub
Private Sub CollegeParkAutoRepair_Load(sender As System.Object,
e As System.EventArgs) Handles MyBase.Load
CreateJobsPerformed()
End Sub
End Class
- Execute the application
- When the message box displays, click OK
- Close the form and return to your programming environment
|
Creating a New Repair Order
|
|
When a customer brings a car to the shop for a repair,
an employee must start a new repart order. We will create a form that provides such
graphic functionality.
|
Practical
Learning: Starting a New Repair Order
|
|
- On the main menu, click Project -> Add Windows
Form...
- Set the Name to NewRepairOrder
- Click Add
- Design the form as follows:
 |
| Control |
(Name) |
Text |
Other Properties |
| GroupBox |
 |
|
Customer Order Identification |
|
| Label |
 |
|
Customer Name: |
|
| TextBox |
 |
TxtCustomerName |
|
|
| Label |
|
|
Address: |
|
| TextBox |
|
TxtAddress |
|
|
| Label |
|
|
City: |
|
| TextBox |
|
TxtCity |
|
|
| Label |
|
|
State: |
|
| TextBox |
|
TxtState |
|
|
| Label |
|
|
ZIP Code: |
|
| TextBox |
|
TxtZIPCode |
|
|
| Label |
|
|
Make/Model: |
|
| TextBox |
|
TxtMake |
|
|
| TextBox |
|
TxtModel |
|
|
| Label |
|
|
Year: |
|
| TextBox |
|
TxtCarYear |
|
AlignText: Right |
| Label |
|
|
Problem Description: |
|
| TextBox |
|
TxtProblemDescription |
|
Multine: True
Scrollbars: Vertical |
| GroupBox |
|
|
Parts Used |
|
| Label |
|
|
Part Name/Description |
|
| Label |
|
|
Unit Price |
|
| Label |
|
|
Quantity |
|
| Label |
|
|
Sub-Total |
|
| TextBox |
|
TxtPartName |
|
|
| TextBox |
|
TxtUnitPrice |
0.00 |
AlignText: Right |
| TextBox |
|
TxtQuantity |
0 |
AlignText: Right |
| TextBox |
|
TxtSubTotal |
0.00 |
AlignText: Right |
| Button |
 |
BtnAddPart |
Add Part |
|
| ListView |
 |
LvwPartsUsed |
|
|
| Columns |
|
| (Name) |
Text |
TextAlign |
Width |
| ColPartName |
Part Name/Description |
|
220 |
| ColUnitPrice |
Unit Price |
Right |
|
| ColQuantity |
Qty |
Right |
30 |
| ColSubTotal |
Sub-Total |
Right |
|
|
| GroupBox |
|
|
Repair Summary |
|
| Label |
|
|
Total Parts: |
|
| TextBox |
|
TxtTotalParts |
0.00 |
AlignText: Right |
| Label |
|
|
Total Labor: |
|
| TextBox |
|
TxtTotalLabor |
0.00 |
AlignText: Right |
| Label |
|
|
Total Parts && Labor: |
|
| TextBox |
|
TxtTotalPartsLabor |
0.00 |
AlignText: Right |
| Label |
|
|
Tax Rate: |
|
| TextBox |
|
TxtTaxRate |
5.75 |
AlignText: Right |
| Label |
|
|
% |
|
| Label |
|
|
Tax Amount: |
|
| TextBox |
|
TxtTaxAmount |
0.00 |
AlignText: Right |
| Label |
|
|
Repair Total: |
|
| TextBox |
|
TxtRepairTotal |
0.00 |
AlignText: Right |
| Label |
|
|
Recommendations: |
|
| TextBox |
|
TxtRecommendations |
|
Multine: True
Scrollbars: Vertical |
| Label |
|
|
Date Received: |
|
| DateTimePicker |
 |
DtpDateReceived |
|
|
| Label |
|
|
Time: |
|
| DateTimePicker |
|
DtpTimeReceived |
|
Format: Time
ShowUpDown: True |
| Label |
|
|
Date Completed: |
|
| DateTimePicker |
|
DtpDateCompleted |
|
|
| Label |
|
|
Time: |
|
| DateTimePicker |
|
DtpTimeCompleted |
|
Format: Time
ShowUpDown: True |
| Label |
|
|
Date Picked Up: |
|
| DateTimePicker |
|
DtpDatePickedUp |
|
|
| Label |
|
|
Time: |
|
| DateTimePicker |
|
DtpTimePickedUp |
|
Format: Time
ShowUpDown: True |
| GroupBox |
|
|
Jobs Performed |
|
| Label |
|
|
Job Performed |
|
| Label |
|
|
Job Cost |
|
| TextBox |
|
TxtJobPerformed |
|
|
| TextBox |
|
TxtJobCost |
0.00 |
AlignText: Right |
| ListView |
|
LvwJobsPerformed |
|
|
| Columns |
|
| (Name) |
Text |
TextAlign |
Width |
| ColJobPerformed |
Job Performed |
|
310 |
| ColJobCost |
Job Cost |
Right |
|
|
| Label |
|
|
Receipt #: |
|
| TextBox |
|
TxtReceiptNumber |
|
|
| Button |
|
BtnSubmit |
Submit |
|
| Button |
|
BtnReset |
Reset |
|
| Button |
|
BtnClose |
Close |
|
| StatusStrip |
 |
|
|
|
|
- Right-click the form and click View Code
- Create a procedure as follows:
Imports System.Data.SqlClient
Public Class NewRepairOrder
Private Sub CalculateRepairOrder()
Dim TaxRate As Double
Dim TotalJobs As Double
Dim TaxAmount As Double
Dim TotalParts As Double
Dim LviJob As ListViewItem
Dim LviPart As ListViewItem
Dim TotalPartsAndLabor As Double
If LvwPartsUsed.Items.Count = 0 Then
TotalParts = 0.0
Else
For Each LviPart In LvwPartsUsed.Items
TotalParts += CDbl(LviPart.SubItems(3).Text)
Next
End If
If LvwJobsPerformed.Items.Count = 0 Then
TotalJobs = 0.0
Else
For Each LviJob In LvwJobsPerformed.Items
TotalJobs += CDbl(LviJob.SubItems(1).Text)
Next
End If
Try
TaxRate = CDbl(TxtTaxRate.Text)
TotalPartsAndLabor = TotalParts + TotalJobs
TaxAmount = TotalPartsAndLabor * TaxRate / 100
TxtTotalParts.Text = FormatNumber(TotalParts)
TxtTotalLabor.Text = FormatNumber(TotalJobs)
TxtTotalPartsLabor.Text = FormatNumber(TotalPartsAndLabor)
TxtTaxAmount.Text = FormatNumber(TaxAmount)
TxtRepairTotal.Text = FormatNumber(TotalPartsAndLabor + TaxAmount)
Catch ex As FormatException
MsgBox("The calculation could not be carried. Please report the error as:" & vbCrLf &
"Error Message: " & ex.Message, MsgBoxStyle.OkOnly Or MsgBoxStyle.Information,
"College Park Auto Repair")
End Try
End Sub
End Class
- In the Object combo box, select TxtQuantity
- In the Procedure combo box, select Leave
- Implement the event as follows:
Private Sub TxtQuantity_Leave(sender As Object,
e As System.EventArgs) Handles TxtQuantity.Leave
Dim UnitPrice As Double
Dim Quantity As Integer
Dim SubTotal As Double
If String.IsNullOrEmpty(TxtUnitPrice.Text) Then
Exit Sub
End If
If String.IsNullOrEmpty(TxtQuantity.Text) Then
Exit Sub
End If
Try
UnitPrice = CDbl(TxtUnitPrice.Text)
Quantity = CInt(TxtQuantity.Text)
SubTotal = FormatNumber(UnitPrice * Quantity)
TxtSubTotal.Text = FormatNumber(SubTotal)
Catch ex As FormatException
MsgBox("The calculation could not be carried. Please report the error as:" & vbCrLf &
"Error Message: " & ex.Message, MsgBoxStyle.OkOnly Or MsgBoxStyle.Information,
"College Park Auto Repair")
End Try
CalculateRepairOrder()
End Sub
- In the Object combo box, select BtnAddPart
- In the Procedure combo box, select Click
- Implement the event as follows:
Private Sub BtnAddPart_Click(sender As Object,
e As System.EventArgs) Handles BtnAddParts.Click
If String.IsNullOrEmpty(TxtPartName.Text) Then
Exit Sub
End If
Dim LviPart As New ListViewItem(TxtPartName.Text)
LviPart.SubItems.Add(FormatNumber(TxtUnitPrice.Text))
LviPart.SubItems.Add(TxtQuantity.Text)
LviPart.SubItems.Add(FormatNumber(TxtSubTotal.Text))
LvwPartsUsed.Items.Add(LviPart)
TxtPartName.Text = ""
TxtUnitPrice.Text = "0.00"
TxtQuantity.Text = "0"
TxtSubTotal.Text = "0.00"
CalculateRepairOrder()
End Sub
- In the Object combo box, select LvwPartsUsed
- In the Procedure combo box, select DoubleClick
- Implement the event as follows:
Private Sub LvwPartsUsed_DoubleClick(sender As Object,
e As System.EventArgs) Handles LvwPartsUsed.DoubleClick
' This code acts if the user double-clicks an item in the list of parts used.
' We conclude that either the user wants to edit the part
' or the user wants to delete the part that was added by mistake
Dim LviPartUsed As ListViewItem = LvwPartsUsed.SelectedItems(0)
TxtPartName.Text = LviPartUsed.SubItems(0).Text
TxtUnitPrice.Text = LviPartUsed.SubItems(1).Text
TxtQuantity.Text = LviPartUsed.SubItems(2).Text
TxtSubTotal.Text = LviPartUsed.SubItems(3).Text
' Remove the selected item from the list view
LviPartUs.Remove()
End Sub
- In the Object combo box, select BtnAddJob
- In the Procedure combo box, select Click
- Implement the event as follows:
Private Sub BtnAddJob_Click(sender As Object,
e As System.EventArgs) Handles BtnAddJob.Click
If String.IsNullOrEmpty(TxtJobPerformed.Text) Then
Exit Sub
End If
Dim LviJob As New ListViewItem(TxtJobPerformed.Text)
LviJob.SubItems.Add(FormatNumber(TxtJobCost.Text))
LvwJobsPerformed.Items.Add(LviJob)
TxtJobPerformed.Text = ""
TxtJobCost.Text = "0.00"
CalculateRepairOrder()
End Sub
- In the Object combo box, select LvwJobsPerformed
- In the Procedure combo box, select DoubleClick
- Implement the event as follows:
Private Sub LvwJobsPerformed_DoubleClick(sender As Object,
e As System.EventArgs) Handles LvwJobsPerformed.DoubleClick
' This code acts if the user double-clicks a job
' from the list of jobs performed.
' We conclude that either the user wants to edit the job
' or the user wants to remove/cancel the job
Dim LviJobPerformed As ListViewItem = LvwJobsPerformed.SelectedItems(0)
TxtJobPerformed.Text = LviJobPerformed.SubItems(0).Text
TxtJobCost.Text = LviJobPerformed.SubItems(1).Text
' Remove the selected job from the list view
LviJobPerformed.Remove()
End Sub
- In the Object combo box, select BtnReset
- In the Procedure combo box, select Click
- Implement the event as follows:
Private Sub BtnReset_Click(sender As Object, e As System.EventArgs) Handles BtnReset.Click
Dim ReceiptNumber As Integer
Dim strServerName As String = "EXPRESSION"
ReceiptNumber = 100000
Using cntCPAR As SqlConnection =
New SqlConnection("Data Source=" & strServerName & ";" &
"Database='CollegeParkAutoRepair1';" &
"Integrated Security=Yes")
Dim cmdCPAR As SqlCommand =
New SqlCommand("SELECT MAX(ReceiptNumber) FROM Repairs.Orders;",
cntCPAR)
cntCPAR.Open()
Dim sdrReceipts As SqlDataReader
sdrReceipts = cmdCPAR.ExecuteReader()
Do While sdrReceipts.Read()
If Not String.IsNullOrEmpty(sdrReceipts(0).ToString()) Then
ReceiptNumber = CInt(sdrReceipts.GetSqlInt32(0).ToString())
End If
Loop
TxtReceiptNumber.Text = CStr(ReceiptNumber + 1)
End Using
DtpDateReceived.Value = Today
DtpTimeReceived.Value = Now
TxtCustomerName.Text = ""
TxtAddress.Text = ""
TxtCity.Text = ""
TxtState.Text = ""
TxtZIPCode.Text = ""
TxtMake.Text = ""
TxtModel.Text = ""
TxtCarYear.Text = ""
TxtProblemDescription.Text = ""
TxtPartName.Text = ""
TxtUnitPrice.Text = "0.00"
TxtQuantity.Text = "0"
TxtSubTotal.Text = "0.00"
LvwPartsUsed.Items.Clear()
TxtJobPerformed.Text = ""
TxtJobCost.Text = "0.00"
LvwJobsPerformed.Items.Clear()
TxtTotalParts.Text = "0.00"
TxtTotalLabor.Text = "0.00"
TxtTaxRate.Text = "5.75"
TxtTaxAmount.Text = "0.00"
TxtTotalPartsLabor.Text = "0.00"
TxtRepairTotal.Text = "0.00"
TxtRecommendations.Text = ""
End Sub
- In the Object combo box, select (NewrepairOrder Events)
- In the Procedure combo box, select Load
- Implement the event as follows:
Private Sub NewRepairOrder_Load(sender As System.Object,
e As System.EventArgs) Handles MyBase.Load
BtnReset_Click(sender, e)
End Sub
- In the Object combo box, select BtnSubmit
- In the Procedure combo box, select Click
- Implement the event as follows:
Private Sub BtnSubmit_Click(sender As Object, e As System.EventArgs) Handles BtnSubmit.Click
Dim LviJob As ListViewItem
Dim LviPart As ListViewItem
Dim strServerName As String = "EXPRESSION"
Using cntCPAR As SqlConnection =
New SqlConnection("Data Source=" & strServerName & ";" &
"Database='CollegeParkAutoRepair1';" &
"Integrated Security=Yes")
Dim cmdCeilInn As SqlCommand =
New SqlCommand("INSERT INTO Repairs.Orders" &
"(ReceiptNumber, CustomerName, Address, City, State, ZIPCode, Make, Model, " &
"CarYear, ProblemDescription, TaxRate, Recommendations, " &
"DateReceived, TimeReceived, DateCompleted, TimeCompleted, " &
"DatePickedUp, TimePickedUp) " &
"VALUES(" & CInt(TxtReceiptNumber.Text) & ", '" & TxtCustomerName.Text &
"', '" & TxtAddress.Text & "', '" & TxtCity.Text & "', '" &
TxtState.Text & "', '" & TxtZIPCode.Text & "', '" & TxtMake.Text &
"', '" & TxtModel.Text & "', " & CInt(TxtCarYear.Text) & ", '" &
TxtProblemDescription.Text & "', " & CDbl(TxtTaxRate.Text) &
", '" & TxtRecommendations.Text & "', '" &
DtpDateReceived.Value.ToShortDateString() & "', '" &
DtpTimeReceived.Value.ToShortTimeString() & "', '" &
DtpDateCompleted.Value.ToShortDateString() & "', '" &
DtpTimeCompleted.Value.ToShortTimeString & "', '" &
DtpDatePickedUp.Value.ToShortDateString() & "', '" &
DtpTimePickedUp.Value.ToShortTimeString & "');",
cntCPAR)
cntCPAR.Open()
cmdCeilInn.ExecuteNonQuery()
End Using
If LvwPartsUsed.Items.Count > 0 Then
For Each LviPart In LvwPartsUsed.Items
Using cntCPAR As SqlConnection =
New SqlConnection("Data Source=" & strServerName & ";" &
"Database='CollegeParkAutoRepair1';" &
"Integrated Security=Yes")
Dim cmdCeilInn As SqlCommand =
New SqlCommand("INSERT INTO Repairs.PartsUsed" &
"(ReceiptNumber, PartName, UnitPrice, Quantity) " &
"VALUES(" & CInt(TxtReceiptNumber.Text) & ", '" &
LviPart.SubItems(0).Text & "', " & CDbl(LviPart.SubItems(1).Text) &
", " & CInt(LviPart.SubItems(2).Text) & ");",
cntCPAR)
cntCPAR.Open()
cmdCeilInn.ExecuteNonQuery()
End Using
Next
End If
If LvwJobsPerformed.Items.Count > 0 Then
For Each LviJob In LvwJobsPerformed.Items
Using cntCPAR As SqlConnection =
New SqlConnection("Data Source=" & strServerName & ";" &
"Database='CollegeParkAutoRepair1';" &
"Integrated Security=Yes")
Dim cmdCeilInn As SqlCommand =
New SqlCommand("INSERT INTO Repairs.JobsPerformed" &
"(ReceiptNumber, JobPerformed, JobPrice) " &
"VALUES(" & CInt(TxtReceiptNumber.Text) & ", '" &
LviJob.SubItems(0).Text & "', '" &
LviJob.SubItems(1).Text & "');",
cntCPAR)
cntCPAR.Open()
cmdCeilInn.ExecuteNonQuery()
End Using
Next
End If
MsgBox("The new repair order has been created.",
MsgBoxStyle.OkOnly Or MsgBoxStyle.Information,
"College Park Auto Repair")
BtnReset_Click(sender, e)
End Sub
- In the Object combo box, select BtnClose
- In the Procedure combo box, select Click
- Implement the event as follows:
Private Sub BtnClose_Click(sender As System.Object,
e As System.EventArgs) Handles BtnClose.Click
Close()
End Sub
As opposed to creating a new repair order, when the
customer has been repaired, or when the customer comes to pick up
the car, entries should/must be made in the repair record. For example, a
technician can make a note that the car has been repaired. Another time, a
cashier can make a note that the customer has come to pay the invoice and
picked up the car. In this and other circumstances, an update must be made
into the repair order.
|
Practical
Learning: Updating a Repair Order
|
|
- On the main menu, click Project -> Add Windows Form...
- Set the Name to UpdateRepairOrder
- Click Add
- Design the form as follows:
 |
| Control |
(Name) |
Text |
Other Properties |
| GroupBox |
|
|
Customer Order Identification |
|
| Label |
|
|
Customer Name: |
|
| TextBox |
|
TxtCustomerName |
|
|
| Label |
|
|
Address: |
|
| TextBox |
|
TxtAddress |
|
|
| Label |
|
|
City: |
|
| TextBox |
|
TxtCity |
|
|
| Label |
|
|
State: |
|
| TextBox |
|
TxtState |
|
|
| Label |
|
|
ZIP Code: |
|
| TextBox |
|
TxtZIPCode |
|
|
| Label |
|
|
Make/Model: |
|
| TextBox |
|
TxtMake |
|
|
| TextBox |
|
TxtModel |
|
|
| Label |
|
|
Year: |
|
| TextBox |
|
TxtCarYear |
|
AlignText: Right |
| Label |
|
|
Problem Description: |
|
| TextBox |
|
TxtProblemDescription |
|
Multine: True
Scrollbars: Vertical |
| GroupBox |
|
|
Parts Used |
|
| Label |
|
|
Part Name/Description |
|
| Label |
|
|
Unit Price |
|
| Label |
|
|
Quantity |
|
| Label |
|
|
Sub-Total |
|
| TextBox |
|
TxtPartName |
|
|
| TextBox |
|
TxtUnitPrice |
0.00 |
AlignText: Right |
| TextBox |
|
TxtQuantity |
0 |
AlignText: Right |
| TextBox |
|
TxtSubTotal |
0.00 |
AlignText: Right |
| Button |
|
BtnAddPart |
Add Part |
|
| ListView |
|
LvwPartsUsed |
|
|
| Columns |
|
| (Name) |
Text |
TextAlign |
Width |
| ColPartName |
Part Name/Description |
|
220 |
| ColUnitPrice |
Unit Price |
Right |
|
| ColQuantity |
Qty |
Right |
30 |
| ColSubTotal |
Sub-Total |
Right |
|
|
| GroupBox |
|
|
Repair Summary |
|
| Label |
|
|
Total Parts: |
|
| TextBox |
|
TxtTotalParts |
0.00 |
AlignText: Right |
| Label |
|
|
Total Labor: |
|
| TextBox |
|
TxtTotalLabor |
0.00 |
AlignText: Right |
| Label |
|
|
Total Parts && Labor: |
|
| TextBox |
|
TxtTotalPartsLabor |
0.00 |
AlignText: Right |
| Label |
|
|
Tax Rate: |
|
| TextBox |
|
TxtTaxRate |
5.75 |
AlignText: Right |
| Label |
|
|
% |
|
| Label |
|
|
Tax Amount: |
|
| TextBox |
|
TxtTaxAmount |
0.00 |
AlignText: Right |
| Label |
|
|
Repair Total: |
|
| TextBox |
|
TxtRepairTotal |
0.00 |
AlignText: Right |
| Label |
|
|
Recommendations: |
|
| TextBox |
|
TxtRecommendations |
|
Multine: True
Scrollbars: Vertical |
| Label |
|
|
Date Received: |
|
| DateTimePicker |
|
DtpDateReceived |
|
|
| Label |
|
|
Time: |
|
| DateTimePicker |
|
DtpTimeReceived |
|
Format: Time
ShowUpDown: True |
| Label |
|
|
Date Completed: |
|
| DateTimePicker |
|
DtpDateCompleted |
|
|
| Label |
|
|
Time: |
|
| DateTimePicker |
|
DtpTimeCompleted |
|
Format: Time
ShowUpDown: True |
| Label |
|
|
Date Picked Up: |
|
| DateTimePicker |
|
DtpDatePickedUp |
|
|
| Label |
|
|
Time: |
|
| DateTimePicker |
|
DtpTimePickedUp |
|
Format: Time
ShowUpDown: True |
| GroupBox |
|
|
Jobs Performed |
|
| Label |
|
|
Job Performed |
|
| Label |
|
|
Job Cost |
|
| TextBox |
|
TxtJobPerformed |
|
|
| TextBox |
|
TxtJobCost |
0.00 |
AlignText: Right |
| ListView |
|
LvwJobsPerformed |
|
|
| Columns |
|
| (Name) |
Text |
TextAlign |
Width |
| ColJobPerformed |
Job Performed |
|
310 |
| ColJobCost |
Job Cost |
Right |
|
|
| Label |
|
|
Receipt #: |
|
| TextBox |
|
TxtReceiptNumber |
|
|
| Button |
|
BtnOpen |
Open |
|
| Button |
|
BtnSubmit |
Submit |
|
| Button |
|
BtnClose |
Close |
|
| StatusStrip |
|
|
|
|
|
- Double-click the Open button
- Implement the event as follows:
Imports System.Data.SqlClient
Public Class UpdateRepairOrder
Private Sub CalculateRepairOrder()
Dim TaxRate As Double
Dim TotalJobs As Double
Dim TaxAmount As Double
Dim TotalParts As Double
Dim LviJob As ListViewItem
Dim LviPart As ListViewItem
Dim TotalPartsAndLabor As Double
If LvwPartsUsed.Items.Count = 0 Then
TotalParts = 0.0
Else
For Each LviPart In LvwPartsUsed.Items
TotalParts += CDbl(LviPart.SubItems(3).Text)
Next
End If
If LvwJobsPerformed.Items.Count = 0 Then
TotalJobs = 0.0
Else
For Each LviJob In LvwJobsPerformed.Items
TotalJobs += CDbl(LviJob.SubItems(1).Text)
Next
End If
Try
TaxRate = CDbl(TxtTaxRate.Text)
TotalPartsAndLabor = TotalParts + TotalJobs
TaxAmount = TotalPartsAndLabor * TaxRate / 100
TxtTotalParts.Text = FormatNumber(TotalParts)
TxtTotalLabor.Text = FormatNumber(TotalJobs)
TxtTotalPartsLabor.Text = FormatNumber(TotalPartsAndLabor)
TxtTaxAmount.Text = FormatNumber(TaxAmount)
TxtRepairTotal.Text = FormatNumber(TotalPartsAndLabor + TaxAmount)
Catch ex As FormatException
MsgBox("The calculation could not be carried. Please report the error as:" & vbCrLf &
"Error Message: " & ex.Message, MsgBoxStyle.OkOnly Or MsgBoxStyle.Information,
"College Park Auto Repair")
End Try
End Sub
Private Sub ResetRepairOrder()
DtpDateReceived.Value = Today
DtpTimeReceived.Value = Now
TxtCustomerName.Text = ""
TxtAddress.Text = ""
TxtCity.Text = ""
TxtState.Text = ""
TxtZIPCode.Text = ""
TxtMake.Text = ""
TxtModel.Text = ""
TxtCarYear.Text = ""
TxtProblemDescription.Text = ""
TxtPartName.Text = ""
TxtUnitPrice.Text = "0.00"
TxtQuantity.Text = "0"
TxtSubTotal.Text = "0.00"
LvwPartsUsed.Items.Clear()
TxtJobPerformed.Text = ""
TxtJobCost.Text = "0.00"
LvwJobsPerformed.Items.Clear()
TxtTotalParts.Text = "0.00"
TxtTotalLabor.Text = "0.00"
TxtTaxRate.Text = "5.75"
TxtTaxAmount.Text = "0.00"
TxtTotalPartsLabor.Text = "0.00"
TxtRepairTotal.Text = "0.00"
TxtRecommendations.Text = ""
End Sub
Private Sub BtnOpen_Click(sender As System.Object, e As System.EventArgs) Handles BtnOpen.Click
Dim strServerName As String = "EXPRESSION"
ResetRepairOrder
If String.IsNullOrEmpty(TxtReceiptNumber.Text) Then
MsgBox("You must enter a receipt number to open its repair order.",
MsgBoxStyle.OkOnly Or MsgBoxStyle.Information,
"College Park Auto Repair")
Exit Sub
End If
Using cntCPAR As SqlConnection =
New SqlConnection("Data Source=" & strServerName & ";" &
"Database='CollegeParkAutoRepair1';" &
"Integrated Security=Yes")
Dim cmdCPAR As SqlCommand =
New SqlCommand("SELECT ALL * " &
"FROM Repairs.Orders " &
"WHERE ReceiptNumber = " & CInt(TxtReceiptNumber.Text) & ";",
cntCPAR)
cntCPAR.Open()
Dim sdrReceipts As SqlDataReader
sdrReceipts = cmdCPAR.ExecuteReader()
Do While sdrReceipts.Read()
If Not String.IsNullOrEmpty(sdrReceipts(0).ToString()) Then
DtpDateReceived.Value = CDate(sdrReceipts(1).ToString())
DtpTimeReceived.Value = New DateTime(DtpDateReceived.Value.Year,
DtpDateReceived.Value.Month,
DtpDateReceived.Value.Day,
Hour(CDate(sdrReceipts(2).ToString())),
Minute(CDate(sdrReceipts(2).ToString())),
Second(CDate(sdrReceipts(2).ToString())))
DtpDateCompleted.Value = CDate(sdrReceipts(3).ToString())
DtpTimeCompleted.Value = New DateTime(DtpDateCompleted.Value.Year,
DtpDateCompleted.Value.Month,
DtpDateCompleted.Value.Day,
Hour(CDate(sdrReceipts(4).ToString())),
Minute(CDate(sdrReceipts(4).ToString())),
Second(CDate(sdrReceipts(4).ToString())))
DtpDatePickedUp.Value = CDate(sdrReceipts(5).ToString())
DtpTimePickedUp.Value = New DateTime(DtpDatePickedUp.Value.Year,
DtpDatePickedUp.Value.Month,
DtpDatePickedUp.Value.Day,
Hour(CDate(sdrReceipts(6).ToString())),
Minute(CDate(sdrReceipts(6).ToString())),
Second(CDate(sdrReceipts(6).ToString())))
TxtCustomerName.Text = sdrReceipts(7).ToString()
TxtAddress.Text = sdrReceipts(8).ToString()
TxtCity.Text = sdrReceipts(9).ToString()
TxtState.Text = sdrReceipts(10).ToString()
TxtZIPCode.Text = sdrReceipts(11).ToString()
TxtMake.Text = sdrReceipts(12).ToString()
TxtModel.Text = sdrReceipts(13).ToString()
TxtCarYear.Text = sdrReceipts(14).ToString()
TxtProblemDescription.Text = sdrReceipts(15).ToString()
TxtTaxRate.Text = CDbl(sdrReceipts(16).ToString())
TxtRecommendations.Text = sdrReceipts(17).ToString()
End If
Loop
End Using
LvwPartsUsed.Items.Clear()
Using cntCPAR As SqlConnection =
New SqlConnection("Data Source=" & strServerName & ";" &
"Database='CollegeParkAutoRepair1';" &
"Integrated Security=Yes")
Dim cmdCPAR As SqlCommand =
New SqlCommand("SELECT ALL * " &
"FROM Repairs.PartsUsed " &
"WHERE ReceiptNumber = " & CInt(TxtReceiptNumber.Text) & ";",
cntCPAR)
Dim sdaPartsUsed As SqlDataAdapter = New SqlDataAdapter
Dim dsPartsUsed As DataSet = New DataSet("PartsUsed")
cntCPAR.Open()
sdaPartsUsed.SelectCommand = cmdCPAR
sdaPartsUsed.Fill(dsPartsUsed)
For Each drPartUsed As DataRow In dsPartsUsed.Tables(0).Rows
Dim LviPartUsed As ListViewItem = New ListViewItem(CStr(drPartUsed("PartName")))
LviPartUsed.SubItems.Add(FormatNumber(CStr(drPartUsed("UnitPrice"))))
LviPartUsed.SubItems.Add(CStr(drPartUsed("Quantity")))
LviPartUsed.SubItems.Add(FormatNumber(CStr(CDbl(CStr(drPartUsed("UnitPrice"))) * CInt(CStr(drPartUsed("Quantity"))))))
LvwPartsUsed.Items.Add(LviPartUsed)
Next
End Using
LvwJobsPerformed.Items.Clear()
Using cntCPAR As SqlConnection = New SqlConnection("Data Source=" & strServerName & ";" &
"Database='CollegeParkAutoRepair1';" &
"Integrated Security=Yes")
Dim cmdCPAR As SqlCommand =
New SqlCommand("SELECT ALL * " &
"FROM Repairs.JobsPerformed " &
"WHERE ReceiptNumber = " & CInt(TxtReceiptNumber.Text) & ";",
cntCPAR)
Dim sdaJobsPerformed As SqlDataAdapter = New SqlDataAdapter
Dim dsJobsPerformed As DataSet = New DataSet("JobsPerformed")
cntCPAR.Open()
sdaJobsPerformed.SelectCommand = cmdCPAR
sdaJobsPerformed.Fill(dsJobsPerformed)
For Each drJobPerformed As DataRow In dsJobsPerformed.Tables(0).Rows
Dim LviJobPerformed As ListViewItem = New ListViewItem(CStr(drJobPerformed("JobPerformed")))
LviJobPerformed.SubItems.Add(FormatNumber(CStr(drJobPerformed("JobPrice"))))
LvwJobsPerformed.Items.Add(LviJobPerformed)
Next
End Using
CalculateRepairOrder()
End Sub
End Class
- In the Object combo box, select BtnAddPart
- In the Procedure combo box, select Click
- Implement the event as follows:
Private Sub BtnAddPart_Click(sender As System.Object,
e As System.EventArgs) Handles BtnAddPart.Click
If String.IsNullOrEmpty(TxtPartName.Text) Then
Exit Sub
End If
Dim LviPart As New ListViewItem(TxtPartName.Text)
LviPart.SubItems.Add(FormatNumber(TxtUnitPrice.Text))
LviPart.SubItems.Add(TxtQuantity.Text)
LviPart.SubItems.Add(FormatNumber(TxtSubTotal.Text))
LvwPartsUsed.Items.Add(LviPart)
TxtPartName.Text = ""
TxtUnitPrice.Text = "0.00"
TxtQuantity.Text = "0"
TxtSubTotal.Text = "0.00"
CalculateRepairOrder()
End Sub
- In the Object combo box, select BtnAddJob
- In the Procedure combo box, select Click
- Implement the event as follows:
Private Sub BtnAddJob_Click(sender As System.Object,
e As System.EventArgs) Handles BtnAddJob.Click
If String.IsNullOrEmpty(TxtJobPerformed.Text) Then
Exit Sub
End If
Dim LviJob As New ListViewItem(TxtJobPerformed.Text)
LviJob.SubItems.Add(FormatNumber(TxtJobCost.Text))
LvwJobsPerformed.Items.Add(LviJob)
TxtJobPerformed.Text = ""
TxtJobCost.Text = "0.00"
CalculateRepairOrder()
End Sub
- In the Object combo box, select LvwPartsUsed
- In the Procedure combo box, select DoubleClick
- Implement the event as follows:
Private Sub LvwPartsUsed_DoubleClick(sender As Object,
e As System.EventArgs) Handles LvwPartsUsed.DoubleClick
' This code acts if the user double-clicks an item in the list of parts used.
' We conclude that either the user wants to edit the part
' or the user wants to delete the part that was added by mistake
Dim LviPartUsed As ListViewItem = LvwPartsUsed.SelectedItems(0)
TxtPartName.Text = LviPartUsed.SubItems(0).Text
TxtUnitPrice.Text = LviPartUsed.SubItems(1).Text
TxtQuantity.Text = LviPartUsed.SubItems(2).Text
TxtSubTotal.Text = LviPartUsed.SubItems(3).Text
' Remove the selected item from the list view
LviPartUsed.Remove()
End Sub
- In the Object combo box, select LvwJobsPerformed
- In the Procedure combo box, select Double-Click
- Implement the event as follows
Private Sub LvwJobsPerformed_DoubleClick(sender As Object,
e As System.EventArgs) Handles LvwJobsPerformed.DoubleClick
' This code acts if the user double-clicks a job
' from the list of jobs performed.
' We conclude that either the user wants to edit the job
' or the user wants to remove/cancel the job
Dim LviJobPerformed As ListViewItem = LvwJobsPerformed.SelectedItems(0)
TxtJobPerformed.Text = LviJobPerformed.SubItems(0).Text
TxtJobCost.Text = LviJobPerformed.SubItems(1).Text
' Remove the selected job from the list view
LviJobPerformed.Remove()
End Sub
- In the Object combo box, select BtnSubmit
- In the Procedure combo box, select Click
- Implement the event as follows:
Private Sub BtnSubmit_Click(sender As System.Object, e As System.EventArgs) Handles BtnSubmit.Click
Dim LviJob As ListViewItem
Dim LviPart As ListViewItem
Dim strServerName As String = "EXPRESSION"
Using cntCPAR As SqlConnection =
New SqlConnection("Data Source=" & strServerName & ";" &
"Database='CollegeParkAutoRepair1';" &
"Integrated Security=Yes")
Dim cmdCeilInn As SqlCommand =
New SqlCommand("UPDATE Repairs.Orders " &
"SET DateReceived = N'" & DtpDateReceived.Value.ToShortDateString() & "', " &
" TimeReceived = N'" & DtpTimeReceived.Value.ToShortTimeString() & "', " &
" DateCompleted = N'" & DtpDateCompleted.Value.ToShortDateString() & "', " &
" TimeCompleted = N'" & DtpTimeCompleted.Value.ToShortTimeString & "', " &
" DatePickedUp = N'" & DtpDatePickedUp.Value.ToShortDateString() & "', " &
" TimePickedUp = N'" & DtpTimePickedUp.Value.ToShortTimeString() & "', " &
" CustomerName = N'" & TxtCustomerName.Text & "', " &
" Address = N'" & TxtAddress.Text & "', " &
" City = N'" & TxtCity.Text & "', " &
" State = N'" & TxtState.Text & "', " &
" ZIPCode = N'" & TxtZIPCode.Text & "', " &
" Make = N'" & TxtMake.Text & "', " &
" Model = N'" & TxtModel.Text & "', " &
" CarYear = " & CInt(TxtCarYear.Text) & ", " &
" ProblemDescription = N'" & TxtProblemDescription.Text & "', " &
" TaxRate = " & CDbl(TxtTaxRate.Text) & ", " &
" Recommendations = N'" & TxtRecommendations.Text & "' " &
"WHERE ReceiptNumber = " & CInt(TxtReceiptNumber.Text) & ";",
cntCPAR)
cntCPAR.Open()
cmdCeilInn.ExecuteNonQuery()
End Using
Using cntCPAR As SqlConnection =
New SqlConnection("Data Source=" & strServerName & ";" &
"Database='CollegeParkAutoRepair1';" &
"Integrated Security=Yes")
Dim cmdCeilInn As SqlCommand =
New SqlCommand("DELETE Repairs.PartsUsed " &
"WHERE ReceiptNumber = " & CInt(TxtReceiptNumber.Text) & ";",
cntCPAR)
cntCPAR.Open()
cmdCeilInn.ExecuteNonQuery()
End Using
If LvwPartsUsed.Items.Count > 0 Then
For Each LviPart In LvwPartsUsed.Items
Using cntCPAR As SqlConnection =
New SqlConnection("Data Source=" & strServerName & ";" &
"Database='CollegeParkAutoRepair1';" &
"Integrated Security=Yes")
Dim cmdCeilInn As SqlCommand =
New SqlCommand("INSERT INTO Repairs.PartsUsed" &
"(ReceiptNumber, PartName, UnitPrice, Quantity) " &
"VALUES(" & CInt(TxtReceiptNumber.Text) & ", N'" &
LviPart.SubItems(0).Text & "', " & CDbl(LviPart.SubItems(1).Text) &
", " & CInt(LviPart.SubItems(2).Text) & ");",
cntCPAR)
cntCPAR.Open()
cmdCeilInn.ExecuteNonQuery()
End Using
Next
End If
Using cntCPAR As SqlConnection =
New SqlConnection("Data Source=" & strServerName & ";" &
"Database='CollegeParkAutoRepair1';" &
"Integrated Security=Yes")
Dim cmdCeilInn As SqlCommand =
New SqlCommand("DELETE Repairs.JobsPerformed " &
"WHERE ReceiptNumber = " & CInt(TxtReceiptNumber.Text) & ";",
cntCPAR)
cntCPAR.Open()
cmdCeilInn.ExecuteNonQuery()
End Using
If LvwJobsPerformed.Items.Count > 0 Then
For Each LviJob In LvwJobsPerformed.Items
Using cntCPAR As SqlConnection =
New SqlConnection("Data Source=" & strServerName & ";" &
"Database='CollegeParkAutoRepair1';" &
"Integrated Security=Yes")
Dim cmdCeilInn As SqlCommand =
New SqlCommand("INSERT INTO Repairs.JobsPerformed" &
"(ReceiptNumber, JobPerformed, JobPrice) " &
"VALUES(" & CInt(TxtReceiptNumber.Text) & ", N'" &
LviJob.SubItems(0).Text & "', N'" &
LviJob.SubItems(1).Text & "');",
cntCPAR)
cntCPAR.Open()
cmdCeilInn.ExecuteNonQuery()
End Using
Next
End If
MsgBox("The repair order has been updated.",
MsgBoxStyle.OkOnly Or MsgBoxStyle.Information,
"College Park Auto Repair")
ResetRepairOrder()
End Sub
- In the Object combo box, select BtnClose
- In the Procedure combo boxl select Click
- Implement the event as follows:
Private Sub BtnClose_Click(sender As Object,
e As System.EventArgs) Handles BtnClose.Click
Close()
End Sub
- Access the main form
- Design it as follows:
 |
| Control |
(Name) |
Text |
| Button |
|
btnNewRepairOrder |
New Repair Order... |
| Button |
|
btnUpdateRepairOrder |
Update Repair Order... |
| Button |
|
btnClose |
Close |
|
- Double-click the New Repair button
- In the Procedure combo box, select Click
- Implement its event as follows:
Private Sub CollegeParkAutoRepair_Load(sender As System.Object,
e As System.EventArgs) Handles MyBase.Load
' CreateJobsPerformed()
End Sub
Private Sub BtnNewRepairOrder_Click(sender As System.Object,
e As System.EventArgs) Handles BtnNewRepairOrder.Click
Dim Nro As NewRepairOrder
Nro = New NewRepairOrder
Nro.ShowDialog()
End Sub
- In the Object combo box, select BtnUpdateRepairOrder
- Im the Procedure combo box, select Click
- Implement their event as follows:
Private Sub BtnUpdateRepairOrder_Click(sender As Object,
e As System.EventArgs) Handles BtnUpdateRepairOrder.Click
Dim Uro As UpdateRepairOrder
Uro = New UpdateRepairOrder
Uro.ShowDialog()
End Sub
- In
the Object combo box, select BtnClose
- In the Procedure combo box, select Click
- Implement the event as follows:
Private Sub BtnClose_Click(sender As Object,
e As System.EventArgs) Handles BtnClose.Click
Close()
End Sub
- Execute the application
- Click the New Repair Order button Create a repair order:
- Click Submit
- Create another repair order
- Click Submit
- Close the forms
- Execute the application again
- Update an existing repair
- Change some values in the record. Here is an example:
- Close the forms
|
|