Ado.

Net – Data Binding

College Park Auto Repair

This is sample application that shows how to create a simple database. This application has two
goals. It shows how to programmatically create a Microsoft SQL Server database from a
Page | 1
Windows Forms application. Another goal is to perform straight-forward data entry in SQL, also
done from a graphical application.

The application is used by a fictitious company used to process car repair orders for customers
who bring their cars to the shop. The application mostly uses only text boxes and buttons.

Practical Learning: Using a Data Reader

1. Start Microsoft Visual Basic and create a new Windows Application named
CollegeParkAutoRepair3
2. In the Solution Explorer, right-click Form1.vb and click Rename
3. Type RepairOrders.vb and press Enter twice
4. Design the form as follows:
Ado.Net – Data Binding

Page | 2

Control Name Text Additional Properties

Order
GroupBox
Identification

Label Customer Name:

TextBox TxtCustomerName

Label Address:

TextBox TxtAddress

Label City:

TextBox TxtCity

Label State:

TextBox TxtState

Label ZIP Code:

Ado.Net – Data Binding

TextBox TxtZIPCode

Label Make/Model:

TextBox TxtMake
Page | 3
TextBox TxtModel

Label Year:

TextBox TxtYear

Problem
Label
Description:
Scrollbars: Vertical
TextBox TxtProblemDescription
Multiline: True

GroupBox Parts Used

Label Part Name

Label Unit Price

Label Qty

Label Sub Total

TextBox TxtPart1Name

TextBox TxtUnitPrice1 0.00 TextAlign: Right

TextBox TxtQuantity1 0 TextAlign: Right

TextBox TxtSubTotal1 0.00 TextAlign: Right

TextBox TxtPart2Name

TextBox TxtUnitPrice2 0.00 TextAlign: Right

TextBox TxtQuantity2 0 TextAlign: Right

TextBox TxtSubTotal2 0.00 TextAlign: Right

TextBox TxtPart3Name

TextBox TxtUnitPrice3 0.00 TextAlign: Right

TextBox TxtQuantity3 0 TextAlign: Right

Ado.Net – Data Binding

TextBox TxtSubTotal3 0.00 TextAlign: Right

TextBox TxtPart4Name

TextBox TxtUnitPrice4 0.00 TextAlign: Right

Page | 4
TextBox TxtQuantity4 0 TextAlign: Right

TextBox TxtSubTotal4 0.00 TextAlign: Right

TextBox TxtPart5Name

TextBox TxtUnitPrice5 0.00 TextAlign: Right

TextBox TxtQuantity5 0 TextAlign: Right

TextBox TxtSubTotal5 0.00 TextAlign: Right

GroupBox Jobs Performed

Label Job Description

Label Price

TextBox TxtJobDescription1

TextBox TxtJobPrice1 0.00 TextAlign: Right

TextBox TxtJobDescription2

TextBox TxtJobPrice2 0.00 TextAlign: Right

TextBox TxtJobDescription3

TextBox TxtJobPrice3 0.00 TextAlign: Right

TextBox TxtJobDescription4

TextBox TxtJobPrice4 0.00 TextAlign: Right

TextBox TxtJobDescription5

TextBox TxtJobPrice5 0.00 TextAlign: Right

GroupBox Order Summary

Label Total Parts:

TextBox TxtTotalParts 0.00 TextAlign: Right

Ado.Net – Data Binding

Label Total Labor:

TextBox TxtTotalLabor 0.00 TextAlign: Right

Label Tax Rate:

Page | 5
TextBox TxtTaxRate 7.75 TextAlign: Right

Label %

Label Tax Amount:

TextBox TxtTaxAmount 0.00 TextAlign: Right

Label Total Order:

TextBox TxtTotalOrder 0.00 TextAlign: Right

Label Recommendations:

Scrollbars: Vertical
TextBox TxtRecommendations
Multiline: True

Button BtnSave Save

Label Receipt #:

TextBox TxtReceiptNumber

Button BtnOpen Open

Button BtnNewRepairOrder New Repair Order

Button BtnClose Close

5. Right-click the form and click View Code

