Assistance With Data Entry

To programmatically 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:

Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
        Using Connect As SqlConnection = _
  		New SqlConnection("Data Source=(local);" & _
      				  "Database='Exercise';" & _
      				  "Integrated Security=yes;")

            Dim Command As SqlCommand = _
      		 New SqlCommand("CREATE TABLE Persons( " & _
       				"FirstName nvarchar(20) NULL, " & _
       				"LastName nvarchar(20) NOT NULL, " & _
       				"Gender smallint);", _
       				Connect)
            Connect.Open()
            Command.ExecuteNonQuery()

            MsgBox("A new table named Persons has been crated.")
        End Using
    End Sub
End Class

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

One of the goals of a good table is to be able to uniquely identity each record. To solve this problem, you can create a particular column whose main purpose is to distinguish one record from another.

To visually an identity column, in the top section, specify the name of the column. Then, 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.

To programmatically create an identity column, 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:

Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
        Using Connect As SqlConnection = _
 	      New SqlConnection("Data Source=(local);" & _
     				"Database='Exercise';" & _
     				"Integrated Security=yes;")

            Dim Command As SqlCommand = _
     		New SqlCommand("CREATE TABLE StoreItems( " & _
       			"StoreItemID int IDENTITY(1, 1) NOT NULL, " & _
      			"Category nvarchar(50), " & _
      			"[Item Name] nvarchar(100) NOT NULL, " & _
      			"Size nvarchar(20), " & _
      			"[Unit Price] money);", _
      			Connect)
            Connect.Open()
            Command.ExecuteNonQuery()

            MsgBox("A new table named StoreItems has been crated.")
        End Using
    End Sub
End Class 

Sometimes, most records under a certain column may hold the same value although just a few would be different. To assist the user with this common value, you create what is referred to as 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.

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 value. Here are examples:

Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
        Using Connect As SqlConnection = _
 	      New SqlConnection("Data Source=(local);" & _
     				"Database='Exercise';" & _
     				"Integrated Security=yes;")

            Dim Command As SqlCommand = _
                New SqlCommand("CREATE TABLE Employees " & _
                 	"( " & _
                 	"FullName NVARCHAR(50), " & _
                 	"Address NVARCHAR(80), " & _
                 	"City NVARCHAR(40), " & _
                 	"State NVARCHAR(40) DEFAULT 'NSW', " & _
                 	"PostalCode NVARCHAR(4) DEFAULT '2000', " & _
                 	"Country NVARCHAR(20) DEFAULT 'Australia');", _
                 	Connect)
            Connect.Open()
            Command.ExecuteNonQuery()

            MsgBox("A new table named Employees has been created")
        End Using
    End Sub
End Class

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. 

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.

To visually create an expression when 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.

To programmatically create an expression, 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:

Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
        Using Connect As SqlConnection = _
 		New SqlConnection("Data Source=(local);" & _
     			"Database='Exercise';" & _
     			"Integrated Security=yes;")

            Dim Command As SqlCommand = _
                New SqlCommand("CREATE TABLE Circle( " & _
                 	"CircleID int identity(1,1) NOT NULL, " & _
                 	"Radius decimal(8, 3) NOT NULL, " & _
                 	"Area AS Radius * Radius * PI());", _
                 		Connect)
            Connect.Open()
            Command.ExecuteNonQuery()

            MsgBox("A new table named Circle has been crated.")
        End Using
    End Sub
End Class

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:

Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
    Using Connect As SqlConnection = _
 		New SqlConnection("Data Source=(local);" & _
     				  "Database='Exercise';" & _
     				  "Integrated Security=yes;")

        Dim Command As SqlCommand = _
                New SqlCommand("INSERT INTO Circle(Radius) VALUES(46.82);" & _
                               "INSERT INTO Circle(Radius) VALUES(8.15);" & _
                               "INSERT INTO Circle(Radius) VALUES(122.57);", _
                               Connect)
        Connect.Open()
        Command.ExecuteNonQuery()

        MsgBox("A few records have been added to the table.")
    End Using
    End Sub
End Class

Some operations are difficult or even impossible to obtain with an expression, or the operation could become too complex to achieve. The alternative is to create a function that would take care of performing the operation and supplying the result to the table.

In order to involve a function with your data entry, you must have one. You can create your own function. Here is an example:

-- =============================================
-- Author: FunctionX
-- Create date: Saturday 22 December 2007
-- Description: Used to calculate the greatest common divisor
-- =============================================
CREATE FUNCTION GCD
(
    @a int, @b int
)
RETURNS int
AS
    BEGIN
	DECLARE @Remainder int;

	WHILE @b <> 0
	    BEGIN
		SET @Remainder = @a % @b;
		SET @a = @b;
		SET @b = @Remainder;
	    END

    RETURN @a
END

When calling the function, follow the normal rules. Here are examples:

INSERT INTO Calculations VALUES(345, 135, dbo.GCD(345, 135));
GO
INSERT INTO Calculations VALUES(40, 6, dbo.GCD(40, 6));
GO
INSERT INTO Calculations VALUES(16, 28, dbo.GCD(16, 28));
GO 

You can use one of the built-in functions of Transact-SQL. For example, imagine you have a database named AutoRepairShop and imagine it has a table used to create repair orders for customers:

CREATE TABLE RepairOrders
(
  RepairID int Identity(1,1) NOT NULL,
  CustomerName nvarchar(50),
  CustomerPhone nvarchar(20),
  RepairDate DateTime
);
GO

When performing data entry for this table, you can let the user enter the customer name and phone number. On the other hand, you can assist the user by programmatically entering the current date. To do this, you would call the GETDATE() function. Here are examples:

INSERT INTO RepairOrders(CustomerName, CustomerPhone, RepairDate)
	    VALUES('Annette Berceau', '301-988-4615', GETDATE());
GO
INSERT INTO RepairOrders(CustomerPhone, CustomerName, RepairDate)
	    VALUES('(240) 601-3795', 'Paulino Santiago', GETDATE());
GO
INSERT INTO RepairOrders(CustomerName, RepairDate, CustomerPhone)
	    VALUES('Alicia Katts', GETDATE(), '(301) 527-3095');
GO
INSERT INTO RepairOrders(RepairDate, CustomerPhone, CustomerName)
	    VALUES(GETDATE(), '703-927-4002', 'Bertrand Nguyen');
GO

You can also involve the function in an operation, then use the result as the value to assign to a field. You can also call a function that takes one or more arguments; make sure you respect the rules of passing an argument to a function when calling it.

If none of the Transact-SQL built-in functions satisfies your requirements, you can create your own. 

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. A check constraint is a Boolean operation performed by the SQL interpreter.

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

This would open the Check Constraints dialog box.

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:

Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
        Using Connect As SqlConnection = _
 		New SqlConnection("Data Source=(local);" & _
     				  "Database='Exercise';" & _
     				  "Integrated Security=yes;")

            Dim Command As SqlCommand = _
                 New SqlCommand("DROP TABLE Employees;" & _
                                "CREATE TABLE Employees " & _
             			"( " & _
             			"[Employee Number] nchar(7), " & _
             			"[Full Name] nvarchar(80), " & _
             			"[Hourly Salary] smallmoney, " & _
             "CONSTRAINT CK_HourlySalary CHECK ([Hourly Salary] > 12.50) " & _
             			");", _
             			Connect)

            Connect.Open()
            Command.ExecuteNonQuery()

            MsgBox("A new table named Employees has been created")
        End Using
    End Sub
End Class