VB'NET and MS Access Databases

1
VB.NET and MS Access Databases

• Accessing MS Access Databases
• Relational Databases and SQL
• Examples From Chapter 10 Schneider Text

2
Database Terminology

• A table is a rectangular array of data.
• Each column of the table, called a field,
  contains the same type of information.
• Each row, called a record, contains all the
  information about one entry in the database.
• In a phone books, there are fields for name,
  address, and phone number. Your entry in the
  phone book is a record.

3
The Cities Table
4
Database Management Software (DBMS)

• Used to create databases
• Databases can contain one or more related tables
• Examples of DBMS include MS Access, MS SQL Server
  and Oracle

5
The Server Explorer

• Allows the programmer to view information located
  on other computers
• Can also be used to view a database

6
Using the Server Explorer

• Place your mouse over the Server Explorer tab
  on the left side of Visual Studio. (may need to
  select View/Server Explorer from the pull-down
  menus)
• Right-click on Data Connections, and select
  Add Connection.
• In the Data Link Properties window that appears,
  click on the Provider tab at the top. Select
  the item Microsoft Jet 4.0 OLE DB Provider from
  the OLE DB Providers List box, and then press the
  Next button.
• Click on the ... button to the right of the
  first text box. This will open up a file browser
  that allows you to locate any file then press
  Open (should use bin Folder).
• Clear the contents of the User name text box.

7
Using the Server Explorer

• Press the Test Connection button. A message box
  stating Test Connection Succeeded should
  appear. Press the OK button on that message box,
  and then press the OK button on the Data Link
  Properties box.
• A new icon should appear in Server Explorer.
  Click on the sign to the left of the icon to
  expand this entry. Three subentries will appear
  Tables, Views, and Stored Procedures.
• Expand the Tables entry to reveal the subentries.
• Expand an entry to reveal the fields of the
  table.
• Double-click on a table to show the table in a
  grid.

8
Server Explorer Window
9
The Cities Table
10
Accessing a Database using VB.NET code

• We will write code to create
• An OleDbDataAdapter object to access data from a
  table in a database
• A DataTable object to hold the data retrieved
  with the OleDbDataAdapter object

11
Accessing a Database with a Data Table

• A DataTable object holds the contents of a table
  as a rectangular array.
• A data table is similar to a two-dimensional
  array it has rows and columns.
• The following declares a DataTable variable
• Dim dt As New DataTable()

12
Connecting with a DataTable

• Dim dt As New DataTable()
• Dim connStr As String _
• "ProviderMicrosoft.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()

13
Properties of the DataTable

• After the six lines of code are executed, the
  number of records in the table is given by
• dt.Rows.Count
• The number of columns in the table is given by
• dt.Columns.Count
• The records are numbered 0 through
• dt.Rows.Count 1
• The fields are numbered 0 through
• dt.Columns.Count 1

14
More Properties

• The name of the jth field is given by
• dt.Columns(j)
• The entry in the jth field of the ith record is
• dt.Rows(i)(j)
• The string containing the entry in the specified
  field of the ith record is
• dt.Rows(i)(fieldName)

15
Bound Controls

• A data table that is bound to a list box can
  transfer information automatically into the list
  box.
• 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
• Note that database object names in a string may
  be case-sensitive

16
Examples

• Example 10-1-1
• Example 10-1-2
• Example 10-1-3
• See o\adams_e\cs206\examples

17
Relational Databases and SQL

• SQL
• SQL Requests
• The DataGrid Control
• Primary and Foreign Keys

18
SQL

• Structured Query Language developed for use with
  relational databases
• Very powerful language
• Allows for the request of specified information
  from a database
• Allows displaying information from database in a
  specific format

19
Sample SQL Requests

• Show all rows in a table
• SELECT FROM TableName
• Examples
• Select from Cities
• Select from Publishers
• Select from Authors

20
Four Sample SQL Requests

• Show the rows of a table in a specified order
• SELECT FROM Table1 ORDER BY field1 ASC
• or
• SELECT FROM Table1 ORDER BY field1 DESC
• Examples
• Select from Cities Order by City ASC
• Select from Cities Order by Country, City ASC
• Select from Cities Order by pop2015 ASC

21
Make available just some of the fields

• SELECT field1, field2, . . ., fieldN FROM
• Table1 WHERE criteria
• Example
• SELECT city, country FROM Cities

22
Show just the rows that meet certain criteria
means "all the fields"
Specified Criteria

• SELECT FROM Table1 WHERE criteria

Name of the Table where the Records may be found
23
Criteria (Where) Clause

• A string containing a Boolean condition of the
  type used with If blocks.
• Uses the standard operators lt, gt, and
• Also can use the operator Like.
• Like uses the wildcard characters _ and to
  compare a string to a pattern.
• Can also use And, Or, Not for more complex
  Boolean conditions

24
Examples

• Select from Cities where Country China
• Select from Cities where pop2015 gt 20
• Select from Cities where City Like S
• (City starts with S)

25
Like Examples

• An underscore character stands for a single
  character in the same position as the underscore
  character.
• The pattern B_d is matched by Bid, Bud, and
  Bad.
• A percent sign stands for any number of
  characters in the same position as the asterisk.
• The pattern Cr is matched by Computer,
  Chair, and Car.

26
The DataGrid Control