6. Just above the Public Class line, import the System.Data.SqlClient namespace
7. In the Class Name combo box, select (RepairOrders Events)
8. In the Method Name combo box, select Load and implement the event as follows:

Imports System.Data.SqlClient

Public Class RepairOrders

Friend Sub CreateDatabase()

Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local); " & _
"Integrated Security='SSPI';")
Ado.Net – Data Binding

Dim strCreateDatabase As String = "IF EXISTS (" & _

"SELECT * " & _
" FROM sys.databases " & _
" WHERE name = N'CollegeParkAutoRepair1' " & _
")" & _
"DROP DATABASE CollegeParkAutoRepair1;" & _
"CREATE DATABASE CollegeParkAutoRepair1;" Page | 6

Dim Command As SqlCommand = _

New SqlCommand(strCreateDatabase, _
Connect)

Connect.Open()
Command.ExecuteNonQuery()

MsgBox("A database named " & _

"CollegeParkAutoRepair1 has been created")
End Using

Using Connect As SqlConnection = _

New SqlConnection("Data Source=(local); " & _
"Database='CollegeParkAutoRepair1'; " & _
"Integrated Security='SSPI';")

Dim strCreateTable As String = _

"CREATE TABLE RepairOrders( " & _
"RepairOrderID int identity(100001, 1) NOT NULL, " & _
"CustomerName varchar(80) NOT NULL, " & _
"Address varchar(100) NOT NULL, " & _
"City varchar(50), State varchar(50), " & _
"ZIPCode varchar(50), CarMake varchar(50), " & _
"CarModel varchar(50), CarYear smallint, " & _
"ProblemDescription text, Part1Name varchar(80), " & _
"Part1UnitPrice money, " & _
"Part1Quantity tinyint, " & _
"Part1SubTotal money, " & _
"Part2Name varchar(80), " & _
"Part2UnitPrice money, " & _
"Part2Quantity tinyint, " & _
"Part2SubTotal money, " & _
"Part3Name varchar(80), " & _
"Part3UnitPrice money, " & _
"Part3Quantity tinyint, " & _
"Part3SubTotal money, " & _
"Part4Name varchar(80), " & _
"Part4UnitPrice money, " & _
"Part4Quantity tinyint, " & _
"Part4SubTotal money, " & _
"Part5Name varchar(80), " & _
"Part5UnitPrice money, " & _
"Part5Quantity tinyint, " & _
"Part5SubTotal money, " & _
"Job1Description varchar(80), " & _
"Job1Price money, " & _
"Job2Description varchar(80), " & _
"Job2Price money, " & _
Ado.Net – Data Binding

"Job3Description varchar(80), " & _

"Job3Price money, " & _
"Job4Description varchar(80), " & _
"Job4Price money, " & _
"Job5Description varchar(80), " & _
"Job5Price money, " & _
"TotalParts money, " & _ Page | 7
"TotalLabor money, " & _
"TaxRate decimal(6,2), " & _
"TaxAmount money, " & _
"TotalOrder money, " & _
"Recommendations text, " & _
"CONSTRAINT PK_RepairOrders PRIMARY KEY (RepairOrderID));"

Dim Command As SqlCommand = _

New SqlCommand(strCreateTable, Connect)

Connect.Open()
Command.ExecuteNonQuery()
MsgBox("A table named RepairOrders has been created")
End Using
End Sub

Private Sub RepairOrders_Load(ByVal sender As Object, _

ByVal e As System.EventArgs) _
Handles Me.Load
CreateDatabase()
End Sub
End Class

9. Execute the application to create the database and its table

10. Close the form and return to your programming environment

11. In the Class Name combo box, select BtnNewRepairOrder
12. In the Method Name combo box, select Click and implement the event as follows:

Private Sub BtnNewRepairOrder_Click(ByVal sender As Object, _

ByVal e As System.EventArgs) _
Ado.Net – Data Binding

Handles BtnNewRepairOrder.Click
' This code is used to reset the form
TxtReceiptNumber.Text = "" : TxtCustomerName.Text = ""
TxtAddress.Text = "" : TxtCity.Text = ""
TxtState.Text = "" : TxtZIPCode.Text = ""

