

Visual Basic for

the Approved

Workman

## by

# Wesley D. Sweetser, Jr.

wesleysweetser@netscape.net

## Companies, names and data used in examples in this text and the accompanying software are fictitious unless otherwise noted or Biblically true. No part of this book may be reprinted without written permission, except for brief quotations in books and critical reviews. The accompanying software may be used by the purchaser in software development enterprises as long as credit is given to its source. Permission to use Microsoft specific software is determined by the license granted by Microsoft for each user or site.

Unless otherwise indicated, Scripture is taken from the New American Standard Bible Copyright © 1960, 1963, 1968, 1971, 1972, 1973, 1975, 1977, 1988 by the Lockman Foundation. Used by permission.

Scripture (marked NIV) taken from the HOLY BIBLE, NEW INTERNATIONAL VERSION Copyright © 1973, 1978, 1984 International Bible Society. Used by permission of Zondervan Bible Publishers.

## Visual Studio and SQL Server are registered trademarks of Microsoft Corporation in the United States of America and other countries.

Copyright © 2018 by Wesley D. Sweetser, Jr. All rights reserved.

## Published electronically in the United States of America.

# Ephesians 2:10

# For we are His workmanship, created in Christ Jesus for good works, which God prepared beforehand, that we should walk in them.

# 2 Timothy 2:15

# Be diligent to present yourself approved to God as a workman who does not need to be ashamed, handling accurately the word of truth.

# Dedication

This work is dedicated to the Lord from Whom I have receive my strength and Who has granted me so great a salvation.

# Romans 10:9-10

# "that if you confess with your mouth Jesus as Lord, and believe in your heart that God raised Him from the dead, you will be saved; for with the heart a person believes, resulting in righteousness, and with the mouth he confesses, resulting in salvation."
CONTENTS

Part 1 - Foundation

1. Introduction to Visual Basic

2. Methods

Part 2 - Tools

3. Interactive Programs

4. Database Input and Output

5. Arithmetic

6. Selection and Control

7. Arrays

Part 3 - Special Topics

8. Reports

9. SQL Server Integration

About the Author
PREFACE

# Drawing from many years of professional software development and teaching experience, I prepared an innovative text that presents the Visual Basic (VB) language in a simple, yet powerful way, at the same time integrating Biblical perspective.

# Few textbooks address the simplicity of Visual Basic, many are unable to address the complexity of real-world systems, and few if any have attempted to integrate faith with content to prepare students and new programmers for the workplace. This textbook has three goals. The first is to arm the Christian student with Scripture compatible with business-oriented programming, that they may be able witnesses for Christ. The second is to present Visual Basic in all its simplicity, that it may be a joy to use. The third is to present a realistic prototype system so students can be prepared for good works in their business endeavors.

# An overview of Visual Basic and the primary Christian emphasis are presented in the first chapter. Then using object-oriented methodology, methods and loops are presented in chapter 2 with only a few commands needed to understand the sample code. Interactive programs are presented in chapter 3, with a menu and a complete suite of software available electronically which can be tailored for use in any system.

# Chapter 4 introduces databases. Chapter 5 and 6 cover arithmetic, along with selection and control. Arrays are presented in chapter 7. Reports are covered in chapter 8 and the text concludes with an introduction to SQL Server Integration.

# Code used in the text is available electronically at

 https://drive.google.com/open?id=1ev_d6IHQAxQnlNAaqH7Uv1LEM3VvcEyW

# Mention of samples folders or .vb files or other files in the text refers to this link.

# I have found it a great joy to develop this text under the influence and leading of the Holy Spirit of God, and count it a privilege to present it to you.

# In Christ's service,

Wesley D. Sweetser, Jr.

# November 2018
Part 1

Return to Contents

Foundation

# 1 Corinthians 3:10-11

# According to the grace of God which was given to me, as a wise master builder I laid a foundation, and another is building upon it.

# But let each man be careful how he builds upon it. For no man can lay a foundation other than the one which is laid, which is Jesus Christ.
CHAPTER ONE

Return to Contents

Introduction to Visual Basic

# Isaiah 28:10

"For He says,

'Order on order, order on order,

Line on line, line on line,

A little here, a little there.' "

1.1 Visual Basic Overview

# Visual Basic is a sophisticated language capable of creating and retrieving data on a massive scale with great flexibility and ease. Visual Basic is the mainstay of many businesses today.

# Visual Basic is a language that helps bring order to business enterprises. Where there is disorder, we can use Visual Basic to create systems of programs to organize and rearrange data in meaningful ways. In a much more awesome manner, God created order from disorder and it was good:

Genesis 1:3-4

# Then God said, "Let there be light"; and

# there was light. And God saw that the

# light was good; and God separated the

# light from the darkness.

Colossians 1:16

# For by Him all things were created, both

# in the heavens and on earth, visible and

# invisible, whether thrones or dominions

# or rulers or authorities -- all things

# have been created by Him and for Him.

Throughout Scripture, we see how God has continued to order the world, particularly Israel in the Old Testament and the church in the New Testament. Each of the twelve tribes of Israel had a particular order in marching through the desert and in the setting up of the tabernacle, and there was order in constructing and rebuilding the Temple, to cite some Old Testament examples. With respect to the New Testament, the body of Christ is well ordered. It consists of many members, each with a particular function or purpose. We are admonished to do everything "decently and in order" (1 Corinthians 14:40) with regard to congregational worship.

Bringing order out of chaos is not only prized within the business enterprise, but order is also highly valued within a Visual Basic program itself. If bringing order out of chaos was good to God, then writing a well-ordered Visual Basic program is a good work. To accomplish orderliness, we desire to have each component of our Visual Basic programs have a particular function or purpose, just as each member of the body of Christ has a particular purpose. We accomplish this by making use of object-oriented programming concepts that are based on using classes, objects, and functions or methods to carry out specific tasks.

# The advantage to a business of having highly structured or well-ordered programs and systems of programs built upon objects and methods, lies in the reduced cost to develop the software initially and to maintain these programs and systems as changes need to be made over time. Our desire is to honor and be like our God, who is a God of order, especially since we are His workmanship created in Him to perform good works (Eph. 2:10).

1.2 The Structure of Visual Basic

# The structure of Visual Basic is somewhat precise. Here is a sample Visual Basic program showing the most basic structure:

Module Hello

' sample showing single method

Sub Main()

Console.WriteLine("Hello")

' keep console open to see Hello message

' press any key or click X to close window

Console.ReadKey()

End Sub

End Module

The Module, Sub Main() and End lines are generated automatically when creating a new project as a Console Application using Visual Studio. The Console Application is the simplest type of program. In later chapters Web Applications will also be discussed.

Main() is a method automatically generated and required for every Visual Basic application. You can optionally use string arguments to pass information from outside the program at the start of program execution, which is discussed further down and in chapter 7.

A method can be a Sub or a Function. Functions return a value. A Sub method is terminated when the End Sub is executed. Every Sub or Function must have a corresponding End Sub or End Function.

Use single quotes or apostrophes to identify comment lines like this

' keep console open to see Hello message

' press any key or click X to close window

# These lines

Console.WriteLine("Hello")

Console.ReadKey()

are referred to as statements or commands.

There are two ways to create an executable file (.exe): within Visual Studio and by using the Developer Command Prompt.

Within Visual Studio choose to either Build a Debug Solution or a Release Solution, then choose Start Debugging (F5) or Start Without Debugging (Ctrl-F5) to execute the code. Visual Studio 2015 was used to execute all the code in this text. Within Visual Studio Sub Main must be set as the Project Startup Object.

# Using the Developer Command Prompt is a bit more complicated. In Windows 10, search for "Developer Command Prompt" in the Start menu, then choose Developer Command Prompt for VS 20xx where xx is the version of Visual Studio. Or from the Start Menu go to the Visual Studio folder, expand and click on Developer Command Prompt for VS 20xx.

In the command-prompt window, navigate to the folder where .vb files are using a command like this

# cd "C:\Users\\{user}\Documents\VB student files Win 10\chapter 1 samples"

Enter this command to compile the Visual Basic program hello.vb

vbc Hello.vb

Type Hello.exe to run the program. When this program is executed, the word Hello is displayed on the screen. Arguments can also be passed to the program. In this example the value "test" is sent to the program

Hello.exe test

In Main() arguments can be accessed with code like this

Dim arguments() As String = Environment.GetCommandLineArgs()

Console.WriteLine(arguments(1))

# For example, the word "test" can be used by a programmer to trigger special lines of code used only during development. Arguments may also be used to pass information during live runs, such as a date or any other information.

The topics methods and data elements are elaborated on in the remainder of this chapter.

1.3 Methods in Visual Basic

Statements are used inside methods in Visual Basic. Multiple methods can be used to facilitate re-usability and testing, and to make programs easier to read and understand.

# A program could be written as one method:

' sample showing single method

Sub Main()

Console.WriteLine("Hello")

' keep console open to see Hello message

' press any key or click X to close window

Console.ReadKey()

End Sub

# OR as two (or more) methods

' sample showing two methods

Public Sub Main()

ConsoleExec()

End Sub

Public Sub ConsoleExec()

Console.WriteLine("Hello")

' keep console open to see Hello message

' press any key or click X to close window

Console.ReadKey()

End Sub

A method will generally carry out some specific task and is sometimes limited in size, say up to a page in length. Statements are not case sensitive in Visual Basic. Regarding spacing, tab to indent for readability and align opening and closing keywords.

Some other keywords help to organize Visual Basic programs. The Namespace serves as a container for related Classes and methods. The Class is a basic building block of object-oriented programming. Methods can be in separate Classes within the same Namespace or in Classes in entirely different Namespaces. Here we'll look at a simple example of Classes in separate Namespaces. In the next chapter we'll see methods in Classes within the same Namespace.

# Using distinct Classes offers the advantage of reusing software already developed, thereby reducing development time and cost. These scenarios can be part of a single project solution or in different solutions. This text limits the discussion to single solutions.

# In this example, two Namespaces are in the same code file of a single solution

Namespace Hello

Class mainProcess

' Main needs to be Shared when defined inside a class

Public Shared Sub Main()

Dim c1 As New ConsoleApp.ConsoleApp

c1.ConsoleExec()

End Sub

End Class

End Namespace

Namespace ConsoleApp

Class ConsoleApp

Public Sub ConsoleExec()

Console.WriteLine("Hello")

' keep console open to see Hello message

' press any key twice or click X to close window

Console.ReadKey()

End Sub

End Class

End Namespace

Note that Main is Shared inside a Class. What also makes this interesting is that we are making use of objects. Consider this line

Dim c1 As New ConsoleApp.ConsoleApp

A new object is being created from the Class ConsoleApp that's part of the Namespace ConsoleApp. This new object called c1 inherits any and all methods from the Class ConsoleApp.

It's also possible to have these Namespaces in separate code files. To accomplish this copy one Namespace to a new file, and add that file to the project, then delete the Namespace code from the original file. The solution to this is available in the chapter 1 samples folder of the accompanying electronic files and is left to the interested reader to peruse.

An example of a special purpose code file could be code to access a database. Code files may be small and have a single method or they may be large and have multiple methods. Each extra code file needs to be included in the project solution with the code file using it.

If code is not intended to be reused, include its functionality as part of a separate class in the primary code file or as code within the main method itself.

# Please note that once there are two or more code files in a project then using the Developer Command Prompt becomes problematic.

1.4 Data Elements in Visual Basic

# Visual Basic has many data types. This chapter covers

  * # String and character

  * # Integer and Double

  * # Boolean

Names are assigned by the programmer. Upper case, lower case and mixed case are acceptable for names. Data elements are not case sensitive in Visual Basic. For instance, WAGE and Wage represent the same data element. Literals are case sensitive. For instance, "Hello" is different from "HELLO".

# Names can exceed 512 characters, which helps make Visual Basic programs self-documenting. It's good programming practice to use names that provide as much information as possible about a data element. Use letters, numbers and the underscore in names as desired.

# Data elements or variables can be set initially within a class or method and can be altered during processing.

1.4.1 String and Character Data Types

# Here are two local text variables

# ' use double quotes for strings and characters

Dim myString As String = "Hello"

Dim myChar As Char = "A"

Strings and characters are enclosed in double quotes. Variables are defined using Dim and As.

# New values are assigned to text variables during processing using the equal operator =

myString = ""

myString = "Bye"

Use Const with As to specify constant string and character data elements – those which cannot be altered or changed

Const langPack1 As String = "Language Pack 1"

1.4.2 Numeric Data Type

# Here are two local numeric variables

Dim myInteger As Integer = 0

Dim myPi As Double = 3.1415926535897931

Variables are defined using Dim and As. New values are assigned to numeric variables during processing using the = operator

myInteger = 10

Use Const with As to specify constant numeric data elements – those which cannot be altered or changed

Const maxSize As Integer = 2500

1.4.3 Boolean Data Type

# Boolean data elements toggle between true and false. Here is a Boolean variable

Dim valueSet As Boolean = False

There may be little added value in using Const to make a Boolean constant but it is allowed.

1.5 Software Development Process

# Every program or system of programs begins with a need. The need is documented and presented in the form of a request for services. If the need is genuine and the resources are available, the request is approved and the process of software development begins. This process consists of

Analysis

\- Developing specifications or objectives based on user

request and additional input from the requester/user.

\- Developing a system plan using CASE tools and

including the inputs, outputs, and number and type of

programs to be written.

\- Developing test data along with controls to keep bad

data out.

Design

\- Designing program processes or methods using

CASE tools, pseudo code, etc.

\- Designing inputs and outputs.

Implementation

\- Writing the programs using Java, C#, Visual Basic, etc.

\- Compiling, linking, testing and debugging all code.

\- Preparing documentation for the requester/user.

\- Converting files as needed.

\- Performing a system test, usually in parallel fashion

where the old and new systems are run side by side for

a period of time.

\- Turning the new system and documentation over to the

requester/user.

# This development process is a good, well-ordered process. The better the analysis and design steps are performed, the easier the implementation task becomes, resulting in lower software development costs.

# It's appropriate at this time to consider a system that may help us remember now the highlighted components listed above, and which will also help keep us focused later on God's transforming Word as we attempt to work in a business software environment as witnesses for Christ (underlines added):

The Christian walk as a system

Objectives:

## 1 Peter 1:4

to obtain an inheritance which is imperishable and undefiled and will not fade away, reserved in heaven for you,

Mark 16:15  
And He said to them, "Go into all the world and preach the gospel to all creation."

Input: the Word of God

Output: walking in the light

## Ephesians 5:8-9

for you were formerly darkness, but now you are light in the Lord; walk as children of light (for the fruit of the light consists in all goodness and righteousness and truth),

Controls, needed to minimize errors:

## Philippians 3:1

Finally, my brethren, rejoice in the Lord. To write the same things again is no trouble to me, and it is a safeguard for you.

## Matthew 26:41

"Keep watching and praying, that you may not enter into temptation; the spirit is willing, but the flesh is weak."

## John 14:15

"If you love Me, you will keep My commandments.

Process or Method:

Titus 3:5

He saved us, not on the basis of deeds which we have done in

righteousness, but according to His mercy, by the washing of

regeneration and renewing by the Holy Spirit,

Romans 12:2

And do not be conformed to this world, but be transformed by the

renewing of your mind, that you may prove what the will of God is, that which is good and acceptable and perfect.

## 2 Corinthians 3:18

But we all, with unveiled face beholding as in a mirror the glory of the Lord, are being transformed into the same image from glory to glory, just as from the Lord, the Spirit.

# Although our Christian walk is not an automated system, this provides us with a basis for understanding the different components required to produce an effective automated system: objectives, input, output, controls and processes or methods. (These verses are not intended to have direct applicability to computers, although some may be referenced in later chapters from a philosophical perspective.) The purpose of this illustration is to show the ordered components of our Christian life, which must also exist in our computer systems. Overall, without these components successfully interacting in our computer system, the system can become ineffective in the same way our Christian walk can become ineffective.

# Once the analysis and design are complete, the implementation begins. The implementation step, particularly writing programs using the Visual Basic language, is the primary focus of the remainder of this text. However, this limited understanding of the analysis and design steps will prove useful as we strive to develop programs in an orderly manner.

1.6 Chapter Summary

# We have seen in this chapter how God is a God of order. He created man in His image, and those who are believers in Jesus Christ are God's workmanship, created to do good works. We can use the Visual Basic language to give glory to God as we produce order from disorder in solving various business problems.

The structure of the language is precise. Use Namespaces with End Namespace to organize classes. Inside Namespaces use Classes which consist of one or more methods used to perform specific activities. Methods can be either Subs or Functions and consist of statements. Statements act on data elements or objects.

# Methods can be arranged in a variety of ways, depending on size and re-usability. Methods can be in Classes within the same Namespace, in Classes within different Namespaces, and they can appear in separate files and projects.

A data element can be defined as textual, numeric or Boolean using Dim and As. Values may be moved to these data elements using the = operator.

The software development process helps us to proceed in an orderly manner as we are presented with requirements and needs of prospective system and program users and are requested to create software for these users. The analysis and design steps present us with the five basic components of every system: objectives, inputs, outputs, controls and processes or methods. We can best remember these components as we relate them to the system of our Christian walk. As we consider these daily in our own lives we can not only be more effective problem-solvers as we endeavor to complete the software implementation steps, but we can be more effective witnesses for Christ as we strive to present ourselves "approved to God as a workman who does not need to be ashamed, handling accurately the word of truth." (2 Timothy 2:15)

1.7 Review Questions

1. Locate a Scripture not specified in the text that identifies God as a God of order.

2. What do we use in Visual Basic to

# accomplish orderliness?

3. How do we identify a comment line?

4. Can we have more than one statement in a method?

5. Is there any advantage to storing Namespaces in separate files?

6. What's the maximum number of characters

# that can be used to name an object?

7. Is object HELLO the same as Hello? Is

# literal "HELLO" the same as "Hello"?

8. What are the three steps of the software

# development process?

9. What are the components of a system which

# we can relate to our Christian walk?

# 10. What Bible verse exhorts us to do quality

# software development?

# 11. How does one become a Christian?

1.8 Projects

# 1. Build and run the program sample1A in section

# 1.3 of the text.

# 2. Build and run the program sample1B in section 1.3 of the text.

# 3. Build and run the program sample1C in section 1.3.1 of the text.

# 4. Create a Visual Basic program to display this

# message:

My name is (your name).

# What is your name?

# How are you today?

# 5. A request has been received to write a program

to print mailing labels. Describe the inputs,

outputs and processes to include.

# 6. A request has been received to create a file

containing names, addresses, hourly rate of pay and date of hire. What sorts of controls would need to be included in the program that will create the file?
CHAPTER TWO

Return to Contents

Methods

# 1 Corinthians 12:18

# But now God has placed the members, each one of them, in the body, just as He desired.

2.1 Methods Overview

# In chapter 1 simple methods were introduced. In this chapter more simple methods are shown and a fully functional application is also used to illustrate methods for label processing.

Methods are typically public or private. Methods can return nothing (Sub) or they can return numbers, text or Boolean values (Function). Methods can pass parameters to other methods.

Starting from Main() other methods can be accessed in Visual Basic. The most basic approach is to access methods exactly once. This is illustrated as follows:

Public Shared Sub Main()

Dim labelFile As New Labels

labelFile.createLabels()

Here we see a new instance of the Labels class being established. Then the createLabels() method is executed for the new instance. Control is transferred to the beginning of the createLabels() method, statements within that method are executed, the method End Sub is reached, and control is returned to Main() which then ends.

# Here's another example

Dim US As New States

US.loadStates(US.statesAbbrev, US.statesArray)

In this case a new instance of the States Class is created. Then loadStates() method is executed for the new instance, passing two parameters. Control is transferred to the beginning of the loadStates() method, statements within that method are executed, the method End Sub is reached, and control is returned to the method that called loadStates().

# We are reminded of how our God of order has set each of us in His church. In 1 Corinthians 12:18 is written, "But now God has placed the members, each one of them, in the body, just as He desired." So we too should place our Visual Basic methods in a proper order just as we desire, and use them in a right manner.

2.1.1 Method Examples

Here's an example using a variable defined at the Class level. The variable is incremented by 1 in Main() and then displayed using another method.

Class mainProcess

' one possible way

' often better to use and pass

' arguments to parameters

Dim test As Integer = 5

' if using Visual Studio, set Main as Startup

' in Project Properties

Public Shared Sub Main()

Dim example As New mainProcess

example.test = example.test + 1

example.testme()

End Sub

Private Sub testme()

Console.WriteLine(test)

End Sub

End Class

# When the code is executed, this results

> simple_method_example1.exe

6

As noted in the commented code, a better practice is to pass an argument to a parameter as shown below

Class mainProcess

Public Shared Sub Main()

Dim example As New mainProcess()

' use argument passed to parameter

example.testme2(8)

End Sub

Private Sub testme2(xxx As Integer)

Console.WriteLine(xxx)

End Sub

End Class

# This result comes back

>simple_method_example2.exe

8

Instead of parameter xxx in the example above, a better practice is to use meaningful names. Please note that Dim is not used when defining a parameter for a Sub or Function.

The next example adds 1 to the argument, then executes the method and also adds 1 after the value is passed to the method. The testme3 method is defined to return an integer value

Class mainProcess

Public Shared Sub Main()

Dim example As New mainProcess()

Dim www As Integer = 77

' add 1 to argument

www += 1

Console.WriteLine(example.testme3(www))

End Sub

' use Function to return a value

Private Function testme3(xxx As Integer) As Integer

' add 1 to parameter

Return xxx + 1

End Function

End Class

# This result is returned

>simple_method_example3.exe

79

# There is no practical limit to the number of parameters. Values may be modified by either the calling or called method depending on the purpose and intended design.

# Parameter data types must correspond in both calling argument list and called parameter lists, and the order of data elements must be the same. The actual names used can be different.

It's possible to have a parameter be Optional. To accomplish this, simply assign a value to the optional variable in the method parameter definition, a value that would never be valid in the context of the method's regular use. In the next example, the optional parameter zzz is set to 0 so no corresponding argument value needs to be tied to zzz in the calling list

Class mainProcess

Public Shared Sub Main()

Dim www As Integer = 77

Dim example As New mainProcess()

' add 1 to argument

www += 1

Console.WriteLine(example.testme3(www))

End Sub

Private Function testme3(xxx As Integer, Optional zzz

As Integer = 0) As Integer

' add 1 to parameter

Return xxx + 1

End Function

End Class

The return command is used for methods that are Functions. The compiler will display an error when return is not used for these methods.

For completeness, this code shows String and Boolean data being returned

Class mainProcess

Public Shared Sub Main()

Dim example As New mainProcess()

Console.WriteLine(example.testme4())

Console.WriteLine(example.testme5())

End Sub

Private Function testme4() As String

Return "Here"

End Function

Private Function testme5() As Boolean

Return True

End Function

End Class

# with results displayed here

>simple_method_example4.exe

Here

True

In all these examples, arguments are passed by value which means the original variable's value is not altered by the method being called. An alternative is to pass by reference, where the original value can be changed by the called method. To use pass by reference, insert the keyword ByRef for a parameter like this

Function validatePart(ByRef browserPart As Part, ByVal

vType As Integer) As Boolean

2.2 Loops Overview

Loops are used to execute statements more than once without having to write the same code again. This section shows three different ways to create loops:

For..Next

While

Do..Loop

2.2.1 For..Next

The For..Next loop lets statements be repeated a fixed number of times. This is good in situations where it is known exactly how many times needed to repeat a set of statements. The following example is part of a complete program Labels.vb available electronically in the chapter 2 samples folder and also partly referenced in the next few pages of this chapter.

For j = 1 To 3

sw.WriteLine("")

Next

The command consists of the word For, a counter initialization, the word To followed by the number of times to execute the loop, and the word Next after the statement(s) to be executed. The counter is implicitly declared, no Dim statement is needed for it anywhere. There can be as many statements or method calls inside the loop as needed. In this example,

1. counter j is set to 1. Other variables can be used and they be set to whatever value is called for in the design.

2. the statement is executed once.

3. the counter is incremented

4. the entire process (2 and 3) repeats two more times

5. control is returned to the statement immediately following

the Next

This particular routine is used to write three blank lines as part of a label output file design. Can you think of other possible applications of For loops?

2.2.2 While

The While loop lets statements be repeated until a condition is met. This next code is executed until an array is completely loaded from a file and the end of the file is reached.

'Read the first line of text

line = sr.ReadLine()

'Continue to read until you reach end of file

While Not IsNothing(line)

abbrev(j) = line.Substring(0, 2)

states(j) = line.Substring(2)

j += 1

'Read the next line

line = sr.ReadLine()

End While

The command consists of the word While followed by the terminating condition, and End While after the statement(s) to be executed. Any statements and/or method calls can go inside this loop, depending on the particular application.

The condition associated with this is based on reading a line from a text file. If the text file is empty or nothing then the condition Not IsNothing(line) is false and the While loop never executes.

# This is what happens when the text file has valid data:

1. the condition is checked to see whether line is not nothing

Since we know the text file has valid data, the function will be executed at least once in this case.

2. the statements inside the While loop are executed

The last statement inside the loop reads the next line.

If this is left out this becomes an infinite loop – one that

never ends.

3. the condition is checked again

if true, step 2 is repeated

if false, control is returned to the statement immediately following the End While

It's important to note that the termination condition may be more elaborate by using And and Or, depending on the design.

# Arrays are discussed in greater detail in chapter 7 while files and databases are covered in chapter 4. Arrays consist of some number of rows, so by using loops an array can be easily loaded from a file, or an array can be searched or output.

2.2.3 Do...Loop

The Do...Loop in its simplest form is another form of the While loop.

Do While condition ' like the While loop

Loop

# There are three more variations

Do Until condition ' loop until the condition is met

Loop

Do ' statements execute at least once

Loop While condition

Do ' statements execute at least once

Loop Until condition ' loop until the condition is met

The first and last of these three loop until a condition is satisfied. For the last two, since the condition is checked at the end of the loop the statements inside the loop are always executed at least once. Here is sample code showing the middle one:

line = sr.ReadLine()

Do

...

' Read the next line

line = sr.ReadLine()

Loop While Not IsNothing(line)

The condition is at the end of the loop, versus the While loop where the condition is at the beginning. Whether to choose one over the other depends on the design.

# Most loop processing is straightforward. If there is a situation where something out of the ordinary may be needed, please note that loops have a couple additional features which can be found in the Microsoft documentation.

2.2.4 Complete Program Example

Now that we have sufficient understanding of the different types of methods and loops, it's time to see the entire Labels.vb program, a program to produce mailing labels using data files. This also serves to review the complete structure of a Visual Basic program.

## Namespace Labels

## Class mainProcess

## ' when using Visual Studio, set Sub Main() as Startup Object

## Public Shared Sub Main()

## Dim labelFile As New Labels

## labelFile.createLabels()

## End Sub

## End Class

Class States

## ...

## End Class

Class Labels

## ...

## End Class

## End Namespace

While some features have been touched on briefly in this chapter many implementation details are still hidden in the two classes States and Labels. Other features will covered in later chapters. The complete program is available for review electronically in the chapter 2 samples folder.

2.3 Chapter Summary

# Just as God has placed the members, each believer, in the body of Christ, so too we must place methods in our Visual Basic programs and access them in an appropriate manner.

The For..Next command can be used to access a set of statements exactly once or a multitude of times. We can perform a set of statements While or Until some condition is satisfied, when a data element takes on a specified value or set of values. We must be careful to avoid infinite loops. We can make complex conditions by using Not, And and Or.

In the chapter 2 samples folder there's a complete VB program that incorporates classes and methods, as well as showing all three types of loops. We now have the opportunity to work with and make changes to these. Since there is nothing new under the sun (Ecclesiastes 1:9) it should be clear that most new programs are really enhancements to or modifications of existing programs, so it's important first to understand the programs presented in the text and elsewhere and work from those models to develop your own particular applications.

2.4 Review Questions

1. Is there a problem with the following code?

# If so, what is it?

Public Shared Sub Main()

Dim RunMe As New mainProcess

RunMe.display()

Main()

End Sub

Private Sub display()

Console.WriteLine("Hello")

End Sub

2. How many times will the mainstream

# method be executed?

Public Shared Sub Main()

Dim field_w As Integer = 37

Dim RunMe As New mainProcess

For i As Integer = 2 To field_w

RunMe.mainstream()

Next

End Sub

Private Sub mainstream()

...

End Sub

3. How many times will the method main_

proc be executed?

Dim exit_flag As Integer = 1

Public Shared Sub Main()

Dim rev3 As New mainProcess()

While rev3.exit_flag <> 1

rev3.main_proc()

End While

End Sub

Private Sub main_proc()

exit_flag = 12

Console.WriteLine("test")

End Sub

4. A truth table for And is as follows, where 0 corresponds to false and 1 corresponds to

# true:

Develop truth tables for Or and Not.

5. Describe one way Do...Loop can be

# especially useful.

6. When might parameters be preferred to variables?

2.5 Projects

# 1. The following is the data description

# for single labels:

Public Structure Label

Public line1FirstName As String

Public line1LastName As String

Public line2StreetAddress As String

Public line3City As String

Public line3State As String

Public line3Zip As String

End Structure

How might this be modified to handle 2-up labels, which are labels that are side by side?

2. Find the labels program in the chapter 2

samples folder and follow the directions in

ch2 readme.txt to change the data paths, then

# compile and execute the labels program.

3. Modify program sample1b from the chapter

1 samples folder to display "Hello" exactly

13 times using a loop.

# 4. Create a new program or modify a sample

program from the chapter 1 samples folder

# to display "Hello" until the user enters the

# word "BYE". To do this declare a string

data element and use the ReadLine command, which is used to accept user input

# from the keyboard as in this example:

Dim checker As String = "GO

...

checker = Console.ReadLine()

The ReadLine command suspends

# processing until the <Enter> key is pressed.

# The user can type any sequence of characters or none at all followed by the <Enter> key to resume processing. Where

# would this new command be placed in the

# program? What other changes are needed?

# Which loop would work best?

# 5. Create a calling-called pair of methods.

# Have the called method display the

# information passed from the calling method.
Part 2

Return to Contents

Tools

1 Corinthians 3:12-13

Now if any man builds upon the foundation

with gold, silver, precious stones, wood, hay, straw,

each man's work will become evident;

for the day will show it, because it is to be revealed with fire; and the fire itself will test the quality of each man's work.
CHAPTER THREE

Return to Contents

Interactive

Programs

# Philippians 4:6

# Be anxious for nothing, but in everything by prayer

# and supplication with thanksgiving let your requests

# be made known to God.

3.1 What are interactive programs?

# As believers, we interact with God through prayer and the study of His Word. We are an interactive people serving an interactive God. We can also interact with the computer, although the information we get from the computer is by no means comparable to what God offers us. All the computer can offer us is what we or someone else has programmed or entered into it.

An interactive program is one where users enter information using a screen, then data is checked for validity and immediately updates a file or database or carries out some other task. This is in contrast to batch programs, where users enter data in batches or bunches of records to be processed at a later time. Historically, users would find errors the next day and have to make corrections then. This meant information was often out of date by the time it reached management. Today, interactive methods via friendly user interfaces provide instant access to update or display information. A web browser is a type of interactive program that displays information on the screen and allows updates to cookies on a computer.

# In the previous chapter methods were looked at. Programs are collections of methods, and interactive programs are the preferred technique today for processing information. Now that we have foundational knowledge of how to use methods, it's appropriate to consider and attempt to understand some useful programs or collections of methods, while still omitting some details until such time as understanding increases. Even then some things may have to be accepted by faith, while others can be accepted after rigorous testing.

# An interactive program or system of programs may consist of one or more of the following:

Menu

Add component

Modify component

Delete component

Display component

Report

# In addition to VB, a basic knowledge of HTML is helpful to fully grasp a VB web application. Not all processing details are presented in this chapter, some are discussed later as new concepts are presented. Not all features of the generated code are explained, and those not touched on are left for self exploration.

3.1.1 Menu

