ADO.NET and VBasic - Lesson 05: Data Entry

	Data Entry

Fundamentals of Table Data Entry

Introduction

Data entry consists of populating a table with the necessary values it is supposed to hold. In the previous lessons, we saw that, to organize its data, a table is divided in sections called columns. The values common to an entry under each column constitute a row or record and a row is made of cells: as you may realize, everything we reviewed about the organization of a table, when studying data sets, is also valid here.

Data entry consists of filling the cells under the columns of a table.

Practical Learning: Introducing Data Entry

Start Notepad and type the following:

Imports System
Imports System.Data.SqlClient

Module Exercise

    Public Sub Main()
        Dim strCreate As String = "CREATE DATABASE WorldStatistics1"
        Dim conDatabase As SqlConnection = New _
         SqlConnection("Data Source=(local);Integrated Security=sspi;")
        Dim cmdDatabase As SqlCommand = New SqlCommand(strCreate, conDatabase)

        conDatabase.Open()

        cmdDatabase.ExecuteNonQuery()
        conDatabase.Close()

        strCreate = "CREATE TABLE Continents (" & _
           "ContinentName varchar(100)," & _
           "Area varchar(30), " & _
           "Population varchar(30))"
        conDatabase = New _
SqlConnection("Data Source=(local);Database='WorldStatistics1';Integrated Security=yes;")
        cmdDatabase = New SqlCommand(strCreate, conDatabase)

        conDatabase.Open()
        cmdDatabase.ExecuteNonQuery()

        strCreate = "CREATE TABLE Countries (" & _
           	    "CountryName varchar(120)," & _
           	    "Area varchar(30)," & _
           	    "Population varchar(30)," & _
           	    "Capital varchar(80)," & _
           	    "Code char(2))"

        cmdDatabase = New SqlCommand(strCreate, conDatabase)

        cmdDatabase.ExecuteNonQuery()
        conDatabase.Close()

    End Sub

End Module

To save the file, on the main menu, click File -> Save
Locate your VBasic folder and display it in the Save In combo box
Click the Create New Folder button. Type Countries3 and press Enter twice to display it in the Save In combo box
Change the Save As Type to All Files
Set the File Name to Exercise.vb
Open the Command Prompt and change to the above Countries4 folder
To compile the exercise, type vbc Exercise.vb and press Enter
To execute the application, type Exercise and press Enter
Return to Notepad

Data Entry Using the SQL

In the SQL, data entry is performed using the INSERT combined with the VALUES keywords. The primary statement uses the following formula:

INSERT TableName VALUES(Column1, Column2, Column_n)

Alternatively, or to be more precise, you can specify that you are entering data in the table using the INTO keyword between the INSERT keyword and the TableName factor. This is done with the following syntax:

INSERT INTO TableName VALUES(Column1, Column2, Column_n)

The TableName factor must be a valid name of an existing table in the database you are using. If the name is wrong, the SQL would simply consider that the table you are referring to doesn't exist. Consequently, you would receive an error.

The VALUES keyword indicates that you are ready to list the values of the columns. The values of the columns must be included in parentheses. The most common technique of performing data entry requires that you know the sequence of columns of the table in which you want to enter data. With this subsequent list in mind, enter the value of each field in its correct order in the parentheses of the above formula.

If the column is a BIT data type, you must specify one of its values as 0 or 1.

If the column is a numeric type, you should pay attention to the number you type. If the column was configured to receive an integer (int, bigint, smallint), you should provide a valid natural number without the decimal separator.

If the column is for a decimal number (float, real, decimal, numeric), you can type the value with its character separator (the period for US English).

If the column was created for a date data type, make sure you provide a valid date.

If the data type of a column is a string type, you should include its entry between single quotes. For example, a shelf number can be specified as 'HHR-604' and a middle initial can be given as 'D'.

Here is an example

INSERT Countries VALUES('Sweden',449964,8875053,'Stockholm','se')
GO

The list of values doesn't have to be typed on the same line. You can use one for each value. Here is example:

INSERT Country
VALUES
(
  'Angola',
  1246700,
  10593171,
  'Luanda',
  'ao'
)
GO

In the same way, the parentheses can be written on their own lines:

INSERT INTO Country VALUES
(
'Mongolia', 1565000, 2694432, 'Ulaanbaator','mn'
)
GO

