Home

Database Example Application:
College Park Auto Repair

 

Introduction

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 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 LearningPractical 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:
     
    College Park Auto Repair
     
    Control Name Text Additional Properties
    GroupBox GroupBox   Order Identification  
    Label Label   Customer Name:  
    TextBox TextBox TxtCustomerName    
    Label Label   Address:  
    TextBox TextBox TxtAddress    
    Label Label   City:  
    TextBox TextBox TxtCity    
    Label Label   State:  
    TextBox TextBox TxtState    
    Label Label   ZIP Code:  
    TextBox TextBox TxtZIPCode    
    Label Label   Make/Model:  
    TextBox TextBox TxtMake    
    TextBox TextBox TxtModel    
    Label Label   Year:  
    TextBox TextBox TxtYear    
    Label Label   Problem Description:  
    TextBox TextBox TxtProblemDescription   Scrollbars: Vertical
    Multiline: True
    GroupBox GroupBox   Parts Used  
    Label Label   Part Name  
    Label Label   Unit Price  
    Label Label   Qty  
    Label Label   Sub Total  
    TextBox TextBox TxtPart1Name    
    TextBox TextBox TxtUnitPrice1 0.00 TextAlign: Right
    TextBox TextBox TxtQuantity1 0 TextAlign: Right
    TextBox TextBox TxtSubTotal1 0.00 TextAlign: Right
    TextBox TextBox TxtPart2Name    
    TextBox TextBox TxtUnitPrice2 0.00 TextAlign: Right
    TextBox TextBox TxtQuantity2 0 TextAlign: Right
    TextBox TextBox TxtSubTotal2 0.00 TextAlign: Right
    TextBox TextBox TxtPart3Name    
    TextBox TextBox TxtUnitPrice3 0.00 TextAlign: Right
    TextBox TextBox TxtQuantity3 0 TextAlign: Right
    TextBox TextBox TxtSubTotal3 0.00 TextAlign: Right
    TextBox TextBox TxtPart4Name    
    TextBox TextBox TxtUnitPrice4 0.00 TextAlign: Right
    TextBox TextBox TxtQuantity4 0 TextAlign: Right
    TextBox TextBox TxtSubTotal4 0.00 TextAlign: Right
    TextBox TextBox TxtPart5Name    
    TextBox TextBox TxtUnitPrice5 0.00 TextAlign: Right
    TextBox TextBox TxtQuantity5 0 TextAlign: Right
    TextBox TextBox TxtSubTotal5 0.00 TextAlign: Right
    GroupBox GroupBox   Jobs Performed  
    Label Label   Job Description  
    Label Label   Price  
    TextBox TextBox TxtJobDescription1    
    TextBox TextBox TxtJobPrice1 0.00 TextAlign: Right
    TextBox TextBox TxtJobDescription2    
    TextBox TextBox TxtJobPrice2 0.00 TextAlign: Right
    TextBox TextBox TxtJobDescription3    
    TextBox TextBox TxtJobPrice3 0.00 TextAlign: Right
    TextBox TextBox TxtJobDescription4    
    TextBox TextBox TxtJobPrice4 0.00 TextAlign: Right
    TextBox TextBox TxtJobDescription5    
    TextBox TextBox TxtJobPrice5 0.00 TextAlign: Right
    GroupBox GroupBox   Order Summary   
    Label  Label   Total Parts:   
    TextBox TextBox TxtTotalParts 0.00 TextAlign: Right
    Label   Label   Total Labor:   
    TextBox TextBox TxtTotalLabor 0.00 TextAlign: Right
    Label Label   Tax Rate:  
    TextBox TextBox TxtTaxRate 7.75 TextAlign: Right
    Label Label   %  
    Label Label   Tax Amount:  
    TextBox TextBox TxtTaxAmount 0.00 TextAlign: Right
    Label Label   Total Order:  
    TextBox TextBox TxtTotalOrder 0.00 TextAlign: Right
    Label Label   Recommendations:  
    TextBox TextBox TxtRecommendations   Scrollbars: Vertical
    Multiline: True 
    Button Button BtnSave Save  
    Label Label   Receipt #:  
    TextBox TextBox TxtReceiptNumber    
    Button Button BtnOpen Open  
    Button Button BtnNewRepairOrder New Repair Order  
    Button 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';")
    
                Dim strCreateDatabase As String = "IF EXISTS (" & _
                   		"SELECT * " & _
                   		" FROM sys.databases " & _
                   		" WHERE name = N'CollegeParkAutoRepair1' " & _
                   		")" & _
                   		"DROP DATABASE CollegeParkAutoRepair1;" & _
                   		"CREATE DATABASE CollegeParkAutoRepair1;"
    
                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, " & _
                    "Job3Description varchar(80), " & _
                    "Job3Price money, " & _
                    "Job4Description varchar(80), " & _
                    "Job4Price money, " & _
                    "Job5Description varchar(80), " & _
                    "Job5Price money, " & _
                    "TotalParts money, " & _
                    "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
     
    College Park Auto Repair
     
    College Park Auto Repair
  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) _
                                        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 = ""
            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
            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
    
            ' 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
                TxtUnitPrice3.Text = "0.00"
                TxtQuantity3.Text = "0"
                TxtSubTotal3.Text = "0.00"
                UnitPrice3 = 0.0
            Else
                Try
                    UnitPrice3 = CDbl(TxtUnitPrice3.Text)
                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"
                    TxtUnitPrice5.Focus()
                End Try
    
                Try
                    Quantity5 = CInt(TxtQuantity5.Text)
                Catch ex As Exception
                    MsgBox("Invalid Quantity")
                    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
            Else
                Try
                    JobPrice4 = CDbl(TxtJobPrice4.Text)
                Catch ex As Exception
                    MsgBox("Invalid Job Price")
                    TxtJobPrice4.Text = "0.00"
                    TxtJobPrice4.Focus()
                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)
            TxtTaxAmount.Text = FormatCurrency(TaxAmount)
            TxtTotalOrder.Text = FormatCurrency(TotalOrder)
    End Sub
  15. Under the above End Sub line, implement the following event:
     
    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, " & _
                 		     "Part4Name, Part4UnitPrice, Part4Quantity, " & _
                 		     "Part4SubTotal, Part5Name, Part5UnitPrice, " & _
                 		     "Part5Quantity, Part5SubTotal, Job1Description, " & _
                 		"Job1Price, Job2Description, Job2Price, " & _
                 		"Job3Description, Job3Price, Job4Description, " & _
                 		"Job4Price, Job5Description, Job5Price, " & _
                 		"Recommendations, TotalParts, TotalLabor, " & _
                 		"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 & "', " & _
                 "    Part4Name = '" & TxtPart4Name.Text & "', " & _
                 "    Part4UnitPrice = '" & TxtUnitPrice4.Text & "', " & _
                 "    Part4Quantity = '" & TxtQuantity4.Text & "', " & _
                 "    Part4SubTotal = '" & TxtSubTotal4.Text & "', " & _
                 "    Part5Name = '" & TxtPart5Name.Text & "', " & _
                 "    Part5UnitPrice = '" & TxtUnitPrice5.Text & "', " & _
                 "    Part5Quantity = '" & TxtQuantity5.Text & "', " & _
                 "    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';" & _
    		              "Integrated Security=yes")
    
            Dim strFindRepair As String = _
                    "SELECT * FROM RepairOrders WHERE RepairOrderID = '" & _
                    strReceiptNumber & "'"
            Dim cmdDatabase As SqlCommand = _
    		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()
                    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()
                    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:
     
    College Park Auto Repair: Repair Order
      
    College Park Auto Repair: Repair Order
  24. Close the form and return to your programming environment
  25. Execute the application again and open a few previously saved cleaning orders
 

Home Copyright © 2008-2012 FunctionX