Columns Fundamentals
|
|
Introduction
|
In the previous lesson, we saw that a table was used to
organize the values of a list by using categories of information. Here is an
example:
| Last Name | First Name | Main Class | Years of Experience |
| Pastore | Albert | Math | 4 |
| Andong | Gertrude | Chemistry | 8 |
| Missiano | Helena | Physical Ed | 5 |
| Jones | Celestine | Comp Sciences | 10 |
With this type of arrangement, each column holds a
particular category of information. A table must have at least one column. This
means that, to create a table using the CREATE TABLE TableName
formula we introduced in the previous lesson, you must specify a column.
|
Column Creation
|
To create the columns of a table, on the right side of the
name, type an opening and a closing parentheses. In the parentheses of the CREATE TABLE
TableName() expression, the formula of creating a column is:
ColumnName DataType Options
Notice that there is only space that separates the sections
of the formula. This formula is for creating one column. If you want the table
to have more than one column, follow this formula as many times as possible but
separate the sections with colons. This would be done as follows:
CREATE TABLE TableName(ColumnName DataType Options, ColumnName DataType Options)
In the next sections, we will review the factors of this
formula. To create a
table in ADO, you can pass the whole statement to the Execute() method of the
Connection class.
|
Characteristics of a Column
|
|
Introduction
|
Like a table of a database, a column must have a name. As mentioned for a table,
the name of a column is very flexible. Because of this, we
will adopt the same types of naming conventions we reviewed for tables:
- The name of a column will start with a letter. In most cases, the name will start in uppercase
- When a name is a combination of words, each part will start in uppercase. Examples are First Name or Date Hired
- In most cases, we will avoid including space in a name
When creating the table, set the name of
the column in the ColumnName placeholder of our formula. Here is an example:
CREATE TABLE Students(FullName, DataType Options)
Notice that the name of the column is not included in quotes.
|
The Types of Columns
|
To exercise a good level of control over the values that can
be entered or stored in a database, you can configure each column to allow
some types of value and/or to exclude some other types. This is done by
specifying an appropriate type of data for the column. To specify the data type of a
column, pass the
name of the data type as the second factor of the column.
Text-Based Columns: If the fields under a column would be used to hold any
type of value, including regular text, such a column is treated as
string-based. There are various data types you can apply to such a column.
You can specify the data type as Char, String or Varchar.
Here are examples of three columns created with these types:
Private Sub btnCreateTable_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnCreateTable.Click
Dim conADO As ADODB.ConnectionClass = New ADODB.ConnectionClass
Dim strSQL As String
conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source='C:\Programs\SchoolRecords.mdb'", Nothing, Nothing, 0)
strSQL = "CREATE TABLE Students(FirstName char, MiddleName String, " & _
"LastName varchar);"
conADO.Execute(strSQL, 0, 0)
conADO.Close
End Sub
Each one of the char, string, or varchar
data types would produce the same effect. A column with the string,
the char, or the varchar data
type allows any type of value made of any character up to 255 symbols. If
you want the column to hold longer text, specify its data type as Text,
Memo, NOTE, or LONGTEXT. Such a column can hold any
type of text, any combination of characters, and symbols, up to 64000 characters.
Here are examples:
Private Sub btnCreateTable_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnCreateTable.Click
Dim conADO As ADODB.ConnectionClass = New ADODB.ConnectionClass
Dim strSQL As String
conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source='C:\Programs\SchoolRecords.mdb'", Nothing, Nothing, 0)
strSQL = "CREATE TABLE Employees(FullName String, JobDescription Text, " & _
"Comments Memo, ShortTimeGoals Note, " & _
"LongTimeGoals LongText);"
conADO.Execute(strSQL, 0, 0)
conADO.Close
End Sub
Boolean Columns: If you want to create a column to hold only values
as being true or being false, specify its
data type as YESNO, BIT, or LOGICAL. Here are
examples:
Private Sub btnCreateTable_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnCreateTable.Click
Dim conADO As ADODB.ConnectionClass = New ADODB.ConnectionClass
Dim strSQL As String
conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source='C:\Programs\SchoolRecords.mdb'", Nothing, Nothing, 0)
strSQL = "CREATE TABLE Employees(FullName String, IsFullTime Bit, " & _
"CanTeachVariousSubjects Logical, " & _
"IsMarried YesNo);"
conADO.Execute(strSQL, 0, 0)
conADO.Close
End Sub
Byte and Integer1: If you want a column to hold
natural numbers, you can specify its data type as Byte or Integer1.
This is suited for a column that will hold small numeric values
not to exceed 255. Here are examples:
Private Sub btnCreateTable_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnCreateTable.Click
Dim conADO As ADODB.ConnectionClass = New ADODB.ConnectionClass
Dim strSQL As String
conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source='C:\Programs\SchoolRecords.mdb'", Nothing, Nothing, 0)
strSQL = "CREATE TABLE EmploymentStatus(StatusID Byte, Category Integer1);"
conADO.Execute(strSQL, 0, 0)
MessageBox.Show("A table named EmploymentStatus has been added to the " & _
"SchoolRecords.mdb database")
conADO.Close
End Sub
Short and Integer2: If you want the column to
hold larger numbers that can exceed 255, specify its data type as SHORT or INTEGER2.
Long: If the column will hold small to very
large numbers, specify its data type as INT, INTEGER, INTEGER4
or Long.
Floating-Point Value With Single Precision: If
you want to create a column that will hold regular decimal values without regards to precision on
its value, specify its data type as Single.
Floating-Point Value With Double Precision: If
the values of a column will require a good level of precision, specify its
data type as Double. Alternatively, you can specify the data type
as Numeric. Here is an example:
Private Sub btnCreateTable_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnCreateTable.Click
Dim conADO As ADODB.ConnectionClass = New ADODB.ConnectionClass
Dim strSQL As String
conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source='C:\Programs\SchoolRecords.mdb'", Nothing, Nothing, 0)
strSQL = "CREATE TABLE Students(FullName varchar, Height Single, " & _
"Weight Numeric, GPA Double);"
conADO.Execute(strSQL, 0, 0)
conADO.Close
End Sub
Money and Currency Columns: If you want
the values of a column to hold monetary
values, specify its data type as Money or Currency.
Here is an example:
Private Sub btnCreateTable_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnCreateTable.Click
Dim conADO As ADODB.ConnectionClass = New ADODB.ConnectionClass
Dim strSQL As String
conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source='C:\Programs\SchoolRecords.mdb'", Nothing, Nothing, 0)
strSQL = "CREATE TABLE Employees(" & _
"FullName Text, " & _
"WeeklyHours Double, " & _
"HourlySalary Money, WeeklySalary Currency);"
conADO.Execute(strSQL, 0, 0)
conADO.Close
End Sub
Both Money and Currency have the same effect.
Date and Time: If you are creating a column
whose values would consist of date, time, or both date and time, specify
its data type as
DATE or DATETIME. Here are examples:
Private Sub btnCreateTable_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnCreateTable.Click
Dim conADO As ADODB.ConnectionClass = New ADODB.ConnectionClass
Dim strSQL As String
conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source='C:\Programs\SchoolRecords.mdb'", Nothing, Nothing, 0)
strSQL = "CREATE TABLE Employees(" & _
"FullName Text, " & _
"DateHired Date, " & _
"DateLastReviewed DateTime);"
conADO.Execute(strSQL, 0, 0)
conADO.Close
End Sub
Both data types have the same effect in Microsoft
Access.
Binary: The binary data type can let a
column accept any type of data but it is equipped to interpret the value.
For example, it can be used to receive hexadecimal numbers.
To specify this when creating a column, specify its data type as either
BINARY or VARBINARY.
Image:
If you are creating a column that will hold external
documents, such as pictures, formatted (from Microsoft Word for example),
or spreadsheet, etc, specify its data type to one of the following:
IMAGE, OLEOBJECT, LONGBINARY, or GENERAL.
|
Column Maintenance
|
|
Introduction
|
Column maintenance consists of adding a new column or deleting an
existing column. Because the columns belong to a table, their maintenance is
related to it. To perform this maintenance, you start with the ALTER TABLE
expression followed by the name of the table.
|
Adding a New Column
|
After a table with one or more columns has been
created, you can add a new column to it. To add a new column, after the ALTER
TABLE statement and the name of the table, include an ADD COLUMN expression using the following
formula:
ALTER TABLE TableName ADD COLUMN ColumnName DataType
The ColumnName factor must be a valid name for
the new column and you must follow the rules for naming columns. The data
type must be one of those we reviewed. Here is an example that adds a new
string-based column named CellPhone to a table named Employees:
Private Sub btnAddColumn_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnAddColumn.Click
Dim conSchoolRecords As New ADODB.Connection
conSchoolRecords.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source='C:\Programs\SchoolRecords.mdb'")
conSchoolRecords.Execute("ALTER TABLE Employees ADD COLUMN CellPhone string;")
MsgBox("A new column named CellPhone has been added to the Employees table.")
conADO.Close
End Sub
It is important to understand that the ADD COLUMN expression
creates a new column at the end of the existing column(s). It cannot be used to
insert a column in a table.
|
Deleting a Column
|
To delete a column, start with the ALTER TABLE
expression followed by the name of the table. After the ALTER
TABLE TableName expression, follow it with a DROP COLUMN
expression using this formula:
ALTER TABLE TableName DROP COLUMN ColumnName;
Replace the name of the undesired column with the ColumnName
factor of our formula. Here is an example:
Private Sub btnDeleteColumn_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnDeleteColumn.Click
Dim conSchoolRecords As New ADODB.Connection
conSchoolRecords.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source='C:\Programs\SchoolRecords.mdb'")
conSchoolRecords.Execute("ALTER TABLE Employees DROP COLUMN Comments;")
MsgBox("The Comments column has been deleted from the Employees table.")
conADO.Close
End Sub
No comments:
Post a Comment