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 Studio
  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
  5. Click OK
  6. In the Server Explorer, expand server.CPAR3.dbo
  7. Under it, right-click Tables and click Add New Table
  8. As the caret is blinking in the first empty box under Column Name, type ReceiptNumber and press Tab
  9. Set its data type to int

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 date Unchecked
    OrderTime time(7)  
    CustomerName nvarchar(50) Unchecked
    CarMake nvarchar(50)  
    CarModel nvarchar(50) Unchecked
    CarYear int Unchecked
    ProblemDescription ntext Unchecked
    TotalParts money Unchecked
    TotalLabor money Unchecked
    TotalOrder money  
    Recommendations ntext  

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.Linq;
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. 

An Identity Column

 

Introduction

One of the goals of a good table is to be able to uniquely identity each record. In most cases, the database engine should not confuse two records. Consider the following table:

Category Item Name Size Unit Price
Women Long-sleeve jersey dress Large 39.95
Boys Iron-Free Pleated Khaki Pants S 39.95
Men Striped long-sleeve shirt Large 59.60
Women Long-sleeve jersey dress Large 45.95
Girls Shoulder handbag   45.00
Women Continental skirt Petite 39.95

Imagine that you want to change the value of an item named Long-sleeve jersey dress. Because you must find the item programmatically, you can start looking for an item with that name. This table happens to have two items with that name. You may then decide to look for an item using its category. In the Category column, there are too many items named Women. In the same way, there are too many records that have a Large value in the Size column, same problem in the Unit Price column. This means that you don't have a good criterion you can use to isolate the record whose Item Name is Long-sleeve shirt.

To solve the problem of uniquely identifying a record, you can create a particular column whose main purpose is to distinguish one record from another. To assist you with this, the SQL allows you to create a column whose data type is an integer type but the user does not have to enter data for that column. A value would automatically be entered into the field when a new record is created. This type of column is called an identity column.

You cannot create an identity column on an existing table, only on a new table.

Visually Creating an Identity Column

To create an identity column, if you are visually working in the design view of the table, in the top section, specify the name of the column. By tradition, the name of this column resembles that of the table but is singular. Also, by habit, the name of the column ends with _id, Id, or ID.

After specifying the name of the column, set its data type to an integer-based type. Usually, the data type used is int. In the bottom section, click and expand the Identity Specification property. The first action you should take is to set its (Is Identity) property from No to Yes.

Once you have set the value of the (Is Identity) property to Yes, the first time the user performs data entry, the value of the first record would be set to 1. This characteristic is controlled by the Identity Seed property. If you want the count to start to a value other than 1, specify it on this property.

After the (Is Identity) property has been set to Yes, the SQL interpreter would increment the value of each new record by 1, which is the default. This means that the first record would have a value of 1, the second would have a value of 2, and so on. This aspect is controlled by the Identity Increment property. If you want to increment by more than that, you can change the value of the Identity Increment property.

Practical LearningPractical Learning: Creating an Identity Column

  1. In the top section of the table, click ReceiptNumber
  2. In the lower section of the table, expand Identity Specification and double-click (Is Identity) to set its value to Yes
  3. Click Identity Seed and type 100000
     
    Visually Creating an Identity Column

Creating an Identity Column Using SQL

If you are programmatically creating a column, to indicate that it would be used as an identity column after its name and data type, type identity followed by parentheses. Between the parentheses, enter the seed value, followed by a comma, followed by the increment value. Here is an example:

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 StoreItems( " +
		 	   "StoreItemID int IDENTITY(1, 1) NOT NULL, " +
			   "Category varchar(50), " +
			   "[Item Name] varchar(100) NOT NULL, " +
			   "Size varchar(20), " +
			   "[Unit Price] money);",
			   connection);
	connection.Open();
	command.ExecuteNonQuery();

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

Data Entry With an Identity Column

After creating an identity column, when performing data entry, don't specify a value for that column. Here is an example:

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

