Data Entry

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.

1. Start a new Windows Application named Countries3
2. Right click the form and click View Code
3. In the top section of the file, import the System.Data and the System.Data.SqlClient namespaces
    

   Imports System.Data Imports System.Data.SqlClient Public Class Form1

    
4. Return to the form and design it as follows:
    

   Control Name Text TabControl tabCountries   TabPage pgeMaintenance Maintenance Button btnCreateDB Create Statistics Database Button btnCreateContinents Create Continents Button btnCreateCountries Create Countries TabPage pgeContinents Continents TabPage pgeCountries Countries Button btnClose Close

5. Double-click the Create Statistics Database button and implement its code as follows:
    

   Private Sub btnCreateDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles btnCreateDB.Click Dim strConnection As String = "IF EXISTS (SELECT * " & _ "FROM master..sysdatabases " & _ "WHERE name = N'CountriesStats')" & _ "DROP DATABASE CountriesStats;" & _ "CREATE DATABASE CountriesStats;" Dim conDatabase As SqlConnection = New _ SqlConnection("Data Source=(local);Integrated Security=sspi;") Dim cmdDatabase As SqlCommand = New SqlCommand(strConnection, conDatabase) conDatabase.Open() cmdDatabase.ExecuteNonQuery() conDatabase.Close() End Sub

6. In the Class Name combo box, select btnCreateContinents
7. In the Method Name combo box, select Click
8. Implement the event as follows:
    

   Private Sub btnCreateContinents_Click(ByVal sender As Object, ByVal e As System.EventArgs) _ Handles btnCreateContinents.Click Dim strCreate As String = "IF EXISTS(SELECT name FROM sysobjects " & _ "WHERE name = N'Continents' AND type = 'U')" & _ "DROP TABLE Continents;" & _ "CREATE TABLE Continents (" & _ "ContinentName varchar(100)," & _ "Area varchar(30), " & _ "Population varchar(30))" Dim conDatabase As SqlConnection = New SqlConnection( _ "Data Source=(local);Database='CountriesStats';Integrated Security=yes") Dim cmdDatabase As SqlCommand = New SqlCommand(strCreate, conDatabase) conDatabase.Open() cmdDatabase.ExecuteNonQuery() conDatabase.Close() End Sub

9. In the Class Name combo box, select btnCreateCountries
10. In the Method Name combo box, select Click
11. Implement the Click event as follows:
     

    Private Sub btnCreateCountries_Click(ByVal sender As Object, ByVal e As System.EventArgs) _ Handles btnCreateCountries.Click Dim strCreate As String = "IF EXISTS(SELECT name FROM sysobjects " & _ "WHERE name = N'Countries' AND type = 'U')" & _ "DROP TABLE Countries;" & _ "CREATE TABLE Countries (" & _ "CountryName varchar(120)," & _ "Area varchar(30)," & _ "Population varchar(30)," & _ "Capital varchar(80)," & _ "Code char(2))" Dim conDatabase As SqlConnection = New SqlConnection( _ "Data Source=(local);Database='CountriesStats';Integrated Security=yes") Dim cmdDatabase As SqlCommand = New SqlCommand(strCreate, conDatabase) conDatabase.Open() cmdDatabase.ExecuteNonQuery() conDatabase.Close() End Sub

12. In the Class Name combo box, select btnClose
13. In the Method Name combo box, select Click
14. Implement its event as follows:
     

    Private Sub btnClose_Click(ByVal sender As Object, ByVal e As System.EventArgs) _ Handles btnClose.Click End End Sub

15. Execute the application
16. Click the top button and wait a few seconds
17. Click the middle button and wait a few seconds
18. Click the bottom button and wait a few seconds
19. Close the form and return to your programming environment

After displaying the table in the SQL Server Enterprise Manager or the Server Explorer, to enter new data, click an empty cell and type the necessary value. After finishing with one cell, you can press Enter, Tab or click another cell. You can start this operation on the most left cell and continue with the cells on its right. When you finish with a row of cells and move to another row, the interpreter creates (or updates) a record. Therefore, entering data also self-creates a record. This also means that, when using the table in the SQL Server Enterprise Manager or the Server Explorer, you will not have to formally create a record of a table: it is automatically created when you enter data.

While performing data entry, the user may skip some fields if the information is not available. The user can skip only columns that allow NULL values. If a column was configured as NOT accepting NULL values, the user must enter something in the field, otherwise he would receive an error and the table would not allow going further.

INSERT TableName VALUES(Column1, Column2, Column_n)

INSERT INTO TableName VALUES(Column1, Column2, Column_n)

In the previous paragraphs, we were stating "you" as if you will be the one performing data entry. In reality, the user will be performing data entry on your products. Therefore, it is your responsibility to reduce, as much as possible, the likelihood of mistakes. Of course, there are various ways, through a "visual" application such as Microsoft Visual C++, etc, that you can take care of

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

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

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().

1. Change the design of the form as follows:
    

   Control Name Text GroupBox   New Continent Label   Name: TextBox txtContinentName   Label   Area: TextBox txtContinentArea   Label   Population TextBox txtContinentPopulation   Button btnNewContinent Create New Continent