# The purpose of the menu is to have a starting point for other processes within a system. A menu is a list of available choices. Each company has its own standards for what menus look like. The following example shows a web page with a menu:

When Sample Menu is clicked, this appears

The web page is created automatically using Visual Studio and is part of a larger web application. Create the menu application by selecting New Project > Visual Basic > Web > ASP.NET Web Application, name it menu then select Web Forms and let it be created.

Minor tailoring was done to add the drop-down menu. HTML code to create it was added to Site.Master (making use of existing styles in the bootstrap.css file automatically included)

<%-- Change Log \--%>

<%-- RI Date \--%>

<%-- code added for dropdown WDS 9/6/2018 --%>

<div class="navbar-header">

<button class="btn btn-default dropdown-toggle" type="button" data-toggle="dropdown">Sample Menu</button>

<ul class="dropdown-menu">

<li><a runat="server" href="~/Add">Add</a></li>

<li class="divider"></li>

<li><a runat="server" href="~/Display">Display</a></li>

<li class="divider"></li>

<li><a runat="server" href="~/ModifyKey">Modify</a></li>

<li class="divider"></li>

<li><a runat="server" href="~/DeleteKey">Delete</a></li>

<li class="divider"></li>

<li><a runat="server" href="~/Report">Report</a></li>

</ul> </div>

A Change Log is a documentation tool used by companies in various forms to keep track of changes along with the individual responsible for those changes (RI).

Files that were changed from the template are available electronically in the chapter 3 and 4 samples folders in a sub-folder named menu code. These represent a complete working system by the end of chapter 4 based on a simple Parts database. Use File Explorer to copy altered project files from the menu code sub-folder to the menu folder inside the Visual Studio Projects menu folder. Build the program and run it to see the menu shown above. If there are any issues with this process, try Adding Existing Items within Visual Studio. The application can be tailored further as desired.

# So far we have a working application and have written no Visual Basic code!

# High-level processing for a menu and related application is fairly straightforward. The option selected by the user from a menu is used to run a specific application component. At any time a menu can be accessed again to use another component depending on what the user is working on.

# The next sections discuss the rest of the components of the complete working system with varying levels of detail.

3.1.2 Add component

Use this to add new rows to a database. The sample code adds data to a MS Access table. The Add.aspx page is displayed by this HTML menu code after choosing Add from the menu

<li><a runat="server" href="~/Add">Add</a></li>

# A screen to add one row at a time is illustrated below:

Code to provide the date and time is added to the file Add.aspx.vb using Visual Basic

Protected Sub Page_Load(ByVal sender As Object, ByVal e As

System.EventArgs) Handles Me.Load

Label1.Text = DateTime.Today.ToString("MM/dd/yyyy")

Label2.Text = DateTime.Now.ToString("HH:mm")

End Sub

# Further discussion of form design is presented in section 3.2.

The application includes a Parts Class, and code needed to access the Parts Class and add a new part is shown below.

Protected Sub btnSave_Click(sender As Object, e As EventArgs)

Handles btnSave.Click

'interact with Parts class

Dim partData As Parts.Parts = New Parts.Parts()

partData.createPart()

End Sub

Within the Parts Class, part data is received from the browser and checked for errors. If there are no errors, a row is added and a Success screen displayed. Further details of the code are discussed below and in later chapters.

3.1.3 Modify component

This allows the user to change rows added earlier. The process requires two steps. The first is to receive the key field. The second is to take the valid key and locate the rest of the row in the database. (The process of using keys is discussed in more detail in Chapter 4.) Two separate screens are involved in the sample code provided. The second one is similar to the add screen, but with no entry allowed for the key field since a key can't be changed. The first screen looks like this:

# Complete modify functionality is provided electronically, and can be tailored as needed to suit a particular need. The essence of the modify process is that a unique search key field must be correct on the first screen for the second screen to be displayed. Then any changes made on the second screen must be correct for the row to be permanently changed, although the user has the option of changing their mind and backing out of the entire process.

3.1.4 Delete component

# The user can delete a row no longer needed. Delete should always be used with caution, with file backups being made regularly. This is a two step process. The user enters a key on one screen, information is then displayed on a second screen. The user is prompted to confirm the deletion, with the option of keeping the row in case the wrong key was entered. It's somewhat similar to the modify process, but simpler.

# The first screen resembles the one for Modify. The second screen looks like this:

3.1.5 Display and Report components

The display function lets the user view a row or rows on the screen. For a single entity, a screen contains information from the file or database the user needs to do their job, similar to previous screen examples. The sample code provided uses a single screen to show all rows (by the end of chapter 4), as well as a hyperlink column pointing to the first modify screen to make changes if desired

From this screen a record or row can be Selected for modifying. By clicking Select, the first modify screen is displayed containing the selected part number.

# A reporting function allows for electronic or hardcopy reports to be generated. Reports vary and are designed as needed for each user and each system. Depending on the design, the report may be sent to a file or sent directly to the printer. If sent to a file, users later have the option of printing and/or viewing. The reporting function may display another menu of reports for a large system, or create a single report for a small system. Reports are covered in chapter 8.

3.2 Form Design

Now that we've had a taste of some interactive functions, it's time to examine Form Design. For web-based forms the way information is presented and retrieved, along with the design of navigation between forms, is also important and covered in section 3.3.

To create a new web form right-click the project name and choosing Add New Item, select Web Form with Master Page, change the name from WebForm1 to a meaningful name, click Add, select Site.Master. Right-click on the name of the newly created form and choose View > Designer to add controls to the form. Choose View > Toolbox to see controls available to add to the form.

Forms can be designed by dragging and dropping labels and text boxes and other controls from the Toolbox to the form while in Design mode. Once the first ones are placed, others can be placed by copying and pasting. To align labels and fields, spaces can be added after the text in each label until a desired look is achieved.

Form elements have properties associated with them including an ID, colors, enabled vs. not enabled, visibility, etc. Some properties are discussed below while others are left for self exploration.

3.2.1 Defining Fields

Fields used for input and output appear as text boxes on a form and can be defined in Visual Basic code as simple variables or by using the Structure keyword. Here's how a part is described in the Parts Class using Visual Basic:

Public Structure Part

Dim PartID As Long

Dim PartNumber As String

Dim PartDescription As String

Dim PartUOM As String

Dim PartUnitCost As Double

Dim PartLocation As String

Dim PartDateUpdated As DateTime

End Structure

# The application architect and the context in which fields are used determine what fields can be entered and received from the user and which ones are automatically created.

Name or rename text boxes on a form by changing the ID property while the form is displayed in Design mode. Examples of form IDs include txtPart, txtDesc, etc.

3.2.2 Defining Display-Only Fields

There are three ways to display information that can't be modified. One is by using a Label. The others are by setting the Enabled property of a TextBox to false or the ReadOnly property to true. Here's an example showing ReadOnly set to true.

Setting Enabled to false is good when data is only being displayed. Setting ReadOnly to true serves a similar purpose but also allows the value in the ReadOnly TextBox, such as a key field, to be accessed when using two screens to modify or delete data.

3.2.3 Colors

# As with any Microsoft product every variety of color is available for text, borders, foreground, background, etc. Use colors to enhance web page design.

3.2.4 GridView

The GridView for the Display form is a special control for displaying data. It appears in the Toolbox under the Data heading

An interesting property for the GridView used to allow selection of a single row from the table is AutoGenerateSelectButton

The property is tied to this code in Display.aspx

AutoGenerateSelectButton="True"

OnSelectedIndexChanged="displayDataGrid_SelectedIndexChanged"

3.3 Screen Handling

# In web applications information is presented to the user, then the user can pass information back to the program and receive a response. For Visual Basic web applications some of this can be accomplished using HTML and JavaScript, while many processes involving data use Visual Basic.

3.3.1 Displaying the Screen

# Web pages are displayed from the menu using HTML code or Visual Basic methods.

HTML code in Site.Master displays the main page for each process when a menu option is selected.

<div class="navbar-header">

<button class="btn btn-default dropdown-toggle" type="button"

data-toggle="dropdown">Sample Menu

</button>

<ul class="dropdown-menu">

<li><a runat="server" href="~/Add">Add</a></li>

<li class="divider"></li>

<li><a runat="server" href="~/Display">Display</a></li>

<li class="divider"></li>

<li><a runat="server" href="~/ModifyKey">Modify</a></li>

<li class="divider"></li>

<li><a runat="server" href="~/DeleteKey">Delete</a></li>

<li class="divider"></li>

<li><a runat="server" href="~/Report">Report</a></li>

</ul> </div>

For modify and delete, after a part number is entered the rest of the data is prepared for the web page using this Visual Basic code in the Parts Class.

HttpContext.Current.Response.Redirect

("Delete.aspx?Key=" + part1.PartNumber +

"&Desc=" + part1.PartDescription +

"&UOM=" + part1.PartUnitCost +

"&Loc=" + part1.PartLocation)

The following code is in the Page_Load method of the web page to be displayed, which causes data to appear on the page.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As

System.EventArgs) Handles Me.Load

....

txtPart.Text = Request.QueryString("Key")

txtDesc.Text = Request.QueryString("Desc")

...

When processing errors occur, the Error.aspx page is displayed using Visual Basic code like this with information to help fix errors

// post error to web page

HttpContext.Current.Response.Redirect

("Error.aspx?Errors=" + errMessage)

3.3.2 Receiving Data

This code in the Parts Class is used to get data supplied by the user from the web page to the program. Only Enabled data can be received.

'need for StreamReader

Imports System.IO

Dim sReader As StreamReader = New

StreamReader(HttpContext.Current.Request.InputStream)

Dim requestFromPost As String = sReader.ReadToEnd()

The user clicks the Save button to trigger this. Visual Basic code to add a new part is coded within the btnSave_Click method as seen here.

Protected Sub btnSave_Click(sender As Object, e As EventArgs)

Handles btnSave.Click

'interact with Parts class

Dim partData As Parts.Parts = New Parts.Parts()

partData.createPart()

End Sub

The validatePart method of the Parts Class includes the StreamReader code.

3.3.3 Using Cancel to Exit a Screen

# The easy way to cancel processing is to leave the current screen and go to the home page, which is the choice made for the textbook application. Here's code used to do this -

Protected Sub btnCancel_Click(sender As Object, e As

EventArgs) Handles btnCancel.Click

Page.Response.Redirect("Default.aspx")

End Sub

# Other options are possible depending on how complex a design is desired and how many resources are available for implementation.

3.3.4 Using Back to Return to a Screen

# JavaScript includes a feature to go back one page and this has been incorporated in several pages to return to a previous page.

<asp:Button ID="btnCancel" runat="server" Text="Back"

OnClientClick="window.history.go(-1);return false;"/>

3.3.5 GridView and Modify

This code takes the part number from a selected row in the GridView (after chapter 4 completed) and sends it to the first modify screen

Dim DataKey As String =

displayDataGrid.SelectedRow.Cells(1).Text.ToString

HttpContext.Current.Response.Redirect("ModifyKey.aspx?key=" +

DataKey)

In the Page_Load method of the first modify screen this code checks if QueryString contains a value

' if being redirected from the display screen pull the key

If Not String.IsNullOrEmpty(Request.QueryString("key"))

Then

txtPart.Text = Request.QueryString("key")

End If

3.4 Chapter Summary

An interactive program consists of a menu, serving as an interface to other programs, along with processes to manipulate a database by adding, modifying or deleting rows. There would be no value in updating a database if it could not be displayed or reported. These processes have now been presented and can be used throughout the remainder of the text, as is or with changes, after chapter 4 guidance. The entire suite of these essential interactive processes is available electronically to be used as needed.

A screen or form is the principal input/output medium for the interactive program. Form design is an important task, yet one which can be performed quite easily with a variety of popular tools. There are many properties that can be changed including ID, color, visibility and enabling or disabling a field, etc. The GridView control has a property that allows for the selection of data that can then be shared with other methods and screens. The Visibility property can be especially useful for Windows Forms applications, and may have some value for web processing but it's not used in any of the sample code.

Screen manipulation for Visual Basic web applications can be accomplished using HTML, JavaScript and Visual Basic. HTTP Redirect and StreamReader methods work using Visual Basic, and VB code is also used to provide the current date and time.

# We can soon build and execute useful web applications to create, modify and display data. In the next chapter, code for manipulating data is shown in greater detail to more fully understand how interactive programs work and to complete the implementation.

3.5 Review Questions

# 1. Find a Bible verse that illustrates how God

# is an interactive God.

# 2. Provide a definition of a useful program.

# 3. What type of program should a student

# registration program be, batch or interactive?

# 4. What's the purpose of a menu? What is a

# menu?

# 5. What's the matter with the following add

# routine?

Protected Sub btnSave_Click(sender As Object, e As

EventArgs) Handles btnSave.Click

'interact with Parts class

partData.createPart()

End Sub

# 6. Why might the user not want to follow

# through with a change or delete operation?

3.6 Projects

1. Follow the instructions in the chapter to create a new web application then copy all files from the chapter 3 samples menu

# folder to replace the default files and include

# new ones. Build the parts inventory system

and execute it. Explore the user interface. NOTE: Some functionality will not work

# until additional information is provided in

# chapter 4.

2. Develop a test plan to test the modify component of the parts inventory system from the previous problem. To do this, first

# examine the screen and consider what you

would expect to be tested, making a check-

list to record observations. (Since you didn't participate in the design of the original system, there's no way you could know what was supposed to be included in a test plan). Add rows using the add component, then rigorously test the modify process and record any deviations from the plan.

# 3. Create a new web form that looks like this (but without the actual data)

Add code to the Page_Load method to

# provide the current date and time.

4. Design a HELP screen by first adding a

# new web form and saving it. Within the HELP screen include brief information on

# which selections can be made and what operations can be performed. Use the name

Help.aspx for the Help page. Build the application to check for errors.

# 5. Integrate the HELP screen created in Project 4 with the Parts application.
CHAPTER FOUR

Return to Contents

Database Input and

Output

# Ephesians 5:8-9

# for you were formerly darkness, but now you are

# light in the Lord; walk as children of light (for the

# fruit of the light consists in all goodness and

# righteousness and truth),

# Matthew 15:11

# "Not what enters into the mouth defiles the man, but what proceeds out of the mouth, this defiles the man."

4.1 What are databases?

Databases are the output from some process or system. They are input to other processes or systems. We are to output God's light in our lives, so that God's Word and His Spirit might be input into someone else's life. The fruit of the Spirit is to be output from our lives, that others might be blessed. Our output can only change when God's Spirit is input and takes control of our lives, by His marvelous grace.

Databases are also how and where data is stored. Both good and bad data can exist in a database, depending on the controls used. For man, that which is stored up in the heart is bad because of the sinful condition of the heart. Born-again believers, however, are repositories of God's good Spirit.

Before databases existed, files were stored sequentially on paper tape and cards. Files are made up of records, while records consist of fields. Examples of records might be an employee payroll record or a student record. Examples of fields would include last name field, student ID field and expiration date field. VB also works with Windows text files.

Databases consist of tables filled with columns and rows. Rows are synonymous with records, while columns are fields. Databases are stored on hard disks, CDs or DVDs and data can be accessed quickly and efficiently using indexes. One popular form of database is the relational database.

A concern today is using and maintaining data on networks. This often involves having exclusive control of a row or table during an update operation (such as changing address information) and we accomplish this by row or table locking.

4.2 Database Details

Visual Basic works with Microsoft SQL Server, MS Access, open source MySQL, and other databases. SQL code to create and load the sample Parts table in SQL Server has been provided electronically in the chapter 4 samples folder, along with sample data. These can be adapted for use with MySQL. A simple MS Access database has also been provided. Adding tables and columns to an Access database is fairly straightforward using the interface provided by Microsoft. The textbook examples and sample code are primarily based on an MS Access database but can be easily modified to work with SQL Server or other databases.

If MS Access is not installed search for and download the free Microsoft product AccessDatabaseEngine to use for interfacing between the Visual Basic application and the database.

# To set up the MS Access database for use with the sample code, copy Inventory.accdb from the chapter 4 samples folder to a folder like this

C:\Users\\[yourname]\Documents\VB_Data

Using the Web.config file is how Microsoft establishes connections between databases and .NET applications. In the Web.config file add or change the following line entry, which is specifically for MS Access, to match the above location on the file system

