Yarmouk University
Faculty of IT and Computer Sciences
Department of Management Information Systems
MIS 312 Programming for MIS Lab
Lab Manual
Prepared By:
Ala'a AL-Aalawneh
2 / 50
Table of Content
Week 1 and 2: Chapter 2: Visual Basic, Controls, and Events ........................................... 3
Week 3: Chapter 3: Numbers, Variables, and Strings ...................................................... 13
Week 4: Chapter 8: Sequential Files ................................................................................. 17
Week 5: Chapter 4: Decisions (If Blocks/ Select Case Blocks) ....................................... 18
Week 6: Chapter 9: Additional Controls and Objects (List Boxes/ Combo Boxes) ......... 23
Week 7: Chapter 5: General Procedures (Sub Procedures) .............................................. 25
Week 8: Chapter 5: General Procedure (Function Procedures) ........................................ 27
Week 9: Chapter 6: Repetition (Loops) ............................................................................ 29
Week10: Chapter 10: Database Management and SQL ................................................... 34
Week 11: Chapter 10: Database Management (Binding a listbox to a datatable) ............ 40
Week 12: Chapter 10: Database Management (Setting Criteria/ Tables Join) ................. 42
Week 13: Chapter 10: Database Management (Data Grid View/ Changing contents) ..... 45
Week 14 & 15: Chapter 10: SQL (insert, delete and update sql statement) ..................... 49
3 / 50
Week 1 and 2: Chapter 2: Visual Basic, Controls, and Events
2.1 An Introduction to Visual Basic
2.2 Visual Basic Controls
2.3 Visual Basic Events
Lesson 1
2.1 An Introduction to Visual Basic & visual basic controls
Learning outcome: be able to:
Develop a Visual Basic Application
Design the Interface for the user.
start a new project
know the toolbox control
how to change the properties of control using properties windows
Determine which events the controls on the window should recognize.
Write the event procedures for those events.
- Visual Basic Start Page
4 / 50
- Start a New Project
New Project Dialog Box
5 / 50
Initial Visual Basic Screen
Toolbox control:
Place a Control from the Toolbox onto the Form Window by using one of
three ways:
Double-click
Drag and Drop
Click, Point, and Drag
6 / 50
Properties Window
Press F4 to display the Properties window for the selected control
Categorized view Alphabetical view
Textbox
properties
setting
7 / 50
Properties Window
Some Often Used Properties
Text
Autosize
Font.Name
Font.Size
ForeColor
BackColor
ReadOnly
Setting Properties
Click on property name in left column.
Enter its setting into right column by typing or selecting from options displayed
via a button or ellipses.
Setting the Text Property
Click on Text property.
Type your first name
8 / 50
Setting the ForeColor Property
Click on ForeColor.
Click on button at right of settings box.
Click on Custom tab to obtain display shown.
Click on a color.
Font Property
Click on Font in left column.
Click on ellipsis at right of settings box to obtain display shown,
Make selections
9 / 50
Button Control
The caption on the button should indicate the effect of clicking on the button.
Text property determines caption
Task :
Create a program with:
form name your name
yellow button containing the word VB in a large italic,centralized and red letter
read-only textbox containing the words" Visual Basic" in bold, centralized white
letter and red background.
10 / 50
Lesson 2:
2.3 Visual Basic Events
Learning outcome for this sections:
Know how to Write the code that executes when events occur
Change the properties of controls using code
General Form:
controlName.property =setting
Code Editor
Code Editor tab Form Designer tab
header Structure of an Event Procedure
Private Sub objectName_event(...)
Handles objectName.event
statements
End Sub
(...) is filled automatically with (By Val sender As System.Object, By Val e As
System.EventArgs
11 / 50
Sample Form
Double Click on txtFirst
Public Class frmDemo
Private Sub txtFirst_TextChanged(...)
Handles txtFirst.TextChanged
End Sub
End Class
Public Class frmDemo
Private Sub txtFirst_TextChanged(...)
Handles txtFirst.TextChanged
txtFirst.ForeColor = Color.Blue
End Sub
End Class
Automatically pops up to give the programmer help.
txtFirst.
Double-click on btnRed
Public Class frmDemo
Private Sub txtFirst_TextChanged(...)
txtfirst
txtsecond
btnred
12 / 50
Handles txtFirst.TextChanged
txtFirst.ForeColor = Color.Blue
End Sub
Private Sub btnRed_Click(...)
Handles btnRed.Click
txtFirst.ForeColor = Color.Red
End Sub
End Class
Event Procedure txtFirst.Leave
Select txtFirst from Class Name box drop-down list.
Select Leave from Method Name box drop-down list.
Private Sub txtFirst_Leave(...) Handles txtFirst.Leave
txtFirst.ForeColor = Color.Black
End Sub
Private Sub txtFirst_TextChanged() Handles txtFirst.TextChanged
txtFirst.ForeColor = Color.Blue
End Sub
Private Sub btnRed_Click(...) Handles btnRed.Click
txtFirst.ForeColor = Color.Red
End Sub
Altering Properties of the Form
The following won't work:
frmDemo.Text = "Demonstration"
The form is referred to by the keyword Me.
Me.Text = "Demonstration
13 / 50
Week 3: Chapter 3: Numbers, Variables, and Strings
3.1 Numbers
3.2 Strings
3.3 Input and Output
Lesson 1: Numbers and variables
Learning outcomes :
- be able to know how to display numbers in a listbox
- be able to know how to declare a variables
Displaying Numbers
Let n be a number or a numeric expression.
The statement:
lstBox.Items.Add(n) displays the value of n in the list box.
Example:
Code and output:
Private Sub btnCompute_Click (...)Handles btnCompute.Click
lstResults.Items.Add(5)
lstResults.Items.Add(2 * 3)
lstResults.Items.Add((2 ^ 3) 1)
End Sub
Output in list box 5
6
7
14 / 50
Declaration of variables
Numeric variables are automatically initialized to 0:
Dim varName As Double
To specify a nonzero initial value
Dim varName As Double = 50
Numeric variables can be used in numeric
expressions.
Example:
Dim balance As Double = 1000
lstBox.Items.Add(1.05 * balance)
Output: 1050
Lesson 2: Strings
Learning outcomes:
- be able to know how to declare a string.
- Be able to know string's method and properties
Strings declaration
You can declare a string variable and assign it a value at the same time.
Dim firstName As String = "Fred"
Add Method
Let str be a string literal or variable. Then,
lstBox.Items.Add(str)
displays the value of str in the list box.
Using Text Boxes for Input and Output
The contents of a text box is always a string
example
strVar = txtBox.Text
Output example
txtBox.Text = strVar
String Properties and Methods
"Visual".Length is 6.
"Visual".ToUpper is VISUAL.
"123 Hike".Length is 8.
"123 Hike".ToLower is 123 hike.
"a" & " bcd ".Trim & "efg" is abcdefg.
15 / 50
Substring Method
Let str be a string.
str.Substring(m, n) is the substring of length
n, beginning at position m in str.
Visual Basic.Substring(2, 3) is sua
Visual Basic.Substring(0, 1) is V
IndexOf Method
Let str1 and str2 be strings.
str1.IndexOf(str2)
is the position of the first occurrence of str2 in str1.
(Note: Has value -1 if str2 is not a substring of str1.)
"Visual Basic".IndexOf("is") is 1.
Task#2
Write a program that parses a name:
Private Sub btnanalyze_Click (...)Handles btnanalyze.Click
Dim fullname, firstname, lastname as string
Dim n as integer
Fullname=txtname.text
N=fullname.indexof(" ")
Firstname=fullname.substring(0,n)
Lastname=fullname.substring(n+1)
Lesson 3:Input and Output
Learning outcomes:
Be able to know how to Format Output with Zones
Reading Data from Files
Formatting Output with Zones:
Use a fixed-width font such as Courier New and divide the characters into zones with
a format string.
Dim fmtStr As String = "{0, 15}{1, 10}{2, 8}"
lstOutput.Items.Add(String.Format(fmtStr, _data0, data1, data2))
Dim fmtStr As String = "{0, 15}{1, 10}"
lstOutput.Items.Add(String.Format(fmtStr, _Name, Major))
lstOutput.Items.Add(String.Format(fmtStr, _Mohammed, MIS))
lstOutput.Items.Add(String.Format(fmtStr, _Ahmed, CIS))
16 / 50
Reading Data from Files
Sample File: PAYROLL.TXT
Mike Jones
9.35
35
John Smith
10.75
33
Steps to Use StreamReader
Execute a statement of the form
Dim readerVar As IO.StreamReader = IO.File.OpenText(filespec)
Read items of data in order, one at a time,from the file with the ReadLine method.
strVar = readerVar.ReadLine
After the desired items have been read from the file, terminate the communications link
readerVar.Close()
Example using StreamReader
Dim name As String
Dim wage, hours As Double
Dim sr As IO.StreamReader = IO.File.OpenText("PAYROLL.TXT")
name = sr.ReadLine
wage = CDbl(sr.ReadLine)
hours = CDbl(sr.ReadLine)
lstBox.Items.Add(name & ": " & wage * hours)
OUTPUT: Mike Jones: 327.25
17 / 50
Week 4: Chapter 8: Sequential Files
8.1 Sequential Files
8.2 Using Sequential Files
Lesson 1: sequential files:
Learning outcomes: be able to know how to:
Create a Sequential File
Add Items to a Sequential File
Creating a Sequential File
1.Choose a filename may contain up to 215 characters
2.Select the path for the folder to contain this file
3.Execute a statement like the following:
Dim sw As IO.StreamWriter = IO.File.CreateText(filespec)
(Opens a file for output.)
4.Place lines of data into the file with statements of the form:
sw.WriteLine(datum)
5.Close the file:
sw.Close( )
Example:
Private Sub btnCreateFile_Click(...) _Handles btnCreateFile.Click
Dim sw As IO.StreamWriter =IO.File.CreateText("PAYROLL.TXT")
sw.WriteLine("Mike Jones") 'Name
sw.WriteLine(9.35) 'Wage
sw.WriteLine(35) Hours worked
sw.WriteLine("John Smith")
sw.WriteLine(10.75)
sw.WriteLine(33)
sw.Close()
End Sub
File: PAYROLL.TXT
Mike Jones
9.35
35
John Smith
10.75
33
Adding Items to a Sequential File
1.Execute the statement
Dim sw As IO.StreamWriter = IO.File.AppendText(filespec)
where sw is a variable name and filespec identifies the file.
2.Place data into the file with the WriteLine method.
3.After all the data have been recorded into the file, close the file with the statement
sw.Close()
18 / 50
Week 5: Chapter 4: Decisions (If Blocks/ Select Case Blocks)
5.1 - If blocks
5.2 - Select Case blocks
Lesson 1: if blocks
Learning outcomes: be able to know
- how to write a code with condition using if clause or else if clause
Example 1: Form
Example 1: Code
Private Sub btnFindLarger_Click(...) _
Handles btnFindLarger.Click
Dim num1, num2, largerNum As Double
num1 = CDbl(txtFirstNum.Text)
num2 = CDbl(txtSecondNum.Text)
If num1 > num2 Then
largerNum = num1
txtFirstNum
txtSecondNum
txtResult
19 / 50
Else
largerNum = num2
End If
txtResult.Text = "The larger number is " & largerNum
End Sub
Example 1: Output
Example 2: Form
Example 2: Partial Code
If costs = revenue Then
txtResult.Text = "Break even"
Else
If costs < revenue Then
profit = revenue - costs
txtResult.Text = "Profit is " & _
FormatCurrency(profit) & "."
Else
loss = costs - revenue
txtResult.Text = "Loss is " & _
20 / 50
FormatCurrency(loss) & "."
End If
End sub
Example 2: Output
Example:Grade Computation
If Score>=90 and Attendance=100
grade=A
If Score>=90 and Attendance<100
grade=B
If Score>=80 and <=90 and Attendance >=90
grade=B
Otherwise
grade=C
Example 2: Partial Code
Dim score, attendance As Double
score = CDbl(txtScore.Text)
attendance = CDbl(txtAttendance.Text)
21 / 50
If score >= 90 Then
If attendance = 100 Then
txtGrade.Text = "A"
Else
txtGrade.Text = "B"
End If
ElseIf score >= 80 And attendance >= 90 Then
txtGrade.Text = "B"
Else
txtGrade.Text = "F"
End If
Lesson 2 : select case
Learning outcomes:
- be able to know how to write a code using select case
Example 1: Form
Example 1: Code
Private Sub btnEvaluate_Click(...) _
Handles btnEvaluate.Click
Dim position As Integer = CInt(txtPosition.Text)
Select Case position
Case 1
txtOutcome.Text = "Win"
Case 2
txtOutcome.Text = "Place"
Case 3
txtOutcome.Text = "Show"
Case 4, 5
txtOutcome.Text = "You almost placed in the money."
Case Else
txtPosition
txtOutcome
22 / 50
txtBox.Text = "Out of the money."
End Select
End Sub
Example 1: Output
23 / 50
Week 6: Chapter 9: Additional Controls and Objects (List
Boxes/ Combo Boxes)
- 9.1 List Boxes, Combo Boxes
Lesson 1: List Boxes, Combo Boxes
Learning outcomes:
- be able to know how to use :
- List Box Control
- Combo Box Control
Example 1: Form
Example 1: Code
Private Sub lstOxys_SelectedIndexChanged(...) _
Handles lstOxys.SelectedIndexChanged
txtSelected.Text = CStr(lstOxys.SelectedItem)
End Sub
Private Sub btnAdd_Click(...) Handles btnAdd.Click
Dim item As String
item = InputBox("Item to Add:")
lstOxys.Items.Add(item)
End Sub
Private Sub lstOxys_DoubleClick(...) _
Handles lstOxys.DoubleClick
lstOxys.Items.RemoveAt(lstOxys.SelectedIndex)
txtSelected.Clear()
End Sub
Lesson 2: The Combo Box Control
Example 2
Private Sub btnDisplay_Click(...) _
Handles btnDisplay.Click
txtSelected
lstOxys
24 / 50
txtDisplay.Text = cboTitle.Text & " " & txtName.Text
End Sub
cboTitle
txtDisplay
txtDisplay
25 / 50
Week 7: Chapter 5: General Procedures (Sub Procedures)
5.1 Sub Procedures
Lesson 1: sub procedure
Learning outcomes: be able to :
Compare function procedure with sub procedures
Know the difference between bassing by value and by reference
general syntax
Sub ProcedureName()
statements
End Sub
Example1:
Private Sub Button1_Click() Handles Button1.Click
lstBox.Items.Clear()
ExplainPurpose()
lstBox.Items.Add("")
End Sub
Sub ExplainPurpose()
lstBox.Items.Add("Program displays a sentence")
lstBox.Items.Add("identifying a sum.")
End Sub
Passing by Value and by reference
Example
Public Sub btnOne_Click (...) Handles _
btnOne.Click
Dim n As Double = 4
Triple(n)
txtBox.Text = CStr(n)
End Sub
Sub Triple(By Val num As Double)
num = 3 * num
End Sub
Output: 4
Same Example: n num
Public Sub btnOne_Click (...) Handles _
26 / 50
btnOne.Click
Dim num As Double = 4
Triple(num)
txtBox.Text = CStr(num)
End Sub
Sub Triple(By Val num As Double)
num = 3 * num
End Sub
Output: 4
Passing by Reference
Example
Public Sub btnOne_Click (...) Handles _
btnOne.Click
Dim num As Double = 4
Triple(num)
txtBox.Text = CStr(num)
End Sub
Sub Triple(ByRef num As Double)
num = 3 * num
End Sub
Output: 12
Example: num n
Private Sub btnOne_Click(...) Handles _
btnOne_Click
Dim n As Double = 4
Triple(n)
txtBox.Text = CStr(n)
End Sub
Sub Triple(ByRef num As Double)
num = 3 * num
End Sub
Output: 12
27 / 50
Week 8: Chapter 5: General Procedure (Function
Procedures)
Lesson 1: 5.3 Function Procedures
Learning outcomes: be able to :
Compare function procedure with sub procedures
Syntax:
Function FunctionName(By Val var1 As Type1, _
By Val var2 As Type2, _
) As dataType
statement(s)
Return expression
End Function
Example: Form
Example: Code
Private Sub btnDetermine_Click(...) _
Handles btnDetermine.Click
Dim name As String
name = txtFullName.Text
txtFirstName.Text = FirstName(name)
End Sub
Function FirstName(By Val name As String) As String
Dim firstSpace As Integer
firstSpace = name.IndexOf(" ")
Return name.Substring(0, firstSpace)
End Function
28 / 50
Example2: Form
Example: Code
Private Sub btnCalculate_Click(...) _
Handles btnCalculate.Click
Dim a, b As Double
a = CDbl(txtSideOne.Text)
b = CDbl(txtSideTwo.Text)
txtHyp.Text = CStr(Hypotenuse(a, b))
End Sub
Function Hypotenuse(By Val a As Double, _
By Val b As Double) As Double
Return Math.Sqrt(a ^ 2 + b ^ 2)
End Function
29 / 50
Week 9: Chapter 6: Repetition (Loops)
6.1 Do Loops
6.2 Processing Lists of Data with Do
Loops
6.3 For...Next Loops
Lesson 1:
6.1 Do Loops
6.2 Processing Lists of Data with Do
Loops
Learning outcomes:
- Be able to know how to Write repetition statements using do loops
Do Loop Syntax:
Do While condition
statement(s)
Loop
Pseudocode and Flow Chart for a Do Loop
Example 1:
Private Sub btnDisplay_Click(...) _
Handles btnDisplay.Click
'Display the numbers from 1 to 7
Dim num As Integer = 1
30 / 50
Do While num <= 7
lstNumbers.Items.Add(num)
num += 1 'Add 1 to the value of num
Loop
MsgBox(num)
End Sub
Example: Form
Example: Code
Private Sub btnCalculate_Click(...) Handles
btnCalculate.Click
Dim balance As Double, numYears As Integer
balance = CDbl(txtAmount.Text)
Do While balance < 1000000
balance += 0.06 * balance
numYears += 1
Loop
txtWhen.Text = "In " & numYears & _
" years you will have a million dollars."
End Sub
Example: Output
31 / 50
Lesson 2: 6.2 Processing Lists of Data with Do Loops
Peek Method
Counters and Accumulators
Peek Method
Example 1: Display the Total Contents of a File (see page 266)
Dim sr As IO.StreamReader = _
IO.File.OpenText("PHONE.TXT")
lstNumbers.Items.Clear()
Do While sr.Peek <> -1
name = sr.ReadLine
phoneNum = sr.ReadLine
lstNumbers.Items.Add(name & " " _
& phoneNum)
Loop
sr.Close()
Example 2: Form
Example 2: Partial Code
Dim name, phoneNum As string
Dim sr As IO.StreamReader= _
IO.File.OpenText(PHONE.TXT)
Do While (name <> txtName.Text) And (sr.Peek <> -1)
name = sr.ReadLine
phoneNum = sr.ReadLine
Loop
If (name = txtName.text) Then
txtNumber.Text = name & & phoneNum
Else
txtNumber.Text = name not found.
End If
sr.Close()
32 / 50
Counters and Accumulators
File COINS.TXT
Example 3: Partial Code
Dim numCoins As Integer = 0
Dim sum As Integer = 0
Dim coin As String
Do While sr.Peek <> -1
coin = sr.ReadLine
numCoins += 1
sum += CDbl(coin)
Loop
Lesson 3: 6.3 ForNext Loops
ForNext Loop Syntax
Example 1: Code
Dim pop as Double = 300000
Dim fmtStr As String = "{0,4}{1,12:N0}"
For yr As Integer = 2008 To 2012
lstPop.Items.Add(String.Format( fmtStr, yr, pop)
pop += 0.03 * pop
Next
33 / 50
Example 1: Output
Example2:
For i As Integer = 1 To 2
For j As Integer = 1 To 3
lstBox.Items.Add(i & * " & j & = " & i * j)
Next
Next
34 / 50
Week10: Chapter 10: Database Management and SQL
10.1 An Introduction to Databases
10.2 Relational Databases and SQL
Lesson 1:
10.1 An Introduction to Databases
Learning outcomes:
Develop database applications to access a database with a data table
Sample Table Cities Table
Sample Table Countries Table
35 / 50
Add Connection Dialog Box
Database Explorer Window after Connection to MEGACITIES.MDB
36 / 50
Connecting with a DataTable
Dim dt As New DataTable()
Dim connStr As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=MEGACITIES.MDB"
Dim sqlStr As String = "SELECT * FROM Cities"
Dim dataAdapter As New _
OleDb.OleDbDataAdapter(sqlStr, connStr)
dataAdapter.Fill(dt)
dataAdapter.Dispose()
Example 1: Form
Example 1: Partial Code
Dim dt As New DataTable()
Dim rowIndex As Integer = 0
Private Sub frmCities_Load(...) Handles MyBase.Load
Dim dt As New DataTable()
Dim connStr As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=MEGACITIES.MDB"
Dim sqlStr As String = "SELECT * FROM Cities"
Dim dataAdapter As New _
OleDb.OleDbDataAdapter(sqlStr, connStr)
dataAdapter.Fill(dt)
dataAdapter.Dispose()
UpdateTextBoxes()
End Sub
Sub UpdateTextBoxes()
'Display contents of row specified by rowIndex variable
txtCity.Text = CStr(dt.Rows(rowIndex)("city"))
txtCountry.Text = CStr(dt.Rows(rowIndex)("country"))
37 / 50
txtPop2005.Text = CStr(dt.Rows(rowIndex)("pop2005"))
txtPop2015.Text = CStr(dt.Rows(rowIndex)("pop2015"))
End Sub
Private Sub btnNext_Click(...) Handles btnNext.Click
'Show the next record if current one is not the last
If (rowIndex < dt.Rows.Count - 1) Then
rowIndex += 1 'Increase rowIndex by 1
UpdateTextBoxes()
End If
End Sub
Private Sub btnPrevious_Click(...) Handles btnPrevious.Click
'Show previous record if current one is not the first
If (rowIndex > 0) Then
rowIndex = rowIndex - 1
UpdateTextBoxes()
End If
End Sub
Private Sub btnFind_Click(...) Handles btnFind.Click
Dim cityName As String
Dim cityFound As Boolean = False
cityName=InputBox("Enter name of city to search for.")
For i As Integer = 0 To (dt.Rows.Count - 1)
If CStr(dt.Rows(i)("city")) = cityName Then
cityFound = True
rowIndex = i
UpdateTextBoxes()
End If
Next
If (Not cityFound) Then
MessageBox.Show("Cannot find requested city")
End If
End Sub
38 / 50
Example 1: Output
Example 2: Form
Example 2: Code
Private Sub btnShow_Click(...) Handles btnShow.Click
Dim fmtStr As String="{0,-15}{1,-10}{2,7:N1}{3,7:N1}{4,7:P0}"
Dim percentIncrease As Double
(Six statements of boilerplate)
lstDisplay.Items.Add(String.Format(fmtStr, "CITY", _
"COUNTRY", "2005", "2015", "INCR."))
For i As Integer = 0 To dt.Rows.Count - 1
percentIncrease = (CDbl(dt.Rows(i)("pop2015")) - _
CDbl(dt.Rows(i)("pop2005"))) / CDbl(dt.Rows(i)("pop2005"))
lstDisplay.Items.Add(String.Format(fmtStr, dt.Rows(i)(0), _
dt.Rows(i)(1),dt.Rows(i)(2),dt.Rows(i)(3),percentIncrease))
Next
End Sub
39 / 50
Example 2: Output
40 / 50
Week 11: Chapter 10: Database Management (Binding a
listbox to a datatable)
Learning outcomes:
Develop database applications to access a database with a listbox
Lesson 1: binding a listbox to a datatable
The following statement binds a list box to a data table:
lstBox.DataSource = dt
The contents of a specified field can be displayed in the list box by:
lstBox.DisplayMember = "country"
Example: Form
Display the list of countries. When the user clicks on a country, its monetary unit should
be displayed.
Example : Code
Dim dt As New DataTable()
Private Sub frmCountries_Load(...) Handles MyBase.Load
(Last five statements of boilerplate)
lstCountries.DataSource = dt 'Bind list box
lstCountries.DisplayMember = "country"
End Sub
Private Sub lstCountries_SelectedIndexChanged(...) _
Handles lstCountries.SelectedIndexChanged
txtMonetaryUnit.Text = _
CStr(dt.Rows(lstCountries.SelectedIndex)("monetaryUnit")
End Sub
41 / 50
Example: Output
42 / 50
Week 12: Chapter 10: Database Management (Setting
Criteria/ Tables Join)
- Relational database and SQL
Learning outcomes: be able to know how to:
- Show just the records that meet certain criteria
- Join between two tables
Lesson 1: Four SQL Requests
1- Show the records of a table in a specified order
SELECT * FROM Table1 ORDER BY field1 ASC
or
SELECT * FROM Table1 ORDER BY field1 DESC
2- Show just the records that meet certain criteria
SELECT * FROM Table1 WHERE criteria
3- Make available just some of the fields
SELECT field1, field2, . . ., fieldN FROM Table1 WHERE criteria
4- Join the tables together
SELECT * FROM Table1 INNER JOIN Table2 ON foreign field = primary field
WHERE criteria
Examples :
1- SELECT * FROM Countries WHERE country LIKE 'I%' ORDER BY pop2005
ASC
2- Show the records from Cities in alphabetical order based on the name of the city
with
SELECT * FROM Cities ORDER BY city ASC
43 / 50
3- Show the records from Cities in alphabetical order based first on the name of the
country and, within each country group, the name of the city with
SELECT * FROM Cities ORDER BY country, city ASC
4- Show the records from Cities in descending order based on the projected 2015
population, using
SELECT * FROM Cities ORDER BY pop2015 DESC
5- Show the records for the Cities in India with
SELECT * FROM Cities WHERE country = 'India'
6- Show the records from Cities whose 2015 population is projected to be at least 20
million, as in
SELECT * FROM Cities WHERE pop2015 >= 20
7- Show the records from Cities whose name begins with the letter "D", with
SELECT * FROM Cities WHERE city Like 'D%'
8- Show the records from the joined table in descending order of the populations of
their countries, using
SELECT * FROM Cities INNER JOIN Countries ON Cities.country =
Countries.country ORDER BY Countries.pop2005 DESC
9- Show the records from the joined table whose monetary unit has "u" as its second
letter with
SELECT * FROM Cities INNER JOIN Countries ON Cities.country =
Countries.country WHERE monetaryUnit Like '_u%'
44 / 50
10- Make available just the city and country fields of the table Cities, using
SELECT city, country FROM Cities
11- Make available just the city and monetaryUnit fields of the joined table, as in
SELECT city, monetaryUnit FROM Cities INNER JOIN Countries ON Cities.country =
Countries.country
45 / 50
Week 13: Chapter 10: Database Management (Data Grid
View/ Changing contents)
- The DataGridView control
- Changing the content of a database
Learning outcomes:
- Develop database applications to access a database with a data table, datagridview
, and change the contents of a database
Lesson 1: The DataGridView control
Example1: Form
Example1: Code
Private Sub frmCities_Load(...) Handles MyBase.Load
UpdateGrid( "Select * From Cities" )
End Sub
Private Sub btnOrderbyPop_Click(...) Handles btnOrderbyPop.Click
UpdateGrid ( "Select * From Cities Order By pop2005 ASC" )
End Sub
Private Sub btnShowMonUnit_Click(...) _
Handles btnShowMonUnit.Click
UpdateGrid( "SELECT city, Cities.country, " & _
"Cities.pop2005, monetaryUnit " & _
"FROM Cities INNER JOIN Countries " & _
"ON Cities.country=Countries.country " & _
"ORDER BY city ASC" )
End Sub
46 / 50
Sub UpdateGrid( By Val sqlStr As String )
Dim dt As New DataTable()
Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = MEGACITIES.MDB"
Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)
dataAdapter.Fill(dt)
dataAdapter.Dispose()
dgvDisplay.DataSource = dt
End Sub
Example1: Output
Example 2: Form
Example 2: Code
Private Sub btnFindCities_Click(...) _
Handles btnFindCities.Click
UpdateGrid( "SELECT city FROM Cities WHERE" & _
"country = '" & txtCountry.Text & _
"' ORDER BY city ASC")
47 / 50
End Sub
Sub UpdateGrid( By Val sqlStr As String)
(Boilerplate, except for Dim sqlStr statement)
If dt.Rows.Count = 0 Then
MessageBox.Show( "No cities from that country "& "in the database" )
Else
dgvDisplay.DataSource = dt
End If
End Sub
Example 2: Output
Lesson 2: Changing the Contents of a Database
48 / 50
Example 3: Form
Example 3: Partial Code
Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=MEGACITIES.MDB"
Dim sqlStr As String = "SELECT * FROM Cities"
Dim dt As New DataTable()
Private Sub btnLoad_Click(...) Handles btnLoad.Click
dt.Clear()
Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)
dataAdapter.Fill(dt)
dataAdapter.Dispose()
dgvDisplay.DataSource = dt
End Sub
Private Sub btnSave_Click(...) Handles btnSave.Click
Dim changes As Integer
Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)
Dim commandBuilder As New _
OleDb.OleDbCommandBuilder(dataAdapter)
changes = dataAdapter.Update(dt)
dataAdapter.Dispose()
If changes > 0 Then
MessageBox.Show(changes & " changed rows.")
Else
MessageBox.Show ("No changes made.")
End If
End Sub
49 / 50
Week 14 & 15: Chapter 10: SQL (insert, delete and update
sql statement)
Learning outcomes:
- Write a program using insert sql statement
- Develop applications with multiple forms
- Design, create, test and debug fully functioning programs using the new Visual
Basic.Net language
Lesson 1: insert, delete sql statement
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)
Delete from table name where condition
Example: form
Example: code
Public Class Form1
Dim conn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Application.StartupPath & "\MEGACITIES.MDB"
Private Sub Form1_Load(By Val sender As System.Object, By Val e As
System.EventArgs) Handles MyBase.Load
updateDataGrid()
End Sub
Private Sub btnInsert_Click(By Val sender As System.Object, By Val e As
System.EventArgs) Handles btnInsert.Click
50 / 50
Dim dt As New DataTable
Dim sql As String = "Insert into cities values ('" & txtCity.Text & "','" &
txtCountry.Text & "','" & txtPop2005.Text & "','" & txtPop2015.Text & "')"
Dim adapter As New OleDb.OleDbDataAdapter(sql, conn)
adapter.Fill(dt)
updateDataGrid()
End Sub
Sub updateDataGrid()
Dim dt As New DataTable
'Dim conn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Application.StartupPath & "\MEGACITIES.MDB"
Dim sql As String = "select * from cities"
Dim adapter As New OleDb.OleDbDataAdapter(sql, conn)
adapter.Fill(dt)
DataGridView1.DataSource = dt
End Sub
Private Sub Button2_Click(By Val sender As System.Object, By Val e As
System.EventArgs) Handles Button2.Click
Dim dt As New DataTable
Dim sql As String = "delete from cities where city='" & txtCity.Text & "'"
Dim adapter As New OleDb.OleDbDataAdapter(sql, conn)
adapter.Fill(dt)
updateDataGrid()
End Sub
Private Sub DataGridView1_CellContentClick(By Val sender As System.Object, By
Val e As System.Windows.Forms.DataGridViewCellEventArgs) Handles
DataGridView1.CellContentClick
'txtCity.Text = DataGridView1.CurrentRow.Cells(0).Value.ToString()
'txtCountry.Text = DataGridView1.CurrentRow.Cells(1).Value.ToString()
'txtPop2005.Text = DataGridView1.CurrentRow.Cells(2).Value.ToString()
'txtPop2015.Text = DataGridView1.CurrentRow.Cells(3).Value.ToString()
End Sub
Private Sub DataGridView1_RowHeaderMouseDoubleClick(By Val sender As Object,
By Val e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles
DataGridView1.RowHeaderMouseClick
txtCity.Text = DataGridView1.CurrentRow.Cells(0).Value.ToString()
txtCountry.Text = DataGridView1.CurrentRow.Cells(1).Value.ToString()
txtPop2005.Text = DataGridView1.CurrentRow.Cells(2).Value.ToString()
txtPop2015.Text = DataGridView1.CurrentRow.Cells(3).Value.ToString()
End Sub
