Databases cont.

WHERE, LIKE - are filters

Relational Operators - use to evaluate if the condition is true
>
<
<>
>=
<=
=

Array:
Dim strSQLStrings() As String = {"SELECT * FROM ApprovedTravelRequests", _
"SELECT [Travel Cost]FROM ApprovedTravelRequests", _
"SELECT [Travel Cost], [Location] FROM ApprovedTravelRequests", _
"SELECT * FROM ApprovedTravelRequests WHERE Location = 'Richmond, VA'", _
"SELECT * FROM ApprovedTravelRequests WHERE Location LIKE 'R%'", _
"SELECT * FROM ApprovedTravelRequests WHERE Location LIKE '%R'", _
"SELECT * FROM ApprovedTravelRequests WHERE [Last Name] LIKE 'S%'"SELECT * FROM ApprovedTravelRequests WHERE [Last Name] LIKE 'RA%'", _
"SELECT * FROM ApprovedTravelRequests WHERE [Last Name] LIKE '%LAS'", _
"SELECT * FROM ApprovedTravelRequests WHERE [Travel Cost] > 1000", _
"SELECT * FROM ApprovedTravelRequests WHERE [Travel Cost] < 1000", _
"SELECT * FROM ApprovedTravelRequests WHERE [First Name] = 'Janet' And [Last Name] = 'Dunford'", _
"SELECT * FROM ApprovedTravelRequests WHERE [First Name] = 'Janet' Or [Last Name] = 'Tirrell'", _
"SELECT * FROM ApprovedTravelRequests WHERE Location = 'Chicago, Illinois' And [Purpose For Travel] = 'Financial Seminar'", _
"SELECT * FROM ApprovedTravelRequests WHERE Location = 'Chicago, Illinois' Or [Purpose For Travel] = 'Financial Seminar'"}
' Anything in ' ' is case sensitive
' 'R%' brings up locations starting with R,
' %R' brings up locations ending in R,
' %A% brings up locations starting & ending with A
' Use [ ] where a space has been inserted in name
' % is a wildcard - fills in for fuzzy characters
' >= is a relational operator to
' logical operators AND OR to combine fields for search
% is a wild card character - fills in for other characters
Use [ ] where a space has been inserted in name
Anything in quotes is case sensitive

Databases - SQL

SQL = Structured Query Language

* in SQL = ALL

Tool used for data base = DataGridView

Functions: Select, Delete, Update, Insert

Concepts:
ConnectionString
ConnectionObject
CommandObject
DataAdapter
DataSetObject

Remember: PrimaryKey & AutoNumber is a unique identity field

ID IS KING

Specify driver for type of string

Databases

Access Database
SQL - Structured Query Language
Language used for insert, update, select, delete

Creates Reports & Queries

Concept for Project
ConnectionString: The supermarket address
ConnectionObject: The car
CommandObject: The shopping list
DataAdapter: Mum doing the shopping
DataSetObject: Pantry

* in SQL = ALL

ID IS KING

Relational Database Management System - Access

Can create queries & reports from Access Database

Table contains:
Columns are knows as "fields"
Rows contain "records"

Declare the datatype for field

ID is used a lot in databases - if you know the ID, that's all that is needed to know to access a record

ID can be the Primary Key - only one field can be the Primary Key

Blog Task

1. What is a data base?
A database is a collection of data that is organised in tables/list about a specific topic that can be easily accessed, managed and updated. Anything that stores lists/tables of data, ie, inventory list.

2. What are the different types of databases?
Flat file database excel spreadsheet.
Relational database, this is a tabular type database in which data is defined so that it can be reorganized and accessed in a number of different ways, eg. lists.
Distributed database database is one that stored on various computers in a network - multiple user, eg. Chisholm.
Object-oriented programming database - works well with object programming languages

Ch 9 File Handling

System IO

IO.File
- Creating Files
- Deleting Files
- Moving Files
- Copying Files
- Opening Files

StreamReader - IO.StreamReader
StreamWriter - IO.StreamWriter - has functionality for text

Constructor = On Switch

Ch 9 Using Arrays & File Handling

Explicite defined array

Dim MyVariable As DateType = x
Dim strName As String = "Rach"
Dim MyArray(5) As DataType
Dim strNames(2) As String

' Assign values
strNames(0) = "Rach"
strNames(1) = "Pam"
strNames(2) = "David"

An array contains multiple elements - array index begins at 0

Implicitly sized Array

Declare an array and populate with no definate value assigned
Dim strNames() As String = ("Baker", "Lopez", "Buck", "Chan", "Tirrell")
Dim intReservations() As Integer = {4, 5, 12, 2, 8}