TxtMake.Text = "" : TxtModel.Text = "" Page | 8

TxtCarYear.Text = "" : TxtProblemDescription.Text = ""

TxtPart1Name.Text = "" : TxtUnitPrice1.Text = "0.00"

TxtQuantity1.Text = "0" : TxtSubTotal1.Text = "0.00"

TxtPart2Name.Text = "" : TxtUnitPrice2.Text = "0.00"

TxtQuantity2.Text = "0" : TxtSubTotal2.Text = "0.00"

TxtPart3Name.Text = "" : TxtUnitPrice3.Text = "0.00"

TxtQuantity3.Text = "0" : TxtSubTotal3.Text = "0.00"

TxtPart4Name.Text = "" : TxtUnitPrice4.Text = "0.00"

TxtQuantity4.Text = "0" : TxtSubTotal4.Text = "0.00"

TxtPart5Name.Text = "" : TxtUnitPrice5.Text = "0.00"

TxtQuantity5.Text = "0" : TxtSubTotal5.Text = "0.00"

TxtJobDescription1.Text = "" : TxtJobPrice1.Text = "0.00"

TxtJobDescription2.Text = "" : TxtJobPrice2.Text = "0.00"
TxtJobDescription3.Text = "" : TxtJobPrice3.Text = "0.00"
TxtJobDescription4.Text = "" : TxtJobPrice4.Text = "0.00"
TxtJobDescription5.Text = "" : TxtJobPrice5.Text = "0.00"

TxtRecommendations.Text = "" : TxtTotalParts.Text = "0.00"

TxtTotalLabor.Text = "0.00" : TxtTaxRate.Text = "7.75"
TxtTaxAmount.Text = "0.00" : TxtTotalLabor.Text = "0.00"

TxtCustomerName.Focus()
End Sub

13. Change to Load event as follows:

Private Sub RepairOrders_Load(ByVal sender As Object, _

ByVal e As System.EventArgs) _
Handles Me.Load
BtnNewRepairOrder_Click(sender, e)
End Sub

14. Under the above End Sub line, create the following procedure:

Friend Sub CalculateOrder()

Dim UnitPrice1 As Double, UnitPrice2 As Double
Dim UnitPrice3 As Double, UnitPrice4 As Double
Dim UnitPrice5 As Double
Dim SubTotal1 As Double, SubTotal2 As Double
Dim SubTotal3 As Double, SubTotal4 As Double, SubTotal5
Dim TotalParts As Double
Dim Quantity1 As Integer, Quantity2 As Integer
Ado.Net – Data Binding

Dim Quantity3 As Integer, Quantity4 As Integer

Dim Quantity5 As Integer
Dim JobPrice1 As Double, JobPrice2 As Double
Dim JobPrice3 As Double, JobPrice4 As Double
Dim JobPrice5 As Double, TotalLabor As Double
Dim TaxAmount As Double, TotalOrder As Double
Dim TaxRate As Double Page | 9

' Don't charge a part unless it is clearly identified

If TxtPart1Name.Text = "" Then
TxtUnitPrice1.Text = "0.00"
TxtQuantity1.Text = "0"
TxtSubTotal1.Text = "0.00"
UnitPrice1 = 0.0
Else
Try
UnitPrice1 = CDbl(TxtUnitPrice1.Text)
Catch ex As Exception
MsgBox("Invalid Unit Price")
TxtUnitPrice1.Text = "0.00"
TxtUnitPrice1.Focus()
End Try

Try
Quantity1 = CInt(TxtQuantity1.Text)
Catch ex As Exception
MsgBox("Invalid Quantity")
TxtQuantity1.Text = "0"
TxtQuantity1.Focus()
End Try
End If

If TxtPart2Name.Text = "" Then

TxtUnitPrice2.Text = "0.00"
TxtQuantity2.Text = "0"
TxtSubTotal2.Text = "0.00"
UnitPrice2 = 0.0
Else
Try
UnitPrice2 = CDbl(TxtUnitPrice2.Text)
Catch ex As Exception
MsgBox("Invalid Unit Price")
TxtUnitPrice2.Text = "0.00"
TxtUnitPrice2.Focus()
End Try

