Imports System.Data.SqlClient

Public Class Exercise

    Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        Using connection As SqlConnection = _
  	    	New SqlConnection("Data Source=(local);" & _
      				  "Integrated Security=yes;")
            Dim command As SqlCommand = _
                New SqlCommand("CREATE DATABASE VideoCollection;", _
                 	       connection)
            Connect.Open()
            command.ExecuteNonQuery()

            MsgBox("A database named VideoCollection has been created.")
        End Using
    End Sub
End Class

Here is a sample table:

Imports System.Data.SqlClient

Public Class Exercise

    Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        Using connection As SqlConnection = _
 		New SqlConnection("Data Source=(local);" & _
     			"Database='VideoCollection';" & _
     			"Integrated Security=yes;")
            Dim command As SqlCommand = _
                 New SqlCommand("CREATE TABLE Videos (" & _
                 		"[Video Title] varchar(120), " & _
                 		"Director varchar(100), " & _
                 		"[© Year] smallint, " & _
                 		"Length varchar(30), " & _
                 		"Rating varchar(6));", _
                 		connection)
            Connect.Open()
            command.ExecuteNonQuery()

            MsgBox("A table named ""Videos"" has been created.")
        End Using
    End Sub
End Class

Because a table is the central part of a database, the information it holds must be meticulously organized. To better manage its information, data of a table is arranged in a series of fields called cells. Once a table contains information, you can review it using either the Microsoft SQL Server Management Studio or a Windows application.

1. Start Microsoft Visual Basic and create a new Windows Application named CollegeParkAutoRepair2
2. In the Server Explorer, right-click Data Connections and click Add New SQL Server Database
3. In the Server Name combo box, select the server or type (local)
4. Set the name of the database to CPAR1 and click OK
5. In the Server Explorer, expand server.CPAR1.dbo
6. Under it, right-click Tables and click Add New Table
7. Complete the table with following columns:
    

   Column Name	Data Type
   ReceiptNumber	int
   OrderDate	datetime
   OrderTime	datetime
   CustomerName	nvarchar(80)
   CarMake	nvarchar(50)
   CarModel	nvarchar(50)
   CarYear	smallint
   ProblemDescription	ntext
   TotalParts	money
   TotalLabor	money
   TaxRate	decimal(6,2)
   TaxAmount	money
   TotalOrder	money
   Recommendations	ntext

8. To save the table, on the Standard toolbar, click the Save button
9. Type RepairOrders as the name of the table and click OK
10. Close the RepairOrders window
11. In the Solution Explorer, right-click Form1.vb and click Rename
12. Type OrderProcessing.vb and press Enter twice
13. Design the form as follows:
     

    Control Name Text Other Properties Group   Order Identification   Label   Order Date/Time:   DateTimePicker dtpOrderDate     DateTimePicker dtpOrderTime   Format: Time ShowUpDown: True Label   Customer Name:   TextBox txtCustomerName     Label   Make / Model:   TextBox txtMake     TextBox txtModel     Label   Year:   TextBox txtCarYear   TextAlign: Right Label   Problem Description:   TextBox txtProblemDescription   Multiline: True ScrollBars: Vertical 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   Multiline: True ScrollBars: Vertical Button btnSaveRepairOrder Save Repair Order   Button btnNewRepairOrder New Repair Order/Reset   Button btnClose Close

14. Right-click the form and click View Code
15. Above the Public Class line, import the System.Data.SqlClient namespace
16. In the Class Name combo box, select btnNewRepairOrder
17. In the Method Name combo box, select Click and implement the event as follows:
     

    Imports System.Data.SqlClient Public Class OrderProcessing Private Sub btnNewRepairOrder_Click(ByVal sender As Object, _ ByVal e As System.EventArgs) _ Handles btnNewRepairOrder.Click dtpOrderDate.Value = DateTime.Today.Date dtpOrderTime.Value = DateTime.Now txtCustomerName.Text = "" txtCarMake.Text = "" txtCarModel.Text = "" txtCarYear.Text = "" txtProblemDescription.Text = "" txtTotalParts.Text = "0.00" txtTotalLabor.Text = "0.00" txtTaxRate.Text = "7.75" txtTaxAmount.Text = "0.00" txtTotalOrder.Text = "0.00" txtRecommendations.Text = "0.00" End Sub End Class

Data Navigation consists of displaying and viewing data. Because information of a database is stored in tables, your primary means of viewing data consists of opening a table in a view that displays its information.