<configuration>

<connectionStrings>

<add name="PartConnectString" connectionString

="Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:/ Users/Wes/Documents/VB_Data/Inventory.accdb"/>

</connectionStrings>

</configuration>

Note that the slashes are reversed in the entry above. In the VB code, the lines shown below establish and open the connection:

Dim strConnString As String = ConfigurationManager.

ConnectionStrings("PartConnectString").

ConnectionString.ToString()

Dim con As OleDbConnection

con = new OleDbConnection(strConnString)

con.Open()

# To close the connection include this statement

con.Close()

# Please keep in mind that to change from MS Access to SQL Server or another database, use a slightly different connection string in the Web.config file. Information on setting up different connection strings is readily available on the web.

# This example from the parts inventory database is referred to in the next sections:

4.2.1 Primary Key

A primary key is a unique column or group of columns associated with each row of a database table. The primary key in MS Access has a Data Type of AutoNumber and is identified by the key image in the leftmost column. Because the primary key entry is unique (Indexed with No Duplicates as seen above), it serves as a search key to locate rows in the table.

# Examples of primary keys include employee number, student number and part number.

4.2.2 Alternate Indexes

An alternate index, parts_item_number in the screenshot above, can also be used to search the file. An alternate index may or may not have duplicates, depending on the system design. Specify alternate indexes when the table is first defined in MS Access.

# One other thing about the parts_item_number is the length or Field Size which is set to 8. This length must be checked in the application and VB code to do this is covered in chapter 6.

4.2.3 Numbers

The last entry to be discussed here is parts_unit_cost which is defined as a number. Since any value can be entered on the web page, it's important to ensure that numbers are just that. Number validation is discussed in chapter 6.

4.3 Database Commands and VB

# To use MS Access database commands, be sure to include these statements at the beginning of the code file

'need for database access

Imports System.Data.OleDb

Imports System.Data.SqlClient

# It's also good to have the row columns defined in the class file. For the Parts table this is at the top of the class as we saw in chapter 3

Public Structure Part

Dim PartID As Long

Dim PartNumber As String

Dim PartDescription As String

Dim PartUOM As String

Dim PartUnitCost As Double

Dim PartLocation As String

Dim PartDateUpdated As DateTime

End Structure

# Next establish the connection as discussed in section 4.2. Once the connection is ready and open, define an OleDbCommand object

Dim cmd As OleDbCommand

# This is used to put an existing connection together with a new command. VB programs may have multiple commands associated with a single connection, depending on the design.

There are many operations one can perform on a database table. These are mostly done with regard to individual rows in the table, with one exception being the display method in the sample code which accesses all rows. Here is what's covered in the remainder of this section

## Handling Errors

## Select

## Insert

## Update

## Delete

4.3.1 Handling Errors

Handling errors is one aspect of controls in a system. Database commands should be used within the error handling construct to enable providing the best feedback to users and avoid confusion if and when errors occur. VB uses this construct to effectively handle errors

Try

Catch

Finally

End Try

The Try block is where main processing occurs, the Catch block is where errors are handled and the Finally block is done to wrap things up.

# Here's an example

Try ' code to update

cmd.Parameters.Clear()

cmd.Parameters.AddWithValue

("@Desc",dataToUpdate.PartDescription)...

cmd.ExecuteNonQuery()

' show success page

HttpContext.Current.Response.Redirect

("Success.aspx", False)

Catch e As Exception ' problem with database, post error

HttpContext.Current.Response.Redirect

("Error.aspx?Errors=" + e.Message, False)

Finally ' close connection

con.Close()

End Try

In the Try block a command is used and a success message displayed.

If an error occurs with the database that has not already been accounted for, the Catch block is used. Most data errors are handled prior to this point by data validation code, other errors are resolved during testing, but some unexpected errors may still occur. When they do, the built-in Exception object gets information that can be used to identify the problem. In particular, the Message property provides specifics that can point the user in the right direction.

There can be situations when the Exception object is not used, such as in cases where special error handling is done.

The Finally block wraps things up, in this case closing the connection. The Finally block executes before the Catch handles the error.

4.3.2 Select

Select can retrieve single or multiple rows, or a count of the number of rows. In this first example one row is retrieved using the part number obtained from the web browser.

Dim str2 As String =

"Select * from Parts WHERE parts_item_number = '" + browserPart.PartNumber + "'"

cmd = new OleDbCommand(str2, con)

Try

reader = cmd.ExecuteReader()

reader.Read()...

A string is defined and set equal to the SQL Select statement. The asterisk means "grab all columns". The part number from the browser is a string and must be enclosed in single quotes, the first one put at the end of the first string. The plus sign and ampersand can be used to concatenate strings in Visual Basic. In this case the SQL code and starting single quote are concatenated with the part number and the closing single quote which is part of the last string.

A new OleDbCommand object is created using the SQL string and the connection object. The command object applies the ExecuteReader() method to retrieve the data and create the reader object. Then reader.Read() tries to access the data. At this point we do NOT know if any data was retrieved, for instance if the part does not exist then no data has been retrieved.

Later in the code, the following line tries to grab a single column from the retrieved row of data. If no row has been found, a Catch block is triggered and appropriate error processing takes place.

partNumber = (reader("parts_item_number").ToString())

The next example gets specific columns of all data to put in a GridView. The partGrid object is a GridView passed as a parameter from a method in a form.

'for DataTable required by GridView

Imports System.Data

Dim str As String = "Select parts_item_number,

parts_item_description, parts_unit_of_measure,

parts_unit_cost, parts_location from Parts"

cmd = new OleDbCommand(str, con)

Dim displayAdapter As OleDbDataAdapter =

New OleDbDataAdapter(cmd)

Dim allParts As DataTable = New DataTable()

displayAdapter.Fill(allParts)

partGrid.DataSource = allParts

partGrid.DataBind()

The Imports entry is placed near the beginning of the code file and is required in order to reference a DataTable in code. The DataTable is required to place data in a GridView.

A string is defined and set equal to the SQL Select statement, with specific columns being referenced. Since all rows are being retrieved there's no need to concatenate strings or use single quotes.

A new OleDbCommand object is created using the SQL string and the connection object. Then a new OleDbDataAdapter object is instantiated based on the command object. A new DataTable is needed and the OleDbDataAdapter loads the DataTable using the Fill method. The DataSource of the GridView object is set to the DataTable, and finally the data is bound to the GridView. The data then appears in the browser.

One other aspect of a Select statement that can be helpful for the GridView is seen here

Dim str As String = "Select parts_item_number as Part,

parts_item_description as Description,..."

The as option is a way to rename the column name to something simpler. This is especially useful for the GridView since the column names become the column headings for the grid.

The final example applies when there is an expectation that a row does not exist, and therefore the error condition is when the row does exist. This could occur in an add program to ensure that a key does not exist as part of a validation routine before trying to add a new row.

Dim str3 As String = "Select COUNT(parts_item_number)

from Parts WHERE parts_item_number = '" +

browserPart.PartNumber + "'"

cmd = new OleDbCommand(str3, con)

' check that part number Is Not in database

Dim result As Int32 = 0

result = Convert.ToInt32(cmd.ExecuteScalar())

If result = 0 Then

' part does Not exist, can be added - good

Count is a SQL function that provides a count of rows returned using a Select statement. The ExecuteScalar command always retrieves a single value, in this case the count, which has to be converted for use in Visual Basic. We want the count to be zero, which means the part is not there so we can then add it.

4.3.3 Insert

Use the Insert command to add a new row to a table. Provide all the new data, including the key, and the row is added.

Dim str2 As String = "INSERT into Parts(parts_item_number, parts_item_description, parts_unit_of_measure, parts_unit_cost, parts_location)

VALUES(@PartNum, @Desc, @UOM, @UnitCost, @Loc)"

cmd = new OleDbCommand(str2, con)

Try ' code to insert

cmd.Parameters.Clear()

cmd.Parameters.AddWithValue("@PartNum",

dataToUpdate.PartNumber)

cmd.Parameters.AddWithValue("@Desc",

dataToUpdate.PartDescription)

cmd.Parameters.AddWithValue("@UOM",

dataToUpdate.PartUOM)

cmd.Parameters.AddWithValue("@UnitCost",

dataToUpdate.PartUnitCost)

cmd.Parameters.AddWithValue("@Loc",

dataToUpdate.PartLocation)

cmd.ExecuteNonQuery()

' show success page

HttpContext.Current.Response.Redirect

("Success.aspx", False)

The general format of the Insert command is

Insert INTO tablename(column1, etc.) VALUES(value1, etc.)

# All column names are together separated by commas and all values are together separated by commas.

In Visual Basic values to be inserted are represented by parameters starting with an @ symbol. First clear all parameters, then set each parameter equal to the value to be inserted. Finally, use the ExecuteNonQuery() method to execute the insert command.

4.3.4 Update

The Update command is used to make changes to a row for a given key.

Dim str1 As String = "UPDATE Parts SET

parts_item_description = @Desc,

parts_unit_of_measure = @UOM,

parts_unit_cost = @UnitCost,

parts_location = @Loc

WHERE parts_item_number = @PartNum"

cmd = new OleDbCommand(str1, con)

Try ' code to update

cmd.Parameters.Clear()

cmd.Parameters.AddWithValue("@Desc",

dataToUpdate.PartDescription)

cmd.Parameters.AddWithValue("@UOM",

dataToUpdate.PartUOM)

cmd.Parameters.AddWithValue("@UnitCost",

dataToUpdate.PartUnitCost)

cmd.Parameters.AddWithValue("@Loc",

dataToUpdate.PartLocation)

cmd.Parameters.AddWithValue("@PartNum",

dataToUpdate.PartNumber)

cmd.ExecuteNonQuery()

' show success page

HttpContext.Current.Response.Redirect

("Success.aspx", False)

The general format of the Update command is

Update tablename SET column1 = value1,

column2 = value2,

etc.

WHERE key column = key value

Column/value pairs are separated by commas. The last comma/value pair has no comma and is followed by the Where clause.

In Visual Basic values to be updated are represented by parameters starting with an @ symbol. First clear all parameters, then set each parameter equal to the value to be updated. Finally, use the ExecuteNonQuery() method to execute the update command.

4.3.5 Delete

Use Delete to remove a row or an entire table. A best practice is to first display all columns for the row to be deleted so the user can confirm they have the correct row. Once deleted the row disappears entirely. Another approach used is where a row is marked as deleted but remains in the database. This is left for the interested reader as a project at the end of the chapter.

The following command removes a single row:

Dim str3 As String = "DELETE from Parts WHERE

parts_item_number = @PartNum"

cmd = new OleDbCommand(str3, con)

Try

cmd.Parameters.Clear()

cmd.Parameters.AddWithValue("@PartNum",

dataToUpdate.PartNumber)

Dim returnCode As Integer = cmd.ExecuteNonQuery()

' show success page

HttpContext.Current.Response.Redirect

("Success.aspx", False)

The general format of the Delete command is

Delete FROM tablename

WHERE key column = key value

Be careful to always include a Where clause or all data in the table will be deleted!

4.4 Text Files

# Databases are the primary data storage mechanism for web applications, but Windows text files can also be of use with Visual Basic applications as was seen in chapter 2 with the labels program.

# Just as with databases, it can be helpful to have the structure of the text file at the top of the Class. For the labels file

Public Structure Label

Public line1FirstName As String

Public line1LastName As String

Public line2StreetAddress As String

Public line3City As String

Public line3State As String

Public line3Zip As String

End Structure

Processing for the labels application involves reading an address file one record at a time to create the label file. A states file is also involved to get the state name for a given state abbreviation. In the sample program provided there is no added value in having Structure for either the states file or the address file. In the case of the address file, that data is copied directly to the label output. For the states file the information is copied to an array.

4.5 Using Text Files with Visual Basic

To use a text file, first open it as input or output. An existing input file is opened using StreamReader, while a file to be created is opened as output using StreamWriter. Opening a file as output erases or destroys any previously existing file with the same name. Here are examples showing how to open two of the files -

'Pass file path and name to StreamWriter constructor

Dim sw As StreamWriter = New StreamWriter(myPath &

"labels.dat")

'Pass file path and name to StreamReader constructor

Dim sr As StreamReader = New StreamReader(myPath &

"address.dat")

Please note the use of the ampersand for concatenation. Assuming all files reside in the same path it's helpful to first define a constant with that path information like this

Const myPath As String = "c:\\\Users\\\[yourname]\\\Documents\\\VB_Data\\\"

When file processing is complete, Close the file. Using the variables defined above, these would be the close statements

sw.Close()

sr.Close()

Other methods available to text files are ReadLine, Write and WriteLine. When extracting data from a text file the Substring method is used where each line in the file has the same positioning. Substring has two parameters, starting position and length. This excerpt from Labels.vb in the chapter 2 samples folder illustrates how these methods can be used together.

line = sr.ReadLine()

Do

...

label1.line2StreetAddress = line.Substring(30, 25)

...

line2 = label1.line2StreetAddress

...

sw.WriteLine(line2)

...

line = sr.ReadLine()

Loop While Not IsNothing(line)

# For files with varying positioning where Substring may not work easily, such as comma-delimited or tab-limited, it's better to create a database table.

Historically, writing to a text file was the way to create a report. Today there are many tools to simplify report creation from databases. This topic is covered in chapter 8.

There is no key component for text files as there is for database tables. Because of this updating a text file is somewhat complex. There would be two inputs involved. One input would contain transactions and the other would have the original data. Both inputs would have to be sorted. A third new file would be created containing all new and changed records. In such cases databases are the preferred choice, and thank God for database processing!

4.6 Locking

# When a database table is on a shared drive and accessed by many users in a network environment, and when data is to be modified by a program it's appropriate to provide exclusive or shared use of either a particular row or an entire table. Once a decision is made about which database product to use in a production environment please refer to that specific system documentation for guidelines and keywords related to locking rows.

4.6.1 Locking Tables

# Rarely is it required to lock an entire table. Situations where it would be appropriate to lock the entire table are when it's being converted to a new format, or it's being provided a new set of values for a particular field or fields all at one time. The alternative to locking the entire table is to lock an individual row.

4.6.2 Locking Rows

# The advantage of locking an individual row is that other users can access the rest of the table while the locked row is being updated. With a shared lock, the row being updated can still be read by other users while in the process of having changes made to it.

Using MS Access, open the database used with the sample code, then open the table and switch to Design mode. Leave that open, go to the web application and try to update a row. The following error message is displayed

# Design mode in MS Access locks an entire table. Within MS Access itself there is other locking capability as shown in this screen

# These settings however do not apply to Visual Basic. For a system with a small user community using a web application, the likelihood that a conflict would occur is very small. For a system with a large number of users, it is best to have a more robust system such as SQL Server to handle such conflicts.

4.7 Chapter Summary

In this chapter both databases and text files have been considered. Databases are a key component of every processing application today, while text files still have a useful purpose.

To help avoid adding garbage to a database, validation techniques must be employed. These are covered in chapter 6. To keep garbage out of ones heart, validation against God's Word and the Holy Spirit is important.

Error-handling can be accomplished with Try-Catch-Finally blocks, special error handling routines, or by combinations of the two.

For .NET applications a connection string is needed to point to the database. With this connection string commands can be used to select, insert, update and delete data.

Text files are identified by providing the path and file name and are accessed using either StreamWriter or StreamReader. ReadLine and WriteLine are two methods available to use with text files, along with the Write method which doesn't write the end of line character. Substring is helpful for extracting data from a record. Both + and & can be used for concatenation. During development of the software for the text, errors occurred using the plus sign which went away when the ampersand was used instead.

For databases stored on a shared network drive or a server, locking rows needs to be taken into consideration along with the use of a robust database product that can handle locking effectively.

4.8 Review Questions

1. What is to be input and output in our lives as born-again Christians?

2. Give an example of a row. Give an

example of a column.

3. What's one difference between a primary

# key and an alternate key?

4. How many primary keys are allowed for

# a table?

5. List two ways of handling errors.

6. What's the purpose of the delete

command?

7. Can update and insert be used with a

# text file?

8. Why would using the + for concatenation