Try
Quantity2 = CInt(TxtQuantity2.Text)
Catch ex As Exception
MsgBox("Invalid Quantity")
TxtQuantity2.Text = "0"
TxtQuantity2.Focus()
End Try
End If

If TxtPart3Name.Text = "" Then

Ado.Net – Data Binding

TxtUnitPrice3.Text = "0.00"
TxtQuantity3.Text = "0"
TxtSubTotal3.Text = "0.00"
UnitPrice3 = 0.0
Else
Try
UnitPrice3 = CDbl(TxtUnitPrice3.Text) Page | 10
Catch ex As Exception
MsgBox("Invalid Unit Price")
TxtUnitPrice3.Text = "0.00"
TxtUnitPrice3.Focus()
End Try

Try
Quantity3 = CInt(TxtQuantity3.Text)
Catch ex As Exception
MsgBox("Invalid Quantity")
TxtQuantity3.Text = "0"
TxtQuantity3.Focus()
End Try
End If

If TxtPart4Name.Text = "" Then

TxtUnitPrice4.Text = "0.00"
TxtQuantity4.Text = "0"
TxtSubTotal4.Text = "0.00"
UnitPrice4 = 0.0
Else
Try
UnitPrice4 = CDbl(TxtUnitPrice4.Text)
Catch ex As Exception
MsgBox("Invalid Unit Price")
TxtUnitPrice4.Text = "0.00"
TxtUnitPrice4.Focus()
End Try

Try
Quantity4 = CInt(TxtQuantity4.Text)
Catch ex As Exception
MsgBox("Invalid Quantity")
TxtQuantity4.Text = "0"
TxtQuantity4.Focus()
End Try
End If

If TxtPart5Name.Text = "" Then

TxtUnitPrice5.Text = "0.00"
TxtQuantity5.Text = "0"
TxtSubTotal5.Text = "0.00"
UnitPrice5 = 0.0
Else
Try
UnitPrice5 = CDbl(TxtUnitPrice5.Text)
Catch ex As Exception
MsgBox("Invalid Unit Price")
TxtUnitPrice5.Text = "0.00"
Ado.Net – Data Binding

TxtUnitPrice5.Focus()
End Try

Try
Quantity5 = CInt(TxtQuantity5.Text)
Catch ex As Exception
MsgBox("Invalid Quantity") Page | 11
TxtQuantity5.Text = "0"
TxtQuantity5.Focus()
End Try
End If

' Don't bill the customer for a job that is not specified
If TxtJobDescription1.Text = "" Then
TxtJobPrice1.Text = "0.00"
JobPrice1 = 0.0
Else
Try
JobPrice1 = CDbl(TxtJobPrice1.Text)
Catch ex As Exception

MsgBox("Invalid Job Price")

TxtJobPrice1.Text = "0.00"
TxtJobPrice1.Focus()
End Try
End If

If TxtJobDescription2.Text = "" Then

TxtJobPrice2.Text = "0.00"
JobPrice2 = 0.0
Else
Try
JobPrice2 = CDbl(TxtJobPrice2.Text)
Catch ex As Exception
MsgBox("Invalid Job Price")
TxtJobPrice2.Text = "0.00"
TxtJobPrice2.Focus()
End Try
End If

If TxtJobDescription3.Text = "" Then

TxtJobPrice3.Text = "0.00"
JobPrice3 = 0.0
Else
Try
JobPrice3 = CDbl(TxtJobPrice3.Text)
Catch ex As Exception
MsgBox("Invalid Job Price")
TxtJobPrice3.Text = "0.00"
TxtJobPrice3.Focus()
End Try
End If

If TxtJobDescription4.Text = "" Then

TxtJobPrice4.Text = "0.00"
JobPrice4 = 0.0
Ado.Net – Data Binding

