Database updating

1
Database (updating)

• Objective
• Databind explained.
• Looping through dataset
• Modifying dataset, deleting and adding rows
• Moving applications to another PC
• and hardcoding the Connectionstring in form_load
  (placing the access .mdb in the /bin directory)

2
Table inside the Dataset
3
Binding Context

• The form points to a Table inside the dataset.
• It only points to one position at a time.
• This position belongs to the bindingcontext of
  (formme, the table), i.e.
• Me.bindingcontext(DsCustomer, Customers).positio
  n

4
Rows (a collection)

• The table Customers is treated as a collection of
  rows (just like a listbox)
• Listbox1.items ?? ds.Customers.rows
• Listbox1.items(3) ?? ds.Customers.rows(3)
• Listbox1.items.count ?? ds.Customers.rows.count
  
• Each row has many elements and
• Each element is accessed using
• ds.Customers.rows(3)(password)

5
Looping thru dataTable

• For i 0 to ds1.custs.rows.count -1
• msgbox(ds1.custs.rows(i)(address))
• Next
• Or
• Dim drow as datarow
• For each drow in ds1.custs.rows
• msgbox(drow(address))
• next

6
Changes made to Dataset

• Combobox, label, textbox, and datagrid are binded
  to a datatable inside a dataset.
• Combobox and datagrid points to a position of the
  datatable (in the dataset).
• Change made in the binded control (e.g. changing
  textbox) also change the Dataset.
• Changes in dataset not ?to the Database. (need
  .update)

7
Updating Database
3
2
1
lo

• Modify the textbox (binded)
• Now dataset is also changed automatically
• Use dataadapter to copy(update) dataset to
  database

8
Update command

• dbCustomer.update (dsCustomers)
• dbCustomer.fill(dsCustomers)

Bind datagrid
9
Deleting a row

• To delete a row from the database (table).
• First delete the row from the datatable.
• E.g. me.dscust1.customers.rows(ix).delete
• Where ix is the row_pos you wish to delete
• After the deletion, you must copy dataset back to
  database using the .update

10
Adding (inserting) a new row

• Create a new row with the datatable format
• dim a_row as datarow dscust1.customers.newrow
• Put data in the new row
• a_row(CustomerID) C001
• a_row(Name) Twins
• Add the a_row to the dataset
• dscust1.customers.rows.add(a_row)
• Perform .update command
• dbcust.update(dscust1)

11
Moving App. to another PC

• When you move your winapp1 from one PC to
  another, you need to re-establish your
  dataconnection.
• To save this
• step you can
• hardcode the connectionString in form_load
  event.
• conRnR.ConnectionString
• "ProviderMicrosoft.Jet.OLEDB.4.0 Data
  SourceRnrBooks.mdb
• When .mdb placed in /bin, no directory
  specification is needed.