# cause runtime errors? See if you can create

# an example where an error occurs.

4.9 Projects

# 1. Design an algorithm to create 2-up labels.

# (See project 1 of chapter 2)

# 2. Write a program to create 2-up labels using

the text file address.dat in the chapter 2

samples folder. Don't use the states.dat file,

just use the state abbreviation in the labels

# output.

# 3. Create a program which writes a single

# record to a text file containing the current

# date along with the message

"This is the day which the Lord has made".

# 4. Write a program to create a database table

using the address.dat file as input. Use Try-Catch-Finally to handle errors.

5. Write a program to read the states.dat file and update a database table. Use Try- Catch-Finally to handle errors.

# 6. Write an interactive program to display the state name whenever the state abbreviation is entered, where the states are contained in a database table.

7. Another way to delete data is to mark it

# deleted. Add a new field to the parts table

# and show the code needed to "delete" by

# changing the new field value. How would

# this change the Select statement to retrieve

# active rows?
CHAPTER FIVE

Return to Contents

Arithmetic

# Leviticus 27:23

# "then the priest shall calculate for him the amount of

your valuation up to the year of jubilee; and he shall

# on that day give your valuation as holy to the LORD."

# Luke 14:28

# "For which one of you, when he wants to build a

tower, does not first sit down and calculate the cost,

to see if he has enough to complete it?"

5.1 The Importance of Arithmetic

# In ancient times, a slave was worth 30 pieces of silver. This was the price Judas received for betraying our Lord and Savior Jesus Christ. But Judas failed to count the cost, and in so doing (unless he repented) is doomed to an eternity in a burning lake of fire. He failed to recognize the importance of arithmetic. He did not calculate the sum of his mistakes, his sins became multiplied, his inheritance may be divided among all those who turn their backs on God, and he has been subtracted from the number of the apostles (Acts 1:17).

# Since the beginning of commerce, calculation has been an integral part of our very existence. Businesses use electronic accounting systems routinely today. As Christians we are to count the cost, be good stewards, etc. A lot of contemporary fiction has highlighted the idea of the computer at work to assist Satan in economically controlling the world. Revelation 13:17-18 states that no one will be able to buy or sell without a special mark which is "the name of the beast or the number of his name." Revelation 13:18 states "Let him who has understanding calculate the number of the beast, for the number is that of a man; and his number is six hundred and sixty-six." This lends itself to the idea that computers will somehow be used to force everyone into an accounting system controlled by Satan and his cohorts.

# It's possible, though not likely, that something we do for good in our programming of calculations into the computer will be used for evil purposes. That should certainly not be our goal however, but our goal should be to honor God in all that we say and do. As long as we are faithful witnesses for Christ He will be faithful to us.

5.2 Arithmetic Objects

There are a number of entities which can be considered as arithmetic objects, each with a defined purpose or function. The counter is one of the simplest arithmetic objects, used even in Bible times with manual systems. Visual Basic examples might be

Dim checkCount As Integer = 0

Dim inputCount As Integer = 0

One purpose of a counter is to keep track of the number of rows in order to provide a reference to check for errors at various points in the system. Counters should be set to zero before being used. In a payroll system, an employee list can include a count of all employees. When a payroll check register is printed, another count can be calculated. If these two numbers differ, it may indicate a problem in the system. This example shows how feedback from different components of the same system can provide visible controls to check for errors.

Calculated objects are those which are based on a calculation involving two or more numeric data elements. Examples include gross pay, net pay and grade point averages.

Dim payrollGrossPay As Decimal

Dim payrollNetPay As Decimal = 3.1875432D

The Decimal data type is used in Visual Basic for payroll and other accounting systems. It is good practice when setting a decimal value to use the letter D as seen above, otherwise the precision of a calculation may be impacted.

# A calculated object is produced by inputs provided by the user and possibly data existing in other tables in conjunction with arithmetic operators. The new object may or may not be stored in a table. Here is an example of payroll calculations

payrollGrossPay = 2000D

payrollNetPay = payrollGrossPay – 200.5D

# There's more than one way to display numbers. Here is one that's straightforward

Console.WriteLine(payrollNetPay.ToString("C"))

The C character is the currency format symbol. The above statement is saying that in the process of formatting the pay output as a string also include the currency format which gives this result $1,799.50 instead of 1799.5.

The following code without the D will work

payrollNetPay = payrollGrossPay – 200.5

If the D is left off and the number has a decimal part, Visual Basic will handle it. However, for larger numbers the result may be less precise.

In the example code below the D is left off the first number which becomes immediately truncated, while the second number retains all its digits

Dim piShort As Decimal = 3.1415926535897931

Dim piLong As Decimal =

3.1415926535897932384626433834D

Extended figures are also calculated figures based on two or more numeric data elements defined within a system, such as

Dim extendedCost As Decimal = 0

Dim extendedSales AS Decimal = 0

# Extended cost is the cost per unit of an item multiplied by the number of units manufactured or purchased. Extended sales is the retail price per unit multiplied by the number of units sold. Units might be measured in terms of a single item, a box of items, or some other quantity appropriate for the item. The advantage of an extended figure is that it doesn't take up space in a table, it's merely calculated each time for a report, display or some other program.

The accumulator keeps track of running totals of fields or calculated objects. Accumulators should be set to zero before being used. There is accumulated cost, accumulated net sales, accumulated payroll medicare deductions, etc.

Related to the accumulator are the subtotal, total and grand total. The subtotal is an accumulated figure for a particular group such as store, salesperson or branch. A total is the sum of subtotals. A grand total is a collection of totals. In many instances the grand total and total are equivalent. One specific example where all three are used is a payroll system where each employee has a subtotal representing their labor hours by cost center, each cost center has a total, and the cost center totals are part of the grand total for the organization. Totals also provide a mechanism for feedback and control within a system.

5.3 Arithmetic Operations

# Common operations in Visual Basic are adding, subtracting, multiplying, dividing and raising to a power. Commands used for these operations are described in this section.

The sample code used in this chapter is available in the chapter 5 samples folder of the zip file software available to be downloaded for the text.

VB has an option for rounding of results, if called for in the system design. Here are sample variables used in the rounding examples below

Dim payrollTaxCharge As Decimal

Dim payrollTaxRate As Decimal = 0.13793921D

payrollGrossPay = 2000

# Here are the statements being executed

payrollTaxCharge = payrollGrossPay * payrollTaxRate

Console.WriteLine("default " & payrollTaxCharge)

Console.WriteLine

("Round " & Math.Round(payrollTaxCharge))

Console.WriteLine

("Round 0 " & Math.Round(payrollTaxCharge, 0))

Console.WriteLine

("Round 1 " & Math.Round(payrollTaxCharge, 1))

Console.WriteLine

("Round 2 " & Math.Round(payrollTaxCharge, 2))

Console.WriteLine

("Round 3 " & Math.Round(payrollTaxCharge, 3))

# Here's the output when these lines are executed

default 275.87842000

Round 276

Round 0 276

Round 1 275.9

Round 2 275.88

Round 3 275.878

# It can be observed from the sample output that the Round function rounds a decimal value to the number of digits specified by the number before the closing parenthesis. Not providing any integer is the same as using 0. By not using the Round function at all, the number is not rounded and all significant digits are shown.

5.3.1 Add

# There are a few ways to accomplish 2 + 2 in Visual Basic. Here's the first one

cdNumber_Credits = cdNumber_Credits + accumCredits

# This adds values of the two data elements on the right-hand side to the variable on the left-hand side. The rightmost remains unchanged and may even be a numeric constant. The other is increased by the value of the second, not replaced, so be sure to properly initialize all result variables. If the result is not started at zero, there may be unpredictable results.

# A second way to use the add command is this

counter += 1

# The number on the right hand side can be anything

counter += 2

Console.WriteLine(counter)

counter += 100

Console.WriteLine(counter)

Working with dates and adding days has to be done a special way. This example adds a given number of days using the AddDays method to the current date to determine a future date

Dim numDays As Integer = 12

Dim CurrDate As DateTime = Date.Now

Dim dueDate As DateTime = CurrDate.AddDays(numDays)

Console.Write(dueDate.ToString("MM/dd/yyyy"))

Be sure to check out the DateTime class for other methods that may be of use.

5.3.2 Subtract

Subtraction in Visual Basic uses the dash symbol -

payrollGrossPay = 2000

payrollDeductions = 200.5

payrollNetPay = payrollGrossPay – payrollDeductions

# The last line subtracts the value of the second data element on the right hand side from the first one. The second one remains unchanged and may even be a numeric constant. The first is decreased by the value of the second, not replaced.

Another technique for subtracting is this shortcut method -=

counter -= 1

# The number on the right hand side can be anything

counter -= 2

Console.WriteLine(counter)

counter -= 100

Console.WriteLine(counter)

5.3.3 Multiply

# An example of multiplication was shown above during the rounding discussion

payrollTaxCharge = payrollGrossPay * payrollTaxRate

# This multiplies the value of the first data element on the right hand side by the second one resulting in the variable on the left hand side. The right hand side remains unchanged. There is no need to initialize the result field since it's calculated each time.

As might be expected using *= is also a valid way to multiply

payrollTaxCharge *= 2

Console.WriteLine(payrollTaxCharge)

5.3.4 Divide

# Here are examples showing division

Dim classSize As Double = 20

Dim classTotal As Double = 893

Dim classAverage As Double

Dim yearlyTotal As Double = 278

Dim monthlyTotal As Double

classAverage = classTotal / classSize

Console.WriteLine(classAverage)

monthlyTotal = yearlyTotal / 12

Console.WriteLine(monthlyTotal)

# The first variable on the right-hand side is divided by the second entity resulting in the value stored in the variable on the left-hand side. The right-hand side remains unchanged and, as seen in one of the examples, numeric constants can also be used.

Using /= is also a valid way to divide

payrollTaxRate /= 2

There is also a remainder option using Mod which proves useful in certain applications

monthlyRemainder = yearlyTotal Mod 12

Console.WriteLine(monthlyRemainder)

5.3.5 Math.Pow and ^

Exponentiation can be done using Math.Pow or ^ like this

Console.WriteLine(Math.Pow(classSize, 2))

Console.WriteLine(classSize ^ 2)

Console.WriteLine(Math.Pow(classSize, 4))

The first two examples square the value contained in classSize. The third one raises the value to the fourth power. Explore the Math class on your own to find other math methods.

5.4 Chapter Summary

The counter is one of the simplest arithmetic objects, having been used since ancient times with manual systems. Accumulators keep track of running totals. Both should be set to zero before being used and both provide a mechanism for feedback and control within a system. Calculated objects are those which are based on a calculation involving two or more numeric data elements.

A syntactical character used in a Visual Basic data declaration and introduced in this chapter is the D for Decimal data types. This can be important when assigning large Decimal numbers to variables. The symbol C can be used to output data in currency format.

There are five basic arithmetic commands used in Visual Basic: add, subtract, multiply, divide and exponentiate. The syntax of these commands is quite precise, yet straightforward. Choose variable data types carefully, whether Double, Decimal or Integer, etc. DateTime methods exist for adding and subtracting dates.

# Recognize that electronic spreadsheets and database management systems (DBMS's) can perform calculations with lower development cost in certain cases than a Visual Basic program. As God's stewards, discern which processing method is going to be more efficient for any given enterprise by counting the cost as part of any analysis. If large volumes of data and a good deal of complicated processing are involved, a spreadsheet or DBMS generally may not work as well as a Visual Basic program.

5.5 Review Questions

1. Discover a Bible verse not referred to in

# the text that is related to arithmetic,

actually or philosophically.

2. How might totals provide a mechanism for

feedback and control within a system?

3. Why would sales ever be negative?

4. Provide an example of a counter you

# encounter routinely.

5. Provide an example of a calculated object

not mentioned in the text.

6. Give an example of an accumulator from

# everyday life.

7. What's potentially wrong with this code?

Dim cdNumberCredits As Double = 5.3

Dim numberGrade As Integer = 4

Dim coursePoints As Integer

coursePoints = cdNumberCredits * numberGrade

5.6 Projects

# 1. Design a timer program which counts off

# until at least five minutes have elapsed, then

# displays the message

# "You have an appointment now!".

# 2. Implement a timer program which counts off

until at least five minutes have elapsed, then

# displays the message

# "You have an appointment now!".

3. Locate the text file cookies.txt and create a

table cookies, which contains names of four

types of cookies and their prices in cents

# (.99). Write an interactive program which

# first displays the table values then lets the

# user type in the search key (one of either

# raisin, sugar, butter or oatmeal) and an amount. The program then calculates how

# many cookies can be purchased and how much change is left over. Assume the user

# always types in an amount > the price.

# 4. Write a program to create and display the

# multiplication table from 5 x 5 through 10 x

# 10. Have it displayed on a single screen.

# 5. Write an interactive program to accept two

# numbers and subtract one from the other to create a third negative number. Store all

# three numbers in a text file.

6. Locate the sequential file due-date.txt in the

chapter 5 samples folder. This file contains

# names and the number of days until a

payment is due. Make a Structure for this file. Write a program to read the file, compute the due date, which is the number

# of days a payment is due plus the present

# date, and output a new text file with names

# and due dates.

This code may prove helpful

Dim numberDays As Integer = 12 'could be file data

Dim CurrDate As DateTime = Date.Now

Dim dueDate As DateTime =

CurrDate.AddDays(numberDays)

Console.Write(dueDate.ToString("MM/dd/yyyy"))
CHAPTER SIX

Return to Contents

Selection and

Control

# Joshua 24:15

## And if it is disagreeable in your sight to serve the LORD, choose for yourselves today whom you will serve: whether the gods which your fathers served which were beyond the River, or the gods of the Amorites in whose land you are living; but as for me and my house, we will serve the LORD.

# Philippians 3:1 (NIV)

## Finally, my brethren, rejoice in the Lord. To write the same things again is no trouble to me, and it is a safeguard for you.

6.1 Introduction

# The most basic form of data processing selection consists of choosing between two options, such as regular pay versus special pay or an error situation versus not an error situation. This type of selection is especially important for us, where we must choose between good and evil. Sometimes the distinction is not as clear as we would hope for, where evil appears quite good in our eyes. For we know that Satan himself masquerades as an angel of light. But the choice must be made, and unlike data processing our choice between good and evil has eternal consequences. Have you made that choice?

# In data processing, program controls prevent software systems from falling into error. If inadequate controls exist, bad data can get into a table making any informational output reports suspect. For man it is similar. In the absence of controls, that which is output from the heart of man is bad because of the sinful condition of the heart. Our output can only change when God's Spirit is input and takes control of our lives, by His marvelous grace. Scripture is given to us to lead us to Christ and as a safeguard or a control against temptation and falling into sin, once we become His.

1 Peter 2:9

# But you are a chosen people, a royal priesthood, a holy nation, a people belonging to God, that you may declare the praises of Him who called you out of

darkness into His wonderful light. (NIV)

6.2 Truth Tables

Conditional expressions can be either true or false. We have already seen conditional expressions with the While and Do..Loop statements for loops in chapter 2. Statements following a conditional expression are executed only if the conditional expression is true.

Truth tables are used to help us evaluate the results of using the operators Not, And and Or. The truth table for And is below, where 0 corresponds to false and 1 corresponds to true:

Essentially, false and anything else is always false, while true and true is always true. The truth table for Or is:

# True or anything else is always true, while false or false is always false.

The truth table for Not is:

# Not true is false, and not false is true.

6.2.1 DeMorgan's Laws

When the Not operator is combined with And or Or, the evaluation becomes slightly more difficult. DeMorgan's Laws allow us to easily simplify certain complex expressions in the following manner:

Law 1 \-- NOT (a AND b) = NOT a OR NOT b

Law 2 \-- NOT (a OR b) = NOT a AND NOT b

Let's prove Law number 1 so we can make use of it with confidence as the need arises. Consider first the case when a is true and b is true. Using truth tables from section 6.2,

## NOT (a AND b) = NOT (true) [left hand side of Law 1]

## = false

## NOT a OR NOT b = false OR false [right hand side of Law 1]

## = false

# Since false equals false, the two sides of the equation are equivalent.

# Consider next the case when a is true and b is false. Using truth tables again,

