Chapter 10

[Page 714 (continued)]

Exercises 10.1

1.	Combined population of the 10 most populous cities in 2015: 209.2 million
3.	China India Japan
5.	Private Sub btnDisplay_Click(...) Handles btnDisplay.Click 'Display the country names in the order they are stored. 'Open a connection to the database. Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=MEGACITIES.MDB" Dim sqlStr As String = "SELECT * FROM Countries" Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr) [Page 715] 'Fill the data table with data from the database. Dim dt As New DataTable() dataAdapter.Fill(dt) dataAdapter.Dispose() 'Display the country names (field/column 0) in the list box. For i As Integer = 0 To dt.Rows.Count - 1 lstOutput.Items.Add(dt.Rows(i)(0)) Next End Sub
7.	Private Sub btnDisplay_Click(...) Handles btnDisplay.Click 'Displays the cities whose populations in 2015 will exceed 20 mil 'Open a connection to the database. 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) 'Fill the data table with data from the database. Dim dt As New DataTable() dataAdapter.Fill(dt) dataAdapter.Dispose() 'Display the city name (column 0) where the 2015 pop exceeds 20. For i As Integer = 0 To dt.Rows.Count - 1 If CDbl(dt.Rows(i)("pop2015")) > 20 Then lstOutput.Items.Add(dt.Rows(i)(0)) End If Next End Sub
9.	Private Sub btnDisplay_Click(...) Handles btnDisplay.Click 'Displays countries and monetary units 'Open a connection to the database. Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=MEGACITIES.MDB" Dim sqlStr As String = "SELECT * FROM Countries" Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr) 'Fill the data table with data from the database. Dim dt As New DataTable() dataAdapter.Fill(dt) dataAdapter.Dispose() 'Display the country (column 0) and its monetary unit (column 2). For i As Integer = 0 To dt.Rows.Count - 1 lstOutput.Items.Add(CStr(dt.Rows(i)(0)) & " - " & _ CStr(dt.Rows(i)(2))) Next End Sub
11.	Structure Country Dim name As String Dim pop2005 As Double Dim monetaryUnit As String End Structure Private Sub btnDisplay_Click(...) Handles btnDisplay.Click 'Displays countries in descending order of their population 'Open a connection to the database. Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=MEGACITIES.MDB" Dim sqlStr As String = "SELECT * FROM Countries" Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr) 'Fill the data table with data from the database. Dim dt As New DataTable() dataAdapter.Fill(dt) dataAdapter.Dispose() 'Declare an array of structures to hold the data. Dim temp, countries(dt.Rows.Count - 1) As Country 'Load the data into the array. For i As Integer = 0 To dt.Rows.Count - 1 countries(i).name = CStr(dt.Rows(i)(0)) countries(i).pop2005 = CDbl(dt.Rows(i)(1)) countries(i).monetaryUnit = CStr(dt.Rows(i)(2)) Next [Page 716] 'Bubble sort on population. For i As Integer = 1 To dt.Rows.Count - 1 For j As Integer = 0 To dt.Rows.Count - i If countries(j - 1).pop2005 < countries(j).pop2005 Then temp = countries(j - 1) countries(j - 1) = countries(j) countries(j) = temp End If Next Next 'Display the country names. For i As Integer = 0 To dt.Rows.Count - 1 lstOutput.Items.Add(countries(i).name) Next End Sub
13.	Private Sub btnDisplay_Click(...) Handles btnDisplay.Click 'Displays cities and monetary units Dim j As Integer 'Open a connection to the database. 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) 'Fill the data table with data from the database. Dim dt As New DataTable() dataAdapter.Fill(dt) dataAdapter.Dispose() 'Load data from Countries table into second data table. sqlStr = "SELECT * FROM Countries" dataAdapter = New OleDb.OleDbDataAdapter(sqlStr, connStr) Dim dt2 As New DataTable() dataAdapter.Fill(dt2) dataAdapter.Dispose() 'Loop for each city. For i As Integer = 0 To dt.Rows.Count - 1 j = 0 'When the city's country is found, then quit the loop. Do While CStr(dt2.Rows(j)(0)) <> CStr(dt.Rows(i)(1)) j += 1 Loop 'Display the city and monetary unit. lstOutput.Items.Add(CStr(dt.Rows(i)(0)) & " - " & _ CStr(dt2.Rows(j)(2))) Next End Sub
15.	The two sequential files have a combined size of 498 bytes, while the database file has size 196 KB.

