Data Entry Assistance

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:

System::Void btnVideoCollection_Click(System::Object^  sender, 
		System::EventArgs^ e)
{
	 ADOX::Catalog ^ catADOX = gcnew ADOX::Catalog;

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

System::Void btnVideos_Click(System::Object^  sender, System::EventArgs^  e)
{
	 ADODB::Connection ^ conVideos = gcnew ADODB::Connection;
	 Object ^obj = gcnew Object;

	 conVideos->Open(L"Provider=Microsoft.Jet.OLEDB.4.0;" +
                         L"Data Source='C:\\Programs\\VideoCollection.mdb'",
			 L"", L"", 0);
	 conVideos->Execute(L"CREATE TABLE Videos(" +
		                L"VideoTitle   STRING NOT NULL, " +
				L"Director     STRING NULL, " +
				L"YearReleased SHORT, " +
				L"Rating       BYTE NULL);",
				obj, 0);
	 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.

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:

System::Void btnVideos_Click(System::Object^  sender, System::EventArgs^  e)
{
	 ADODB::Connection ^ conVideos = gcnew ADODB::Connection;
	 Object ^obj = gcnew Object;

	 conVideos->Open(L"Provider=Microsoft.Jet.OLEDB.4.0;" +
		             L"Data Source='C:\\Programs\\VideoCollection.mdb'",
			 L"", L"", 0);
	 conVideos->Execute(L"CREATE TABLE Videos(" +
		                L"ShelfNumber  COUNTER, " + 
		                L"VideoTitle   STRING NOT NULL, " +
				L"Director     STRING NULL, " +
				L"YearReleased SHORT, " +
				L"Rating       BYTE NULL);",
				obj, 0);
	 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:

System::Void btnVideos_Click(System::Object^  sender, System::EventArgs^  e)
{
	 ADODB::Connection ^ conVideos = gcnew ADODB::Connection;
	 Object ^obj = gcnew Object;

	 conVideos->Open(L"Provider=Microsoft.Jet.OLEDB.4.0;" +
		             L"Data Source='C:\\Programs\\VideoCollection.mdb'",
			 L"", L"", 0);
	 conVideos->Execute(L"CREATE TABLE Videos(" +
		                L"ShelfNumber  COUNTER, " +
				L"VideoTitle   STRING(120) NOT NULL, " +
				L"Director     VARCHAR(80) NULL, " +
				L"YearReleased SHORT, " +
				L"Rating       TEXT(20) NULL);",
				obj, 0);
	 conVideos->Close();
}

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:

System::Void btnVideos_Click(System::Object^  sender, System::EventArgs^  e)
{
	 ADODB::Connection ^ conVideos = gcnew ADODB::Connection;
	 Object ^obj = gcnew Object;

	 conVideos->Open(L"Provider=Microsoft.Jet.OLEDB.4.0;" +
		             L"Data Source='C:\\Programs\\VideoCollection.mdb'",
			 L"", L"", 0);
	 conVideos->Execute(L"CREATE TABLE Videos(" +
		                L"ShelfNumber  COUNTER, " +
				L"VideoTitle   STRING(120) NOT NULL, " +
				L"Director     VARCHAR(80) NULL, " +
				L"YearReleased SHORT, " +
				L"Rating       TEXT(20) NULL Default='PG-13');",
				obj, 0);
	 conVideos->Close();
}