Else
Try
JobPrice4 = CDbl(TxtJobPrice4.Text)
Catch ex As Exception
MsgBox("Invalid Job Price")
TxtJobPrice4.Text = "0.00"
TxtJobPrice4.Focus() Page | 12
End Try
End If

If TxtJobDescription5.Text = "" Then

TxtJobPrice5.Text = "0.00"
JobPrice5 = 0.0
Else

Try
JobPrice5 = CDbl(TxtJobPrice5.Text)
Catch ex As Exception
MsgBox("Invalid Job Price")
TxtJobPrice5.Text = "0.00"
TxtJobPrice5.Focus()
End Try
End If

SubTotal1 = UnitPrice1 * Quantity1

SubTotal2 = UnitPrice2 * Quantity2
SubTotal3 = UnitPrice3 * Quantity3
SubTotal4 = UnitPrice4 * Quantity4
SubTotal5 = UnitPrice5 * Quantity5

TxtSubTotal1.Text = FormatCurrency(SubTotal1)
TxtSubTotal2.Text = FormatCurrency(SubTotal2)
TxtSubTotal3.Text = FormatCurrency(SubTotal3)
TxtSubTotal4.Text = FormatCurrency(SubTotal4)
TxtSubTotal5.Text = FormatCurrency(SubTotal5)

TotalParts = SubTotal1 + SubTotal2 + SubTotal3 + _

SubTotal4 + SubTotal5

TotalLabor = JobPrice1 + JobPrice2 + JobPrice3 + _

JobPrice4 + JobPrice5

Try
TaxRate = CDbl(TxtTaxRate.Text)
Catch ex As Exception
MsgBox("Invalid Tax Rate")
TxtTaxRate.Text = "7.75"
TxtTaxRate.Focus()
End Try

Dim TotalPartsAndLabor As Double = TotalParts + TotalLabor

TaxAmount = TotalPartsAndLabor * TaxRate / 100
TotalOrder = TotalPartsAndLabor + TaxAmount

TxtTotalParts.Text = FormatCurrency(TotalParts)
TxtTotalLabor.Text = FormatCurrency(TotalLabor)
Ado.Net – Data Binding

TxtTaxAmount.Text = FormatCurrency(TaxAmount)
TxtTotalOrder.Text = FormatCurrency(TotalOrder)
End Sub

15. Under the above End Sub line, implement the following event:

Page | 13
Private Sub ControlLeave(ByVal sender As Object, _
ByVal e As EventArgs) _
Handles TxtUnitPrice1.Leave, _
TxtUnitPrice2.Leave, _
TxtUnitPrice3.Leave, _
TxtUnitPrice4.Leave, _
TxtUnitPrice5.Leave, _
TxtQuantity1.Leave, _
TxtQuantity2.Leave, _
TxtQuantity3.Leave, _
TxtQuantity4.Leave, _
TxtQuantity5.Leave, _
TxtJobPrice1.Leave, _
TxtJobPrice2.Leave, _
TxtJobPrice3.Leave, _
TxtJobPrice4.Leave, _
TxtJobPrice5.Leave, _
TxtTaxRate.Leave
' When one of the above controls looses focus, (re)calculate the
order
CalculateOrder()
End Sub

16. In the Class Name combo box, select BtnSave

17. In the Method Name combo box, select Click and implement the event as follows:

Private Sub BtnSave_Click(ByVal sender As Object, _

ByVal e As System.EventArgs) _
Handles BtnSave.Click
Dim strCommand As String = ""

' Connect to the database on the server

Using Connect As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Database='CollegeParkAutoRepair1';" & _
"Integrated Security=SSPI;")

' If the Receipt Number text box is empty, it appears that

' the user/clerk wants to create a new cleaning order
If TxtReceiptNumber.Text = "" Then
strCommand = "INSERT INTO RepairOrders( " & _
"CustomerName, Address, City, " & _
"State, ZIPCode, CarMake, " & _
"CarModel, CarYear, ProblemDescription, " & _
"Part1Name, Part1UnitPrice, Part1Quantity, " & _
"Part1SubTotal, Part2Name, Part2UnitPrice, " & _
"Part2Quantity, Part2SubTotal, Part3Name, " & _
"Part3UnitPrice, Part3Quantity, Part3SubTotal, " & _
Ado.Net – Data Binding

