Data Access

11/5/2015

- VB supports various data access techniques, such as Data Access Object (DAO), Remote Data Object (RDO), and Active Data Object (ADO). It can access data from a number of sources, namely, Jet, ISAM, ODBC, and so on. Visual Basic can also access data from flat files.

Data Access Techniques:
Data access refers to software and activities related to storing, retrieving, or acting on data housed in a database or other repository. Following are the various techniques used in accessing the data.

Data Access Object (DAO)
DAO/ODBCDirect
Remote Data Objects (RDO)
Active Data Objects (ADO)

ADO object Model:

Stored Procedures:

A stored procedure sometimes called a sproc is a program or procedure, which is physically stored within a database. The exact implementation of a stored procedure varies from one database to the other.

Recordset:

File Handling

11/5/2015

Handling Run time Errors:

Handle file I/O:

Resume:

The Resume statement is used to specify where to restart the flow of execution. This can either be a label or Next. If Next is used, the execution is continued from the statement following the statement that caused the error. An example of the Resume statement is displayed here. The 

ON ERROR Resume Next statement skips any run-time errors that may occur. The ON ERROR GoTo 0 statement turns off error trapping.

Err Object:

Visual Basic provides an inbuilt object called Err for handling errors. This object is used to retrieve information about the type of run-time error that has occurred. It can also be used to present information to the user if the user can solve the problem. The Err object has a set of properties and methods. These properties are reset to zero or zero-length strings (" ") after an Exit Sub, Exit Function, Exit Property, or Resume Next statement within an error-handling routine. You cannot reset the properties of the Err object by using any form of the Resume statement outside an error-handling routine. You can use the Clear method to reset the Err object explicitly.

    1. Number Property
          2. Description Property

      3. Source Property   

4. Clear Method

5. Raise Method 

Source Property -- It is a string expression of the location where the error has occurred in the application. If an unexpected run-time error occurs, Visual Basic sets the Err.Source property. If the error occurs in a standard topic, the source will be set to the project name. If the error occurs in a class topic, Visual Basic uses the project name and the class topic name.

Raise Method??? need more analysis

The Raise method of the Err object is used to generate errors within the code.

3 Types of file handling in VB:

1. Random Access

                                                                  2. Sequential Access(Works best with files containing only text)

Line Input# ----- the end-of-line character is not included when the line is read into the variables. 

Input# ------ The Input # statement is used to read the list of numbers or string expressions.

Write----- The Write statement writes data of any data type or format to an opened file.  You need to open a file for output, append, or random before you use the Write statement. It inserts a comma and quotes around strings automatically.

Controls and Coding

11/5/2015

Two types of Controls:

1. Intrinsic (Basic set of controls)
2. Custom controlled (Exist as separate file)

Intrinsic Controls:

Custom Controls:

The ImageList control acts as a storehouse for images that are referenced by index. It acts as a central repository to supply images to other controls. The images are added at design time and run time.
The TreeView control displays a hierarchical list of node objects. It is possible to manipulate these node objects at run time.
The ListView control displays the items in one of the four views, namely, large icons, small icons, list, and report.
The ToolBar control contains a collection of button objects and these objects are used to create a toolbar for a VB application. It helps to display the appropriate icons on the buttons by using the Imagelist control.
The StatusBar control provides a frame at the bottom of an application. It displays the status information.


ControlArray:??? Prpvide more analysis results

- Control array is a group of controls that shares the same name, type, and event procedures.
Each control of a control array is referred by an index.

- A control array composed of more than one type of element (textboxes and labels) cannot be created. When you format one control, for example, changing the BackColor property, then that format is applied to all other controls in the array.

Load and Unload methods are used to add or remove elements to/from Control Array.

Count, Item(index), Ubound, and Lbound are the methods available to access a control array.

The Count method returns the number of elements in a control array. The Item(index) method returns the element of the specified index. The Ubound method sets the upper bound of a control array. The Lbound method sets the lower bound of a control array.