17.	Two tables: Publishers and Titles
19.	10
	[Page 717]
21.	Dim dt As New DataTable() 'Data table holds the data. Private Sub frmStates_Load(...) Handles MyBase.Load 'Loads the information into the data table 'Open a connection to the database. Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source = STATE_ABBR.MDB" Dim sqlStr As String = "SELECT * FROM States" Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr) 'Fill the data table with data from the database. dataAdapter.Fill(dt) dataAdapter.Dispose() End Sub Private Sub btnDisplay_Click(...) Handles btnDisplay.Click 'Display the state that matches the abbreviation. 'Convert the abbreviation to uppercase. mtxtAbbr.Text = mtxtAbbr.Text.ToUpper If mtxtAbbr.Text = "" Then 'Display error message. txtOutput.Text = "Enter a state." Else txtOutput.Clear() 'Loop over all states. For i As Integer = 0 To 49 'If the abbreviation matches, then display the name. If mtxtAbbr.Text = CStr(dt.Rows(i)(0)) Then txtOutput.Text = CStr(dt.Rows(i)(1)) End If Next If txtOutput.Text = "" Then 'Display error message. txtOutput.Text = "Not found." End If End If 'Set focus back on abbreviation. txtAbbr.Focus() End Sub

Exercises 10.2

1.	Could cause a problem if the country added is not one of the countries in the Countries table
3.	No problem

5.	(B)
7.	(A)

9.	(D)
11.	(C)

13.	SELECT country, monetaryUnit FROM Countries WHERE pop2005>130 AND pop2005<200 ORDER BY pop2005 ASC
15.	SELECT * FROM Cities WHERE country='India' ORDER BY pop2015 DESC
	[Page 718]
17.	SELECT * FROM Cities WHERE pop2005>18 AND pop2005<19 ORDER BY pop2005 ASC, pop2015 ASC
19.	SELECT city, pop2015, monetaryUnit FROM Cities INNER JOIN Countries ON Countries.country=Cities.country WHERE Countries.country='India' ORDER BY pop2015 DESC

21.

Dim dt As New DataTable() 'Holds the data Private Sub frmCurrency_Load(...) Handles MyBase.Load 'Get all fields from the Rates table sorted by country. Dim connStr As String = "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source = EXCHRATE.MDB" Dim sqlStr As String = "SELECT * FROM Rates ORDER BY Country" Dim dataAdapter As New _ OleDb.OleDbDataAdapter(sqlStr, connStr) 'Fill the data table, and close the data adapter. dataAdapter.Fill(dt) dataAdapter.Dispose() 'Set the list box's data source to the countries field. lstCountries.DataSource = dt lstCountries.DisplayMember = "Country" End Sub Private Sub lstCountries_SelectedIndexChanged(...) _ Handles lstCountries.SelectedIndexChanged 'Display the country's monetary unit and exchange rate. txtCurrency.Text = CStr(dt.Rows(lstCountries.SelectedIndex)(1)) txtRate.Text = FormatNumber(dt.Rows(lstCountries.SelectedIndex)(2)) End Sub

23.

