Virtual Table and Data Grid View Virtual Table

Virtual Table and Data. Grid. View • Virtual Table • Data. Grid. View Ch. 10 1

Virtual Tables • SQL statements create a new “virtual” table from existing tables. • Think of the following statement as creating a "virtual table" SELECT city, pop 2015 FROM Cities WHERE pop 2015>=20 city pop 2015 Bombay 22. 6 • Results in: Delhi Mexico City Sao Paulo Tokyo Ch. 10 20. 9 20. 6 20. 0 36. 2 2

Another Virtual Table SELECT * FROM Countries WHERE country Like 'I%' ORDER BY pop 2005 ASC • Results in: country Indonesia India pop 2005 222. 8 1103. 4 Ch. 10 monetary. Unit rupiah rupee 3

Views • “Virtual” tables don’t exist physically. • For all practical purposes, Visual Basic acts as if they did. • You may also see a “virtual” table called a view. Ch. 10 4

The Data. Grid. View Control • The Data. Grid. View displays the values for an entire view in a table format similar to the table displayed by Database Explorer. • The prefix for the name of a Data. Grid. View control is dgv. • After a data table has been filled, the statement dgv. Display. Data. Source = dt displays the contents of the data table dt in the data grid. Ch. 10 5

Lab sheet 10. 5: Form dgv. Display Ch. 10 6

Lab sheet 10. 5: Code Private Sub frm. Cities_Load(. . . ) Handles My. Base. Load Update. Grid("Select * From Cities") End Sub Private Sub btn. Orderby. Pop_Click(. . . ) Handles btn. Orderby. Pop. Click Update. Grid("Select * From Cities Order By pop 2005 ASC") End Sub Private Sub btn. Show. Mon. Unit_Click(. . . ) _ Handles btn. Show. Mon. Unit. Click Update. Grid("SELECT city, Cities. country, " & _ "Cities. pop 1995, monetary. Unit " & _ "FROM Cities INNER JOIN Countries " & _ "ON Cities. country=Countries. country " & _ "ORDER BY city ASC") End Sub Ch. 10 7

Example Lab sheet 10. 5: Code continued Sub Update. Grid(By. Val sql. Str As String) Dim dt As New Data. Table() Dim conn. Str As String ="Provider=Microsoft. Jet. OLEDB. 4. 0; " & _ "Data Source = MEGACITIES. MDB" Dim data. Adapter As New Ole. Db. Data. Adapter(sql. Str, conn. Str) data. Adapter. Fill(dt) data. Adapter. Dispose() dgv. Display. Data. Source = dt End Sub Ch. 10 8

Lab sheet 10. 5: Output Click on the “Show Monetary Unit” button. Ch. 10 9

Lab sheet 10. 6: Form txt. Country dgv. Display Ch. 10 10

Lab sheet 10. 6: Code Private Sub btn. Find. Cities_Click(. . . ) _ Handles btn. Find. Cities. Click Update. Grid("SELECT city FROM Cities WHERE" & _ "country = '" & txt. Country. Text & _ "' ORDER BY city ASC") End Sub Update. Grid(By. Val sql. Str As String) (Boilerplate, except for Dim sql. Str statement) If dt. Rows. Count = 0 Then Msg. Box("No cities from that country " & _ "in the database") Else dgv. Display. Data. Source = dt End If End Sub Ch. 10 11

Lab sheet 10. 6: Output Ch. 10 12