Remove, Add(used to add a control), TypeOf, TypeName.... could be applied on the control array.

Procedure

11/4/2015

Procedure is of type:
- Functions
- Sub
- Property

Two types of procedures in vb are Functions and Sub procedures

Note: Exit is used to come out of procedure
Call - is used to call a procedure


- Function:

- Property Procedure:

- What are Sub Main(),,, where to use them

Functions

11/4/2015:

4 Types of functions;

1. String Functions
2. Inbuilt Functions
3. MsgBox 
4. InputBox

String Functions:

• StrReverse -- helps to reverse the string completely
• & -- concatenate string
• Replace -- -Used to replace characters in a string
• Len --- Length of string
• Left --- Used to return specified number of characters from the left side of the string
• Right ... Used to return specified number of characters from the right side of the String
• Mid ... Returns the String of given numbers from a specified location in another String
• InStr .... Determines whether a character or string exist within another string
• Split ... Takes a list of words stored in a String and split them into a String array
• Filter ... Helps to create a new list that is a filtered version of the first list
• Join ... Combines all the elements in String Array
• UCase and LCase...converts the string to upper and lower case

Inbuilt functions:

• Convert one data type to another
• Validation functions ---- IsEmpty(), IsError(), IsDate()....

MsgBox:

• MsgBox consists of one command button to acknowledge the message
• Icons and multiple buttons also could exist

vbCritical, vbExclamation, vbQuestion, vbInformation.....helps in displaying various icons in the msg box.

• The six set of command buttons can be used --- vbOKOnly, vbOKCancel. vbYesNoCancel....
• We can retrieve value from MsgBox
• 
  

Arrays

11/4/2015

- Arrays have lower and upper limit. Lower limit in Array is 0 and it called as base.

Syntax of declaring Array:
There are 3 methods of declaring an array:
Source: http://www.tutorialspoint.com/vbscript/vbscript_arrays.htm

 'Method 1 : Using Dim

Dim arr1() 'Without Size

'Method 2 : Mentioning the Size
Dim arr2(5)  'Declared with size of 5

'Method 3 : using 'Array' Parameter
Dim arr3
arr3 = Array("apple","Orange","Grapes")

Method 4:
dim arr3(0 to 5)

Dynamic Array:

Here size of an array could be changed during the run time.

Sub arraysize()


                   Dim arrTestArray()

          intSize = 0

'ReDim - to redeclare the size of an array
'Preserve - to
ReDim Preserve arrTestArray(intSize)
arrTestArray(intSize) = 10
ReDim Preserve arrTestArray(intSize + 1)
arrTestArray(intSize + 1) = 20
MsgBox UBound(arrTestArray)
MsgBox LBound(arrTestArray)
For i = LBound(arrTestArray) To UBound(arrTestArray)
MsgBox arrTestArray(i)
Next

End Sub


- Option Base statement:

Option Base statement in Array allows user to specify base index.

Ex:
Option Base 1
Dim Weekday(7) as String

Note: Here the Weekday is array, its lower/base value starts with 1.

- To keyword is used to declare min and max index numbers for an array.

dim array1(1 to 10) as Integer

- Boundaries of an array could be found using UBound and LBound.

- 3 Types of Arrays are:

Single, Multi and Dynamic array.

Dynamic arrays can be resized at any time. They help to manage memory efficiently. The ReDim statement is used to specify the bounds of array at run time.

Dim arr1() as Int
Redim arr1(2.3.1) --

Explain more about Redim:

1. Can be Resized using Redim
2. Redim cannot be used to change the data type of an array
3. Arrays must be declared without initial size while using Redim statement
4. Redim cannot be used to change the dimensions
5. Preserve keyword in the Redim statement is used to preserve the existing values

          Redim Preserve customer(20) 

Website:
http://windowsitpro.com/article/vbscript/understanding-vbscript-arrays-5627

The website has complete detials of Array.

Redim:

Source: https://msdn.microsoft.com/en-us/library/w8k3cys2.aspx

