CIS159 - VB

1
CIS159 - VB

• Chapter 10.2
• Updating Databases
• ch10_2.ppt

2
Overview of Topics

• Connecting to a Database - Reviewed
• Connection
• Data Adapter
• DataSet
• Updating Data Overview

3
Database Terminology

• A database is made up of one or more related
  tables.
• Conceptually a table is made up of rows and
  columns.
• Each row represents the data for one
  record(person, inventory item, course
  information).
• Each column (field) is a data element (name,
  address, city, state, zip).
• SQL Structured Query Language Basic set of
  commands that are common in all DBMS.
• SQL commands operate on record sets.Delete From
  customers where zip 85202All rows matching
  criteria would be deleted.

4
Accessing a Database ADO.Net

• Use ActiveX Data Objects (ADO)
• For an Access database use
• OleDbConnection connection tool establishes a
  link to a data source.
• OleDbDataAdapter data adapter handles
  retrieving and updating the data and creates a
  Dataset.
• Dataset bind columns in the Dataset to controls
  (textbox, listbox) by setting the DataBinding
  property.
• Use the Fill method of the data adapter to load
  the data into the dataset (daVBMail.Fill(dsCustome
  r)). The Fill method is usually placed in the
  form load event.

5
Setup Process

• Create a new project (VB14).
• Copy and paste the interface provided on the
  website into the new form file.
• Copy the database file (VBMail.mdb) into the bin
  folder of the project.
• Add an OleDbDataAdapter control.
• Use Configuration Wizard to
• Create and configure the Data Adapter.
• Create and configure the Data Connection.

6
Generate Data Set

• After the Wizard has finished, the Data Adapter
  and Connection will have been created.
• Under the Properties of the Data Adapter, youll
  see three options
• Configure Data Adapter starts wizard again
• Generate Data Set creates data set to use in
  program
• Preview Data displays data stored in table
• Click on Generate Data Set.

7
Bind the Data to Controls

• There should be three items in the component
  tray.
• OleDbDataAdapter daVBMail
• OleDbConnection - dcVBMail
• Dataset - dsCustomer
• Use the data in the Data Set to populate labels
  or text boxes with data from the table.
• This process is referred to Binding the data.

8
Connection String

• The data adapter wizard adds many parameters to
  the connection string property of the Connection.
• When the database file is selected in the wizard,
  the directory path is included with the file
  name. For example (E\VB14\bin\VBMail.mbd).
• If you use a different computer to run the
  program later, or rename a folder, or move the
  project, the connection will fail because the
  drive letter or directory may be different.
• We can change the connection string at runtime by
  adding the following command (all on one line) in
  the form load event proceduredcVBMail.Connectio
  nString ProviderMicrosoft.Jet.OLEDB.4.0
  Data SourceVBMail.mdb
• Make sure to place the database file in the bin
  folder.

9
Updating a Dataset

• To allow users to update the data, the data must
  be binded to text boxes rather than labels.
• Remember that a dataset is a temporary set of
  data in memory, disconnected from the original
  data source.
• After a user make changes to the data, the Update
  method of the data adapter must be executed.
• Update should be executed after each update, or
  it may be executed once right before the program
  terminates.

10
VB14 - Updating Database
11
Dirty Data

• If the update is going to be deferred until the
  user exists the application, then a variable
  (flag) to help us record that data in the dataset
  has been changed is need.
• The textbook uses this technique.
• If the data has been changed, some consider the
  data to be dirty.
• I consider data to be dirty when it has not been
  validated, but well go along with the textbook.
• Create a module level variable to track
  changes.Dim mblnIsDirty As Boolean
• Set the variable to True in each procedure that
  allows changes (Add, Edit, Delete).

12
Update As Needed

• Instead of waiting until the end to save changes,
  update the database after each transaction that
  changes the data.
• Users could loose a lot of changes if the
  application waits until the end to save the
  changes.
• The Update command tries to actually write the
  data back to the database, and this attempt could
  fail.
• The problem could be in one row, but all of the
  changes would be lost.
• The update could fail for various reasons, but
  one is that there could be additional constraints
  defined in the database for the columns.
• The database for VB14 (vbmail.mdb) actually does
  not allow blank information to be loaded. If the
  user leaves the name blank during an Add, the
  update will fail. When testing your program, be
  sure to enter a value for all fields.

13
Editing Records Logic

• When the form is loaded, lock the text boxes by
  setting their ReadOnly property to True.
• Create a sub procedure to lock and one to unlock
  the textboxes.
• When the user clicks the Edit button unlock the
  text boxes and disable navigation and other
  buttons that are not valid during an Edit.
• When the user clicks on Save lock the textboxes,
  enable the require buttons, and Update the
  database or set mblnIsDirty to True.
• If the user clicks on Cancel, use the data in the
  dataset to restore the values in text boxes that
  may have been changed.
• See page 435 441 (reject changes) for
  additional details.

14
Save Changes

• When editing, the data binding transfers the data
  in the textboxes to the data adapter.
• However, the data is not transferred until the
  user navigates to the next row. This may be an
  Access and data binding issue.
• If the user clicks on Save, force a navigation so
  that the changes are recognized, and then issue
  the Update command.

15
Reject Changes Column Names

• If the user clicks on Cancel during an Edit, we
  want to reject the changes.
• The commands to reject changes require us to
  reference the actual column names as defined in
  the database.txtAuthor.Text curRow(Author,
  _ DataRowVersion.Original).ToString
• Use the data properties of the data set
  (dsCustomer) or the data schema (dsCustomer.xsd)
  to see the table and column names that should be
  used in the VB code.

16
Deleting Records Logic

• When the Delete button is clicked, get the
  current record number.intRecNum
  Me.BindingContext(dsCustomer, Customer).Position
  
• Execute the Delete method.dsCustomer.Customer.Ro
  ws(intRecNum).Delete( )
• Update the database or set the IsDirty flag to
  True.mblnIsDirty True
• Display the current record position.

17
Adding Records Logic

• When the user clicks on Add
• Unlock the text boxes
• Clear the text boxes
• Wait for input.
• When the user clicks on Save
• Create a new row.
• Assign the values in the text boxes to the
  columns.
• Add the new row to the dataset.
• Update the database or set the IsDirty flag.
• If the user clicks on Cancel
• Use the reject changes logic to restore the
  values in the text boxes with the data from the
  dataset.

18
Table and Column Names in VB Code

• When a record is added, the table and column name
  is referenced in the declaration and usage of
  newRow.Dim newRow As DataRow
  dsCustomer.Customer.NewRownewRow(LastName)
  txtLast.Text
• The name of the table is used to declare a new
  row.
• The name of the column is used with the newRow
  object.
• The names of the table and columns must match the
  names in the database.
• Use the data properties of the data set
  (dsCustomer) or the data schema (dsCustomer.xsd)
  to see the table and column names that should be
  used in the VB code.
• For VB14, pay close attention to LastName,
  FirstName, and ZipCode no spaces between the
  words.

19
Table and Columns Names - continued

• The table name is referenced in many places.
• The column names are referenced in the Add and
  Reject Changes procedures.
• Usually the database design must be completed
  before applications are developed, or else it
  will create a lot of rework.
• In Visual Studio, double-click on the data schema
  (dsCustomer.xsd) to see the table and column
  names that should be used in the VB code.

20
Data Schema dsCustomer.xsd
21
Summary

• Connecting to a Database Reviewed
• Updating Data Overview
• Next in Class
• Complete VB14 Updating Databases