When a table displays its records, you navigate through its fields using the mouse or the keyboard. With the mouse, to get to any cell, you can just click it. To navigate through records using the keyboard, you can press:

• The right arrow key to move to the right cell; if the caret is already in the most right cell, it would be moved to the first cell of the next record, up to the last empty cell of the first empty record
• The left arrow key to move to the previous cell; if the caret is in, or reaches, the most left cell of the first record, nothing would happen when you press the the left arrow key
• The down arrow key to move to the cell under the current one; if the caret is already in the last cell of the current column, nothing would happen
• The up arrow key to move to the cell just above the current one; if the caret is already in the first cell of the current column, nothing would happen
• The Page Down to move to the next group of cell that would correspond to the next page; if the number of records is less than a complete page, the caret would move to the last cell of the current column
• The Page Up to move to the next group of cell that would correspond to the next page; if the number of records is less than a complete page, the caret would move to the first cell of the current column

As you are probably aware already, columns are used to organize data by categories. Each column has a series of fields under the column header. One of the actual purposes of a table is to display data that is available for each field under a particular column. Data entry consists of providing the necessary values of the fields of a table. Data is entered into a field and every time this is done, the database creates a row of data. This row is called a record. This means that entering data also self-creates rows.

There are various ways you can perform data entry for a Microsoft SQL Server table:

• In Microsoft SQL Server Management Studio, you can use a table from the Object Explorer
• In Microsoft SQL Server Management Studio, you can enter data by typing code in a query window
• In Microsoft Visual Studio, you can open the table from the Server Explorer
• You can import data from another object or another database
• You can use an external application such as Microsoft Access, Microsoft Visual Basic, Borland C++ Builder, Microsoft Visual C++, Borland Delphi, Microsoft Visual Basic, C#, Visual C#, J#, etc
• You can create a Windows application in Microsoft Visual Studio

Probably the easiest and fastest way to enter data into a table is by using either Microsoft SQL Server Management Studio or Microsoft Visual Studio. Of course, you must first open the desired table from a database connection. In the Server Explorer, after expanding the connection to the database and the Tables nodes, right-click the desired table and click Show Table Data. If the table does not contain data, it would appear with one empty row. If some records were entered already, their rows would show and the table would provide an empty row at the end, expecting a new record.

To perform data entry on a table, you can click in a cell. Each column has a title, called a caption, on top. This gray section on top is called a column header. In Microsoft SQL Server, it displays the actual name of the column. You refer to the column header to know what kind of data should/must go in a field under a particular column. This is why you should design your columns meticulously. After identifying a column, you can type a value. Except for text-based columns, a field can reject a value if the value does not conform to the data type that was set for the column. This means that in some circumstances, you may have to provide some or more explicit information to the user.

1. In the Server Explorer, if necessary, expand the Tables node under Server.CPAR1.dbo.
   Right-click the RepairOrders node and click Show Table Data
2. Click the empty box under CustomerName, type Jamil Harrah
3. Click the box under ReceiptNumber, type 1244LPD and press Enter
4. Notice that you receive an error because the letters are not allowed:
    
   
5. Click OK on the error message box.
6. Change the value to 1244 and press Tab
7. Under OrderDate, type 2006/02/16 and press the down arrow key
8. Notice that the date changes to 2/16/2006
9. For the second box under OrderDate, type 06/06/06 and press Tab
10. For the OrderTime of the second record, type 14:48 and press the up arrow key
11. Notice that the value changes to today's date followed by the time you had entered
12. For the first record under OrderTime, type 04:25 PM and press Enter
13. Close the RepairOrders window

To perform data entry using SQL:

• In the Object Explorer of Microsoft SQL Server Management Studio, you can right-click the table, position the mouse on Script Table As -> INSERT To -> New Query Editor Window
• Open an empty query window and type your code

In the SQL, data entry is performed using the INSERT combined with the VALUES keywords. The primary statement uses the following syntax:

INSERT TableName VALUES(Column1, Column2, Column_n);

Alternatively, or to be more precise, you can use the INTO keyword between the INSERT keyword and the TableName factor to specify that you are entering data in the table. This is done with the following syntax:

INSERT INTO TableName VALUES(Column1, Column2, Column_n)

The TableName factor must be a valid name of an existing table in the database you are using. If the name is wrong, the SQL interpreter would simply consider that the table you are referring to does not exist. Consequently, you would receive an error.

