The Records of a Database

Data Entry Fundamentals

 

Introduction

After creating a table and its column(s), you can populate the database with data. You and the user can use either the table or the form but as mentioned previously, the form is sometimes the appropriate object to do this. Data entry consists of filling a database with the necessary values. A series of values that corresponds to same levels of columns is called a row or a record. 

Here are the database and the table we will use:

Private Sub btnPersons_Click(ByVal sender As System.Object, _

		ByVal e As System.EventArgs) Handles btnPersons.Click

        Dim catPeople As New ADOX.CatalogClass

        Dim conPeople As New ADODB.ConnectionClass

        Dim strSQL As String



        catPeople.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _

                         "Data Source='C:\Programs\People.mdb';")

        MsgBox("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'", Nothing, Nothing, 0)

        strSQL = "CREATE TABLE Persons(" & _

                 "PersonID COUNTER NOT NULL, " & _

                 "FirstName Text(20), " & _

                 "LastName Text(20), " & _

                 "Gender Text(20));"

        conPeople.Execute(strSQL, 0, 0)

        MessageBox.Show("A table named Persons has been created in the " & _

                        "People.mdb database")



        conPeople.Close()

End Sub

This creates a Microsoft JET database named People, followed by a table named Persons, and populates it with four records. 

New Record Creation

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.

Adjacent Data entry

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:

Private Sub btnCreateRecord_Click(ByVal sender As System.Object, _

		ByVal e As System.EventArgs) Handles btnCreateRecord.Click

        Dim conPeople As New ADODB.ConnectionClass



        conPeople.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

                       "Data Source='C:\Programs\People.mdb'", Nothing, Nothing, 0)

        conPeople.Execute("INSERT INTO Persons(FirstName, LastName, Gender) " & _

                                 "VALUES('James', 'Carlton', 'Male');")

        MsgBox("A new record has been created in the Persons table")

        conSchoolRecords.Close()

End Sub

 

Random Data Entry

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:

Private Sub btnCreateRecord_Click(ByVal sender As System.Object, _

                    ByVal e As System.EventArgs) Handles btnCreateRecord.Click

        Dim conPeople As New ADODB.ConnectionClass



        conPeople.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

                       "Data Source='C:\Programs\People.mdb'", Nothing, Nothing, 0)

  conPeople.Execute("INSERT INTO Persons(LastName, Gender, FirstName) " & _

                             "VALUES('Germain', 'Male', 'Ndongo');")

        MsgBox("A new record has been created in the Persons table")

        conPeople.Close()

End Sub

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