DatabaseMultiformChecklistbox

1
Database/Multiform/Checklistbox

• Objective
• Multiple forms using same connection and datasets
• Table select method
• Use of temporary tablesMerging datasets
• TableStyles
• Parameterized query
• Moving applications Assignment 2 DB.

2
Passing datasets to another form.

• Option 1 having a new connection, dataadapter
  and dataset for each new form.
• (Rather clumsy and should be avoided)
• Option 2 pass the father (parent) as a parameter
  to the second form.

3

Multiforms(review)

• In form1

Form2

Dim f2 as New form2 f2.showdialog()
Note form1 controls form2, and form1 can see the
labels, textboxes in form2. Form2 cannot see
form1. Do not, in form2, NEW a form1.
4
Passing (me) to form2

• Use an overload NEW method

form2
form1
Dim father as form1 Sub New (p as form1)
MyBase.New() InitializeComponent()
father p End sub Sub form_load()
father.dataset11..
Dim f2 as New form2(me) f2.showdialog()

• Now form2 can see all of form1,
• using the parameter p me
• and father p me form1.
• To get form1s dataset, just use
• Father.dataset11

5
Look up with Select Method

• To find a row using the PK, we can use the
  FindbyPK method
• To select rows using a criteria Use the
  data_table.select ( Select-string )
• The Select-string is similar to a SQL statement.
  E.g. To select ProductType toy,
• DS1.Product.Select(Producttype Toy )
• Note you need the quotation around Toy.

6
Select Method

• The .Select method returns one or more rows. I.e.
  an array of rows. Therefore, we have
• dim selectrows() as datarow where ( ) is an
  array
• selectrows ds1.products.Select (Producttype
  Toy )
• To use a variable (such as textbox1) in the
  selection
• selectrows ds1.products.Select (Producttype
  
• textbox1.text )

7
DataTable Select Method

• selectrows ds1.products.Select (Producttype
  Toy )
• selectrows
• To get the price of P03 we use selectrows(2)(Pric
  e)
• Use a for loop or for-each loop to get all the
  rows.

8
Looping thru Selectedrows

• Using for loop to go thru
• For i 0 to selectrows.length -1
• msgbox(selectrows(i)(Price))
• Next
• For each drow in selectrows
• msgbox(drow(Price))
• next

9
FindbyPK/Select exception

• When no row is found by FindbyPK or Select method
  an exception occurs.
• Use a try-catch-block to handle the exceptions.
• Try
• rows ds1.product.select(..)
• Catch .
• End try

10
Diming a temp-datatable

• When performing bookings/registrations that
  involve multiple entries, it is better to use a
  temporary datatable rather than the actual
  dataset. (e.g. in case of abort the actual table
  is not touched)
• E.g. Adding buy orders (details) to tempDatatable
• First, Dim the temp table

11
Temporary datatable
Real datatable
Temp datatable
Merge

• Add the entries to the temp table
• When confirmed - merge it with the real dataset.
• The following code dim a tempOD (table) add
• rows to it

12
Temporary datatable

• Dim tempOD As New dsORdetail.OrderDetailDataTable
• Sub add
• Dim newrow As DataRow tempOD.NewRow
• newrow("OrderID") 8
• newrow("Productid") Me.ComboBox1.Text
• newrow("Quantity") Me.TextBox1.Text
• tempOD.Rows.Add(newrow)
• Me.DataGrid1.DataSource tempOD
• End Add

13
Merging datasets(or table)

• When user confirms the purchases, merge the
  temptable into the real dataset and update the
  database.
• Sub confirm
• Me.DsOrDetail1.Merge(tempOD)
• Me.dbOrderDetail.Update(Me.DsOrDetail1)
• End sub

14
DataGrid TableStyle

• You can customized what is displayed in the
  datagrid using the tablestyle properties.
• Click the TableStyle(collection)

15
Datagrid-TableStyle

• Click add to add DatagridTableStyle
• Set the
• Mapping-
• Name to
• the table,
• e.g.Product

16
Tablestyle-GridcolumnStyle

• Click add to add GridcolumnStyle and add columns
• Set the Mapping Name to the Column ID e.g OrderID
• Also set headertext to OrderID and set readonly
  also.

17
CheckListBox control

• Checklistbox is useful when multiple selections
  are done.
• E.g. to buy 3 items
• P03, p04 p06.
• The code to read which items have been selected
  is as follows
• Dim i As Integer
• For i 0 To Me.CheckedListBox1.CheckedItems.C
  ount - 1
• MsgBox(Me.CheckedListBox1.CheckedItems
  (i))
• Next