Teknologi OLEDB dapat digunakan
untuk membuat program database menggunakan Microsoft Access, SQL Server, dan
Oracle. Untuk membuat program database dengan Microsoft Access menggunakan
OLEDB dengan kode program ikuti langkah-langkah berikut :
1. Buka Microsoft Visual Studio
2008, pilih File-New Project, pilih Project Types : Visual Basic dan Templates
: Windows Application. Klik OK.
2. Pada Form1 dari Toolbox control
DataGridView, TextBox, Label dan Button, seperti tampak pada tampilan berikut :
3. Tampilkan
kode program Form1.vb milik Form1. Pada bagian imports paling atas tambahkan
reference-reference yang akan dipakai, yaitu : System.Data.Odbc dan System.IO.
Imports System.IO
4. Kemudian
di bawahnya pada deklarasi variabel dan objek global milik Class Form1
tambahkan kode program berikut ini :
Public
Class Form1
Public cn As OdbcConnection
Public ds As DataSet
Public tb As DataTable
Public WithEvents bs As BindingSource
Declare Function ShellExecuteA Lib
"shell32.dll" ( _
ByVal hWnd As IntPtr, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Integer) As IntPtr
5. Klik dua kali pada Form1
kemudian pada Private Sub Form1_Load tambahkan kode program berikut :
Private Sub Form1_Load(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim i As Integer
cn = New
OdbcConnection("Driver={Microsoft Access Driver (*.mdb)};DBQ=" &
Path.GetDirectoryName(Application.ExecutablePath) & "\data.mdb")
cn.Open()
Dim cm As New OdbcCommand("SELECT
name, address, phone FROM contacts", cn)
Dim rd As OdbcDataReader
rd = cm.ExecuteReader()
ds = New DataSet
tb = New DataTable
tb.TableName = "contacts"
ds.Tables.Add(tb)
ds.Load(rd, LoadOption.Upsert, tb)
For i = 0 To (tb.Columns.Count - 1)
If (tb.Columns(i).DataType.ToString
= "System.String") Then tb.Columns(i).MaxLength = 255
Next
rd.Close()
cn.Close()
bs = New BindingSource()
bs.DataSource = ds
bs.DataMember = "contacts"
DataGridView1.DataSource = bs
End Sub
- Untuk event-event yang lain, program selengkapnya sebagai berikut :
Imports
System.Data.OleDb
Imports
System.IO
Public
Class FAccessOLEDB
Public cn As OleDbConnection
Public ds As DataSet
Public tb As DataTable
Public WithEvents bs As BindingSource
Private Sub FAccessOLEDB_Load(ByVal sender
As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim i As Integer
'cn = New
OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Persist Security
Info=True;User ID=sa;Initial Catalog=world;Password=")
'cn = New
OleDbConnection("Provider=MSDAORA;Data Source=localhost.world;Persist
Security Info=True;User ID=admin;Password=")
cn = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Path.GetDirectoryName(Application.ExecutablePath) & "\world.mdb")
cn.Open()
Dim cm As New OleDbCommand("SELECT
name, address, phone FROM contacts", cn)
Dim rd As OleDbDataReader
rd = cm.ExecuteReader()
ds = New DataSet
tb = New DataTable
tb.TableName = "contacts"
ds.Tables.Add(tb)
ds.Load(rd, LoadOption.Upsert, tb)
For i = 0 To (tb.Columns.Count - 1)
tb.Columns(i).MaxLength = -1
Next
rd.Close()
cn.Close()
bs = New BindingSource()
bs.DataSource = ds
bs.DataMember = "contacts"
gContacts.DataSource = bs
End Sub
Private Sub bs_CurrentChanged(ByVal sender
As Object, ByVal e As System.EventArgs) Handles bs.CurrentChanged
If ((bs.Count > 0) And
(bs.DataMember = "contacts")) Then
tName.Text =
bs.Item(bs.Position)("name").ToString
tAddress.Text =
bs.Item(bs.Position)("address").ToString
tPhone.Text =
bs.Item(bs.Position)("phone").ToString
End If
End Sub
Private Sub bFirst_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles bFirst.Click
bs.Position = 0
End Sub
Private Sub bPrev_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles bPrev.Click
bs.Position = bs.Position - 1
End Sub
Private Sub bNext_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles bNext.Click
bs.Position = bs.Position + 1
End Sub
Private Sub bLast_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles bLast.Click
bs.Position = bs.Count - 1
End Sub
Private Sub bAdd_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles bAdd.Click
bs.AddNew()
End Sub
Private Sub bEdit_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles bEdit.Click
'
End Sub
Private Sub bSave_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles bSave.Click
Dim cm As New OleDbCommand
cn.Open()
If
bs.Item(bs.Position)("name").ToString() = "" Then
cm.Dispose()
cm = New OleDbCommand("INSERT
INTO contacts (name, address, phone) VALUES (?, ?, ?)", cn)
Else
cm.Dispose()
cm = New OleDbCommand("UPDATE
contacts SET name = ?, address = ?, phone = ? WHERE name = ?", cn)
End If
cm.Parameters.Add("@name",
OleDbType.VarChar, 0, "").Value = tName.Text
cm.Parameters.Add("@address",
OleDbType.VarChar, 0, "").Value = tAddress.Text
cm.Parameters.Add("@phone",
OleDbType.VarChar, 0, "").Value = tPhone.Text
If
bs.Item(bs.Position)("name").ToString() <> "" Then
cm.Parameters.Add("@Original_name", OleDbType.VarChar, 0,
"").Value = bs.Item(bs.Position)("name").ToString
End If
cm.ExecuteNonQuery()
cn.Close()
bs.Item(bs.Position)("name")
= tName.Text
bs.Item(bs.Position)("address") = tAddress.Text
bs.Item(bs.Position)("phone")
= tPhone.Text
bs.EndEdit()
End Sub
Private Sub bCancel_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles bCancel.Click
bs.CancelEdit()
End Sub
Private Sub bDelete_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles bDelete.Click
cn.Open()
Dim cm As New OleDbCommand("DELETE
FROM contacts WHERE name = ?", cn)
cm.Parameters.Add("@Original_name", OleDbType.VarChar, 0,
"").Value = bs.Item(bs.Position)("name").ToString
cm.ExecuteNonQuery()
cn.Close()
bs.RemoveAt(bs.Position)
End Sub
Private Sub bRefresh_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles bRefresh.Click
Dim i As Integer
gContacts.DataSource = Nothing
bs.DataSource = Nothing
ds.Dispose()
tb.Dispose()
cn.Open()
Dim cm As New OleDbCommand("SELECT
name, address, phone FROM contacts WHERE name LIKE '%" &
tFind.Text.Trim & "%'", cn)
Dim rd As OleDbDataReader
rd = cm.ExecuteReader()
ds = New DataSet
tb = New DataTable
tb.TableName = "contacts"
ds.Tables.Add(tb)
ds.Load(rd, LoadOption.Upsert, tb)
For i = 0 To (tb.Columns.Count - 1)
tb.Columns(i).MaxLength = -1
Next
rd.Close()
cn.Close()
bs = New BindingSource()
bs.DataSource = ds
bs.DataMember = "contacts"
gContacts.DataSource = bs
End Sub
End Class
Jika
Server Database yang digunakan adalah SQL Server, maka ConnectionString-nya
harus diubah menjadi seperti di bawah ini :
cn = New
OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Persist Security
Info=True;User ID=sa;Initial Catalog=world;Password=")
Jika
Server Database yang digunakan adalah SQL Server, maka ConnectionString-nya
harus diubah menjadi seperti di bawah ini :
cn = New
OleDbConnection("Provider=MSDAORA;Data Source=localhost.world;Persist
Security Info=True;User ID=admin;Password=")
