Getting the Right Value

Introduction

When performing data entry, there are various ways you can either assist the user or impose some behaviors on your application. You can:

• Configure some columns of a table to accept only some types of value and reject others
• You can create a scenario that would make sure that each value under a column is unique
• You can create an expression that would specify the value of a column rather than the user entering it
• Using the data grid view or some other controls of the Toolbox, you can present a list of values to the user who would only select from that list instead of typing a value 

All these functionalities are already available, either in the classes of the data set system or built-in the available Windows controls.

During data entry, the user is expected to enter various values under each column and each value would belong to a particular record. As a result, it is not unusual to have the same value belonging to different records. For example, it is not surprising to have two employees holding the same first or last name, just as it is not unusual to have two customers living in the same city. On the hand, there are values that should be unique among the records. For example, two employees should not have the same employee number and two customer orders from two different customers should not have the same receipt number. In these cases, you would want each record to hold a different value under the same column. This is referred to as a unique value.

To support unique values, the DataColumn class is equipped with a Boolean property named Unique. The default value of this property is False, which means various records can have the same values for a column.

To visually specify that a column would require (or not require) unique values, in the Members list of the Columns Collection Editor, click the name of the column and, in the Properties list, (accept or) change the value of the Unique field. To programmatically control the uniqueness of values, assign the desired Boolean value to the Unique property of the column.

If you create an application that allows the user to enter some values, you would wish the user enter the right type of data under each column. To assist you with this, the DataColumn class allows you to specify an appropriate or desired data type for each column. The data type of a column allows it to accept or reject an inappropriate value. Although we saw that the name was the most important aspect of a column, in reality, a data type is also required.

To specify the data type of a column, if you are visually creating the table, in the Columns Collection Editor, under Members, create or select the name of a column. In the Properties list, click the arrow of the DataType field and select from the list:

To supports data types for a column, the DataColumn class relies on the following .NET Framework structures: Boolean, Byte, Char, DateTime, Decimal, Double, Int16, Int32, Int64, SByte, Single, String, TimeSpan, UInt16, UInt32, and UInt64. The DataColumn class can also support an array of Byte values, as in Byte(), for a column. When creating a new column, if you do not specify its data type, it is assumed to be a string and the String data type is automatically applied to it.

To programmatically specify the data type of a column, you have two main alternatives. When declaring a column, to specify its data type, you can initialize the DataColumn variable using the third constructor of the class. Its syntax is:

Public Sub New(columnName As String, dataType As Type)

To specify a column's data type, select one from the Type class of the System namespace by calling the Type.GetType() method. The GetType() method is overloaded with three versions. The first version has the following syntax:

Public Shared Function GetType(typeName As String) As Type

This method expects as argument a valid data type defined in the .NET Framework. The data type must be retrieved from the Type class of the System namespace. The name of the data type must be qualified with a period operator. Here is an example:

Public Class Exercise
    Private dsRedOakHighSchool As DataSet
    Private tblRegistration As DataTable
    Private colStudentNumber As DataColumn

    Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        colStudentNumber = _
	    New DataColumn("StudentNumber", Type.GetType("System.Int32"))

        tblRegistration = New DataTable("Student")
        tblRegistration.Columns.Add(colStudentNumber)

        dsRedOakHighSchool = New DataSet("SchoolRecords")
        dsRedOakHighSchool.Tables.Add(tblRegistration)
    End Sub
End Class

If you used the default constructor to create a DataColumn, to specify its data type, assign its qualified type to the DataColumn.DataType property. Here is an example:

Public Class Exercise
    Private dsRedOakHighSchool As DataSet
    Private tblRegistration As DataTable
    Private colStudentNumber As DataColumn
    Private colFirstName As DataColumn
    Dim colLivesInASingleParentHome As DataColumn

    Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        colStudentNumber = New DataColumn("StudentNumber", _
					  Type.GetType("System.Int32"))

        colFirstName = New DataColumn("FullName")
        colFirstName.DataType = Type.GetType("System.String")

        colLivesInASingleParentHome = New DataColumn("LSPH")
        colLivesInASingleParentHome.DataType = Type.GetType("System.Boolean")

        tblRegistration = New DataTable("Student")
        tblRegistration.Columns.Add(colStudentNumber)

        dsRedOakHighSchool = New DataSet("SchoolRecords")
        dsRedOakHighSchool.Tables.Add(tblRegistration)
    End Sub
End Class

Remember that there are various techniques you can use to create a column by specifying its name and its data type.

When performing data entry, the user is expected to enter a value for each column. Sometimes, most values under a certain column would be the same. For example, if you are creating an application that would be used in a tri-state area such as MD-DC-VA and the product would be used to dry-clean items from customers all over the region, most customers would come from the state where the company is based. In the column used to enter the state, you can provide a default value so that, if the user does not enter it, it would be selected by default.

A default value is one that is automatically applied to a column so the user can simply accept it but the user can change it if it does not apply.

To visually create a default value on a column, in the Column Collection Editor, select a column in the Members list. In the Properties list, click DefaultValue and replace <DBNull> with the desired value.

To programmatically specify the default value, assign the desired value to the DefaultValue property of the data column variable.

So far, to perform data entry, we created the data fields and expected the user to enter values in them. In some cases, instead of the user typing data, you may want to specify your own constant value or you may want to combine some values. An expression can be:

• A constant value such as 288, "Aaron Watts", or 48550.95
• A combination of two or more constants such 50 + 428, "HourlySalary" & 25.85, or "John" & " " & "Santini", 
• The name of a column such as Filename, CountryCode or DateOfBirth
• The combination of a constant and one or more column names such as Username & "@gmail.com"
• Or a combination of two or more columns such as FirstName & LastName

Besides the items in this list, you can also use some functions and/or combine them with the items in the above list. The expression then creates or represents a value. To create an expression, there are various rules you must follow:

• If the expression is algebraic, you can use the normal math operations (+, -, *, and /) applied to one or more constants combined to one or more column names
• To create a combination of strings, you can use the + operator

Once you have decided about this expression, you can use it as the value assigned to a column.

If you are visually creating a column, under the Members list of the Column Collection Editor, select a column. To specify an expression for it, in the Properties list, click Expression and type the desired expression. Here is an example: 

To programmatically specify the expression used on a column, assign the expression, as a string, to its variable name. Here is an example:

Public Class Exercise
    Private dsRedOakHighSchool As DataSet
    Private tblRegistration As DataTable
    Private colFirstName As DataColumn
    Private colLastName As DataColumn
    Private colFullName As DataColumn

    Dim dgvStudents As DataGridView

    Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        colFirstName = New DataColumn("FirstName")
        colFirstName.DataType = Type.GetType("System.String")

        colLastName = New DataColumn("LastName")
        colLastName.DataType = Type.GetType("System.String")

        colFullName = New DataColumn("FullName")
        colFullName.DataType = Type.GetType("System.String")
        colFullName.Expression = "FirstName + ' ' + LastName"

        tblRegistration = New DataTable("Student")
        tblRegistration.Columns.Add(colFirstName)
        tblRegistration.Columns.Add(colLastName)
        tblRegistration.Columns.Add(colFullName)

        dsRedOakHighSchool = New DataSet("StudentsRecords")
        dsRedOakHighSchool.Tables.Add(tblRegistration)

        dgvStudents = New DataGridView()
        dgvStudents.Location = New Point(12, 12)
        dgvStudents.Size = New Size(350, 100)
        dgvStudents.DataSource = dsRedOakHighSchool
        dgvStudents.DataMember = "Student"

        Controls.Add(dgvStudents)
    End Sub
End Class

Thanks to this code, the user can type both the first and the last names. Then two things:

1. When the user moves to the next record, the expression is used to create the value of the full name column
    
   
2. The user cannot enter a value in the column that has an expression

If a column is configured to receive text, that is, if its data type is set to String, by default, it can hold 0 to 32767 characters. This is (too) long for most cases. For example, if a column is made for people's names, this length is certainly too high. Fortunately, to customize the behavior of a column, you can limit the number of characters that can be entered in a column.

To support the ability to control the number of characters that a text-based column would allow, the DataColumn class is equipped with a property named MaxLength. The default value of this property is -1, which means there is no limit.

To visually set the maximum length, in the Columns Collection Editor, click a column in the Members list. In the Properties list, click MaxLength and type the desired value. To programmatically specify the maximum length, assign an integer value to the MaxLength property of the column's variable. Here are examples:

Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
    colFirstName = New DataColumn("FirstName")
    colFirstName.DataType = Type.GetType("System.String")
    colFirstName.MaxLength = 50

    colLastName = New DataColumn("LastName")
    colLastName.DataType = Type.GetType("System.String")
    colLastName.MaxLength = 50

    colFullName = New DataColumn("FullName")
    colFullName.DataType = Type.GetType("System.String")
    colFullName.Expression = "FirstName + ' ' + LastName"
    colFullName.MaxLength = 120

    tblRegistration = New DataTable("Student")
    tblRegistration.Columns.Add(colFirstName)
    tblRegistration.Columns.Add(colLastName)
    tblRegistration.Columns.Add(colFullName)

    dsRedOakHighSchool = New DataSet("StudentsRecords")
    dsRedOakHighSchool.Tables.Add(tblRegistration)
End Sub

When performing data entry, if the user does not have a value for a certain column, he or she may skip it. In some cases, you may want a value to be required; that is, you would not let the column be left empty. When a column is left empty, it is referred to as null.

To support the ability to have a null value or to require it, the DataColumn class is equipped with a Boolean property named AllowDBNull. If you want the user to be able to skip a column and not provide a value, you can ignore this property or set it to True. To require a value for a column, set this property to False. Here is an example:

Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        colFirstName = New DataColumn("FirstName")
        colFirstName.DataType = Type.GetType("System.String")
        colFirstName.MaxLength = 50

        colLastName = New DataColumn("LastName")
        colLastName.DataType = Type.GetType("System.String")
        colLastName.MaxLength = 50
        colLastName.AllowDBNull = False

        colFullName = New DataColumn("FullName")
        colFullName.DataType = Type.GetType("System.String")
        colFullName.Expression = "FirstName + ' ' + LastName"
        colFullName.MaxLength = 120

        tblRegistration = New DataTable("Student")
        tblRegistration.Columns.Add(colFirstName)
        tblRegistration.Columns.Add(colLastName)
        tblRegistration.Columns.Add(colFullName)

        dsRedOakHighSchool = New DataSet("StudentsRecords")
        dsRedOakHighSchool.Tables.Add(tblRegistration)
End Sub