namespace ExoDB1
{
    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='Exercise';" +
                                  "Integrated Security=yes;"))
            {
                SqlCommand command =
                    new SqlCommand("CREATE TABLE StoreItems" +
                                   "(" +
                                   "ItemID int identity(1, 1) NOT NULL, " +
                                   "Category nvarchar(50)," +
                                   "[Item Name] nvarchar(100) NOT NULL," +
                                   "Size nvarchar(20)," +
                                   "[Unit Price] money);" +
                  "INSERT INTO StoreItems(Category, [Item Name], Size, [Unit Price])" +
                  "VALUES(N'Men', N'Simplicity Leather Dress Oxfords', N'9', 65.85);",
                           connection);
                connection.Open();
                command.ExecuteNonQuery();

                MessageBox.Show("A new table named StoreItems has been created.");
            }
        }
    }
}

If you provide a value for the identity column, you would receive an error.

Creating a Value for an Identity Column

If you want to specify a value for the identity column, call the SET IDENTITY_INSERT flag. The formula it uses is:

SET IDENTITY_INSERT [ database_name.[schema_name].] table { ON | OFF }

The database_name is the optional name of the database that owns the table. If you previously use the USE statement, in most cases, you can omit the name of the database. The schema_name is the (optional) name of the schema in which the table was created. The table factor is the name of the table the identity column belongs to. After specifying the name of the table, set the flag as ON to allow a value for the identity column, or OFF to disallow it.

If you decide to use the SET IDENTITY_INSERT, you must provide a list of columns after the name of the table in the INSERT or INSERT INTO statement. Here is an example:

private void btnDatabase_Click(object sender, EventArgs e)
{
    using (SqlConnection connection = new SqlConnection("Data Source=(local);" +
                                                        "Database='Exercise';" +
                                                        "Integrated Security=yes;"))
    {
        SqlCommand command =
            new SqlCommand("SET IDENTITY_INSERT StoreItems ON;" +
                           "INSERT INTO StoreItems(ItemID, Category, [Item Name], Size, [Unit Price])" +
                           "VALUES(10, N'Girls', N'Girls 2-6X Short-Sleeved Mesh Polo Shirt', N'2T', 18.95);",
                           connection);
        connection.Open();
        command.ExecuteNonQuery();

        MessageBox.Show("A new record has been added to the StoreItems table.");
    }
}

This time, the data entry would not produce an error.

As mentioned already, after setting the IDENTITY_INSERT ON, you can put it back OFF and add a record without a value for the identity column. Here is an example:

SET IDENTITY_INSERT StoreItems ON;
GO
INSERT INTO StoreItems(ItemID, Category, [Item Name], Size, [Unit Price])
VALUES(50, N'Men', N'Simplicity Leather Dress Oxfords', N'10.5', 65.85);
GO
SET IDENTITY_INSERT StoreItems OFF;
GO
INSERT INTO StoreItems(Category, [Item Name], Size, [Unit Price])
VALUES(N'Women', N'Fiona High Heel Boot', N'6.50', 295.00);
GO
INSERT INTO StoreItems(Category, [Item Name], Size, [Unit Price])
VALUES(N'Women', N'Saddle East/West Leather Hobo Bag', N'Medium', 345.50);
GO

If you do this, the next value of the identity column would be the increment from the previous value.

 

The Default Value of a Column

 

Introduction

Sometimes most records under a certain column may hold the same value although just a few would be different. For example, if a school is using a database to register its students, all of them are more likely to be from the same state. In such a case, you can assist the user by automatically providing a value for that column. The user would then simply accept the value and change it only in the rare cases where the value happens to be different. To assist the user with this common value, you create what is referred to as a default value.

Visually Creating a Default Value

You can create a default value of a column when creating a table. To specify the default value of a column, in the top section, click the column. In the bottom section, click Default Value or Binding, type the desired value following the rules of the column's data type:

It the Data Type is Intructions
Text-based (char, varchar, text, and their variants) Enter the value in single-quotes
Numeric-based Enter the value as a number but following the rules of the data type.
For example, if you enter a value higher than 255 for a tinyint, you would receive an error
Date or Time Enter the date as either MM/DD/YYYY or YYYY/MM/DD. You can optionally include the date in single-quotes.
Enter the time following the rules set in the Control Panel (Regional Settings).
Bit Enter the value as 0 for FALSE or any other long integer value for TRUE

Programmatically Creating a Default Value

To specify the default value in a SQL statement, when creating the column, before the semi-colon or the closing parenthesis of the last column, type the DEFAULT keyword followed by the desired value. Here are examples:

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 Employees " +
			   "( " +
			   "FullName VARCHAR(50), " +
			   "Address VARCHAR(80), " +
			   "City VARCHAR(40), " +
			   "State VARCHAR(40) DEFAULT 'NSW', " +
			   "PostalCode VARCHAR(4) DEFAULT '2000', " +
			   "Country VARCHAR(20) DEFAULT 'Australia');",
			   connection);
	connection.Open();
	command.ExecuteNonQuery();

	MessageBox.Show("A new table named Employees has been created");
    }
}
 

After creating the table, the user does not have to provide a value for a column that has a default value. If the user does not provide the value, the default would be used when the record is saved.

Author Note If the user provides a value for a column that has a default value and then deletes the value, the default value rule would not apply anymore: The field would simply become empty.
 

Using Expressions For Data Entry

 

Introduction

There are various ways you can assist the user with data entry. You can create an expression using one or a combination of arithmetic and/or SQL operators. You can create an expression when creating a table.

Visually Creating an Expression

To create an expression when visually creating a table, in the top section, specify the column's name (only the column name is important). In the bottom section, expand the Computed Column Specification field and, in its (Formula) field, enter the desired expression.

Practical LearningPractical Learning: Creating an Expression Column

  1. In the top section, click TotalOrder
  2. In the bottom section, expand the Computed Column Specification field
  3. Click (Formula), type TotalParts + TotalLabor, and press Enter
     
    Expression
  4. To save the table, on the Standard toolbar, click the Save button Save
  5. Set the name to RepairOrders
  6. Click OK
  7. Close the table by clicking its Close button Close
  8. To open the table to show its records, in the Server Explorer, right-click RepairOrders and click Show Table Data
  9. Enter the following values in the fields:
     
    OrderDate OrderTime CustomerName CarMake CarModel CarYear ProblemDescription TotalParts TotalLabor
    10/08/2010 08:12 James Cranston Honda Accord 2004 None 125.58 150
    06/04/2010 14:07 Margaret Arndt Ford Focus 2002 Tires Replacement 262.32 102.60
  10. Close the table
  11. In the Server Explorer, right-click Server.CPAR3.dbo and click Delete
  12. When asked for a confirmation, click Yes

Creating a SQL Expression

You can also programmatically create an expression in a SQL expression you are using to create a table. To do this, in the placeholder of the column, enter the name of the column, followed by AS, and followed by the desired expression. Here is an example:

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 Circle( " +
			   "CircleID int identity(1,1) NOT NULL, " +
			   "Radius decimal(8, 3) NOT NULL, " +
			   "Area AS Radius * Radius * PI());",
			   connection);
	connection.Open();
	command.ExecuteNonQuery();

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

Using an Expression During Data Entry

When performing data entry, you must not provide a value for a column that has an expression; the SQL interpreter would provide the value automatically. Here is an example of entering data for the above Circle table:

INSERT INTO Circle(Radius) VALUES(46.82);
GO
INSERT INTO Circle(Radius) VALUES(8.15);
GO
INSERT INTO Circle(Radius) VALUES(122.57);
GO
 

Check Constraints

 

Introduction