## NOT (a AND b) = NOT (false) [left hand side of Law 1]

## = true

## NOT a OR NOT b = false OR true [right hand side of Law 1]

## = true

# Since true equals true, the two sides of the equation are equivalent.

# Finally, consider the case when a is false and b is false. Using truth tables one last time,

## NOT (a AND b) = NOT (false) [left hand side of Law 1]

## = true

## NOT a OR NOT b = true OR true [right hand side of Law 1]

## = true

# Since true equals true, the two sides of the equation are equivalent.

# Having proven all 3 cases, Law 1 is shown to be true. Showing that Law 2 is always true is left as a question at the end of the chapter.

# We can now use DeMorgan's Laws to help understand, and perhaps avoid, certain complex expressions. Consider this pseudocode:

if NOT overtime-worked AND NOT holiday-worked

# compute regular pay

# Using DeMorgan's Law 2, this is the same as

# if NOT (overtime-worked OR holiday-worked)

# compute regular pay

# Decide for yourself which one is easier to understand.

# These laws also help us avoid pitfalls when attempting to modify code. Suppose this pseudocode represents a statement in a program:

# if clean-driving-record AND zero-claims-filed

# compute 10% insurance discount

If we decided to apply NOT to this statement in order to carry out a different function this code would be incorrect:

if NOT clean-driving-record AND NOT zero-claims-filed

compute additional 10% premium

# because by DeMorgan's Laws that's equivalent to

if NOT (clean-driving-record OR zero-claims-filed)

compute additional 10% premium

which is negating a different statement altogether. The correct way to negate the initial statement would be this

# if NOT clean-driving-record OR NOT zero-claims-filed

# compute additional 10% premium

Keep these rules in mind if you ever have occasion to use Visual Basic Not with either And or Or in any of your software development.

6.3 Selection

In Visual Basic, there are two commands available for selection – If and Select. The If command may also include an Else and an ElseIf.

The following example shows If used with both ElseIf and Else, a case where more than two choices exist:

selectOption = 2

If selectOption = 1 Then

example.addRoutine()

ElseIf selectOption = 2 Then

example.displayRoutine()

Else

example.modifyRoutine()

End If

The If..Else is seen in section 6.3.2. The ElseIf is not covered further in the text as the Select command provides a simpler structure. When more than two choices exist, the Select command is generally superior and is discussed in section 6.3.4.

6.3.1 If

The If command is used to select from among different alternatives. Consider this block of code

If IsNothing(line) Then

loadedFlag = 1

End If

# In this example,

==> when the condition specified in the If

statement is true, then the indented block of

# code is executed

==> when the condition specified in the If

statement is false, then the indented block of

# code is NOT executed

Indentation helps to identify the code to be executed with its corresponding If statement. End If marks the end of the If block. The first (and only in this case) choice is made when the condition is true. No choice is made when the condition is false.

In the condition above, when line is nothing then 1 is moved to the data element loadedFlag. The statement loadedFlag = 1 is executed when the statement is true. The second choice, unstated here, is when the condition is false and no other action is taken.

When there is only a single action to be taken the simple If statement can be simplified to this, where no End If is needed

If IsNothing(line) Then loadedFlag = 1

6.3.2 If..Else

The If..Else is a variation of the If command and is used to select from among different alternatives. The Else acts like another If for the second choice, which triggers execution when the actual If statement is false. Please note that Else is optional and cannot be used by itself.

# Consider this code

If validatePart(part1, 2) Then

updateDatabase(part1, fType)

Else

displayError()

End If

# In this example,

==> when the condition specified in the If statement

# is true, then the first indented block of code

# is executed

==> when the condition specified in the If statement

is false, then the Else statement is executed

The Else saves us the trouble of coding the statement If Not validatePart(part1, 2), which helps reduce the possibility of coding error.

Here's an example of the use of If..Else from Labels.vb:

If abbrevIndex > -1 Then

Return statesArray(abbrevIndex)

Else

Return "State Not Found"

End If

When abbrevIndex is greater than -1, the first Return is executed. Otherwise, the second Return is executed. The Else saves us the trouble of coding the statement if abbrevIndex <= -1, which helps reduce the possibility of coding error.

6.3.3 Nested Ifs

Nested Ifs occur when one If command is within another If block. Recognize that the inner command is only executed when the outer condition is true. Each If has its own End If. Nesting can occur for many levels, but more than one or two levels of nesting can make software testing difficult.

Here's an example from Parts.vb

If Not String.IsNullOrEmpty

(HttpContext.Current.Request.Form

("ctl00$MainContent$txtPart")) Then

browserPart.PartNumber =

HttpContext.Current.Request.Form

("ctl00$MainContent$txtPart")

If browserPart.PartNumber.Length <> 8 Then

errType(0) = 1

errSet = True

End If

End If

6.3.4 Select

The Select command is an efficient means of handling multiple choices. It makes software testing cleaner and more reliable. Use it whenever possible in place of If. Parts.vb uses Select in several places. Here's an example highlighting the structure

' fType determines which update processes occur

Select Case fType

Case 0 ' for modify

Dim str1 As String = "UPDATE Parts SET ..."

con = new OleDbConnection(strConnString)

cmd = new OleDbCommand(str1, con)

con.Open()

...

Case 1 ' add new fields

Dim str2 As String = "INSERT into Parts(...)"

con = new OleDbConnection(strConnString)

cmd = new OleDbCommand(str2, con)

con.Open()

...

Case 2 ' for delete

Dim str3 As String = "DELETE from Parts ..."

con = new OleDbConnection(strConnString)

cmd = new OleDbCommand(str3, con)

con.Open()

...

End Select

The Select command uses a single variable, which can be from a variety of data types, after the word Case. The End Select command marks the end of the block. Each Case with the block is like a separate If statement.

Here's another example that shows If..Else blocks inside a Select block (under Case 2).

Dim partExists As Boolean

Function checkDB(ByVal updateType As

Integer) As Boolean

Select Case updateType

' for checking part against DB

' (modify and delete)

' check that part IS in database

Case 1

Try

Dim partNumber As String = ""

partExists = True

' if part doesn't exist, this will throw

' an exception and catch applies

...

Catch

partExists = False

End Try

' check that part NOT in database

Case 2

Try

...

If result = 0 Then

' part doesn't exist, can be added

partExists = False

Else

partExists = True

End If

Catch e As Exception

' problem with database

' post error to web page

...

End Try

End Select

Return partExists

6.4 Controls

Program controls prevent software systems from accepting bad data. There once were batch programs whose sole purpose was data validation and printing errors in a report for correction in a later run. Interactive programs can and should provide data validation and feedback immediately for each screen entry which can be checked against a given set of criteria. Here are some things to check for

are entries blank

are numbers numbers

are numbers zero

does data take on a specific values/sizes

does a specific table row already exist

are any database constraints violated

6.4.1 Are entries blank

To ensure that entries contain data, Visual Studio provides the RequiredFieldValidator control which checks that entries are not blank before data actually reaches the program.

To use the RequiredFieldValidator control drag the control from the ToolBox to the Form for each input field and update the code like this in the .aspx file under each TextBox code tag

<asp:TextBox ID="txtPart"

runat="server"></asp:TextBox>

</strong>

<asp:RequiredFieldValidator

ControlToValidate="txtPart"

Display="Static"

ErrorMessage="8 characters required"

ID="valPart"

RunAt="Server" />

Set ControlToValidate to the unique ID of the TextBox. Each validator ID must be also unique. When a field on the screen is left blank, the ErrorMessage is displayed after the Save button is clicked.

# The next layer of data control is to check data constraints with a variety of Visual Basic commands.

6.4.2 Are numbers numbers

Visual Basic can check that numbers are numbers and there is more than one way to do this. Two techniques are shown here. The first one uses the TryParse method and an if statement to check that integer input is a valid number.

Dim daysInMonth As Integer

Dim testInput As String = "bad1"

Dim numericCheck As Boolean

numericCheck = Integer.TryParse(testInput, daysInMonth)

' false returned, input not valid number

If Not numericCheck Then

Console.WriteLine("Input invalid, must be a number")

End If

If anything other than numbers make up the testInput entry, then an error message is returned to the user for making corrections.

The second technique includes an If statement to first double-check that a valid value is being passed from the screen to the program, then uses a Try-Catch to check that a number is a valid double data type.

If Not String.IsNullOrEmpty

(HttpContext.Current.Request.Form

("ctl00$MainContent$txtUnitCost")) Then

Try

browserPart.PartUnitCost = Convert.ToDouble

(HttpContext.Current.Request.Form

("ctl00$MainContent$txtUnitCost"))

Catch

errType(1) = 1

errSet = True

End Try

End If

# Note that the following object copied from above represents the box on the screen where data is being entered

(HttpContext.Current.Request.Form ["ctl00$MainContent$txtUnitCost"]))

This information can be obtained using View Page Source from the web browser, while the application is running and screen is displayed.

6.4.3 Are numbers zero

# To avoid ending up with numbers that are zero, mimic the following:

If unitCost = 0

errorFlag = 2

End If

If the unit cost is zero, then this condition is true and 2 is moved to errorFlag.

6.4.4 Checking specific values/sizes

# In the sample code there are two instances where either a specific value or size is required.

# The unit of measure or UOM is predefined in a dropdown list on the .aspx web pages

<asp:DropDownList ID="lstUOM" runat="server">

<asp:ListItem>BX</asp:ListItem>

<asp:ListItem Selected="True">EA</asp:ListItem>

</asp:DropDownList>

# A value is always provided for this entry which ensures the database always receives a valid value.

# The part number must be 8 characters in size, so this code is included to check the length

If browserPart.PartNumber.Length <> 8 Then

errType(0) = 1

errSet = True

End If

6.4.5 Checking duplicate rows

# The question is this, does the row already exist?

There are two situations to handle. One is for modifying and deleting rows to ensure the original row does exist. The other is when adding a new row to ensure the row does not already exist.

Code for both these cases was presented in chapter 4, section 4.3.2, and is available in context electronically in the Parts.vb file. Using Try-Catch handles the case for modify and delete, while an If..Else works well for add.

# This is the error message for an attempted Add displayed to the user to provide feedback so corrections can be made.

6.4.6 Database constraints

# For the final control layer, the database has controls to ensure data integrity. We saw earlier that existing code checks that the part number is exactly 8 characters and the unit cost is a valid number. However the Access database also requires that the location be no greater than 4 characters.

# This code catches database errors including if a location is entered with 5 or more characters

Catch e As Exception HttpContext.Current.Response.Redirect

("Error.aspx?Errors=" + e.Message, False)

# It's left as an end of chapter project to modify the code to check for the correct location length before processing reaches this point.

6.5 Chapter Summary

This chapter dealt with the concepts of selection and control. Selection has to do with choosing between a number of possible alternatives, much as we do in our Christian experience on a daily basis. We must choose to take up our cross daily to follow Christ. In VB, the If, If..Else and Select commands are available. It's acceptable to have nested Ifs, but too many levels of nesting can complicate the software testing process.

Control in a Visual Basic program involves keeping bad data out. Some of the things to check for include whether entries are blank, numbers are numeric or non-zero, entries have specific valid values/sizes and whether a specific row already exists. Visual Studio provides a control for the first of these to check data automatically before even reaching the program.

Truth tables and DeMorgan's Laws were introduced to assist with understanding how conditions are evaluated. And, Or and Not are operators that can be used to make complex conditions in Visual Basic programs.

With the coverage of selection, most of the files and programs included in the software available via the download link can be more fully understood at this time. The remaining chapters cover additional topics of interest. Arrays in the next chapter are useful for special applications and can also be used in place of database tables in certain instances. Different Reports can be created and some tools for generating reports are covered, which have features to simplify programming and reduce development effort. The last chapter introduces SQL Server Integrated Services (SSIS) which allows for merging of data from MS Access, text files and other sources into SQL Server.

6.6 Review Questions

1. What choices have you made recently,

# where you had to select between two or

# more reasonably good alternatives? What

# Biblical point of reference did you use in

# arriving at a decision?

2. Show the equivalence of the two sides of

the equation for DeMorgan's Law 2

# NOT (a OR b) = NOT a AND NOT b

# when a and b are both true, both false and

# both different.

3. What do truth tables do and what are they

# used for?

4. Which commands are available for

selection?

5. Change this code to replace the Else with

another If.

If processFlag = 2 Then

example.displayScreen()

Else ' processFlag = 3

example.modifyRoutine()

End If

6. True or False: Nesting of ifs can occur for

# only two levels.

7. True or False: The Select command is an

inefficient means of handling multiple

# choices.

8. Name a Visual Basic method that can be

# used to make sure a number is valid.

9. Give an example of some numeric field not

# mentioned in the text that you would not

# usually want to be zero.

6.7 Projects

# 1. Design a simple yet realistic text data file to

input to a console application data validation program. Create raw data, including

# possible data errors, using any available text

# editor and save the data. Develop guidelines

# for what would constitute valid versus invalid data and prepare a set of specifications detailing these guidelines.

# 2. Design a console application data validation

# program that meets the specifications from

# the previous problem. List any records with

# data errors in one text file along with an

# appropriate mes sage, and list valid records

# in another text file.

# 3. Write a program to simulate a cash register. It will accept an amount and a quantity to multiply the amount by. Ensure the numbers are valid numbers.

# 4. You've received a request to create a file containing names, addresses, hourly rate of pay and date of hire. Design a screen to

# input the data and write an interactive

program, including controls, to create the

# file.

# 5. Create a calling-called pair of methods in a console application. Have the calling

# method pass three parameters to the called

# method. The first parameter will come from

# the keyboard and can be a zero or a one.

Have appropriate controls to verify this.

# Display the second parameter value in the

# called method if the first parameter is a zero.

# Display the third parameter value in the

# called method if the first parameter is a one.

6. Locate the text file cookies.txt, which contains the names of four types of cookies and their prices in cents (.99). Write an interactive program which does not read this file but which simply displays the contents in a dropdown list, then lets the user select

# the name of the cookie from the dropdown

# (one of either raisin, sugar, butter or oatmeal) and an amount. The program then

