Home

Data Entry Assistance

 

The Nullity of a Field

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:

private void btnVideoCollection_Click(object sender, EventArgs e)
{
       ADOX.Catalog catADOX = new ADOX.Catalog();

       catADOX.Create("Provider=Microsoft.Jet.OLEDB.4.0;" +
                      "Data Source='C:\\Programs\\VideoCollection.mdb';");
       MessageBox.Show("A new Microsoft JET database named VideoCollection.mdb " +
                       "has been created");
}

private void btnVideos_Click(object sender, EventArgs e)
{
            ADODB.Connection conVideos = new ADODB.Connection();
            object obj = new object();

            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);",
                       out obj, 0);
            MessageBox.Show("A new table named Videos has been created");
            conVideos.Close();
}

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.

Auto-Increment

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:

private void btnVideos_Click(object sender, EventArgs e)
{
            ADODB.Connection conVideos = new ADODB.Connection();
            object obj = new object();

            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);",
                       	      out obj, 0);
            MessageBox.Show("A new table named Videos has been created");
            conVideos.Close();
}

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.

Fields Sizes

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:

private void btnVideos_Click(object sender, EventArgs e)
{
            ADODB.Connection conVideos = new ADODB.Connection();
            object obj = new object();

            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);",
                       out obj, 0);
            MessageBox.Show("A new table named Videos has been created");

	 conVideos.Close();
}
 

Default Values

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:

private void btnVideos_Click(object sender, EventArgs e)
{
            ADODB.Connection conVideos = new ADODB.Connection();
            object obj = new object();

            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')",
                              out obj, 0);
            MessageBox.Show("A new table named Videos has been created");
            conVideos.Close();
}

 

 

Previous Copyright © 2005-2007 FunctionX, Inc.