Transcript 09sDch10

Database Programming with
Visual Basic .Net and MS Access
2009.4.29
Database Management and Analysis
IKE Lab.
Yunho Song
1/36
Database Programming with Visual Basic .Net and MS Access
Contents
1. Installation
2. VB .NET and Database
1. The Database Wizard (Visual Studio users)
2. Write your own VB .NET database code
3. Learn about DataSets and Data Adaptors
4. Display the Data in the DataSet
5. Navigate a Database with VB .NET
6. How to Move through the Database
7. Add, Update and Delete Records
8. Add a New Record using VB .NET
9. Delete a Record using VB .NET
10. A VB .NET Database Project
2/36
1. Installation
Installation
Database Programming with Visual Basic .Net and MS Access
1. Installation
4/36
Installation
Database Programming with Visual Basic .Net and MS Access
1. Installation
5/36
Installation
Database Programming with Visual Basic .Net and MS Access
1. Installation
6/36
Installation
Database Programming with Visual Basic .Net and MS Access
1. Installation
First run - choose the default environment settings
7/36
2. VB .NET and Database
VB .NET and Database
Database Programming with Visual Basic .Net and MS Access
2.1. Database Wizard
Let's make a start on our Database project
 Click File > New Project from the menu bar
 Select Windows Application, and then give it the Name AddressBook. Click OK
 Locate the Solution Explorer on the right hand side
 Select Data Sources
9/36
VB .NET and Database
Database Programming with Visual Basic .Net and MS Access
2.1. Database Wizard
The Wizard display all your tables, fields and objects
10/36
VB .NET and Database
Database Programming with Visual Basic .Net and MS Access
2.1. Database Wizard
The Data Sources area displays information about your database

Click the plus symbol next to tblContacts
11/36
VB .NET and Database
Database Programming with Visual Basic .Net and MS Access
2.1. Database Wizard
To add a Field to your Form
 A textbox and a label will be added
12/36
VB .NET and Database
Database Programming with Visual Basic .Net and MS Access
2.1. Database Wizard
Run your program by hitting the F5 key
 Drag and Drop more Fields to your form
13/36
VB .NET and Database
Database Programming with Visual Basic .Net and MS Access
2.2. Write your own Database code
The Connection Object
 What you need if you want to connect to a database
 OLE(Object Linking and Embedding)
• allow you to connect to data sources in general, and not just databases. You can
use it, for example, to connect to text files, SQL Server, email, and a whole lot more
 Place a button on your form. Change the Name property to btnLoad
• Double click your button to open up the code window
•
Add the following line:
Dim con As New OleDb.OleDbConnection
 At the top of your code window, before Public Class Form 1, type the following:
Imports System.Data
14/36
VB .NET and Database
Database Programming with Visual Basic .Net and MS Access
2.2. Write your own Database code
Coding window will look like this
 Get a pop up box from where you can select OleDbConnection
 Use to connect to an Access database
15/36
VB .NET and Database
Database Programming with Visual Basic .Net and MS Access
2.2. Write your own Database code
Setting a Connection String
 The technology is called the Provider; and you use "Data Source" to specify where your
database is
con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data
Source = C:\AddressBook.mdb"
 The first part specifies which provider technology we want to use to do the connecting
(JET)
 The second part, typed after a semi-colon, points to where the database is
 In the above code, the database is on the C drive, in the root folder. The name of the
Access file we want to connect to is called AddressBook.mdb
16/36
VB .NET and Database
Database Programming with Visual Basic .Net and MS Access
2.2. Write your own Database code
Opening the Connection

