|
 Practical
Learning: Assisting With Data Entry
|
|
- Start Microsoft Visual Basic and create a Windows Application named WattsALoan1
- To create a new form, in the Solution Explorer, right-click WattsALoan1
-> Add -> Windows Forms...
- Set the Name to LoanAllocations and click Add
- From the Data section of the Toolbox, click DataSet and click the form
- Select the Untyped Dataset radio button and click OK
- In the Properties window, change the following characteristics:
DataSetName: dsLoanAllocations
(Name): LoanAllocations
- Click Tables and click its ellipsis button
- To create a new table, click Add and change the properties as follows:
TableName: Loan
(Name): tblLoan
- Click Columns and click its ellipsis button
- Click Add 10 times and change the properties as follows:
| ColumnName |
(Name) |
| DateAllocated |
colDateAllocated |
| LoanNumber |
colLoanNumber |
| PreparedBy |
colPreparedBy |
| PreparedFor |
colPreparedFor |
| Principal |
colPrincipal |
| InterestRate |
colInterestRate |
| Periods |
colPeriods |
| InterestEarned |
colInterestEarned |
| FutureValue |
colFutureValue |
| MonthlyPayment |
colMonthlyPayment |
|
The Unique Value of a Column |
|
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.
|
Practical
Learning: Controlling the Uniqueness of a Column |
|
- In the Members list, click LoanNumber
- In the Properties list, double-click Unique to change its value to True
|
The Data Type of a 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.
|
Practical
Learning: Applying Data Types on Columns |
|
- In the Members list, click Principal
- In the Properties list, click DataType, click the arrow of its combo box
and select System.Double
- In the same way, change the data types of the following columns:
| Member |
DataType |
| DateAllocated |
System.DateTime |
| LoanNumber |
System.String |
| PreparedBy |
System.String |
| PreparedFor |
System.String |
| Principal |
System.Double |
| InterestRate |
System.Double |
| Periods |
System.Double |
| InterestEarned |
System.Double |
| FutureValue |
System.Double |
| MonthlyPayment |
System.Double |
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.
|
Practical
Learning: Applying Data Types on Columns |
|
- In the Members list, click Principal
- In the Properties list, click DefaultValue and delete <DBNull>
- Type 0.00
- In the same way, change the default values of the following columns:
| Member |
DefaultValue |
| Principal |
0.00 |
| InterestRate |
8.75 |
| Periods |
36 |
|
The Expression of a Column |
|
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:
- When the user moves to the next record, the expression is used to create
the value of the full name column
- The user cannot enter a value in the column that has an expression
|
Practical
Learning: Creating Expressions on Columns |
|
- In the Members list, click FutureValue
- In the Properties list, click Expression and type Principal + InterestEarned
- In the same way, change the data types of the following columns:
| Member |
Expression |
| InterestEarned |
Principal * (InterestRate / 100) * (Periods / 12) |
| FutureValue |
Principal + InterestEarned |
| MonthlyPayment |
FutureValue / Periods |
|
Text Length and Null Values |
|
|
The Maximum Length of a Column |
|
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
|
Practical
Learning: Nullifying a Column |
|
|
|
