Home

Data Entry With SQL

 

Introduction

To perform data entry using SQL:

  • In the Object Explorer of Microsoft SQL Server Management Studio, you can right-click the table, position the mouse on Script Table As -> INSERT To -> New Query Editor Window
  • Open an empty query window and type your code

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

INSERT TableName VALUES(Column1, Column2, Column_n);

Alternatively, or to be more precise, you can use the INTO keyword between the INSERT keyword and the TableName factor to specify that you are entering data in the table. 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 interpreter 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.

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 or time data type (datetime or smalldatetime), 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'.

In your Windows application, you can pass the INSERT statement to a command object. 

Adjacent Data Entry

The most common technique of performing data entry requires that you know the sequence of fields 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 position. Here is an example:

private void btnDatabase_Click(object sender, EventArgs e)
{
    using (SqlConnection connection =
	new SqlConnection("Data Source=(local);" +
			  "Database='VideoCollection1';" +
			  "Integrated Security=yes;"))
    {
	SqlCommand command =
	    new SqlCommand("INSERT INTO Videos " +
	"VALUES('A Few Good Men','Rob Reiner',1992,'138 Minutes', 'R');",
			   connection);
	connection.Open();
	command.ExecuteNonQuery();

	MessageBox.Show("A new record has been created.");
    }
}

During data entry on adjacent fields, if you do not have a value for a numeric field, you should type 0 as its value. For a string field whose data you do not have and cannot provide, type two single-quotes '' to specify an empty field.

Practical LearningPractical Learning: Performing Data Entry

  1. On the OrderProcessing form, double-click the Save Repair Order button and implement its event as follows:
     
    private void btnSaveRepairOrder_Click(object sender, EventArgs e)
    {
        string strConnection =
    	"Data Source=(local);" +
    	"Database='CollegeParkAutoRepair1';" +
    	"Integrated Security=yes;";
        string strRepairOrder = "INSERT INTO RepairOrders(CustomerName, " +
    		"CarMake, CarModel, CarYear, ProblemDescription, " +
    		"TotalParts, TotalLabor, TotalOrder) " +
    		"VALUES ('" + txtCustomerName.Text + "', '" +
    		txtCarMake.Text + "', '" + txtCarModel.Text + "', '" +
    		txtCarYear.Text + "', '" + txtProblemDescription.Text + 
    		"', '" + txtTotalParts.Text + "', '" + txtTotalLabor.Text +
    		"', '" + txtTotalOrder.Text + "');";
    
        using (SqlConnection connection = new SqlConnection(strConnection))
        {
    	SqlCommand command =
    		new SqlCommand(strRepairOrder, connection);
    
    	connection.Open();
    	command.ExecuteNonQuery();
    
    	MessageBox.Show("A new repair order has been created.");
        }
    }
  2. Execute the application
  3. Enter all values in the form
  4. Click the Save Repair Order button. Here is an example:
     
    Repair Order
  5. Close the form and return to your programming environment

Random Data Entry

The adjacent data entry we have performed requires that you know the position of each column. The SQL provides an alternative that allows you to perform data entry using the name of a column instead of its position. This allows you to provide the values of columns in an order of your choice. We have just seen a few examples where the values of some of the fields were 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 the fields' names to specify the fields whose data you want to provide.

To perform data entry in an order of your choice, you must provide your list of the fields of the table. 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 fields, just those you want, in the order you want.

Here are examples:

private void btnDatabase_Click(object sender, EventArgs e)
{
    using (SqlConnection connection =
	new SqlConnection("Data Source=(local);" +
			  "Database='VideoCollection1';" +
			  "Integrated Security=yes;"))
    {
	SqlCommand command =
	    new SqlCommand(
	"INSERT INTO Videos([Video Title], Director, [ Year], Length) " +
"VALUES('The Silence of the Lambs','Jonathan Demme',1991,'118 Minutes'); " +
		"INSERT INTO Videos([Video Title], Director, Length) " +
"VALUES('The Distinguished Gentleman', 'James Groeling', '112 Minutes'); " +
		"INSERT INTO Videos([Video Title], Director, Length) " +
	"VALUES('The Lady Killers', 'Joel Coen & Ethan Coen', '104 Minutes'); " +
		"INSERT INTO Videos([Video Title], Director, Length) " +
		"VALUES('Ghosts of Mississippi', 'Rob Reiner', '130 Minutes');",
		connection);
	connection.Open();
	command.ExecuteNonQuery();

	MessageBox.Show("A few records have been created.");
    }
} 

Practical LearningPractical Learning: Performing Data Entry

  1. Change the code of the Save Repair Order button as follows:
     
    private void btnSaveRepairOrder_Click(object sender, EventArgs e)
    {
        string strConnection =
    	"Data Source=(local);" +
    	"Database='CPAR2';" +
    	"Integrated Security=yes;";
        string strRepairOrder = "INSERT INTO RepairOrders(CustomerName, " +
    		"CarMake, CarModel, CarYear, ProblemDescription, " +
    		"TotalParts, TotalLabor, TotalOrder) " +
    		"VALUES ('" + txtCustomerName.Text + "', '" +
    		txtCarMake.Text + "', '" + txtCarModel.Text + "', '" +
    		txtCarYear.Text + "', '" + txtProblemDescription.Text + 
    		"', '" + txtTotalParts.Text + "', '" + txtTotalLabor.Text +
    		"', '" + txtTotalOrder.Text + "');";
    
        using (SqlConnection connection = new SqlConnection(strConnection))
        {
    	SqlCommand command =
    		new SqlCommand(strRepairOrder, connection);
    
    	connection.Open();
    	command.ExecuteNonQuery();
    
    	MessageBox.Show("A new repair order has been created.");
        }
    }
  2. Execute the application
  3. Enter the customer name, the name of the car, its model, its year, a description of a job to be performed, the cost of the parts, the cost of the labor, and the total price paid to repair
  4. Click the Save Repair Order button
  5. Enter the values again and click the Save Repair Order button again
  6. Close the form and return to your programming environment
 

Published on Thursday 03 January 2008

 

Previous Copyright © 2007 FunctionX, Inc. Next