Database multiple tables

1
Database (multiple tables)

• Objective
• Working with 2 tables (using PK-FK)
• Dim DataAdapter using connection-string,
  SQL-string in program.
• Limitation of Designer generated Adapter
• Try Catching database errors.

2
DB tables Linked by PK

• OrderDetail table contains ProductID and
  Quantity. But Price is in the Product table
• Suppose you need to calculate the subtotal for
• P04 with quantity 3.
• Need to look up Price in Product table using
  primary key.

3
2 Tableslinked by FK and PK

• P04 is the primay key (PK) in Products
• and p04 is the foreign key in OrderDetail.

FK
PK
4
Look up using ProductID

• First look in the product table using p04
• Then get the row the corresponding price.

5
Find Product Row by looping

• for each a_row in dsProd.products..rows
• if a_row(ProductID) po4 then
• Exit For
• end if
• msgbox( a_row(ProductName))
• next
• The msgbox will output dragonBall.

6
Look up with FindbyPK

• VB generates ,for the dataset, a method called
• FindbyPK( .. Pk value ..)
• In VB .net you can look up a row in another
• table using FindbyPK (a_PKvalue)
• E.g. to find the row with p04
• Dim a_row as datarow
• a_row dsprod1.products.FindbyProductID(p04)
• NoteFindbyProductID is generated by VB.

7
FindbyPK returns a Row
a_row dsprod1.products.FindbyProductID(p04)

• Now a_row
• the price corresponding to p04 is given by
• a_row(price)

8
Calculation of Subtotal

• To calculate subtotal
• point to the selected row using ix _
• Me.bindingcontext(dsOrderDetail.orderdetail).posit
  ion
• prodID dsOrderDetail.orderdetail.rows(ix)(produ
  ctid)
• a_price dsProd.Products.findbyProductID(prodID).
  price
• Quan dsOrderDetail.orderdetail.rows(ix)(quantit
  y)
• Subtotal Quan a_price

9
Dim dataAdapter, dataTable

• Dim connStr As String _
• "ProviderMicrosoft.Jet.OLEDB.4.0" _
• "Data Source MailOrder.mdb "
• Dim SQLStr As String "SELECT FROM Products"
• Dim dbProd As New _
• OleDb.OleDbDataAdapter(SQLStr, connStr)
• Dim dtab As New DataTable() - dt is a datatable
• dbProd.Fill(dtab)
• dbProd.Dispose() destroy dbProd in memory.
• Note DataAdapter uses a SQL and Connection
  String.
• Dispose removes the Adapter from memory.

10
DataBinding (using code)

• You have to bind the datagrid using vb code
• Datagrid1.DataSource dtab product table
• Bind the combobox as follows
• ComboBox1.DataSource dtab
• ComboBox1.DisplayMember "ProductID"
• The BindingContext position is given by
• Me.bindingcontext(dt).position

11
Updating using dataadapter

• When using the designer to generate adapter.
• It includes Insert, Update, and Delete
  statements.
• When dim-ing the adapter in vb code,
• the insert, update, and delete statement
• are not generated.
• To generate the insert/update/delete statements,
  use the CommandBuilder.

12
OledbCommandBuilder

• Dim cb As OleDbCommandBuilder
• cb new OleDbCommandBuilder(dbProd)
• The commandbuilder builds (generates) the
  insert/update/delete statements for the adapter -
  dbProd
• Once cb is created, the dbProd.update command
  will work.

13
Datatable counts

• After filling the datatable, the datatable
  properties are
• 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
dataTable properties

• The name of the jth (column)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
Limitation of designer dataadapter

• When your dataadapters SQL statement includes
  more than one table, the following limitations
  occur
• The insert,update,
• delete statements
• are not generated.

16
Try Catch block

• Use the try-catch block to catch all database
  related errors.
• Try
• me.DsProd.Products.Add(new_row)
• me.dbProd.Update(DsProd)
• Catch myerr as exception
• msgbox( myerr.Message)
• End try

17
DataBase Errors

• Typical errors
• Adding a record with duplicate primary key
• S001 already exists (adding S001 will crash)
• Non existing tables, field-name
• Spelling error, e.g. boks instead of books