"Part4Name, Part4UnitPrice, Part4Quantity, " & _

"Part4SubTotal, Part5Name, Part5UnitPrice, " & _
"Part5Quantity, Part5SubTotal, Job1Description, " & _
"Job1Price, Job2Description, Job2Price, " & _
"Job3Description, Job3Price, Job4Description, " & _
"Job4Price, Job5Description, Job5Price, " & _
"Recommendations, TotalParts, TotalLabor, " & _ Page | 14
"TaxRate, TaxAmount, TotalOrder) " & _
"VALUES('" & TxtCustomerName.Text & "', '" & _
TxtAddress.Text & "', '" & TxtCity.Text & _
"', '" & TxtState.Text & "', '" & _
TxtZIPCode.Text & "', '" & TxtMake.Text & _
"', '" & TxtModel.Text & "', '" & _
TxtCarYear.Text & "', '" & _
TxtProblemDescription.Text & "', '" & _
TxtPart1Name.Text & "', '" & TxtUnitPrice1.Text & "', '" & _
TxtQuantity1.Text & "', '" & TxtSubTotal1.Text & "', '" & _
TxtPart2Name.Text & "', '" & TxtUnitPrice2.Text & "', '" & _
TxtQuantity2.Text & "', '" & TxtSubTotal2.Text & "', '" & _
TxtPart3Name.Text & "', '" & TxtUnitPrice3.Text & "', '" & _
TxtQuantity3.Text & "', '" & TxtSubTotal3.Text & "', '" & _
TxtPart4Name.Text & "', '" & TxtUnitPrice4.Text & "', '" & _
TxtQuantity4.Text & "', '" & TxtSubTotal4.Text & "', '" & _
TxtPart5Name.Text & "', '" & TxtUnitPrice5.Text & "', '" & _
TxtQuantity5.Text & "', '" & TxtSubTotal5.Text & "', '" & _
TxtJobDescription1.Text & "', '" & TxtJobPrice1.Text & "', '" & _
TxtJobDescription2.Text & "', '" & TxtJobPrice2.Text & "', '" & _
TxtJobDescription3.Text & "', '" & TxtJobPrice3.Text & "', '" & _
TxtJobDescription4.Text & "', '" & TxtJobPrice4.Text & "', '" & _
TxtJobDescription5.Text & "', '" & TxtJobPrice5.Text & "', '" & _
TxtRecommendations.Text & "', '" & TxtTotalParts.Text & "', '" & _
TxtTotalLabor.Text & "', '" & TxtTaxRate.Text & "', '" & _
TxtTaxAmount.Text & "', '" & TxtTotalOrder.Text & "');"
Else ' Since there is a receipt number, update/edit the cleaning order
strCommand = "UPDATE CleaningOrders " & _
"SET CustomerName = '" & TxtCustomerName.Text & "', " & _
" Address = '" & TxtAddress.Text & "', " & _
" City = '" & TxtCity.Text & "', " & _
" State = '" & TxtState.Text & "', " & _
" ZIPCode = '" & TxtZIPCode.Text & "', " & _
" CarMake = '" & TxtMake.Text & "', " & _
" CarModel = '" & TxtModel.Text & "', " & _
" CarYear = '" & TxtCarYear.Text & "', " & _
" ProblemDescription = '" & TxtProblemDescription.Text & "', " & _
" Part1Name = '" & TxtPart1Name.Text & "', " & _
" Part1UnitPrice = '" & TxtUnitPrice1.Text & "', " & _
" Part1Quantity = '" & TxtQuantity1.Text & "', " & _
" Part1SubTotal = '" & TxtSubTotal1.Text & "', " & _
" Part2Name = '" & TxtPart2Name.Text & "', " & _
" Part2UnitPrice = '" & TxtUnitPrice2.Text & "', " & _
" Part2Quantity = '" & TxtQuantity2.Text & "', " & _
" Part2SubTotal = '" & TxtSubTotal2.Text & "', " & _
" Part3Name = '" & TxtPart3Name.Text & "', " & _
" Part3UnitPrice = '" & TxtUnitPrice3.Text & "', " & _
" Part3Quantity = '" & TxtQuantity3.Text & "', " & _
" Part3SubTotal = '" & TxtSubTotal3.Text & "', " & _
Ado.Net – Data Binding

