DataviewVIEWGlobal variables

1
Dataview/VIEW/Global variables

• Objective
• Use of View Access view
• Dataview Binding context, updates
• Databinding Problems with Listbox and combobox
• Use of module to make datasets GLOBAL
• Setting location of form or control,TabControl
• Quiz Coverage

2
Use of View (ACCESS)

• Query (or View) in Access can be used to
  generated tables.This tables can be used in VB
  just like a real table.
• Create a Query in Access with the correct SQL
  expression.
• Drag the VIEW-table onto the form

3
To create query in ACCESS

SELECT Category.CategoryCode, sum
(Course.Capacity) AS subtotal FROM Category,
Course GROUP BY Category.CategoryCode
4
VIEW

• You can use the view-table just like any other
  table. Dragging it onto the form and creating a
  dataadapter and dataset.
• However, you cannot update, insert or delete rows
  in VIEW. In our example, finding the sum of
  capacity for each category

5
DataView(revision)

• DataSet ? RowFilter ? DataView
• (ProductTypeTOY )

DataSet
Dataview
6
DataView BindingContext
DataGrid
Dataview Output

• To get the current row (ix) of the
  datagrid/dataview binding, we use
• ix Me.bindingContext(dataView1).position
• To get the Price belonging the current row
• Dataview1.item(ix)(Price)

Bind
7
Modify Dataset Through Dataview

• Rows in Dataview in fact point to the real
  dataset.
• Updating rows in
• the dataview is
• same as updating
• the real dataset.

Dataview
8
Updating Dataset

• In fact, the rows returned by FindbyPK and
  .SelectMethod also points to rows in the real
  dataset. Therefore, updating those rows is the
  same as updating the real Dataset.
• In our example, to change ( update) the price of
  p03 in the real dataset, we have
• dataview1.item(2)(Price) 1000000000.
• dbProd.update(dsProd1)

9
GLOBAL Datasets

• Declaring a public dataset in module makes it
  accessible in all forms.e.g. In module1 public
  g_Ds1 as dsInstructor
• Note that New is not used. Therefore, it is only
  used to point to the real dsProd1.

form1
form2
Form1_load() dbinstruct.fill(dsInstructor1)
g_Ds1 dsInstructor1
Datagrid1.datasource g_Ds1
10
Delay with Binding Context

• When using the combobox together with the
  bindingcontext you get a delay action. E.g.
• When Using the following code
• Private Sub ComboBox1_SelectedIndexChanged(ByVal
• Dim ix As Integer
• ix BindingContext(dsInstructor,
  "Instructor").Position
• Label3.Text dsInstructor.Instructor.Rows(
  ix)("Name")
• End Sub

11
Delay with Binding Context

• However, no delay happens
• with Button_click event

Private Sub Login_Click(ByVal Dim ix As
Integer ix Me.BindingContext(DS1,
"Instructor").Position Me.Label5.Text
DS1.Instructor.Rows(ix)("Name") End Sub
12
DataBind Listbox

• ListBoxes that are bind (connected using
  datasource) to dataset act like a dataview.
• I.e. its items (rows) points to rows in dataset.
  Therefore, they are not Strings.
• E.g., to get an item in a listbox that is bind to
  DS1.Prod, you use
• Listbox1.items(index)(ProdName)

13
Alternative to DataBinding

• Directly access the dataset rows
• ds1.product.rows(index)(fieldname)
• Use for-loop to fill comboboxes, listboxes from
  Dataset.
• Use findbyPK to find the row e.g.
• txtName ds1.product.findbyProductid(P01).Name
  
• Using select method to fill datarows and
• use a for loop to read datarows(ix)(fieldname
  )

14
Setting Control/Form Location

• Form1.location New Point (0,0) top left
• TabControl.location new Point (100,100)

15
Revision(1)

• Steps to update the database using a
  dataadapter??
• Change the dataset (b)dbCourse.update(dsCourse)
• How to get Fee for row-3 from a dataset?
• Dscourse1.course.Rows(3)(Fee)
• What steps do you need to do before a database
  Application can be run after you move it to
  another PC?

16
Revision(2)

• Write a statement to find the login password in
  the dataset given a loginID.
• Write a statement to select the rows in a dataset
  given a producttype.
• Looping through a dataset
• Understanding of dataview (draw a picture of how
  it works)
• The use of row filter
• Questions mostly are from lecture 13 18.

17
Revision(3)

• Understanding bindingcontext
• What is wrong when you bind the datasource to
  ds1.prod instead of ds1?