The VALUES keyword indicates that you are ready to list the values of the columns. The values of the columns must be included in parentheses.

If the column is a BIT data type, you must specify one of its values as 0 or 1.

If the column is a numeric type, you should pay attention to the number you type. If the column was configured to receive an integer (int, bigint, smallint), you should provide a valid natural number without the decimal separator.

If the column is for a decimal number (float, real, decimal, numeric), you can type the value with its character separator (the period for US English).

If the column was created for a date or time data type (datetime or smalldatetime), make sure you provide a valid date.

If the data type of a column is a string type, you should include its entry between single quotes. For example, a shelf number can be specified as 'HHR-604' and a middle initial can be given as 'D'.

In your Windows application, you can pass the INSERT statement to a command object. 

The most common technique of performing data entry requires that you know the sequence of fields of the table in which you want to enter data. With this subsequent list in mind, enter the value of each field in its correct position. 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 connection As SqlConnection = _
 		New SqlConnection("Data Source=(local);" & _
     			"Database='VideoCollection';" & _
     			"Integrated Security=yes;")
            Dim command As SqlCommand = _
                New SqlCommand("INSERT INTO Videos " & _
           "VALUES('A Few Good Men','Rob Reiner',1992,'138 Minutes', 'R');", _
                 connection)
            Connect.Open()
            command.ExecuteNonQuery()

            MsgBox("A new record has been created.")
        End Using
    End Sub
End Class

During data entry on adjacent fields, if you do not have a value for a numeric field, you should type 0 as its value. For a string field whose data you do not have and cannot provide, type two single-quotes '' to specify an empty field.

1. In the Class Name combo box, select btnSaveRepairOrder

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

   Private Sub btnSaveRepairOrder_Click(ByVal sender As Object, _ ByVal e As System.EventArgs) _ Handles btnSaveRepairOrder.Click Dim strConnection As String = _ "Data Source=(local);" & _ "Database='CPAR1';" & _ "Integrated Security=yes;" Dim strRepairOrder As String = "INSERT INTO RepairOrders(CustomerName, " & _ "CarMake, CarModel, CarYear, ProblemDescription, " & _ "TotalParts, TotalLabor, TotalOrder) " & _ "VALUES ('" & txtCustomerName.Text & "', '" & _ txtCarMake.Text & "', '" & txtCarModel.Text & "', '" & _ txtCarYear.Text & "', '" & txtProblemDescription.Text & _ "', '" & txtTotalParts.Text & "', '" & txtTotalLabor.Text & _ "', '" & txtTotalOrder.Text & "');" Using connection As SqlConnection = _ New SqlConnection(strConnection) Dim command As SqlCommand = _ New SqlCommand(strRepairOrder, connection) Connect.Open() Command.ExecuteNonQuery() MsgBox("A new repair order has been created.") End Using End Sub

2. Execute the application
3. Enter all values in the form
4. Click the Save Repair Order button. Here is an example:
    
   
5. Click the New Repair Order/Reset button
6. Create another order as follows:
    
   
7. Close the form and return to your programming environment

The adjacent data entry we have performed requires that you know the position of each column. The SQL provides an alternative that allows you to perform data entry using the name of a column instead of its position. This allows you to provide the values of columns in an order of your choice. We have just seen a few examples where the values of some of the fields were not available during data entry. Instead of remembering to type 0 or NULL for such fields or leaving empty quotes for a field, you can use the fields' names to specify the fields whose data you want to provide.

To perform data entry in an order of your choice, you must provide your list of the fields of the table. You can either use all columns or provide a list of the same columns but in your own order. In the same way, you do not have to provide data for all fields, just those you want, in the order you want.

Here are examples:

Imports System.Data.SqlClient

Public Class Exercise

    Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        Using connection As SqlConnection = _
 		New SqlConnection("Data Source=(local);" & _
     			"Database='VideoCollection';" & _
     			"Integrated Security=yes;")
            Dim command As SqlCommand = _
                New SqlCommand( _
         "INSERT INTO Videos([Video Title], Director, [© Year], Length) " & _
         "VALUES('The Silence of the Lambs','Jonathan Demme',1991,'118 Minutes'); " & _
         "INSERT INTO Videos([Video Title], Director, Length) " & _
         "VALUES('The Distinguished Gentleman', 'James Groeling', '112 Minutes'); " & _
         "INSERT INTO Videos([Video Title], Director, Length) " & _
         "VALUES('The Lady Killers', 'Joel Coen & Ethan Coen', '104 Minutes'); " & _
         "INSERT INTO Videos([Video Title], Director, Length) " & _
         "VALUES('Ghosts of Mississippi', 'Rob Reiner', '130 Minutes');", _
             		connection)
            Connect.Open()
            command.ExecuteNonQuery()

            MsgBox("A few records have been created.")
        End Using
    End Sub