" Part4Name = '" & TxtPart4Name.Text & "', " & _

" Part4UnitPrice = '" & TxtUnitPrice4.Text & "', " & _
" Part4Quantity = '" & TxtQuantity4.Text & "', " & _
" Part4SubTotal = '" & TxtSubTotal4.Text & "', " & _
" Part5Name = '" & TxtPart5Name.Text & "', " & _
" Part5UnitPrice = '" & TxtUnitPrice5.Text & "', " & _
" Part5Quantity = '" & TxtQuantity5.Text & "', " & _ Page | 15
" Part5SubTotal = '" & TxtSubTotal5.Text & "', " & _
" Job1Description = '" & TxtJobDescription1.Text & "', " & _
" Job1Price = '" & TxtJobDescription1.Text & "', " & _
" Job2Description = '" & TxtJobDescription2.Text & "', " & _
" Job2Price = '" & TxtJobDescription2.Text & "', " & _
" Job3Description = '" & TxtJobDescription3.Text & "', " & _
" Job3Price = '" & TxtJobDescription3.Text & "', " & _
" Job4Description = '" & TxtJobDescription4.Text & "', " & _
" Job4Price = '" & TxtJobDescription4.Text & "', " & _
" Job5Description = '" & TxtJobDescription5.Text & "', " & _
" Job5Price = '" & TxtJobDescription5.Text & "', " & _
" TotalParts = '" & TxtTotalParts.Text & "', " & _
" TotalLabor = '" & TxtTotalLabor.Text & "', " & _
" TaxRate = '" & TxtTaxRate.Text & "', " & _
" TaxAmount = '" & TxtTaxAmount.Text & "', " & _
" TotalOrder = '" & TxtTotalOrder.Text & "' " & _
" WHERE RepairOrderID = '" & TxtReceiptNumber.Text & "';"
End If

Dim cmdCleaningOrders As SqlCommand = _

New SqlCommand(strCommand, _
Connect)

Connect.Open()
cmdCleaningOrders.ExecuteNonQuery()
MsgBox("The record has been saved")
BtnNewRepairOrder_Click(sender, e)
End Using
End Sub

18. In the Class Name combo, box, select BtnOpen

19. In the Method Name combo box, select Click and implement the event as follows:

Private Sub BtnOpen_Click(ByVal sender As Object, _

ByVal e As System.EventArgs) _
Handles BtnOpen.Click
Dim strReceiptNumber As String = TxtReceiptNumber.Text

If strReceiptNumber.Length = 0 Then
MsgBox("You open a repair order, " & _
"enter its receipt number and click Open.")
Exit Sub
End If

Using Connect As SqlConnection = _

New SqlConnection("Data Source=(local);" & _
"Database='CollegeParkAutoRepair1';" &
_
Ado.Net – Data Binding

"Integrated Security=yes")

Dim strFindRepair As String = _

"SELECT * FROM RepairOrders WHERE RepairOrderID = '"
& _
strReceiptNumber & "'"
Dim cmdDatabase As SqlCommand = _ Page | 16
New SqlCommand(strFindRepair, Connect)

Connect.Open()

Dim rdrRepairOrder As SqlDataReader

rdrRepairOrder = cmdDatabase.ExecuteReader()

While rdrRepairOrder.Read()
TxtCustomerName.Text = rdrRepairOrder.GetString(1)
TxtAddress.Text = rdrRepairOrder.GetString(2)
TxtCity.Text = rdrRepairOrder.GetString(3)
TxtState.Text = rdrRepairOrder.GetString(4)
TxtZIPCode.Text = rdrRepairOrder.GetString(5)
TxtMake.Text = rdrRepairOrder.GetString(6)
TxtModel.Text = rdrRepairOrder.GetString(7)
TxtCarYear.Text = rdrRepairOrder.GetSqlInt16(8).ToString()
TxtProblemDescription.Text = rdrRepairOrder.GetString(9)

