Home

Assistance With Data Entry

 

The Nullity of a Field

 

Introduction

During data entry, users of your database face fields that expect data. Sometimes, for one reason or another, data will not be available for a particular field. An example would be an MI (middle initial) field: some people have a middle initial, some others either don't have it or would not (or cannot) provide it. This aspect can occur for any field of your table. Therefore, you should think of a way to deal with it.

A field is referred to as null when no data entry has been made to it:

  • Saying that a field is null doesn't mean that it contains 0 because 0 is a value
  • Saying that a field is null doesn't mean that it is empty. A field being empty could mean that the user had deleted its content or that the field itself would not accept what the user was trying to enter into that field, but an empty field can have a value

A field is referred to as null if there is no way of determining the value of its content (in reality, the computer, that is, the operating system, has its own internal mechanism of verifying the value of a field) or its value is simply unknown. As you can imagine, it is not a good idea to have a null field in your table. As a database developer, it is your responsibility to always know with certainty the value held by each field of your table.

A field is referred to as required if the user must provide a value for it before moving to another record. In other words, the field cannot be left empty during data entry.

Practical LearningPractical Learning: Introducing Database Records

  1. Start Microsoft Visual C#
  2. In the Server Explorer, right-click Data Connections and click Add New SQL Server Database
  3. In the Server Name combo box, select the server or type (local)
  4. Set the name of the database to CPAR3 and click OK
  5. In the Server Explorer, expand server.CPAR3.dbo
  6. Under it, right-click Tables and click Add New Table
  7. As the caret is blinking in the first empty box under Column Name, type ReceiptNumber and press Tab
  8. Set its data type to int
  9. To save the table, on the Standard toolbar, click the Save button
  10. Set the name to RepairOrders and click OK

Visually Setting the Nullity of a Field

To solve the problem of null and required fields, Microsoft SQL Server proposes one of two options: allow or not allow null values on a field. For a typical table, there are pieces of information that the user should make sure to enter; otherwise, the data entry would not be validated. To make sure the user always fills out a certain field before moving to the next field, that is, to require the value, if you are visually creating the table, clear the Allow Nulls check box for the field. On the other hand, if the value of a field is not particularly important, for example if you don't intend to involve that value in an algebraic operation, check its Allow Nulls check box.

Practical LearningPractical Learning: Applying Fields Nullity

  1. Under Allow Nulls, in the first check box that corresponds to the ReceiptNumber, click the check box to clear it
  2. Complete the nullity of the fields as follows:
     
    Column Name Data Type Allow Nulls
    ReceiptNumber int Unchecked
    OrderDate datetime  
    OrderTime datetime  
    CustomerName varchar(80) Unchecked
    CarMake varchar(50)  
    CarModel varchar(50) Unchecked
    CarYear int Unchecked
    ProblemDescription text Unchecked
    TotalParts money Unchecked
    TotalLabor money Unchecked
    TotalOrder money  
    Recommendations text  
  3. Save the table

Programmatically Setting the Nullity of a Field

If you are programmatically creating the table using SQL, to specify that a column can allow null values, type NULL on the right side of the column definition. To specify that the values of the column are required, on the right side, type NOT NULL. If you do not specify NULL or NOT NULL, the column will be created as NULL. Here are examples:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Exercise
{
    public partial class Exercise : Form
    {
	public Exercise()
	{
	    InitializeComponent();
	}

	private void btnDatabase_Click(object sender, EventArgs e)
	{
	    using (SqlConnection connection =
		new SqlConnection("Data Source=(local);" +
				  "Database='Exercise1';" +
				  "Integrated Security=yes;"))
	    {
		SqlCommand command =
		    new SqlCommand("CREATE TABLE Persons( " +
				   "FirstName varchar(20) NULL, " +
				   "LastName varchar(20) NOT NULL, " +
				   "Gender smallint);",
				   connection);
		connection.Open();
		command.ExecuteNonQuery();

		MessageBox.Show("A new table named Persons has been crated.");
	    }
	}
    }
}

If the table was created already and it holds some values, you cannot change its nullity option. 

 

Published on Thursday 03 January 2008

 

Home Copyright © 2007 FunctionX, Inc. Next