Open method of the Connection Object:
con.Open( )
MsgBox("A Connection to the Database is now open“)

Close method of the Connection Object:
con.Close()
MsgBox("The Connection to the Database is now Closed“)
17/36
VB .NET and Database
Database Programming with Visual Basic .Net and MS Access
2.2. Write your own Database code
Coding window
Test out our code
18/36
VB .NET and Database
Database Programming with Visual Basic .Net and MS Access
2.3. Data Sets and Data Adapters
Data Sets
 Hidden from you, and just stored in memory
 Imagine a grid with rows and columns. Each imaginary row of the DataSet represents a
Row of information in your Access database. And each imaginary column represents a
Column of information in your Access database (called a Field in Access)
Data Adapter
 The Connection Object and the DataSet can't see each other
 They need a go-between so that they can communicate
 This go-between is called a Data Adapter
 The Data Adapter contacts your Connection Object, and then executes a query that you
set up. The results of that query are then stored in the DataSet.
19/36
VB .NET and Database
Database Programming with Visual Basic .Net and MS Access
2.3. Data Sets and Data Adapters
Data Adapter and DataSet are objects
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
da = New OleDb.OleDbDataAdapter(sql, con)
 OleDb.OleDbDataAdapter
• Called da
• Hold a reference to the Data Adapter
 da = New OleDb.OleDbDataAdapter(sql, con)
• creates a new Data Adapter object
• Need to put two things in the round brackets of the Object declaration
• SQL string (which we'll get to shortly), and connection object
• Connection Object is stored in the variable called con
 Data Adaptor acting as a go-between for the Connection Object and the Data Set
20/36
VB .NET and Database
Database Programming with Visual Basic .Net and MS Access
2.3. Data Sets and Data Adapters
Structured Query Language(SQL)

A way to query and write to databases
• Hold a reference to the Data Adapter
Select * from Table_Name
 To select just the first name and surname columns from our database
SELECT tblContacts.FirstName, tblContacts.Surname FROM tblContacts
 To SELECT all (*) the records from the table called tblContacts
sql = "SELECT * FROM tblContacts”
21/36
VB .NET and Database
Database Programming with Visual Basic .Net and MS Access
2.3. Data Sets and Data Adapters
Structured Query Language(SQL)
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim sql As String
sql = "SELECT * FROM tblContacts"
da = New OleDb.OleDbDataAdapter(sql, con)
22/36
VB .NET and Database
Database Programming with Visual Basic .Net and MS Access
2.3. Data Sets and Data Adapters
Filling the DataSet
 Data Adapter can Fill a DataSet with records from a Table
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "AddressBook“)
 The DataSet (ds) will now be filled with the records we selected from the table called
tblContact
 One slight problem - nobody can see the data yet! We'll tackle that in the next part
23/36
VB .NET and Database
Database Programming with Visual Basic .Net and MS Access
2.4. Displaying the Data in the DataSet
To display the records on a Form
 Add two textboxes to your form
 Change the Name properties of your textboxes to txtFirstName and txtSurname
 Go back to your code window
 Add the following two lines:
txtFirstName.Text = ds.Tables("AddressBook").Rows(0).Item(1)
txtSurname.Text = ds.Tables("AddressBook").Rows(0).Item(2)
24/36
VB .NET and Database
Database Programming with Visual Basic .Net and MS Access
2.5. Navigate a Database
To see a more practical example
 Add two Textboxes. Change the Name properties to txtFirstName and txtSurname
 Add four Buttons. Change the Name and Text properties to these:
Button Name
btnNext
btnPrevious
btnFirst
btnLast
Button Text
Next Record
Previous Record
First Record
Last Record
25/36
VB .NET and Database
Database Programming with Visual Basic .Net and MS Access
2.5. Navigate a Database
To see a more practical example
 Add the following code to the Form1 Declarations area:
 When the Form Loads, we can connect to our database, use the data Adaptor to grab
some records from the database and then put these records into the DataSet
• So in the Form1 Load Event, add the following code:
26/36
VB .NET and Database
Database Programming with Visual Basic .Net and MS Access
2.5. Navigate a Database
To see a more practical example
 You've met all the code before, except for these two lines:
MaxRows = ds.Tables("AddressBook").Rows.Count
inc = -1
 In the MaxRows variable, we can store how many rows are in the DataSet
 Get how many rows are in DataSet with Rows.Count:
To navigate through the records
 Use inc variable. We'll either add 1 to it, or take 1 away
 Use the variable for the Rows in the DataSet
 It's better to do this in a Subroutine of own
 So add this Sub to code:
Private Sub NavigateRecords()
txtFirstName.Text = ds.Tables("AddressBook").Rows(inc).Item(1)
txtSurname.Text = ds.Tables("AddressBook").Rows(inc).Item(2)
End Sub
27/36
VB .NET and Database
Database Programming with Visual Basic .Net and MS Access
2.5. Navigate a Database
The important part is Rows(inc). This moves us through the Rows in the DataSet.
Then placing the values into the two Textboxes
28/36
VB .NET and Database
Database Programming with Visual Basic .Net and MS Access
2.6. How to Move through the Database
How to Move Forward One Record at a Time
 Double click your Next Record button to access the code
If inc <> MaxRows - 1 Then
inc = inc + 1
NavigateRecords()
Else
MsgBox("No More Rows")
End If
Move Back One Record at a Time
 to add to your btnPrevious:
If inc > 0 Then
inc = inc - 1
NavigateRecords()
ElseIf inc = -1 Then
MsgBox("No Records Yet")
ElseIf inc = 0 Then
MsgBox("First Record")
End If
29/36
VB .NET and Database
Database Programming with Visual Basic .Net and MS Access
2.6. How to Move through the Database
Moving to the Last Record in the DataSet
 to add to your btnLast:
If inc <> MaxRows - 1 Then
inc = MaxRows - 1
NavigateRecords()
End If
Moving to the First Record in the DataSet
 to add to your btnFirst:
If inc <> 0 Then
inc = 0
NavigateRecords()
End If
30/36
VB .NET and Database
Database Programming with Visual Basic .Net and MS Access
2.7. Add, Update and Delete Records
DataSet is disconnected from the database
 Not adding the record to the database: adding it to the DataSet
How to add, delete, update new records
 Add five more buttons to your form
 Change the Name properties
btnAddNew
btnCommit
btnUpdate
btnDelete
btnClear
31/36
VB .NET and Database
Database Programming with Visual Basic .Net and MS Access
2.7. Add, Update and Delete Records
Updating a Record
 The changes will just get made to the DataSet
 Add the following code to btnUpdate:
ds.Tables("AddressBook").Rows(inc).Item(1) = txtFirstName.Text
ds.Tables("AddressBook").Rows(inc).Item(2) = txtSurname.Text
MsgBox("Data updated“)
"Changes are made to the DataSet, and NOT to the Database“
 To update the database, Add following code
Dim cb As New OleDb.OleDbCommandBuilder(da)
ds.Tables("AddressBook").Rows(inc).Item(1) = txtFirstName.Text
ds.Tables("AddressBook").Rows(inc).Item(2) = txtSurname.Text
da.Update(ds, "AddressBook")
MsgBox("Data updated“)
32/36
VB .NET and Database
Database Programming with Visual Basic .Net and MS Access
2.8. How to Add a New Record
Add a New Record
 Add New Record button:
btnCommit.Enabled = True
btnAddNew.Enabled = False
btnUpdate.Enabled = False
btnDelete.Enabled = False
txtFirstName.Clear()
txtSurname.Clear()
The Clear/Cancel button can be used to switch it back on again
 Add this code to btnClear:
btnCommit.Enabled = False
btnAddNew.Enabled = True
btnUpdate.Enabled = True
btnDelete.Enabled = True
inc = 0
NavigateRecords()
33/36
VB .NET and Database
Database Programming with Visual Basic .Net and MS Access
2.8. How to Add a New Record
To add a new record to the database
 Add this code to btnCommit
If inc <> -1 Then
Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim dsNewRow As DataRow
dsNewRow = ds.Tables("AddressBook").NewRow()
dsNewRow.Item("FirstName") = txtFirstName.Text
dsNewRow.Item("Surname") = txtSurname.Text
ds.Tables("AddressBook").Rows.Add(dsNewRow)
da.Update(ds, "AddressBook“)
MsgBox("New Record added to the Database“)
btnCommit.Enabled = False
btnAddNew.Enabled = True
btnUpdate.Enabled = True
btnDelete.Enabled = True
End If
34/36
VB .NET and Database
Database Programming with Visual Basic .Net and MS Access
2.8. Delete a Record from a Database
Deleting Records from a Database
 Add this code to btnDelete
Dim cb As New OleDb.OleDbCommandBuilder(da)
ds.Tables("AddressBook").Rows(inc).Delete()
MaxRows = MaxRows - 1
inc = 0
NavigateRecords()
da.Update(ds, "AddressBook“)
 To display a message box asking users if they really want to delete this record
If MessageBox.Show("Do you really want to Delete this Record?", _
"Delete", MessageBoxButtons.YesNo, _
MessageBoxIcon.Warning) = DialogResult.No Then
MsgBox("Operation Cancelled")
Exit Sub
End If
35/36
Thank You!!
36/36