TxtPart1Name.Text = rdrRepairOrder.GetString(10)
TxtUnitPrice1.Text = rdrRepairOrder.GetSqlMoney(11).ToString()
TxtQuantity1.Text = rdrRepairOrder.GetSqlByte(12).ToString()
TxtSubTotal1.Text = rdrRepairOrder.GetSqlMoney(13).ToString()

TxtPart2Name.Text = rdrRepairOrder.GetString(14)
TxtUnitPrice2.Text = rdrRepairOrder.GetSqlMoney(15).ToString()
TxtQuantity2.Text = rdrRepairOrder.GetSqlByte(16).ToString()
TxtSubTotal2.Text = rdrRepairOrder.GetSqlMoney(17).ToString()

TxtPart3Name.Text = rdrRepairOrder.GetString(18)
TxtUnitPrice3.Text = rdrRepairOrder.GetSqlMoney(19).ToString()
TxtQuantity3.Text = rdrRepairOrder.GetSqlByte(20).ToString()
TxtSubTotal3.Text = rdrRepairOrder.GetSqlMoney(21).ToString()

TxtPart4Name.Text = rdrRepairOrder.GetString(22)
TxtUnitPrice4.Text = rdrRepairOrder.GetSqlMoney(23).ToString()
TxtQuantity4.Text = rdrRepairOrder.GetSqlByte(24).ToString()
TxtSubTotal4.Text = rdrRepairOrder.GetSqlMoney(25).ToString()

TxtPart5Name.Text = rdrRepairOrder.GetString(26)
TxtUnitPrice5.Text = rdrRepairOrder.GetSqlMoney(27).ToString()
TxtQuantity5.Text = rdrRepairOrder.GetSqlByte(28).ToString()
TxtSubTotal5.Text = rdrRepairOrder.GetSqlMoney(29).ToString()

TxtJobDescription1.Text = rdrRepairOrder.GetString(30)
TxtJobPrice1.Text = rdrRepairOrder.GetSqlMoney(31).ToString()
TxtJobDescription2.Text = rdrRepairOrder.GetString(32)
TxtJobPrice2.Text = rdrRepairOrder.GetSqlMoney(33).ToString()
TxtJobDescription3.Text = rdrRepairOrder.GetString(34)
TxtJobPrice3.Text = rdrRepairOrder.GetSqlMoney(35).ToString()
Ado.Net – Data Binding

TxtJobDescription4.Text = rdrRepairOrder.GetString(36)
TxtJobPrice4.Text = rdrRepairOrder.GetSqlMoney(37).ToString()
TxtJobDescription5.Text = rdrRepairOrder.GetString(38)
TxtJobPrice5.Text = rdrRepairOrder.GetSqlMoney(39).ToString()

TxtTotalParts.Text = rdrRepairOrder.GetSqlMoney(40).ToString()
TxtTotalLabor.Text = rdrRepairOrder.GetSqlMoney(41).ToString() Page | 17
TxtTaxRate.Text = rdrRepairOrder.GetSqlDecimal(42).ToString()
TxtTaxAmount.Text = rdrRepairOrder.GetSqlMoney(43).ToString()
TxtTotalOrder.Text = rdrRepairOrder.GetSqlMoney(44).ToString()

TxtRecommendations.Text = rdrRepairOrder.GetString(45)
End While

rdrRepairOrder.Close()
End Using
End Sub

20. In the Class Name combo box, select BtnClose

21. In the Method Name combo box, select Click and implement the event as follows:

Private Sub BtnClose_Click(ByVal sender As Object, _

ByVal e As System.EventArgs) _
Handles BtnClose.Click
End
End Sub

22. Execute the application

23. Create a few repair orders. Here are two examples:
Ado.Net – Data Binding

Page | 18

24. Close the form and return to your programming environment

25. Execute the application again and open a few previously saved cleaning orders