End Class

1. Change the code of the Save Repair Order button as follows:
    

   Private Sub btnSaveRepairOrder_Click(ByVal sender As Object, _ ByVal e As System.EventArgs) _ Handles btnSaveRepairOrder.Click Dim strConnection As String = _ "Data Source=(local);" & _ "Database='CPAR1';" & _ "Integrated Security=yes;" Dim strRepairOrder As String = _ "INSERT INTO RepairOrders(CustomerName, " & _ "CarMake, CarModel, CarYear, ProblemDescription, " & _ "TotalParts, TotalLabor, TotalOrder) " & _ "VALUES ('" & _ txtCustomerName.Text & "', '" & _ txtCarMake.Text & "', '" & _ txtCarModel.Text & "', '" & _ txtCarYear.Text & "', '" & _ txtProblemDescription.Text & "', '" & _ txtTotalParts.Text & "', '" & _ txtTotalLabor.Text & "', '" & _ txtTotalOrder.Text & "');" Using connection As SqlConnection = New SqlConnection(strConnection) Dim command As SqlCommand = _ New SqlCommand(strRepairOrder, connection) Connect.Open() Command.ExecuteNonQuery() MsgBox("A new repair order has been created.") End Using End Sub

2. Execute the application
3. Enter the customer name, the name of the car, its model, its year, a description of a job to be performed, the cost of the parts, the cost of the labor, and the total price paid to repair
4. Click the Save Repair Order button
5. Enter the values again and click the Save Repair Order button again
6. Close the form and return to your programming environment

Before visually performing some operations on a table, you must first select one or more records. In the Table window, to select one record, position the mouse on the left button of the record and click:

Instead of one, you can select more than one record at a time. To select a range of records, click the gray button of one of the records, press and hold Shift, then click the gray button of the record at the other extreme:

To select some records at random, select one record, press and hold Ctrl, then click the gray button of each desired record:

To select all records of a table, you can click the gray button on the left of the first column:

Alternatively, you can first click a record header and press Ctrl + A.

Record maintenance includes viewing records, looking for one or more records, modifying one or more records, or deleting one or more records.

If you find out that a record is not necessary, not anymore, or is misplaced, you can remove it from a table.

To visually delete a record in SQL in Microsoft SQL Server Management Studio or Microsoft Visual Studio, open the table to show its records. On the table, you can right-click the gray box on the left of a record and click Delete:

You can also first select the record and press Delete. You would receive a warning to confirm your intention.

In SQL, to delete a record, use the DELETE FROM statement associated with the WHERE operator. The formula to follow is:

DELETE FROM TableName
WHERE Condition(s)

The TableName factor is used to identify a table whose record(s) would be removed.

The Condition(s) factor allows you to identify a record or a group of records that carries a criterion. Make sure you are precise in your criteria so you would not delete the wrong record(s).

Here is an example used to remove a particular record from the table:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Using connection As SqlConnection = _
	 New SqlConnection("Data Source=(local);" & _
     			"Database='VideoCollection';" & _
     			"Integrated Security=yes;")
        Dim command As SqlCommand = _
             New SqlCommand("DELETE FROM Videos " & _
        	            "WHERE [Video Title] = 'The Lady Killers';", _
               		    connection)
        Connect.Open()
        command.ExecuteNonQuery()

        MsgBox("The video title ""The Lady Killers"" has been deleted")
    End Using
End Sub

Instead of one, you can delete more than one record at a time. To do this, first select the group of records, either in a range or at random, then either right-click the selection and click Delete or press Delete:

After clicking Delete, you would receive a warning. If you still want to delete the records, you can click OK.

To programmatically delete a group or records, apply the DELETE FROM table formula and use a WHERE condition that can identify each one of the records.

If you think all records of a particular table are, or have become, useless, you can clear the whole table, which would still keep its structure.

To visually delete all records from a table, open it in design view, first select all of them, and press Delete. You would receive a warning. If you still want to delete the records, click Yes. If you change your mind, click No.

