Home

The Default Value of a Column

   

Introduction

Sometimes most records under a certain column may hold the same value although just a few would be different. For example, if a school is using a database to register its students, all of them are more likely to be from the same state. In such a case, you can assist the user by automatically providing a value for that column. The user would then simply accept the value and change it only in the rare cases where the value happen to be different. To assist the user with this common value, you create what is referred to as a default value.

 

Visually Creating 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:

It the Data Type is Intructions
Text-based (char, varchar, text, and their variants) Enter the value in single-quotes
Numeric-based Enter the value as a number but following the rules of the data type.
For example, if you enter a value higher than 255 for a tinyint, you would receive an error
Date or Time Enter the date as either MM/DD/YYYY or YYYY/MM/DD. You can optionally include the date in single-quotes.
Enter the time following the rules set in the Control Panel (Regional Settings).
Bit Enter the value as 0 for FALSE or any other long integer value for TRUE

 

Programmatically Creating a Default Value

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:

private void btnDatabase_Click(object sender, EventArgs e)
{
    using (SqlConnection connection =
	new SqlConnection("Data Source=(local);" +
			  "Database='Exercise1';" +
			  "Integrated Security=yes;"))
    {
	SqlCommand command =
	    new SqlCommand("CREATE TABLE Employees " +
			   "( " +
			   "FullName VARCHAR(50), " +
			   "Address VARCHAR(80), " +
			   "City VARCHAR(40), " +
			   "State VARCHAR(40) DEFAULT = 'NSW', " +
			   "PostalCode VARCHAR(4) DEFAULT = '2000', " +
			   "Country VARCHAR(20) DEFAULT = 'Australia');",
			   connection);
	connection.Open();
	command.ExecuteNonQuery();

	MessageBox.Show("A new table named Employees has been created");
    }
}
 

After creating the table, the user does not have to provide a value for a column that has a default. If the user does not 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
   

 

 

Published on Thursday 03 January 2008

 

Previous Copyright © 2007 FunctionX, Inc. Next