• A DataGrid displays the values for an entire view
  in a table format identical to the table
  displayed by Server Explorer.
• The prefix for the name of a DataGrid control is
  dg.
• After a data table has been filled, the statement
• dgDisplay.DataSource dt
• displays the contents of the data table dt in the
  data grid.

27
Example 10.2.1

• Private Sub Form1_Load(...) Handles MyBase.Load
• UpdateDataGrid("Select From Cities")
• End Sub
• Private Sub btnOrderbyPop_Click(...) Handles
  btnOrderbyPop.Click
• UpdateDataGrid("Select From Cities Order By
  pop1995 ASC")
• End Sub
• Private Sub btnShowCurrency_Click(...)
• Handles btnShowCurrency.Click
• UpdateDataGrid("SELECT city, Cities.country, "
  _
• "Cities.pop1995, currency " _
• "FROM Cities INNER JOIN Countries "
  _
• "ON Cities.countryCountries.country
  " _
• "ORDER BY city ASC")
• End Sub

28
Example 1 continued

• Sub UpdateDataGrid(ByVal sqlStr As String)
• Dim dt As New DataTable()
• Dim connStr As String "ProviderMicrosoft.Jet.O
  LEDB.4.0" _
• "Data Source
  MEGACITIES.MDB"
• Dim dataAdapter As New OleDb.OleDbDataAdapter(sq
  lStr, connStr)
• dataAdapter.Fill(dt)
• dataAdapter.Dispose()
• dgDisplay.DataSource dt
• End Sub

29
Example 10.2.2

• Private Sub btnFindCities_Click(...)
• Handles btnFindCities.Click
• This example builds a select query using data
  from
• a text box
• UpdateDataGrid("SELECT city FROM Cities WHERE"
  _
• "country '" txtCountry.Text _
• "' ORDER BY city ASC")
• End Sub
• Sub UpdateDataGrid(ByVal sqlStr As String)
• 'Declare and populate the data table.
• Dim dt As New DataTable()
• Dim connStr As String "Provider " _
• "Microsoft.Jet.OLEDB.4.0Data Source "
  _
• "MEGACITIES.MDB"

30
Example 10.2.2

• Dim dataAdapter As New _
• OleDb.OleDbDataAdapter(sqlStr, connStr)
• dataAdapter.Fill(dt)
• dataAdapter.Dispose()
• 'Display the names of the cities in the
  specified 'country.
• If dt.Rows.Count 0 Then
• MsgBox("No cities from that country " _
• "in the database")
• Else
• dgDisplay.DataSource dt
• End If
• End Sub

31
Example 10.2.2 Output
32
Changing the Contents of a Database

• Data grids can also be used to add, modify, and
  delete records from a database.
• After a DataAdapter has been created, the
  statement
• Dim commandBuilder As New OleDb.OleDbCommandBui
  lder(dataAdapter)
• will automatically generate the commands used
  for the Insert, Update, and Delete operations.

33
Using the DataAdapter to Change a Database

• If changes is an Integer variable, then the
  statement
• changes dataAdapter.Update(dt)
• will store all of the insertions, updates, and
  deletions made in the data table to the database
  and assign the number of records changed to the
  variable changes.

34
Examples

• Example 10-2-1
• Example 10-2-2
• Example 10-2-3
• See o\adams_e\cs206\examples

35
Primary Keys

• A primary key is used to uniquely identify each
  record.
• Databases of student enrollments in a college
  usually use a field of Social Security numbers as
  the primary key.

36
Primary Key Fields

• When a database is then created, a field can be
  specified as a primary key.
• The DBMS will insist that every record have an
  entry in the primary-key field and that the same
  entry does not appear in two different records.
• If the user tries to enter a record with no data
  in the primary key, the error message Index or
  primary key cant contain a null record. will be
  generated.
• If the user tries to enter a record with the same
  primary key data as another record, the error
  message The changes you requested to the table
  were not successful"

37
Two or More Tables

• When a database contains two or more tables, the
  tables are usually related.
• For instance, in the MegaCities database, the two
  tables Cities and Countries are related by their
  country field. (Open Megacities2002 Demo)
• Notice that every entry in Cities.country appears
  uniquely in Countries.country and
  Countries.country is a primary key.
• We say that Cities.country is a foreign key of
  Countries.country.

38
Foreign Keys

• Foreign keys can be specified when a table is
  first created. VB.NET will insist on the Rule of
  Referential Integrity.
• This Rule says that each value in the foreign key
  must also appear in the primary key of the other
  table.

39
Join

• A foreign key allows VB.NET to link (or join)
  together two tables from a relational database
• When the two tables Cities and Countries from
  MEGACITIES.MDB are joined based on the foreign
  key Cities.country, the result is Table 10.4.
• The record for each city is expanded to show its
  countrys population and its currency.

40
Join the tables together

• SQL commands must specify table names and primary
  and foreign key fields
• SELECT
• FROM Table1, Table2
• WHERE Table1.PrimaryKey Table2.ForeignKay
• Examples
• SELECT FROM Cities, Country
• WHERE Cities.Country Countries.Country

41
More on SQL statements

• The single quote, rather than the normal double
  quote, is used to surround strings.
• Fields may be specified with the table they come
  from by tableName.FieldName

42
(No Transcript)