Using SQL, to clear a table of all records, use the DELETE operator with the following formula:

DELETE TableName;

When this statement is executed, all records from the TableName factor would be removed from the table. Be careful when doing this because once the records have been deleted, you cannot get them back.

1. In the Class Name combo box, select btnClose
2. 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 Dim strConnection As String = _ "Data Source=(local);" & _ "Database='CPAR1';" & _ "Integrated Security=yes;" Dim strRepairOrder As String = "DELETE RepairOrders;" Using connection As SqlConnection = _ New SqlConnection(strConnection) Dim command As SqlCommand = New SqlCommand(strRepairOrder, connection) Connect.Open() command.ExecuteNonQuery() MsgBox("All repair orders have been deleted.") End Using Close() End Sub

3. Execute the application to display the form
4. Click the Close button
    
   
5. Return to your programming environment and change the code of the Close button as follows:
    

   Private Sub btnClose_Click(ByVal sender As Object, _ ByVal e As System.EventArgs) _ Handles btnClose.Click End End Sub

6. Save all

Updating a record consists of changing its value for a particular column. To visually update a record, open the table to show its records, locate the value that needs to be updated and edit it.

To update a record using SQL:

• In the Object Explorer of Microsoft SQL Server Management Studio, you can right the table, position the mouse on Script Table As -> UPDATE To -> New Query Editor Window
• Open an empty query window and type your code

To support record maintenance operations, the SQL provides the UPDATE keyword that is used to specify the table on which you want to maintain the record(s). The basic formula to use is:

UPDATE TableName
SET ColumnName = Expression

With this formula, you must specify the name of the involved table as the TableName factor of our formula. The SET statement allows you to specify a new value, Expression, for the field under the ColumnName column.

Imagine that, at one time, on a particular table, all records need to receive a new value under one particular column or certain columns. There is no particular way to visually update all records of a table. You can just open the table to view its records, and then change them one at a time.

In SQL, the primary formula of the UPDATE statement as introduced on our formula can be used to update all records. Here is an example:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Using connection As SqlConnection = _
	 New SqlConnection("Data Source=(local);" & _
     			"Database='VideoCollection';" & _
     			"Integrated Security=yes;")
        Dim command As SqlCommand = _
               New SqlCommand("UPDATE Videos SET Rating = 'R';", _
        		      connection)
        Connect.Open()
        command.ExecuteNonQuery()

        MsgBox("All video records have been rated R.")
    End Using
End Sub

With this code, all records of the Videos table will have their Rating fields set to a value of R:

Editing a record consists of changing a value in a field. It could be that the field is empty, such as the © Year of the the 'The Lady Killers' video of the following table. It could be that the value is wrong, such as the Director of the the 'The Distinguished Gentleman' video of this table:

To edit a record, first open the table to view its records. Locate the record, the column on which you want to work, and locate the value you want to change, then change it.

In SQL, you must provide a way for the interpreter to locate the record. To do this, you would associate the WHERE operator in an UPDATE statement using the following formula:

UPDATE TableName
SET ColumnName = Expression
WHERE Condition(s)

The WHERE operator allows you to specify how the particular record involved would be identified. It is  very important, in most cases, that the criterion used be able to uniquely identify the record. In the above table, imagine that you ask the interpreter to change the released year to 1996 where the director of the video is Rob Reiner. The UPDATE statement would be written as follows:

UPDATE Videos
SET YearReleased = 1996
WHERE Director = 'Rob Reiner';

In the above table, there are at least two videos directed by Rob Reiner. When this statement is executed, all video records whose director is Rob Reiner would be changed, which would compromise existing records that did not need this change. Therefore, make sure your WHERE statement would isolate one particular record or only those that need to be updated. Here is an example used to change the name of the director of a particular video:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Using connection As SqlConnection = _
	 New SqlConnection("Data Source=(local);" & _
     			"Database='VideoCollection';" & _
     			"Integrated Security=yes;")
        Dim command As SqlCommand = _
                New SqlCommand("UPDATE Videos " & _
        		       "SET Director = 'Jonathan Lynn' " & _
 		           "WHERE [Video Title] = 'The Distinguished Gentleman';", _
            			connection)
        Connect.Open()
        command.ExecuteNonQuery()

        MsgBox("The director of 'The Distinguished Gentleman' " & _
               "video has been updated.")
    End Using
End Sub