The adjacent data entry we have used above requires that you know the order of columns of the table. If you don't know or don't want to follow the exact order of the columns, you can perform data entry with an order of your choice. This allows you to provide the values of fields in any order of your choice. We have just seen a few examples where the values of some of the fields are not available during data entry. Instead of remembering to type 0 or NULL for such fields or leaving empty quotes for a field you can use their names to specify the fields whose data you want to provide.

To perform data entry at random, you must provide a list of the fields of the table in the order of your choice. You can either use all columns or provide a list of the same columns but in your own order. In the same way, you don't have to provide data for all columns, just those you want, in the order you want. Here is an example:

INSERT Country(CountryName, Capital,InternetCode,Population,Area)
VALUES('Taiwan', 'Taipei', 'tw', 22548009, 35980)
GO

Here is another example:

INSERT
Country(InternetCode, CountryName, Capital,    Area)
VALUES(	'mx',	        'Mexico',       'Mexico', 1972550)
GO

Instead of first creating a table and then performing data entry, you can create a table and add records at once as long as you separate the statements with GO. To proceed, in your code, you must first create the table, which would save it, use GO to end the statement that creates the table, start the statement or each statement used to add a record, and it or each with GO. Consider the following example:

-- =============================================
-- Database: VideoCollection
-- Table:    Videos
-- =============================================
IF EXISTS(SELECT name 
	  FROM 	 sysobjects 
	  WHERE  name = N'Videos' 
	  AND 	 type = 'U')
    DROP TABLE Videos
GO

USE VideoCollection
GO

CREATE TABLE Videos (
VideoTitle varchar(100),
Director varchar(80),
YearReleased int,
VideoLength varchar(30),
Rating varchar(20))
GO

INSERT INTO Videos VALUES('A Few Good Men', 'Rob Reiner', 1992, '138 Minutes', 'R')
GO
INSERT INTO Videos(Director, VideoLength, VideoTitle, YearReleased)
VALUES('Jonathan Dame', '118 Minutes', 'The Silence of the Lambs', 1991)
GO
INSERT INTO Videos(VideoLength, Rating, Director, VideoTitle)
VALUES('112 Minutes', 'R', 'Jonathan Line', 'The Distinguished Gentleman')
GO
INSERT INTO Videos(Rating, VideoTitle, Director, VideoLength)
VALUES('R', 'The Lady Killers', 'Joel Coen & Ethan Coen', '104 Minutes')
GO
INSERT INTO Videos VALUES('Ghosts of Mississipi', 'Rob Reiner', 1996, '130 Minutes', '')
GO

Data Entry On Command

To programmatically perform data entry using a SQL statement, create an INSERT statement exactly following the descriptions made for SQL Query Analyzer. Once the statement is ready, pass it as string to a SqlCommand object and execute it with a call to SqlCommand.ExecuteNonQuery().

Practical Learning: Performing Data Entry With a SqlCommand Object

To enter data for a continent, change the code as follows:

Imports System
Imports System.Data.SqlClient

Module Exercise

    Public Sub Main()

        Dim strContinent As String
        Dim strArea As String
        Dim strPopulation As String

        Console.Write("Enter the name of the continent: ")
        strContinent = Console.ReadLine()
        Console.Write("Enter the area of the continent: ")
        strArea = Console.ReadLine()
        Console.Write("Enter the population of the continent: ")
        strPopulation = Console.ReadLine()

        Dim strInsert As String = "INSERT INTO Continents VALUES('" & _
             strContinent & _
                            "', '" & strArea & _
                                        "', '" & strPopulation & "')"
        Dim conDatabase As SqlConnection = New _
SqlConnection("Data Source=(local);Database='Countries1';Integrated Security=yes;")
        Dim cmdDatabase As SqlCommand = New SqlCommand(strInsert, conDatabase)

        conDatabase.Open()

        cmdDatabase.ExecuteNonQuery()
        conDatabase.Close()

    End Sub

End Module

Save the file and return to the Command Prompt
Compile and execute the program
Create a continent with a name, its area, and its population
Return to Notepad

Data Maintenance: Deleting Records

Introduction