2. Double-click the Create New Continent button and implement its code as follows:
    

   Private Sub btnNewContinent_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles btnNewContinent.Click Dim strContinentName As String = txtContinentName.Text If strContinentName = "" MsgBox("You must provide a name for the continent") Exit Sub End If Dim strInsert As String = "INSERT INTO Continents VALUES('" & _ strContinentName & "', " & "'" & txtContinentArea.Text & _ "', " & "'" & txtContinentPopulation.Text & "')" Dim conDatabase As SqlConnection = New _ SqlConnection("Data Source=(local);Database='CountriesStats';Integrated Security=yes") Dim cmdDatabase As SqlCommand = New SqlCommand(strInsert, conDatabase) conDatabase.Open() cmdDatabase.ExecuteNonQuery() conDatabase.Close() txtContinentName.Text = "" txtContinentArea.Text = "" txtContinentPopulation.Text = "" txtContinentName.Focus() End Sub

3. Execute the application and fill out the form as follows:
    
   
4. Click Create New Continent
5. Close the form and return to your programming environment

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 a record consists of removing it from a table, this includes all entries, if any, under each column for a particular row.

If you are working from SQL Server Enterprise Manager, before removing a record, first display the table in a view that shows its record. You can do this by right-click the table, positioning the mouse on Open Table, and clicking Return All Rows. If you are working in the Server Explorer, to display the table and show all records, double-click the table. In both cases, once in the Data window, to remove a record, right-click the gray box on the left side of the record and click Delete:

After clicking Delete, you would receive a warning message box:

If you still want to continue, you can click Yes and that record would disappear. If you want to change your mind, click No.

To remove a range of records from a table, you can click and drag from one gray box at one end of the range to the gray box at the other end of the range. As an alternative, you can click a gray box at one end of the range, press and hold Shift, then click the gray box at the other other end of the desired range. Once the selection is made, right-click anywhere in the selection and click Delete:

If you click Delete, you would receive a warning that lets you know the number of records that would be deleted. If you still want to delete them, you can click Yes. To change your mind, click No.

To remove all records from a table, you must first select all of them. To do this, you can click the gray box on the left of the first (or the last) record, press and hold Shift, then click the gray box of the last (or first) record:

Once the selection is made, right-click anywhere in the table and click Delete. You would receive the same type of warning for a range of records and you can proceed the same way.

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.

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.

To change a record in SQL Server Enterprise Manager, first open the table with the view that displays records (Right-click the table . Open Table . Return All Rows). In the Data window, locate the value you want to change, click it, edit it, and then click somewhere else:

Once the cell loses focus, the new value is automatically saved:

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 Query Analyzer, you would not be warned.

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.

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.

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 the SQL Server Enterprise Manager or the Server Explorer, display the table is design view. To proceed, first click the column in the top section of the table. Then, in the lower section, click Default Value and type the desired value in single-quotes. Here is an example:

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:

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

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

If you are creating the table in a Windows Forms Application, use the same rules of the SQL statement and create the table as we have done already.

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

If you create a Windows Forms Application and provide a form that allows the user to perform data entry for a table using a form, the default values for columns would not display in the Windows control. When writing your code, you can omit passing the values for the columns that have default values. In this case, the SQL interpreter, not the C++ compiler would use the default values for the columns you omit.

1. In the design view of the table and in the top section, click Gender
2. In the lower section, click Default Value and type 'Unknown'
3. In the top section, click State
4. In the lower section, click Default Value and type 'MD'
5. Save the table

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).

1. In the Server Explorer, right-click Students and click Design Table
2. Under the Allow Nulls column, remove the check boxes the correspond to the StudentNbr and the LastName columns
3. Save the table

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. Here is an example:

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 setting the Identity to Yes, you must then specify where the numeric counting would start. By default, this number is set to 1, meaning the first record would have a number of 1, the second would have a number of 2, and so on. If you want, you can ask the interpreter to start with a different number.

To specify the starting value of the identity column, if you are working in the Design Table window, in the lower section of the table, enter the desired value in the Identity Seed field. Here is an example:

If you are working with a SQL statement, to specify the starting value of the identity column, enter the desired number in the parentheses of the IDENTITY keyword.

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 a Design Table window, in the lower portion of the table, enter the desired value in the Identity Increment field. 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:

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

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

1. Right-click anywhere in the StudentNbr line and click Insert Column
2. In the new empty column, set the name to StudentNumber
3. Set the Data Type to int
4. In the lower section of the table, double-click the No value of Identifier to change it to Yes.
   Notice that the Identity Seed and the Identity Increment have been set to 1
5. Close the table
6. When asked whether you want to save, click Yes

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.

If you are creating a table in the Design Table window of the SQL Server Enterprise Manager or from the Server Explorer of Microsoft Visual Studio .NET, to indicate the column that would be used as the primary key, first click the name of the column. Then, on the toolbar, click the Primary Key button . You can also right-click the desired column and click Primary Key. The button on the left side of the name of the column would become equipped with a key icon. By tradition, which is not a rule, the name of the column used as the primary key of a table ends with ID. For example, instead of the column being named StaffNumber, it would be named StaffMemberID or something like that.

To specify that more than one column would be used as the primary key, first select them. To do this, you can click the left gray button of one of the column, press Ctrl, and click the left gray button of each of the other columns that would be involved. After selecting the column, on the toolbar, click the Primary button . You can also right-click one of the selected columns and click Primary Key.

1. Change the name of the first column from StudentNumber to StudentID
2. Right-click it and click Primary Key
3. Save and close the table

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.