Introduction to the Records of a Database

Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_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)
            connection.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

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
        Using connection As SqlConnection = _
   		New SqlConnection("Data Source=(local);" & _
        			  "Database='VideoCollection4';" & _
        			  "Integrated Security=yes;")
            Dim command As SqlCommand = _
                 New SqlCommand("CREATE TABLE Videos (" & _
                   		"VideoTitle nvarchar(120), " & _
                   		"Director nvarchar(100), " & _
                   		"CopyrightYear smallint, " & _
                   		"Length nvarchar(30), " & _
                   		"Rating nvarchar(6));", _
                   		connection)
            connection.Open()
            command.ExecuteNonQuery()

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

To better manage its information, data of a table is arranged in a series of fields. Once a table contains information, you can review it.

The columns of a table are used to organize data by categories. Each column has a series of fields under the column header. One of the aspects 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.

To perform data entry using SQL, use the INSERT combined with the VALUES keywords. The primary statement uses the following syntax:

INSERT TableName VALUES(Column1, Column2, Column_n);

Alternatively, you can use the INTO keyword between the INSERT keyword and the TableName. 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 databaser.

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 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 (date, time, or datetime2), 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. 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)
            connection.Open()
            command.ExecuteNonQuery()

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

Of you are getting the values from a web page, make sure each value comes from the appropriate web control. Here is an example:

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)
        connection.Open()
        Command.ExecuteNonQuery()
        MsgBox("A new repair order has been created.")
    End Using
End Sub

The adjacent data entry requires that you know the position of each column. 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. 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)
            connection.Open()
            command.ExecuteNonQuery()

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

Once again remember that, if it is a visitor who is submitting the values from a web page, make sure you get the values from the web controls. Here is an example:

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)

            connection.Open()
            Command.ExecuteNonQuery()

            MsgBox("A new repair order has been created.")
        End Using
End Sub

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

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)
        connection.Open()
        command.ExecuteNonQuery()

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

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

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. Here is an example: 

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)

        connection.Open()
        command.ExecuteNonQuery()
        MsgBox("All repair orders have been deleted.")
    End Using

    Close()
End Sub

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.

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)
        connection.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.

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)
        connection.Open()
        command.ExecuteNonQuery()

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