uses a selection process (without reading

# the file), calculates and displays how many

# cookies can be purchased for that amount

# and how much change is left over.

Use a control to check that the user doesn't type in an amount less than the price.

# 7. Add code to the sample menu application

provided with the text to check the size of

# the location field, that it not be greater than

# 4. Display an appropriate error message on

# the Error page, which may also involve

# modifying code.
CHAPTER SEVEN

Return to Contents

Arrays

# Ecclesiastes 12:9

# In addition to being a wise man, the Preacher also taught the people knowledge; and he pondered, searched out and arranged many proverbs.

7.1 What are arrays?

# Arrays provide a means of arranging and accessing data within a program (while files and tables are generally used to arrange and store data outside a program.) There are two types of arrays:

# internally defined arrays

# arrays loaded from external sources

An array can exist solely within a program as an internally defined array. These are usually small. An array can also be loaded with data from an existing file or table at runtime.

Choosing whether to use an internally defined array or one loaded with data may depend to a large degree on expected changes to the array data. When the array exists solely in the program, it is hard-coded and becomes more difficult to maintain. If changes are to occur frequently, it would be unwise to hard-code the data because of the high cost associated with software development. Every time array data changed, the program would have to be changed, recompiled and retested.

# When array data exists outside the program, say as a database table, it can be loaded into the program when needed. A table has greater flexibility in terms of maintaining the data, versus storing the array as a data object within the program. But if the data is already in a table, what possible advantages can there be in taking the extra step of loading it into a program array?

# Operating systems make regular use of arrays to store a variety of information. The most notable business application of arrays is represented by spreadsheets, where data is created or retrieved, manipulated, then stored on disk. This is a simple example of a partial array using Scripture:

Reference Proverb

# {Proverbs offer us wisdom as we strive to complete the work God has given us.}

7.2 Why use arrays?

There are many unique situations that call for the use of an internal array in a Visual Basic program. For instance, internal arrays provide the opportunity to sort intermediate results before outputting data. You have to assess any design problem that arises and determine whether an array would meet the solution requirements. In addition, with regard to an array loaded from a table, faster processing can often occur using the array if the data is to be accessed numerous times.

# The offsetting consideration is the amount of time needed to load the data into the array compared with the time to just read the data. Given modern processing speed, such factors become insignificant given a small to moderately sized data sources. If the table is very large, consider whether the number of accesses to the data justify using an array a worthwhile option.

To optimize database storage a best practice is to use codes or IDs in a primary data table, and have related names such as department names, account names or course names in a separate database table referred to as a lookup table which can also be loaded into an array. A cost account code consisting of 3 digits could be part of each primary row, while cost account codes and corresponding account names of say 30 characters could be stored in a smaller internal lookup table.

# The following calculation shows how much space could be saved by having a lookup table

## account name not stored in primary table 30

account code stored in primary table \- 3

## Savings per row 27

Primary data rows x 40,000

Total savings 1,080,000 bytes

# The next calculation shows the lookup table size, which can also be loaded into an array

## Assume there are 500 different cost accounts

## Size of account name 30

Size of account code +3

## Size of lookup table row 33

Lookup table rows x 500

## Total lookup table size 16,500 bytes

Even after subtracting the small size of a lookup table from the previous calculation, about 1MB of storage is saved in this example. The codes and names can be loaded from the lookup table and stored in arrays during processing. The name is substituted for the code after a successful array search operation, discussed in section 7.4.

7.3 Array definition

# In Visual Basic, as mentioned earlier, there are two ways to define an internal array -

# internally defined arrays

# arrays loaded from external sources

# The first technique is to define the array plus data as a data object within the program. The second one is to define the array as a data object within the program and load the array data from an outside source.

7.3.1 Internally defined arrays

# Here is a simple array

Dim testme(2) As String

To declare this array first use Dim followed by the array name, then use parentheses ( ) including the size of the array inside the parentheses. By specifying an array size of 2 means the array can hold three elements. After the closing parenthesis use As followed by the data type, in this case String. Data types can be chosen from any of the available Visual Basic data types depending on the particular need.

A size of two means there is a testme(0) and a testme(1) and a testme(2). If an attempt is made to add a fourth array element to an array of size 2, a runtime error occurs. If the array size needs to be increased use ReDim to reset the dimensions of the array like this

ReDim Preserve testme(3)

Using Preserve saves the original array data, leaving Preserve off deletes the original data.

# To add data to an array inside the program do something like this or use a loop

testme(0) = "This"

testme(1) = "That"

testme(2) = "One more"

Console.WriteLine(testme(0) \+ " " + testme(2))

Arrays start at position or index 0. Values can be also specified using braces {} at the time the array is defined, which is seen in the array example below. This array is used in Parts.vb to store messages

Dim errMessages() As String =

{

"Part number must be 8 characters.",

"Unit cost must be a number.",

"Part number not found.",

"Part number already exists."

}

# For the project at the end of the previous chapter this array would be changed to include an additional message to inform the user the location has to be no more than 4 characters.

To declare this array first use Dim followed by the array name, then have empty parentheses ( ) followed by As and the data type, in this case String, then braces { } enclosing the array elements all separated by commas. Here the array elements are used to set the size.

There are a couple good practices to specify the size of an array. The first technique is to define a global constant as the array size. The constant can be used anywhere in the program, and if the size of the array changes, the only place that value needs to be changed is where the constant is defined. Here's an example related to Parts.vb

' indexes go from 0 to 3

Public Const maxErrors As Integer = 3

...

For i = 0 To maxErrors

Select Case errType(i)

Case 1

errMessage = errMessage + errMessages(i)

End Select

Next

Another technique especially helpful for loop processing is to use GetUpperBound(0). Here's an example using the message array

For k = 0 To errMessages.GetUpperBound(0)

Console.Writeline(errMessages(k))

Next

7.3.2 Loaded arrays

# The simplest form of an array loaded from an external data source is seen here

Dim arguments() As String =

Environment.GetCommandLineArgs()

This defines and loads a special string array used by Main() to accept data from the console at the start of execution. The right-hand side gets data from the console to store in the array on the left-hand side.

# This next line displays two array elements

Console.WriteLine(arguments(0) + " " + arguments(1))

Array elements start at position or index 0 and arguments(0) for this special array is the name of the command being executed. The code above displays the arguments separated by spaces like this

chapter7 value1

Please note that if the program tries to display more values than are entered in the console a runtime error will occur, so be sure appropriate error handling is in place so as not to confuse a user.

Here's an example related to the Labels.vb program where state information is loaded from a text file. In Labels.vb a global constant storing the array size and two arrays are defined

' includes DC, indexes go from 0 to 50

Public Const numberStates As Integer = 50

Public statesAbbrev(numberStates) As String

Public statesArray(numberStates) As String

# To read and store data in this array a method was created

Public Sub loadStates(abbrev() As String, states() As String)

Dim line As String

' load states from file to array

Dim j As Integer = 0

Try

'Pass file path and name to StreamReader constructor

Dim sr As StreamReader = New

StreamReader(myPath & "states.dat")

'Read the first line of text

line = sr.ReadLine()

'Continue to read until you reach end of file

While Not IsNothing(line)

abbrev(j) = line.Substring(0, 2)

states(j) = line.Substring(2)

j += 1

'Read the next line

line = sr.ReadLine()

End While

'close the file

sr.Close()

Catch e As Exception

Console.WriteLine("Exception: " + e.Message)

End Try

End Sub

This example is using a lookup table. The first record is read outside the loop, then with index j starting at 0 the two lines inside the loop load the array, using the array parameters defined for the method

abbrev(j) = line.Substring(0, 2)

states(j) = line.Substring(2)

The first spot in the array is filled first. Then j += 1 is used to increment the index value. The next record is read inside the loop, and the array is filled in one row at a time.

7.4 Array handling

There are four main techniques to access or process the elements of a Visual Basic array: using a specific index value, sorting using the Sort method, and searching using IndexOf and BinarySearch methods.

Using a specific index value to access an array element has already been shown. For example, this statement will access the third element or row of the array and cause it to be displayed

Console.WriteLine(testme(2))

7.4.1 Sorting an array

In its simplest form the Visual Basic Sort command sorts arrays from lowest to highest. This is an example sorting the state names

Array.Sort(statesArray)

# Refer to Microsoft .NET documentation on the web if additional sorting features are needed.

7.4.2 IndexOf

Suppose an input value has been provided from some part of the program - for instance, a state abbreviation. At the top level, this method call can be used to effectively locate the state name associated with the abbreviation:

label1.line3State =

US.getStateName(line.Substring(67, 2))

The abbreviation is in line.Substring(67, 2).

Inside the called method IndexOf returns the index of the abbreviation being searched for in the abbreviations array. When the abbreviation is found, the index is returned. The index is used to select the state name, since both arrays are in the same sequence.

Public Function getStateName(abbrev As String) As String

Dim abbrevIndex As Integer = -1

abbrevIndex = Array.IndexOf(statesAbbrev, abbrev)

If abbrevIndex > -1 Then

Return statesArray(abbrevIndex)

Else

Return "State Not Found"

End If

End Function

# If the abbreviation is not there for some reason a message is returned instead of the state name.

7.4.3 BinarySearch

The BinarySearch method requires a sorted array. For states and state abbreviations the first step is to capture all the data in a single array, using code similar to what is shown in section 7.3.2 but for just a single array.

Sort this single array using the Sort method described earlier. Then create an abbreviation array using the sorted array so the indexes for the abbreviations match the indexes for the states. Note: state abbreviations and states do NOT naturally sort in the same sequence.

This code captures these steps, starting with the Sort

Array.Sort(states)

' build abbreviation array from sorted array

Dim Abbrev(numberStates) As String

For j = 0 To 50

Abbrev(j) = states(j).Substring(0, 2)

Next

At this point a state abbreviation input value is provided to strParm from some part of the program and the BinarySearch looks like this

Dim stateIndex As Integer =

Array.BinarySearch(Abbrev, strParm)

Console.WriteLine

(states(stateIndex).Substring(2))

Please keep in mind the array must be sorted for a binary search to work correctly.

7.5 Chapter Summary

Arrays provide a means of arranging and accessing data within a program. To optimize databases a best practice is to use codes or IDs in a primary data table, and have related names, oftentimes lengthy descriptions, in a separate lookup table which could also be loaded in an array. There are many unique situations today that can make use of an array in a Visual Basic program.

There are two types of arrays: an array can exist solely within a program as a relatively small internally defined array and arrays can be loaded with external data from an existing table at run time. An array index points to the location of an array element within the array. Indexes start at position 0.

Internal arrays are hard-coded and difficult to maintain. When the array data exists outside the program, such as in a table, it can be loaded into the program when needed. A table has greater flexibility by being maintained using interactive software. Faster processing can often occur for arrays versus files or tables in situations where data is to be accessed numerous times, assuming the amount of time needed to load the data initially is not prohibitive. It's also possible to just use tables and avoid arrays altogether.

Array handling consists primarily of sorting and searching. Sort is straightforward. Searching can be accomplished by using IndexOf, or with BinarySearch when the array is sorted.

7.6 Review Questions

1. Write an array definition for an array to

# store all the Proverbs.

2. What advantage is there in loading a

# table into a program array?

3. Give an example of a database product you

# are aware of or listed in current literature.

4. List two array operations.

5. Design an array not mentioned in the text

# which might be used in a program.

6. How many ways are there to define an

# array in a Visual Basic program?

7. What is required for the BinarySearch

# method to work?

7.7 Projects

# 1. Write a simple program to define and

# manipulate an array.

# 2. Write a simple program to sort an array. First display the unsorted array, then sort the array. Finally, display the sorted array.

# 3. Write a program to create 2-up labels.

# Reference an array loaded from the

states.dat file to get the state name.

# 4. Design a program to create and store 2-up

labels in an array, sort then print them.

# 5. Code and test a program to create and store

2-up labels in an array, sort then print them.

6. Locate the text file cookies.txt and create an

# array definition for it. The file contains the

names of four types of cookies and their

# prices in cents (.99). Write an interactive

# program which first loads the file data into

# an array, displays the contents of the array,

# then lets the user type in the search key (one

# of either raisin, sugar, butter or oatmeal) and an amount. The program will then

# calculate how many cookies can be purchased for that amount and how much

change is left over. Provide controls in the

# event the user types in an amount less than

# the price.

7. Write an interactive program to accept a

User-ID and password. Create a table

consisting of 5 User IDs and passwords,

which must be loaded into an array. Display

the message "Invalid User-ID/Password"

whenever either the User-ID or password

aren't found in the array.
Part 3

Return to Contents

Special

Topics

1 Corinthians 3:14-15

If any man's work which he has built upon it remains,

he shall receive a reward.

If any man's work is burned up, he shall suffer loss;

but he himself shall be saved, yet so as through fire.
CHAPTER EIGHT

Return to Contents

Reports

# Joshua 18:4

Appoint three men from each tribe. I will send them out to make a survey of the land and to write a description of it, according to the inheritance of each. Then they will return to me. (NIV)

# 1 John 5:13

# These things I have written to you who believe in the name of the Son of God, in order that you may know that you have eternal life.

8.1 What are Reports?

# Reports are produced to convey information. Reports are often selective. There are book reports, reports written to summarize scientific research, reports written to detail survey results as in the time of Joshua, sales reports, inventory reports, payroll reports and accounting reports. Some business reports show the names of those who have failed to make their payments on time. God's book of life contains the names of those who will be in the presence of God for eternity:

Revelation 20:15

And if anyone's name was not found written in the

# book of life, he was thrown into the lake of fire.

# The Bible itself, as a collection of historical reports with God Himself as the Author/Editor-in-Chief, was written to lead us to God and to provide the assurance that we will be with God if we personally receive Jesus Christ as Lord and Savior.

Business reports are written to lead us to profits and provide information in an easy-to-read format. Computer-generated reports generally fall into one of three categories: transaction, exception and summary. Transaction reports list individual transactions such as sales, payments or employee wage information. Exception reports list information that requires immediate attention, such as too much overtime, too little inventory, or material that is excess and/or obsolete. Summary reports lump all detailed transactions together to give a bigger picture in terms of totals by store, totals by department, totals by branch or division, etc. Computers are capable of being used to produce reports efficiently and quickly.

8.2 Report Components

# Reports in general consist of a beginning, middle and end, also referred to as heading, body and summary. In computer jargon these components are also referred to as headings, detail lines, and subtotals and totals. The Labels report from chapter 2 was a special purpose report which consisted of only detail lines.

8.2.1 Headings

# The heading provides information at the top of a report, which identifies the report title, date, subheadings and column headings

This heading contains the date and time, the name of the company, the title Inventory System and heading Parts Report. Column headings are Part, Description, UOM, Unit_Cost and Loc, arranged over the corresponding data.

Defining column headings can be accomplished for a Web application by renaming database table column names using as inside a Select statement

string str = "Select parts_item_number as Part,

parts_item_description as Description, parts_unit_of_measure as UOM, parts_unit_cost as Unit_Cost,

parts_location as Loc from Parts"

Showing column headings can be done for a Console application using WriteLine

sw.WriteLine(" GPA Report ")

sw.WriteLine(" " + DateTime)

sw.WriteLine()

sw.WriteLine(" Student GPA")

sw.WriteLine()

8.2.2 Detail Lines

Detail lines provide information from each transaction, or summary of transactions in the case of a summary report, based on the input file or table. There is one detail line generated for each input in the Parts report, seen in section 8.3. There are multiple detail lines for each input of the Labels application from chapter 2. In the following example of a summary report, there is information from multiple input records combined into a single detail line. Two lines are displayed from an unknown number of input records:

GPA Report

09/30/18

Student GPA

123456 3.09

222222 4.00

Student Count 2

In a properly designed system, validation controls are in the program which creates raw data in a file or table, eventually to be used by a report program. What validation controls might you want to have in the program used to get raw data?

8.2.3 Subtotals and Totals

Totals provide the user with controls to verify that figures from the beginning of a process make it through to the end. Some data may kick out as errors, and there will be totals for that part of the system. Other data may show up on a primary report, and there are totals for that. As long as the totals add up to what's expected, there's confidence that the entire system is working properly and that data has been entered flawlessly. Accountants are especially concerned with this.

The chapter dealing with arithmetic showed how to make totals. The chapter dealing with the if statement showed how to implement controls in our programs. The rptGPAs.vb program includes a total student count, as seen above in section 8.2.2. This next command is executed inside a method called from an else command and also at the end of the program

studentCounter += 1

MS Access, discussed in section 8.5.1, also has relatively easy techniques to produce totals. When an Access report is first created there's the option to do sorting and grouping. This can also be done after a report is created by clicking Group and Sort from the Design menu

Data in Inventory.accdb was modified to have multiple parts in the same location, so location is now a good Group, Sort and Total field. Both counts and totals are available. For this example Counts and a Grand Total were selected

After some mouse tweaking, rearranging and Property Sheet format changes to make two Border Styles Transparent, in a very short time the report is complete and portions of the grouped report look like this, with markings added to highlight a subtotal and final total

# Microsoft SQL Server, discussed in section 8.5.2, has report grouping and total capabilities

8.3 Web Application Report

The simplest way to create a report for a Visual Basic Web Application is to use the GridView control, then use the browser Print command. Using the working system created in chapter 4, from the menu select Report to see this screen

The dropdown list is designed to have a variety of reports to select from, but for this example only a single report is available. Click Next to see this

Here's the Page_Load method for the report

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Label1.Text =DateTime.Today.ToString("MM/dd/yyyy")

Label2.Text = DateTime.Now.ToString("HH:mm")

Dim partData As Parts.Parts = New Parts.Parts()

partData.displayData(reportParts)

End Sub

This is similar to the Page_Load code for the Display screen from chapter 3. What's been added to this GridView are sortable columns. Sorting is a feature that's turned on by adding code to ReportView1.aspx

<asp:GridView ID="reportParts" runat="server"

HorizontalAlign="Center"

AllowSorting="true"

OnSorting="reportParts_Sorting" >

</asp:GridView>

In addition, some code has been added to ReportView1.aspx.vb including this method

Protected Sub reportParts_Sorting(sender As Object, e As

GridViewSortEventArgs)

'Retrieve the table from the session object.

Dim dt As DataTable = Session("partData")

If Not IsNothing(dt) Then

'Sort the data.

dt.DefaultView.Sort = e.SortExpression + " " +

getSortDirection(e.SortExpression)

reportParts.DataSource = Session("partData")

reportParts.DataBind()

End If

End Sub

This uses the Sort command discussed in the previous chapter, now applied to a DataTable from chapter 4. What's new here is the use of the Session object. The data is first saved as part of the Session object in Parts.vb so that data can be remembered and used throughout the session

'save data in Session object for Reports

HttpContext.Current.Session("partData") = allParts

# Sorting places data in ascending or descending sequence, any column can be sorted. Here's a screenshot after the report is sorted by part number

To print the sorted or unsorted report, choose Print from the browser menu

# and this is displayed

# From here a Print button can be clicked providing the option to save the report as a PDF file or to print it on paper

8.4 Console Application Report

The final Visual Basic console application rptGPAs.vb, in the chapter 8 samples folder and GPA report subfolder, reads two text files and produces two reports. The first report is an example of a summary report, while the other is an example of an exception report. Please note that one of the input text files grades.dat must be in ascending sequence for this particular program to work.

# The program reads student information, uses a lookup table loaded from a text file to find the number of credits for a given course, converts a letter grade to a numeric grade, calculates course points and totals them, then finally calculates the GPA. If a course in the student file is not in the course file, the information is written to an exception report and the entire job has to be re-run after corrections are made.

The sample GPA summary report was shown in section 8.2.2. Here is the sample exception report

09/30/18 Exception Report

No course found for CIS221

The interested reader is encouraged to review the code as it captures a good deal of what's been covered in this text in a single program. The code was adapted from the Labels.vb code and can be adapted to other scenarios. Some end-of-chapter projects also refer to it.

8.5 Database Report Tools

Report Tools provide for relatively easy report-generating capabilities. MS Access and SQL Server both have reporting capability first mentioned in section 8.2.3 that are highlighted further in this section.

8.5.1 MS Access

Since the database already exists in MS Access, reports can be created without importing the data. Open Inventory.accdb located in the chapter 8 samples folder. Click Create then Report Wizard

# Using the wizard, select fields for the report

# The next screens provide options for grouping and sorting. Choose to sort by part number

This is static sorting, not the dynamic sorting the Visual Basic web report provides. Click Next for more options or click Finish. Here's the report

To make this look better, switch to Design View

# to see this

# Additional time can be spent modifying the design depending on user requirements. This part of the discussion focuses on changing column headings and formatting the unit cost. To change column headings, click on a heading and type the new text like this

# After each heading is changed, use the mouse to resize and move some boxes to the left, to get to this new look after a few minutes work

To format unit cost, select the report field and change Format to Currency on the Property Sheet

# The top of the final report looks like this

8.5.2 SQL Server Reporting Service

SQL Server has a reporting capability that ties in with Visual Studio. SQL Server must be configured to use SQL Server Reporting Service (SSRS). Run services.msc to check that SSRS is running

# Run Report Server Configuration Manager to configure SSRS

More guidance to accomplish the configuration is available on the web and will not be presented here. Once configuration is complete type your server name followed by /ReportServer to login to this screen using Windows authentication for the specific server

# This shows a Data Sources link and two reports previously created using Visual Studio.

# In Visual Studio, create a new report using the Wizard

The wizard is somewhat similar to the one in MS Access. A couple differences include providing the connection string for the SQL Server database on one of the screens, which is copied from Web.config and is specific to the installed SQL Server

The Select command with as is also copied from previously used Visual Basic code

# The end result is displayed in the browser

There are options to Print the report using the Printer icon and to Save the report to Word, PDF, etc. from the disk image dropdown.

Details about connecting this to the original menu project are left to the next chapter, while the SSRS project code is provided in the chapter 8 samples folder.

8.6 Chapter Summary

# Reports are produced to convey information and are often selective. The Bible, as a collection of historical reports inspired by God, was written to lead us away from sin toward God and to provide the assurance that we will be with God if we personally receive Jesus Christ as Lord and Savior.

Business reports are written to lead to profits. Computer-generated reports may be transaction, exception or summary reports. A transaction report lists individual details, an exception report lists information that requires immediate attention, and summary reports lump detailed transactions together in an organized way. Exception reports could be produced sorted by "quantity on hand" showing overstocked or understocked items, or sorted by "date last used" showing obsolete items.

Report data elements consist of descriptive headings, data detail and summary lines, subtotals and totals. Subtotals and totals provide the user with controls to verify that the figures they start with at the beginning of a process all make it through the process. Visual Basic programs can calculate and display totals with some coding involved. MS Access and SQL Server can automatically generate totals.

Reports can be produced in Visual Basic using the GridView or with code. MS Access and SQL Server have wizards to quickly walk developers through the report creation process.

8.7 Review Questions

1. What response have you made to the

reports presented by God in the Bible?

2. List the three types of business reports and

give examples of each.

3. What controls might you want to have in

# a system used to enter raw grades?

4. Create a simple diagram showing the

# interrelationships between a grades

subsystem used to create and maintain

grades.dat, a course subsystem used to

create and maintain courses.dat, and the

rptGPAs.vb report program.

5. Search the web to find out more about

# Session objects.

8.8 Projects

1. Build and run the rptGPAs.vb report

program in the chapter 8 samples folder.

2. Change some code for the rptGPAs.vb

program and see what errors you can

# generate. Copy at least 5 different errors

# and related descriptions.

3. Add more data to the input file for the GPA program, enough to generate 16 lines of output, and test it.

4. Use MS Access to create a table consisting of at least 10 employee numbers, employee names and weekly paycheck amounts. Use

# the Report Wizard to create a report,

# including a grand total.

5. Use MS Access to create a table of at least 10 dates and temperatures. Use the

# Property sheet to change the date field

# to the Medium Date format.

6. Implement an interactive VB system to

create, maintain and report courses.dat.

7. Implement an interactive VB system to

create, maintain and report the grades.dat

file.

CHAPTER NINE

Return to Contents

SQL Server

Integration

1 John 2:17

And the world is passing away, and

also its lusts; but the one who does

the will of God abides forever.

9.1 Introduction

Files and databases, where data is stored, are output from one process or system of processes and input to another process or system. Both good and bad data can exist, so effective controls must be used to ensure that only good data is included. This concept has been discussed throughout the text.

The first files were stored as a sequence of records on paper tape and cards, the records consisting of fields. Today they are called text files. These sequential files were maintained using processes similar to the rptGPAs.vb Console application code mentioned in the last chapter.

Databases were introduced in chapter 4. Databases are stored on hard disks, CDs and DVDs and data can be accessed quickly and efficiently using indexes. However, some processes may not work with a particular database. Fortunately, text files can be created from databases by exporting data as .csv or .txt or .xml files. This data can then be imported into other systems. Importing data using SQL Server Integration Services (SSIS) along with integrating SSRS reports with a Visual Basic application are the focus of this final chapter.

Know that one day the world as it is today will pass away, including databases, text files, even Visual Basic itself. The Bible teaches that knowledge will pass away,

1 Corinthians 13:8b (NIV)

...where there are tongues, they will be stilled;

where there is knowledge, it will pass away.

There will be a new heaven and a new earth,

Revelation 21:1 (NIV)

Then I saw a new heaven and a new earth, for the first heaven

and the first earth had passed away, and there was no longer any sea.

It's therefore doubtful that computers are part of God's plan for eternity. The sun will not even be needed because the light of our Lord will shine bright

Revelation 21:23

The city does not need the sun or the moon to shine on it,

for the glory of God gives it light, and the Lamb is its lamp.

In spite of what will transpire at some future point in time, it is not known when these events will occur. Therefore, always be prepared to continue to do a good work in this present age, working not only with the best available software development tools and languages, but also to share knowledge and truth with others, to be a light for Christ in this dark world in order that others might be led to the cross. And of equal or greater importance, be prepared to meet the Lord face to face. Are you ready?

9.2 Integrating Visual Basic with SSRS

Before introducing SQL Server Integration Services (SSIS) lets see how to link a SQL Server Report with the menu project.

Be reminded from chapter 1 that God is a God of order, and order is highly valued within a Visual Basic solution itself. Writing a well-ordered Visual Basic program is good. This is illustrated within each program example presented throughout the text. When dealing with the integration, order also has its place.

First, create a new web form. To create a new web form right-click the project name and choosing Add New Item, select Web Form with Master Page, change the name from WebForm1 to SQL_Server, click Add, select Site.Master.

For the purpose of this exercise the link to the SQL Server report will be next to Contact on the main page. Open the Site.Master and add a line to the Source

Be sure the Report service is running, then run the original SQL Server report in the browser to grab the URL. Add HTML to SQL_Server.aspx and format it to look something like this

Be sure to use the URL for the SQL Server report that works on your system. Related code is in the chapter 9 samples folder in the software download for the text, if needed.

The new page could have more than a single link for a live system. For instance it could contain a list of SQL Server reports to choose from. Clicking on the link displays the report in a new page. Since the report runs on the Report server no additional code is needed to complete the integration.

9.3 SSIS

For businesses that routinely use SQL Server, there is often a need to use data from another source and get it into SQL Server so everyone can reference it, view it or rearrange it, etc. SQL Server Integration Services (SSIS) has the capability to import a variety of different file types into SQL Server. This section will focus on importing a .csv file.

NOAA (https://www.ncdc.noaa.gov/swdi/#Intro) offers free downloads of experimental weather data in .csv file format. After downloading one of the files, a very small subset of data was extracted for the purposes of walking through the process of using SSIS. Here is one line of sample data

20180501000027,-98.70013,43.03269,KUEX,Q1,163,356,-999,-999,-999

Before SSIS can be used a SQL Server table must be designed to receive the data. Here's the table design in SQL Server

The image shows SSISDB which is the catalog database used to store user databases for SSIS. The database created for user tables is testSSIS, and the table for the weather data is called dbo.Weather_Data. To run on a Windows 10 machine, the SSIS catalog must be created. Scroll down further in SQL Server Management Studio until the Integration Services Catalogs folder appears

Right-click to select Create Catalog, then the process to create the SSISDB catalog is completed by filling out this screen

Be sure to remember the password. If there are other issues setting up SSIS, troubleshoot by searching the web. A Bing search for "How to resolve SSIS access denied error" may prove useful.

Now that the SSIS catalog is in place, the testSSIS database and weather table can be created. Here's code used to create the table

CREATE TABLE Weather_Data (

id int PRIMARY KEY IDENTITY(1,1) NOT NULL,

ZTIME datetime2 NOT NULL,

Longitude decimal(10,5) NOT NULL,

Latitude decimal(10,5) NOT NULL,

WSR_ID char(4) NOT NULL,

CELL_ID char(2) NOT NULL,

DATA_RANGE INT NOT NULL,

AZIMUTH INT NOT NULL,

SEVPROB INT NOT NULL,

PROB INT NOT NULL,

MAXSIZE decimal(5,2) NOT NULL

);

Since the data contains a date field, that date needs to be formatted so a SQL insert command will execute successfully. The other data is just fine, but the date requires some adjustment to fit into an appropriate pattern. A small Visual Basic console program was written to read the file contents, reformat the date piece and write the file out again. Here is some of the Visual Basic code used in that program

'Read the second line of text

line = sr.ReadLine()

// want '2018-05-01 00:00:27'

While Not IsNothing(line)

newDate = line.Substring(0, 4) + "-" +

line.Substring(4, 2) + "-" +

line.Substring(6, 2) + " " +

line.Substring(8, 2) + ":" +

line.Substring(10, 2) + ":" +

line.Substring(12, 2)

newLine = newDate + line.Substring(14)

sw.WriteLine(newLine)

End While

The entire program is in the chapter 9 samples folder and illustrates how simple yet useful a Visual Basic program can be. Note that the first column heading line is copied in unchanged, which happens before this code.

Now that the table design is set and the data is properly formatted, a new SSIS project can be built. Within Visual Studio create a new Integration Services Project

This is the starting point for the next step, where Package.dtsx is selected and Control Flow is highlighted

Grab a Data Flow Task from the SSIS Toolbox on the left and drop it on the empty design

Double-click the new Data Flow Task and note that the Toolbox content changes. Next connect the .csv data file with the package. Double-click the Source Assistant in the Toolbox to continue. Follow the instructions and select Flat File then click OK.

Change the settings to match those seen here

Preview the data

Note that the date that was formatted appears as expected. Click OK.

The next part of the process is to choose the destination, for which we use the Destination Assistant. Drag or double-click it to add to the canvas

Select SQL Server and double-click New. Select your local server and the database TestSSIS. Ignore the red arrow. Grab and drag the blue arrow to connect to the destination

Double-click the destination box, pick the Weather_Data database and complete the mappings

The exclamation mark shown in the next image is not a concern for this data. Click Start to test the package and this is the expected result

Start SQL Server Management Studio and use this Select command to view the new data

Select * from dbo.Weather_Data

To complete the integration, deploy the package. In SQL Server, scroll down to Integration Services Catalogs and right-click to create a new folder

Expand the new folder to right-click on the Projects folder and select Import Packages

Know where your package was created by Visual Studio then follow the steps to first Browse to the package folder, load the package and provide a Project name

Select Next without having to enter anything for several screens until you get to this screen

To complete the deployment right-click on the Projects folder and select Deploy Project

Click Connect on this screen

and end up with these results

Right-click on Projects again to Refresh and expand the folders to see the deployed package

To execute the package from SQL Server, right-click the package name and select Execute, then click OK. A message will be displayed. If you choose not to delete data in the table, the number of rows will increase. If you delete the rows first, the exact number of rows from the .csv file will appear in the table.

The integration is now complete. From an operational perspective, other rather simple steps can be taken to create a new job within SQL Server to be scheduled to run at a certain time each day. The job can include a step to first delete the old data. This is left as an exercise for the interested reader.

9.4 Chapter Summary

Data was processed on the first computers through the use of text files. These still serve a useful purpose today. Text files with the .csv extension, among others, are often used to transfer information between systems. SQL Server Integration Services provides a mechanism to accomplish that.

Know that one day the world as it is now known will pass away, including databases, text files, even Visual Basic itself. The Bible teaches that knowledge will pass away,

1 Corinthians 13:8b

...where there are tongues, they will be stilled;

where there is knowledge, it will pass away. (NIV)

And there will be a new heaven and a new earth

Revelation 21:1

Then I saw a new heaven and a new earth, for the first heaven and the first earth had passed away, and there was no longer any sea. (NIV)

In spite of what God declares will happen at some future point in time it isn't known when these events will occur, so lets do our best at what we are capable of doing. Therefore, rejoice in the Lord, serve Him with great joy until that day. And may God richly bless you in return.

9.5 Review Questions

1. Discover another Bible verse to indicate

that computers will pass away.

2. Develop a process flow diagram for

creating and deploying and SSIS package.

3. Find a web link that discusses how to create

SQL Server jobs.

9.6 Projects

1. Build a report project in Visual Studio.

Prepare a test plan to thoroughly test the

program and have the new test data fulfill

all the requirements in the plan. Summarize

the results in a brief paper.

2. Create a simple .csv file to use with SSIS

and create a new Integration Services

project to import the data to SQL Server. Test the solution and summarize the results

in a brief paper.

3. Create a job to load the .csv data in the

weather table in SQL Server using SQL

Server Agent. Be sure SQL Server Agent is

running as a service. The first job step is to delete the weather table data. The next job step is to execute the package.
About the Author

# With an earned M.S. in Computer Science and over 40 years experience developing software and databases, managing projects and teaching, Wes Sweetser "retired" in 2018.

# In his spare time he and his wife enjoy bike riding and spending time with family. They draw their strength from the Lord Jesus Christ. At age 30, Wes realized that Jesus was indeed risen from the dead, having believed the testimony of the 500 (1 Cor 15:6), and received from Lord Jesus the great gift of eternal life along with the Holy Spirit. It's been a wonderful ride since that day!

# The Approved Workman Series

C# for the Approved Workman is available online from Smashwords.

COBOL for the Approved Workman is available online from Smashwords.

Visual Basic for The Approved Workman is available online from Smashwords.
