Data Entry Fundamentals

Introduction

So far, we have learned how to create a database and how to create a table. Here are the database and the table we will use:

Imports ADOX
Imports ADODB

Module Central

    Sub Main()
        Dim catPeople As Catalog = New Catalog
        Dim conPeople As New Connection
        Dim obj As Object

        obj = Nothing

        Try
            catPeople.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                             "Data Source='C:\Programs\People.mdb';")
            Console.WriteLine("A new Microsoft JET database named " & _
                              "People.mdb has been created")

            conPeople.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                           "Data Source='C:\Programs\People.mdb'", _
                           "", "", 0)

            conPeople.Execute("CREATE TABLE Persons(FirstName Text(20), " & _
                                          "LastName Text(20), " & _
                                          "Gender Text(20));", obj, 0)
            Console.WriteLine("A table named Persons has been " & _
                              "created in the People.mdb database")
        Finally
            conPeople.Close()
        End Try
    End Sub

End Module

This creates a Microsoft JET database named People, followed by a table named Persons equipped with three columns.

After creating a table and its column(s), you can populate the database with data. Data entry consists of filling a table with the necessary values. A series of values that corresponds to same levels of columns is called a row or a record.

Before performing data entry on a table, you must know how the table is structured, the sequence of its columns, the type of data that each column is made of. To enter data in a table, you start with the INSERT combined with the VALUES keywords. The statement uses the following syntax:

INSERT TableName VALUES(Column1, Column2, Column_n)

Alternatively, or to be more precise, you can specify that you are entering data in the table using the INTO keyword between the INSERT keyword and the TableName factor. 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 currently selected database. 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. Specify the value of each column in the parentheses that follow the VALUES keyword:

Boolean Values: If the column is Boolean-based, you must specify its value as 0 or 1.

Numeric Values: If the column is a numeric type and if the number is an integer, you should provide a valid natural number without the decimal separator. If the column is for a decimal number, you can type the value with its character separator (the period for US English).

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

Date and Time Values: If the column was created for a date or a time data type, you should/must use an appropriate formula with the year represented by 2 or 4 digits. You should also include the date in single-quotes. If you want to specify the year with 2 digits, use the formula:

'yy-mm-dd'

Or

'yy/mm/dd'

You can use the dash symbol "-" or the forward slash "/" as the date separator. An alternative to representing a year is with 4 digits. In this case, you would use the formulas:

'yyyy-mm-dd'

Or

'yyyy/mm/dd'

The year with 4 digits is more precise as it properly expresses a complete year.

A month from January to September can be represented as 1, 2, 3, 4, 5, 6, 7, 8, or 9. Day numbers follow the same logic.

The most common technique of performing data entry requires that you know the sequence of columns 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.

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

Imports ADOX
Imports ADODB

Module Central

    Sub Main()
        Dim catPeople As Catalog = New Catalog
        Dim conPeople As New Connection
        Dim obj As Object

        obj = Nothing

        Try
            conPeople.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                           "Data Source='C:\Programs\People.mdb'", _
                           "", "", 0)

         conPeople.Execute("INSERT INTO Persons(FirstName, LastName, Gender) " & _
                              "VALUES('James', 'Carlton', 'Male');", obj, 0)
            Console.WriteLine("A new record has been created in the Persons table")
        Finally
            conPeople.Close()
        End Try
    End Sub

End Module

The adjacent data entry 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 fields in any order of your choice.

To perform data entry at random, you must provide a list of the columns of the table in the order of your choice. You can either use all columns or provide a list of the same columns but in your own order. Here is an example:

Imports ADOX
Imports ADODB

Module Central

    Sub Main()
        Dim catPeople As Catalog = New Catalog
        Dim conPeople As New Connection
        Dim obj As Object

        obj = Nothing

        Try
            conPeople.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                           "Data Source='C:\Programs\People.mdb'", _
                           "", "", 0)

            conPeople.Execute("INSERT INTO Persons(LastName, " & _
                                                  "Gender, FirstName) " & _
                              "VALUES('Germain', 'Male', 'Ndongo');", obj, 0)
          Console.WriteLine("A new record has been created in the Persons table")
        Finally
            conPeople.Close()
        End Try
    End Sub

End Module

You don't have to provide data for all columns, just those you want, in the order you want. To do this, enter the names of the desired columns on the right side of the name of the table, in parentheses. The syntax used would be:

INSERT TableName(ColumnName1, Columnname2, ColumnName_n)
VALUES(ValueFormColumnName1, ValueFormColumnName2, ValueFormColumnName_n);

When performing data entry, you can expect the user to skip any column whose value is not available and move to the next. In some cases, you may require that the value of a column be specified before the user can move on. If you are creating a column and if you want to let the user add or not add a value for the column, type the NULL keyword on the right side of the data type. If you want to require a value for the column, type NOT NULL. Here are examples:

Imports ADOX
Imports ADODB

Module Central

    Sub Main()
        Dim catVideos As Catalog = New Catalog
        Dim conVideos As New Connection
        Dim obj As Object

        obj = Nothing

        Try
            catVideos.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                      "Data Source='C:\Programs\VideoCollection.mdb';")
  Console.WriteLine("A new Microsoft JET database named VideoCollection.mdb " & _
                             "has been created")

            conVideos.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                           "Data Source='C:\Programs\VideoCollection.mdb'", _
                           "", "", 0)
            conVideos.Execute("CREATE TABLE Videos(" & _
                              "VideoTitle   STRING NOT NULL, " & _
                              "Director     STRING NULL, " & _
                       "YearReleased SHORT, " & _
                       "Rating       BYTE NULL);", _
                        obj, 0)
            Console.WriteLine("A new table named Videos has been created")
        Finally
            conVideos.Close()
        End Try
    End Sub

End Module

In this case, when performing data entry, the user must always provide a value for the VideoTitle column in order to create a record. If you omit to specify the nullity of a field, it is assumed NULL; that's the case for the YearReleased column of the above Videos table.

When we study relationships, we will see that, on a table, each record should be uniquely identified. This should be the case even if many records seem to have the same values for each column. We saw already that you can require that the user provide a value for each record of a certain column. In some cases, the user may not have the right value for a column but at the time, the record would need to be created, even if it is temporary. To solve this type of problem and many others, you can create a column that provides its own value. On the other hand, to create a special column that can be used to uniquely identify each record, you can apply an integer data type to it but ask the database engine to automatically provide a numeric value for the column.

If you are creating the column, you can specify its data type as either COUNTER or AUTOINCREMENT. Only one column of a table can have one of these data types. Here is an example:

Imports ADODB

Module Central

    Sub Main()
        Dim conVideos As New Connection
        Dim obj As Object

        obj = Nothing

        Try
            conVideos.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                           "Data Source='C:\Programs\VideoCollection.mdb'", _
                           "", "", 0)
            conVideos.Execute("CREATE TABLE Videos(" & _
                              "ShelfNumber  COUNTER, " & _
                              "VideoTitle   STRING NOT NULL, " & _
                              "Director     STRING NULL, " & _
                              "YearReleased SHORT, " & _
                              "Rating       BYTE NULL);", _
                              obj, 0)
            Console.WriteLine("A new table named Videos has been created")
        Finally
            conVideos.Close()
        End Try
    End Sub

End Module

By default, when you apply the COUNTER or the AUTOINCREMENT data type, when the user creates the first record, the field int the auto-incrementing column receives a number of 1. If the user creates a second record, the auto-incrementing value receives a number of 2, and so on. If you want, you can make the first record receive a number other than 1. You can also make it increment to a value other than 1. To apply this feature, the COUNTER and the AUTOINCREMENT types use a seed as their parentheses:

COUNTER(x,y)

or

AUTOINCREMENT(x,y)

The x value represents the starting value of the records. The y value specifies how much would be added to a value to get the next.

When reviewing the data types available for columns, we saw that some of them could use a string-based data type, namely TEXT, CHAR, or VARCHAR. By default, if you create a table and you set a column's data type to TEXT, CHAR, or VARCHAR, it is made to hold 255 characters. If you want, you can control the maximum number of characters that would be allowed in a column during data entry.

To specify the number of characters of the string-based column, add the parentheses to the TEXT, the CHAR, or the VARCHAR data types, and in the parentheses, enter the desired number. Here are examples:

Imports ADODB

Module Central

    Sub Main()
        Dim conVideos As New Connection
        Dim obj As Object

        obj = Nothing

        Try
            conVideos.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                           "Data Source='C:\Programs\VideoCollection.mdb'", _
                           "", "", 0)
            conVideos.Execute("CREATE TABLE Videos(" & _
                              "ShelfNumber  COUNTER, " & _
                              "VideoTitle   STRING(120) NOT NULL, " & _
                              "Director     VARCHAR(80) NULL, " & _
                              "YearReleased SHORT, " & _
                              "Rating       TEXT(20) NULL);", _
                              obj, 0)
            Console.WriteLine("A new table named Videos has been created")
        Finally
            conVideos.Close()
        End Try
    End Sub

End Module

A default value allows a column to use a value that is supposed to be common to most cells of a particular column. The default value can be set as a constant value or it can use a function that would adapt to the time the value is needed.

To specify a default value, after the name and the data type of a column, type DEFAULT and assign it the desired value, based on the data type. Here is an example:

Imports ADODB

Module Central

    Sub Main()
        Dim conVideos As New Connection
        Dim obj As Object

        obj = Nothing

        Try
            conVideos.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                           "Data Source='C:\Programs\VideoCollection.mdb'", _
                           "", "", 0)
            conVideos.Execute("CREATE TABLE Videos(" & _
                              "ShelfNumber  COUNTER, " & _
                              "VideoTitle   STRING(120) NOT NULL, " & _
                              "Director     VARCHAR(80) NULL, " & _
                              "YearReleased SHORT, " & _
                              "Rating       TEXT(20) NULL Default='PG-13')", _
                              obj, 0)
            Console.WriteLine("A new table named Videos has been created")
        Finally
            conVideos.Close()
        End Try
    End Sub

End Module