When performing data entry, in some columns, even after indicating the types of values you expect the user to provide for a certain column, you may want to restrict a range of values that are allowed. To assist you with checking whether a newly entered value fits the desired range, Transact-SQL provides what is referred to as a check constraint.

A check constraint is a Boolean operation performed by the SQL interpreter. The interpreter examines a value that has just been provided for a column. If the value is appropriate:

  1. The constraint produces TRUE
  2. The value gets accepted
  3. The value is assigned to the column

If the value is not appropriate:

  1. The constraint produces FALSE
  2. The value gets rejected
  3. The value is not assigned to the column

You create a check constraint at the time you are creating a table.

Visually Creating a Check Constraint

To create a check constraint, when creating a table, right-click anywhere in (even outside) the table and click Check Constraints...

Check Constraints

This would open the Check Constraints dialog box. From that window, you can click Add. Because a constraint is an object, you must name it. The most important piece of information that a check constraint should hold is the mechanism it would use to check its values. This is provided as an expression. Therefore, to create a constraint, you can click Expression and click its ellipsis button. This would open the Check Constraint Expression dialog box.

To create the expression, first type the name of the column on which the constraint will apply, followed by parentheses. In the parentheses, use the arithmetic and/or SQL operators we studied already. Here is an example that will check that a new value specified for the Student Number is greater than 1000:

Check Constraint Expression

After creating the expression, you can click OK. If the expression is invalid, you would receive an error and given the opportunity to correct it.

You can create as many check constraints as you judge necessary for your table:

Check Constraints

After creating the check constraints, you can click Close.

Programmatically Creating a Check Constraint

To create a check constraint in SQL, first create the column on which the constraint will apply. Before the closing parenthesis of the table definition, use the following formula:

CONSTRAINT name CHECK (expression

The CONSTRAINT and the CHECK keywords are required. As an object, make sure you provide a name for it. Inside the parentheses that follow the CHECK operator, enter the expression that will be applied. Here is an example that will make sure that the hourly salary specified for an employee is greater than 12.50:

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 Employees( " +
		"[Employee Number] nchar(7), " +
		"[Full Name] varchar(80), " +
		"[Hourly Salary] smallmoney, " +
		"CONSTRAINT CK_HourlySalary CHECK ([Hourly Salary] > 12.50));",
		connection);

	connection.Open();
	command.ExecuteNonQuery();

	MessageBox.Show("A new table named Employees has been created");
    }
} 

It is important to understand that a check constraint is neither an expression nor a function. A check constraint contains an expression and may contain a function as part of its definition.

After creating the constraint(s) for a table, in the Object Explorer of the Microsoft SQL Server Management Studio, inside the table's node, there is a node named Constraints and, if you expand it, you would see the name of the constraint.

Using a Check Constraint

With the constraint(s) in place, during data entry, if the user (or your code) provides an invalid value, an error would display. Instead of an expression that uses only the regular operators, you can use a function to assist in the checking process. You can create and use your own function. You can use one of the built-in Transact-SQL functions. Or you can use a method from a built-in .NET Framework class.

Other Features of Data Entry

 

Is RowGuid

This property allows you to specify that a column with the Identity property set to Yes is used as a ROWGUID column.

Data Import

Another technique used to get data into one or more tables consists of importing already existing data from another database or from any other recognizable data file. Microsoft SQL Server provides various techniques and means of importing data.

One of the easiest types of data that can be imported into SQL Server, and which is available on almost all database environments, is the text file. Almost every database environment allows you to import a text file but data from that file must be formatted appropriately. For example, the information stored in the file must define the columns as distinguishable by a character that serves as a separator. This separator can be the single-quote, the double-quote, the comma, or any valid character. Data between the quotes is considered as belonging to a distinct field. The last piece of information the file must provide is to distinguish each record from another. This is easily taken car of by the end of line of a record. This is also recognized as the carriage return.

