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.

Practical
Learning: Introducing Database Records

Start Microsoft Visual C# and create a new Windows Application named
CollegeParkAutoRepair3

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

Visual Data Entry

Introduction

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

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.

Practical
Learning: Introducing Data Entry

In the Server Explorer, if necessary, expand the Tables node under
Server.CPAR2.dbo.
Right-click the RepairOrders node and click Show Table Data

Click the empty box under CustomerName,
type Jamil Harrah

Click the box under ReceiptNumber, type 1244LPD and press Enter

Notice that you receive an error because the letters are not allowed:

Click OK on the error message box.

Change the value to 1244 and press Tab

Under OrderDate, type 2006/02/16 and press the down
arrow key

Notice that the date changes to 2/16/2006

For the second box under OrderDate, type 06/06/06
and press Tab

For the OrderTime of the second record, type 14:48
and press the up arrow key

Notice that the value changes to today's date followed by the time
you had entered

For the first record under OrderTime, type 04:25 PM
and press Enter

Close the RepairOrders window

Data Entry With SQL

Introduction

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 doesn't
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.

Adjacent Data Entry

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:

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.

Practical
Learning: Performing Data Entry

On the OrderProcessing form, double-click the Save Repair Order
button and implement its event as follows:

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 don't have to provide data for all fields,
just those you want, in the order you want.

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

Click the Save Repair Order button

Enter the values again and click the Save Repair Order button again

Close the form and return to your programming environment

Record Maintenance: Selecting Records

Selecting a Record

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:

Selecting Many Records

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:

Selecting all Records

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: Deleting Records

Deleting a Record

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

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 FROMtable formula and use a WHERE
condition that can identify each one of the records.

Deleting all 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.

Practical
Learning: Deleting Records

On the form double-click the Close button and implement it as
follows:

Return to your programming environment and change the code of the
Close button as follows:

private void btnClose_Click(object sender, EventArgs e)
{
Close();
}

Save all

Records Maintenance: Updating the Records

Updating a Record

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.

Updating all Records

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:

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: