Lesson 38: Data Binding

	Data Binding

.NET Framework Data Binding Objects

Introduction

Consider the following table named StoreItems and that was created in a database named Exercise:

Store Items

To present data to the user, we can use some familiar objects such as the data grid view, the text box, or the combo box. Although the data grid view is the most complex, one of the most complete, and one of the most aesthetic controls of the .NET Framework, it cannot suit every possible scenario. Besides, the data grid view is a .NET object, not a Win32 control. As you may know already from your familiarity with Microsoft Windows, the operating system provides many more controls than that. Most users are more familiar with those controls and they would appreciate if the database was presented to them through these controls. The Windows controls in the .NET Framework were created to suit various types of controls, not just databases. Because they were made as broad as possible, the Windows controls are not readily made to display data. They need an intermediary object that can transmit data to them.

Using a Data Reader

In Lesson 23, we saw that you could use a data reader to read the value of a table. Once the values have been read, to access these values, you first call the Read() method of the class. The values read by the data adapter are stored in an indexed property of the class. When accessing each value, you must remember the order in which they are listed in the table.

As you read and access each value by its index, you can retrieve it and do what you want with it. For example you can assign it to a Windows control to display to the user. Here is an example:

Imports System.Data.SqlClient

Public Class Exercise

    Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        Using Connect As SqlConnection = _
 		New SqlConnection("Data Source=(local);" & _
     				  "Database='Exercise';" & _
     				  "Integrated Security=SSPI;")

            Dim strItems As String = _
                "SELECT [Item Name] FROM StoreItems WHERE [Item Number] = '209457';"
            Dim Command As SqlCommand = _
  		New SqlCommand(strItems, Connect)

            Connect.Open()

            Dim rdr As SqlDataReader = Command.ExecuteReader()

            While rdr.Read()
                TxtMerchandiseDescription.Text = rdr(0)
            End While
        End Using
    End Sub

End Class

When reading the records of a table, as mentioned already, the data reader reads one record at a time and moves to the next. Before moving to the next record, you can access the values stored in the current record. To help with this, the columns of the table being read are stored in a collection and each column can be referred to with a numeric index. The first column has an index of 1. The second column has an index of 2, and so on. To retrieve the actual data stored in a column, you may need to know the type of information the column is holding so you can read it accurately.

Depending on the data type that a column was created with, you can access it as follows:

If the column holds the following data type	Use the following method		System.Data.SqlTypes Equivalent
bit	GetBoolean()		GetSqlBoolean()
char, nchar	GetChar()		GetSqlChar()
varchar, nvarchar	GetString()		GetSqlString()
text, ntext	GetString()		GetSqlString()
binary, varbinary	GetBinary()		GetSqlBinary()
decimal	GetDecimal()	GetDouble()	GetSqlDecimal()
float	GetFloat()		GetSqlSingle()
int	GetInt32()		GetSqlInt32()
money, smallmoney	GetDecimal()		GetSqlDecimal()
bigint	GetInt64()		GetSqlInt64()
datetime, smalldatetime	GetDateTime()		GetSqlDateTime()
smallint, tinyint	GetInt16()	GetByte()	GetSqlInt16()

When using one of the Get... or GetSql... methods, the compiler does not perform any conversion. This means that, before sending the data, you must convert the value read to the appropriate (and probably exact) format. For example, if you read a natural number from a column created with the tinyint data type, the compiler you use for your application would not perform or assume the conversion. For example, the value of a column created with tinyint must be read with GetByte() or GetSqlByte() and trying to use GetInt32() or GetSqlInt32() would throw an error.

Practical Learning: Using a Data Reader

Start Microsoft Visual Basic and create a new Windows Application named CollegeParkAutoRepair3
In the Solution Explorer, right-click Form1.vb and click Rename
Type RepairOrders.vb and press Enter twice

Design the form as follows:

Control	Name	Text	Additional Properties
GroupBox		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	TxtYear
Label		Problem Description:
TextBox	TxtProblemDescription		Scrollbars: Vertical 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
TextBox	TxtSubTotal3	0.00	TextAlign: Right
TextBox	TxtPart4Name
TextBox	TxtUnitPrice4	0.00	TextAlign: Right
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
Label		Total Labor:
TextBox	TxtTotalLabor	0.00	TextAlign: Right
Label		Tax Rate:
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:
TextBox	TxtRecommendations		Scrollbars: Vertical Multiline: True
Button	BtnSave	Save
Label		Receipt #:
TextBox	TxtReceiptNumber
Button	BtnOpen	Open
Button	BtnNewRepairOrder	New Repair Order
Button	BtnClose	Close

Right-click the form and click View Code
Just above the Public Class line, import the System.Data.SqlClient namespace
In the Class Name combo box, select (RepairOrders Events)

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

Execute the application to create the database and its table
Close the form and return to your programming environment
In the Class Name combo box, select BtnNewRepairOrder

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

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

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

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

In the Class Name combo box, select BtnSave

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

In the Class Name combo, box, select BtnOpen

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

In the Class Name combo box, select BtnClose

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

Execute the application
Create a few repair orders. Here are two examples:
Close the form and return to your programming environment
Execute the application again and open a few previously saved cleaning orders


Previous	Copyright � 2008 Yevol	Next