Redim is used to redeclare the size of an array.

You can use the ReDim statement to change the size of one or more dimensions of an array that has already been declared. If you have a large array and you no longer need some of its elements, ReDim can free up memory by reducing the array size. On the other hand, if your array needs more elements, ReDim can add them.

The ReDim statement is intended only for arrays. It's not valid on scalars (variables that contain only a single value), collections, or structures. Note that if you declare a variable to be of type Array, the ReDim statement doesn't have sufficient type information to create the new array.

Preserve:

Helps to preserve the values in an array if the size is being reduced. Actually when Redim is used vb script will create new array with specified size, inorder to make use of values in the existing array use Preserve, else it will not copy the values but it does create new array will all default values in it.

Examples:

VB

Dim intArray(10, 10, 10) As Integer
ReDim Preserve intArray(10, 10, 20)
ReDim Preserve intArray(10, 10, 15)
ReDim intArray(10, 10, 10)

The Dim statement creates a new array with three dimensions. Each dimension is declared with a bound of 10, so the array index for each dimension can range from 0 through 10. In the following discussion, the three dimensions are referred to as layer, row, and column.

The first ReDim creates a new array which replaces the existing array in variable intArray. ReDim copies all the elements from the existing array into the new array. It also adds 10 more columns to the end of every row in every layer and initializes the elements in these new columns to 0 (the default value of Integer, which is the element type of the array).

The second ReDim creates another new array and copies all the elements that fit. However, five columns are lost from the end of every row in every layer. This is not a problem if you have finished using these columns. Reducing the size of a large array can free up memory that you no longer need.

The third ReDim creates another new array and removes another five columns from the end of every row in every layer. This time it does not copy any existing elements. This statement reverts the array to its original size. Because the statement doesn't include the Preserve modifier, it sets all array elements to their original default values.

Datatypes, Variables, Operators

Subtype	Note: If the variable is not declared, the default data type it would be assigned to is 'Variant' and the default value of Variant is empty/null. Data types: Description
Empty	Variant is uninitialized. Value is 0 for numeric variables or a zero-length string ("") for string variables.
Null	Variant intentionally contains no valid data.
Boolean	Contains either True or False.
Byte	Contains integer in the range 0 to 255.
Integer	Contains integer in the range -32,768 to 32,767.
Currency	-922,337,203,685,477.5808 to 922,337,203,685,477.5807.
Long	Contains integer in the range -2,147,483,648 to 2,147,483,647.
Single	Contains a single-precision, floating-point number in the range -3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values.
Double	Contains a double-precision, floating-point number in the range -1.79769313486232E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values.
Date (Time)	Contains a number that represents a date between January 1, 100 to December 31, 9999.
String	Contains a variable-length string that can be up to approximately 2 billion characters in length.
Object	Contains an object.
Error	Contains an error number.

Type Declaration Characters: Are the symbols that represents data types.

Identifier type character	Data type	Example
%	Integer	Dim L%
&	Long	Dim M&
@	Decimal	Const W@ = 37.5
!	Single	Dim Q!
#	Double	Dim X#

Note: Dim x% is same as Dim x as Integer.

User Defined types:

Combine variables and fundamental data types to create user-defined data types.

Use Type and End Type statements in the declaration section to create a user defined data types.

In the above example EmpDet is user defined data type that has Byte and Integer variables.

And in the below screen, Student type is used while declaring st1 variable.?????

Implicit and Explicit Declaration of variable:

1. If the variable is declared using Dim it means, it is explicit declaration here you have to mention the data type of the variable that you using

2. Implicit declaration is when you use the variable without declaring it, but the type of variable depends on the value assigned to it.

Scope of the variables:

Public and Private.

Static Variables??

Static variables are used to retain the value of the variable even after the procedure has finished the execution.

syntax: Static <var name> as <data type>

Loop structures in VB include:

1. For .... Next

2. Do...While

3. While .... Wend??

4. With ....End With ??