Like databases, tables, and columns, records need maintenance too. Some of the operations you can perform include deleting a whole record, changing the value of a record under a particular column (which is equivalent to editing a record), etc. Just as done for a column, before changing anything on a record, you must locate it first. This operation is somehow different than the maintenance performed on databases, tables, and columns. The reason is that, although you always know the name of a database, the name of a table, or the name of a column, when it comes to records, you cannot know in advance the information it holds. For this reason, you must use additional operators to help you locate a record. Fortunately, as always, you have various options.

Deleting Records in SQL

The SQL code that deletes all records from a table uses the following formula:

DELETE TableName

When you create this statement, provide the name of the table as TableName. When you execute this statement, all records of the table would be removed.

To remove one particular record from a table, use the following formula:

DELETE TableName
WHERE CriteriaToFindTheRecord

The DELETE and the WHERE keywords are required. The TableName factor allows you to specify the name of the table that the record belongs to. In order to delete the record, you must provide a way to locate it. Consider the following table named Videos from a database named VideoCollection:

Imagine that you want to remove the record whose video title is "The Silence of the Lambs". In this case, the TableName is Videos. The criterion to find the correct record is that the VideoTitle value of that record = The Silence of the Lambs. To remove it, you would use code as follows:

USE VideoCollection
GO
/* Code used to remove the video titled
   The Silence of the Lambs */
DELETE Videos
WHERE VideoTitle = 'The Silence of the Lambs'
GO

If you use the DELETE formula to remove a record, notice that, as always in SQL Query Analyzer, you would not be warned.

Deleting a Record on Command

To programmatically delete a record, create a DELETE statement using the same rules we reviewed for SQL Query Analyzer, pass it to a SqlCommand object, and execute the statement by calling the SqlCommand.ExecuteNonQuery() method.

Data Maintenance: Updating Records

Updating a Record in the SQL Query Analyzer

The SQL statement used to change the value of a record uses the following formula:

UPDATE TableName
	SET ColumnName = NewValue
	WHERE CriteriaToLocateRecord

The UPDATE keyword allows you to specify the name of the table whose record you want to change. The table is identified with the TableName factor of our formula.

The SET keyword allows you to identify the column under which exists the value you want to change. The column is identified as ColumnName in our formula. On the right side of the column name, type the assignment operator, followed by the value you want the cell to hold. If the update is successful, the value stored under that column would be replaced.

The WHERE clause allows you to specify the criterion used to locate the particular record that the existing value belongs to.

Consider you have the above table, imagine that, on the video titled "The Distinguished Gentleman", you want to change the name of the director from "Jonathan Line" to "Jonathan Lynn. The table name is Videos. The column that owns the value is named Director. The criterion to use is to identify the record whose VideoTitle is "The Distinguished Gentleman". The code to perform this update would be:

USE VideoCollection
GO
-- Code used to change the name of a director
UPDATE Videos
SET Director = 'Jonathan Lynn'
WHERE VideoTitle = 'The Distinguished Gentleman'
GO

Once again, remember that when performing an operation in the SQL, you would not be warned.

Updating a Record on Command

To update a record in a Windows Forms Application, create an UPDATE statement using the same rules we reviewed for SQL Query Analyzer, pass it to a SqlCommand object before executing the statement with a call to the SqlCommand.ExecuteNonQuery() method.

Assistance With Data Entry

Introduction

Microsoft SQL Server and the SQL provide various ways to assist you with data entry. For example, if you have a table in a Microsoft SQL Server database, a Microsoft Access database, or another system, such as a text file, you can import the values of that table. Another type of assistance you can get with data entry is to copy records from one table to another.

Data Import

Another technique used to perform data entry 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.

The easiest type of data that can be imported into SQL Server, and which is available on almost all database environments is the text file. Almost any database application you can think of can be imported as a text file but data from that file must be formatted in an acceptable format. 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, or any valid character. SQL Server is able to recognize the double-quote as a valid separator of columns. Data between the quotes is considered as belonging to a distinct field. Besides this information, the database would need to separate information from two different columns. Again, a valid character must be used. Most databases, including SQL Server, recognize the comma as such a character. 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 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 it so another application can easily use such data. That is the case for the data we will use in the next exercise: it is data that resided on a Microsoft Access database and was prepared to be imported in SQL Server.

After importing data, you should verify and possibly format it to customize its fields.

