ADO.NET - Lesson 5: Data Entry Techniques

	Techniques of Performing Data Entry

Data Entry Using the Enterprise Manager or Server Explorer

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.

Data Entry Using the SQL Query Analyzer

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.

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

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 fielde s 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

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

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

private: System::Void btnNewContinent_Click(System::Object *  sender, System::EventArgs *  e)

{

	 String *strContinentName = this->txtContinentName->Text;



	 if( strContinentName->Equals(S"") )

	 {

		 MessageBox::Show(S"You must provide a name for the continent");

		 return;

	 }



	 String *strInsert = String::Concat(S"INSERT INTO Continents VALUES('",

			                       strContinentName,

				       S"', '", this->txtContinentArea->Text,

				       S"', '", this->txtContinentPopulation->Text, S"');");

 	 SqlConnection *conDatabase = new 

       SqlConnection(S"Data Source=(local);Database='CountriesStats';Integrated Security=yes");

	 SqlCommand    *cmdDatabase = new SqlCommand(strInsert, conDatabase);



	 conDatabase->Open();



	 cmdDatabase->ExecuteNonQuery();

	 conDatabase->Close();



	 this->txtContinentName->Text = S"";

	 this->txtContinentArea->Text = S"";

	 this->txtContinentPopulation->Text = S"";

	 this->txtContinentName->Focus();

}

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


Previous	Copyright � 2005 FunctionX, Inc.	Next