Structure Rate Dim country As String Dim monetaryUnit As String Dim dollarRate As Double End Structure Dim rateTable As New DataTable() Dim rateTable2 As New DataTable() Private Sub frmCurrency_Load(...) Handles MyBase.Load 'Get all fields from the Rates table sorted by country. Dim connStr As String = "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source = EXCHRATE.MDB" Dim sqlStr As String = "SELECT * FROM Rates ORDER BY Country" Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr) 'Fill the from and to table and close the data adapter dataAdapter.Fill(rateTable) dataAdapter.Fill(rateTable2) dataAdapter.Dispose() 'Set the list boxes' data sources to the respective data tables. lstFrom.DataSource = rateTable lstFrom.DisplayMember = "Country" lstTo.DataSource = rateTable2 lstTo.DisplayMember = "Country" End Sub Private Sub btnConvert_Click(...) Handles btnConvert.Click 'Convert the monetary unit, and display the result. Dim result, amount As Double [Page 719] Dim fromRate, toRate As Rate Dim fromIndex As Integer = lstFrom.SelectedIndex Dim toIndex As Integer = lstTo.SelectedIndex 'Display an error message if input is insufficient. If (fromIndex < 0) Or (toIndex < 0) Or _ (txtAmount.Text.Trim = "") Then MsgBox("Both countries and an amount must be provided.", 0, _ "Insufficient Input") Else 'Get each row from the data table, and store into a structure. fromRate = GetRate(fromIndex) toRate = GetRate(toIndex) 'Convert the amount amount = CDbl(txtAmount.Text) result = Convert(fromRate.dollarRate, toRate.dollarRate, amount) 'Display the result formatted with the rates. txtResult.Text = FormatNumber(amount) & " " & _ fromRate.monetaryUnit & "s from " & fromRate.country & _ " equals " & FormatNumber(result) & " " & _ toRate.monetaryUnit & "s from " & toRate.country End If txtAmount.Focus() End Sub Function GetRate(ByVal rowNum As Integer) As Rate 'Store the fields from a row into a structure. Dim rateInfo As Rate rateInfo.Country = CStr(rateTable.rows(rowNum)("Country")) rateInfo.monetaryUnit = CStr(rateTable.Rows(rowNum)("monetaryUnit")) rateInfo.dollarRate = CDbl(rateTable.Rows(rowNum)("dollarRate")) Return rateInfo End Function Function Convert(ByVal fromRate As Double, _ ByVal toRate As Double, _ ByVal amount As Double) As Double 'Convert the amount from the from Currency to the to Currency. Return amount * toRate / fromRate End Function

27.

Private Sub btnDisplay_Click(...) Handles btnDisplay.Click   'Display the data from the data table in the data grid   Dim sqlStr As String   sqlStr = "Select player From Players INNER JOIN Teams ON " & _           "Players.team=Teams.team WHERE location='New York'"   UpdateGrid(sqlStr) End Sub Sub UpdateGrid(ByVal sqlStr As String)   Dim dt As New DataTable()   Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _                         "Data Source=BASEBALL.MDB"   Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)   dataAdapter.Fill(dt)   dataAdapter.Dispose()   dgvDisplay.DataSource = dt End Sub

[Page 720]

35.

Private Sub btnDisplay_Click(...) Handles btnDisplay.Click 'Display a telephone directory. Dim lastName, str As String Dim fmtStr As String = "{0,-30} {1,7}" Dim connStr As String = "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source = PHONEBOOK.MDB" Dim sqlStr As String = "SELECT * FROM Entries ORDER BY " & _ "lastName, firstName, middleInitial ASC" Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr) Dim dt As New DataTable() dataAdapter.Fill(dt) dataAdapter.Dispose() 'Loop over all rows, remembering the current lastname. lastName = "" lstOutput.Items.Add(String.Format(fmtStr, "NAME, ADDRESS", _ "PHONE #")) For i As Integer = 0 To dt.Rows.Count - 1 If lastName <> CStr(dt.Rows(i)(0)) Then 'If the last name has changed, then remember it. lastName = CStr(dt.Rows(i)(0)) 'LASTNAME firstName middleInitial streetNumber street str = lastName.ToUpper & " " & CStr(dt.Rows(i)(1)) & " " & _ CStr(dt.Rows(i)(2)) & " " & _ CStr(dt.Rows(i)(3)) & " " & CStr(dt.Rows(i)(4)) Else 'firstName middleInitial streetNumber street str = " " & CStr(dt.Rows(i)(1)) & " " & _ CStr(dt.Rows(i)(2)) & " " & _ CStr(dt.Rows(i)(3)) & " " & CStr(dt.Rows(i)(4)) End If lstOutput.Items.Add(String.Format(fmtStr, str, _ dt.Rows(i)(5))) Next End Sub