Parallel Arrays
Dim intDinnerBookings(2) As Integer
Dim strNames(2) As String
' Loop to populate
' Data Table - strNames
' Bookings - intDinnerBookings

Dim ItemArray() As String = {"x", "y", "z"}
' 3 elements
Dim intCount As Integer
'Declared an Integer Variable

For intCount = 0 To ItemArray.Length -1
'Length = number of elements so .Length = -1
'Extract each item into the loop - start at 0
lstBox.Item.Add(ItemArray(intCount))

Next

Length returns the number of elements - not the upperbound

Index begins at zero
if the -1 is omitted from the .Length - it will loop 4 times instead of 3

= IndexOutOfRange Exception. A Try-Catch statement can catch this exception, but it is best to stay within the array boundaries of zero and the upper-bound array subscript.

Use loops to populate the array

Sorting Arrays
When applied - (default) the lowest value is placed in the 1st element in the array with an index of zero, next lowest is placed in the second element, etc

Array.Sort(ArrayName)
'Sort is a Function
Dim intAges() as Integer = {16, 64, 41, 8, 19, 81, 23}
Array.Sort(intAges)

Search Arrays
Sequential - searching each element with first name - not particularly efficient in large list
Binary - searches a sorted array for a value using a binary search algorithm = by repeatedly dividing the search interval in half = sort first - splits in half - searches on upper half of array to match the index - if match continues to split - no match - searches on lower half - them splits until found

Revision - Ch 8

Defining A Function - what values need to be passed into the function

Public Function MultiplyAge(intAge As Integer)
intAge = intAge * 3
return intAge
End Function

Procedure

Public Sub MultiplyAge(intAge As Integer)
intAge = intAge * 3
End Sub

Try-Catch Structure

Detects exceptions and takes corrective action
"Try" - to execute this code
"Catch" - errors here

starts with specific exception - ends with generic exception

Commonly used:
FormatException = most common
OverflowException = follows
SystemException = finish with

Everything in the Try-Catch will be done as a block

Chapter 7 - Exception Handling

Exception handling = handling errors
Expect the worst from users

Allows the program to elegantly negotiate the error

To work with numeric:
try
intNumber=txtTextBox.Text
Catch ThisException As Exception
'reporting
'reporting
End try

Exception is a Class Object

AgumentNullException is a variable that has no value is passed to a procedure
Dim strTerm As String
Me.lstDisplay.Items.Add(strTerm)

DivideByZeroException is where a value is divided by zero
intResult= intNum / 0

FormatException - A format is converted to another type that is not possible
strTerm = "Code"
intValue = Convert.ToInt32(strTerm)

NullReferenceException - A procedure is called when the result is not possible
Dim strTerm As String
intValue = strTerm.Length

OverflowException = A value exceeds its assigned data type
Dim intCost as Integer
intCost = 58 ^ 4000000000

System Exception - Generic
Catches all other exceptions

Chapter 8 - Using Procedures & Exception Handling

Visual Basics provides a generic splash screen template you can add to your project with or without modification. You can change the generic graphic on the splash screen by changing the BackgroundImage property on the Properties window. To use the generic splash screen, follow the steps starting on pg 567.

Methods, Ops, Procedures
Function:

Public Function DoesItEqualTen(intNumberOne As Integer,intNumberTwo As Integer) As Boolean
Dim blnTrueOrFalse As Boolean=False
If intNumberOne + intNumberTwo=10 Then
blnTrueOrFalse=True
Else
blnTrueOrFalse=False
Return blnTrueOrFalse
End Function

Return String

Public Function DoesItEqualTen(intNumberOne As Integer,intNumberTwo As Integer) As String
Dim blnTrueOrFalse As Boolean=False
If intNumberOne + intNumberTwo=10 Then
blnTrueOrFalse=True
Else
blnTrueOrFalse=False
Return "xxx"
End Function

Common Function
Public Sub xxx()
If IsNumeric(txtTextBox.text) Then
.......

End If
End Sub

Public Function IsNumeric(StringToLookAt As String) As Boolean
End Function

Public Function Customer Balance(AccountNumber As String) As Decimal
Dim decCustomerBalance As Decimal
............
............
Return decCustomerBalance
End Function

Calling Procedure

txtBalance.text=CustomerBalance(strAccountNumber)

Parameters

ByRef
When you type in an argument - you will get a ByRef
Pass a reference to the object

ByVal
Object is duplicated and passed to the function
Working with new object - looks the same

2 objects can have same name - different hash codes