Koneksi ADO OLEDB vb.net dengan Ms Accesb II

Ini adalah contoh penggunaan database dengan control textbox dan datagrid.

Scenarionya adalah apabila kita pilih add maka akan muncul idnumber automatis

1. apabila kita klik pada salah satu row di datagrid maka akan muncul pada textbox
2. apabila kita edit pada textbox maka dengan cepat datagrid akan berubah
3. apabila kita klik pada datagrid dan kita tekan tombol delete maka data akan hilang dari datagrid
4. semua hal diatas secara otomatis terupdate ke database

deklarasi :

#Region "declarations"
Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db1.MDB")
Dim dad As New OleDbDataAdapter
Dim ds As New DataSet
Dim t As DataTable
Dim rc As Integer
Dim r As DataRow
#End Region

----------------------------------------------------------

Saat program diload :

Private Sub coba_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
cn.Open()
dad = New OleDbDataAdapter("select * from [table1]", cn)
dad.Fill(ds, "mdt_emplo")
t = ds.Tables("mdt_emplo")
dg1.DataSource = t
cn.Close()
End Sub

---------------------------------------------------------

Tombol add_clik :

Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
Dim i As Integer
Dim str As String
i = t.Rows.Count + 1
'str = t.Rows(dg1.CurrentRowIndex).Item(0)
TextBox1.Text = i
TextBox1.ReadOnly = True
End Sub

---------------------------------------------------------

Tombol delete_clik :

Private Sub btndelet_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelet.Click
Dim cmd As New OleDbCommandBuilder(dad)
rc = dg1.CurrentRowIndex
t.Rows(rc).Delete()
dg1.Refresh()
MsgBox("deleted successfully")
dad.Update(ds, "emp1")
End Sub

-----------------------------------------------------------------

Tombol save_clik ;

cn.Open()
Dim cmd As New OleDbCommandBuilder(dad)
r = t.NewRow
r(0) = TextBox1.Text
r(1) = TextBox2.Text
r(2) = TextBox3.Text
r(3) = TextBox4.Text
t.Rows.Add(r)
dad.Update(ds, "mdt_emplo")
cn.Close()
End Sub

-------------------------------------------------------

Tombol edit_clik ;

Dim i As Integer
Dim str As String
Dim cmd As New OleDbCommandBuilder(dad)
str = TextBox1.Text
i = Integer.Parse(str)
r = t.Rows(i - 1)
r("empname") = TextBox2.Text
r("salary") = TextBox3.Text
r("location") = TextBox4.Text
dad.Update(ds, "mdt_emplo")
MsgBox("sukses update")

-----------------------------------------------------------------

salah satu row Datagrid diselect :

Private Sub dg1_MouseUp(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dg1.MouseUp
rc = dg1.CurrentRowIndex
r = t.Rows(rc)
With t.Rows(rc)
TextBox1.Text = .Item(0)
TextBox2.Text = .Item(1)
TextBox3.Text = .Item(2)
TextBox4.Text = .Item(3)
End With
End Sub