These directives can help you manually create a text file that can be imported into Microsoft SQL Server. In practicality, if you want to import data that resides on another database, you can ask that application to create the source of data. Most applications can do that and format the data.

Records and Permissions

 

Introduction

As far as users are concerned, the primary reason for using a database application is to view and/or create records. You on the other hand need to control who has access to a table and what a particular user can do on it. Fortunately, both C# and Transact-SQL can let you control all types of access to the records of any table of your database. As seen for databases, you can grant or deny access to a table, to some users individually or to a group of users.

Before exercising security on a table for a user, you must have created a user account for that user.

Managing Permissions on a Table

To visually grant or deny operations at the table level, in the Object Explorer of Microsoft SQL Server, right-click the table and click Properties. In the Select a Page list, click Permissions. In the Users or Roles list, click the name of the user or click Select to locate the user. In the Permissions column, locate the type of permission you want. Manage the operations in the Grant and in the Deny columns.

The basic formula to programmatically grant one or more permissions to a user is:

GRANT Permission1,Permission2, Permission_n
ON [ OBJECT :: ][ schema_name ].object_name [ (Column1, Column2, Column_n ] ) ]
TO Login1, Login2, Login_n ]

The basic formula to programmatically deny (a) permission(s) is:

DENY Permission1,Permission2, Permission_n
ON [ OBJECT :: ][ schema_name ].object_name [ (Column1, Column2, Column_n ] ) ]
TO Login1, Login2, Login_n ]

You start with the GRANT (or DENY) keyword. To grant a permission, type it. After specifying the types of permissions you want, type ON or ON OBJECT::. This is followed by the name of the object, that is, the table, on which you want to grant permissions. If necessary, or this is optional, precede the name of the object with the name of the schema. After the name of the object, type TO, followed by the login name that will receive the permission. Here is an example:

USE master;
GO
CREATE DATABASE Exercise1;
GO
USE Exercise1;
GO
CREATE TABLE Employees
(
    EmployeeNumber nchar(10),
    FirstName nvarchar(20),
    LastName nvarchar(20),
);
GO
CREATE USER [Peter Mukoko]
FOR LOGIN rkouma;
GO

GRANT ALTER 
ON OBJECT::Employees
TO [Peter Mukoko];
GO

If you want to combine permissions, separate them with commas. Here is an example:

USE Exercise1
GRANT INSERT, UPDATE
ON OBJECT::dbo.Payroll
TO [James Galvin];
GO

If you wan to grant the permission(s) to more than one account, separate them with commas.

The permissions of a table are very interconnected. This means that giving one type of access may not be enough to achieve the right result. This also means that you must know how to combine permissions:

  • ALTER: If this permission is granted, a user can change the structure (design) of a table. For example, a person can add a new column or delete an existing column. In most cases, regular users should be denied this right
  • INSERT: This permission allows a user to create new records. If you don't want a user to add new records to the table, then deny this permission. If you do, when the user tries to add a new record, he or she would receive an error. Here is an example:
     
    Permission: INSERT
    As this indicates, the user would be denied the ability to create a new record
  • UPDATE: If granted, the UPDATE permission allows the user to change or edit existing records. If this permission is denied, the user would receive an error when trying to save a record that has been changed. Here is an example:
    Permission: UPDATE
  • DELETE: This permission allows a user to delete one or more records. The reverse is to deny this right so the user can only see the records but cannot remove them

Extending Permissions

As mentioned for a database, you can give one account the ability to grant or deny permissions to other accounts. To do this visually, access the Database Properties for the database. In the Users or Roles section, select the user. In the Persmissions, use the check boxes in the With Grant column.

The formula to programmatically give an account the ability to grant or deny permissions to other accounts is:

GRANT Permission1,Permission2, Permission_n
TO Login1, Login2, Login_n
WITH GRANT OPTION

In this formula, you add the WITH GRANT OPTION expression.

   
 

Previous Copyright © 2010-2011 C#Key Home