The Default Value

When performing data entry, the records under a certain column usually have the same value. For example, for a local database with a table that includes an address, most employees would live in the same state and the same country. When creating a column with a value that occurs regularly, you can specify that value as default.

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, assign the desired value to the DEFAULT keyword. Here are examples:

CREATE TABLE StaffMembers (
FullName VARCHAR(50),
Address VARCHAR(80),
City VARCHAR(40),
State VARCHAR(40) DEFAULT = 'NSW',
PostalCode VARCHAR(4) DEFAULT = '2000',
Country VARCHAR(20) DEFAULT = 'Australia')
GO

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

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

Constraints in Data Entry

Introduction

A constraint in a database is a rule used to apply restrictions on what is allowed and what is not allowed in the application. To assist you in creating an effective database, the SQL provides various types of constraints you can apply to your table(s).

The Nullity of a Field

During data entry, users of your database will 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 provide it to the user. 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 its value or its value is simply unknown. As you can see, 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. Remember that even if a field is empty, you should know what value it is holding because being empty could certainly mean that the field has a value.

To solve the problem of null values, the SQL 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, her data entry would not be validated. To make sure the user always fills out a certain field before moving to the next field, you must make sure the field doesn't allow null values this will ensure that you know that the field is holding a value and, eventually, you can find out what that value is.

To control the nullity of a column with a SQL statement, you can use NULL, NOT NULL, or omit it.

Am I my Record's Keeper?

When updating a record and changing a value, just like the user can make a mistake and change the wrong value, you too can. Consider the following table:

Imagine you ask the user to open this table and, for the video that is rated R, to change the name of the director to Jonathan Lynn. The user would be confused because there is more than one video that is rated R. This means that you should use the most restrictive criterion to locate the record. In future lessons, when we study data analysis, we will review other operators you can use, such as asking the user to locate the video whose title is "The Distinguished Gentleman" AND whose director is Jonathan Lynn.

To be able to uniquely identify each record, you can create a special column and make sure that each value under that column is unique. You have two main options. You can put the responsibility on the user to always provide a unique value. For example, if the table includes records of students of a school, since each student must have a student number and that number must be unique from one student to another, you can ask the user to make sure of this during data entry. What if the user forgets? What if the user cannot get that number at the time of data entry? What if that number can only be generated by the administration but only after the student has been registered? Based on this, an alternative is to ask the SQL interpreter to automatically generate a new and unique number for each record.

A column whose values are automatically generated by the database engine is referred to as an identity column. An identity column can have only a numeric-based data type: bigint, decimal, int, numeric, smallint, or tinyint.

To create an identity column, if you are working the SQL Server Enterprise Manager or the Server Explorer, in the Design Table window, in the top section of the table, create the column by specifying its name and data type as one of the above. Then, in the lower section, set the Identify field to Yes from its default No.

If you are working from a SQL statement, to create an identity column, when creating the table, after the name of the column and before the semi-colon or the closing parenthesis of the last column, enter IDENTITY().

After the starting value of the identity column has been set, you can specify how much value would be added to the values of the column with each new record. By default, each previous number would be incremented by 1. If you want a different value, you can change it from 1.

To specify the incrementing value of an identity column, if you are working with a SQL statement, to specify the incrementing value, enter it as the second argument of the IDENTITY keyword. Here is an example:

CREATE TABLE StaffMembers (
StaffNumber int IDENTITY(1,1),
FullName VARCHAR(50) NOT NULL,
Address VARCHAR(80),
City VARCHAR(40),
State VARCHAR(40) NULL DEFAULT = 'NSW',
PostalCode VARCHAR(4) DEFAULT = '2000',
Country VARCHAR(20) DEFAULT = 'Australia')
GO

The Primary Key

We have seen that an identity column is used to make sure that a table has a certain column that holds a unique value for each record. In some cases, you can use more than one column to uniquely identify each record. For example, on a table that holds the list of employees of a company, you can use both the employee number and the social security number to uniquely identity each record.

In our description of the identity column, we saw that it applied only to one column but we also mentioned that a more that one column could be used to uniquely identity each record. The column or the combination of columns used to uniquely identity each column is called a primary key.

CHECK

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. This is done using the CHECK constraint.


Previous	Copyright © 2005-2016, FunctionX