Applikasi database untuk pemula dalam VB.NET

Import package yang diperlukan :

Imports System.Data
Imports System.Data.OleDb

pada kesempatan ini kita akan menggunakan ADO.NET OLEDB dengan database Ms.Acces 2003
dengan tabel nama user terdiri dari field user dan password dan user sebagai primery keynya

langkah kedua :
-------------------------------------------------
deklarasi :

deklarasi untuk objek koneksi dan string koneksinya
Dim objcon As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\dotnut\My Project\project 1\trainingharian\user.mdb;")

deklarasi komponen ado oledb

Dim objdatatable As New DataTable
Dim objdatset As New DataSet
Dim objcommand As OleDbCommand
Dim objdataadapter As OleDbDataAdapter
Dim objdatreader As OleDbDataReader
Dim strsql As String
Dim r As DataRow
Dim cmd As OleDbCommandBuilder
Dim t As Integer

Buat form seperti gambar di bawah
terdiri dari label ,textbox dan datagrid dan button

Tombol add di klik

Try
Dim box As String = Trim(TextBox1.Text)
Dim box2 As String = Trim(TextBox2.Text)
strsql = " INSERT INTO [user]([userid],[password]) values ('" & TextBox1.Text & "','" & TextBox2.Text & "') "
objcommand = New OleDbCommand
objcommand.Connection = objcon
objcommand.CommandType = CommandType.Text
objcommand.CommandText = strsql
objcon.Open()
objcommand.ExecuteNonQuery()
objcon.Close()
MsgBox("sukses")

Catch ex As Exception
MsgBox(ex.Message)
End Try

Tombol edit di klik

Try

'cara kuno------------------------
'mykoneksi.open()
Dim NAMA As String = Trim(TextBox1.Text)
Dim PASS As String = TextBox2.Text
strsql = "UPDATE [user] set [password] = '" & TextBox2.Text & " ' where userid = '" & TextBox1.Text & "'"
objcommand = New OleDbCommand
objcommand.Connection = mykoneksi.open
objcommand.CommandType = CommandType.Text
objcommand.CommandText = strsql
objcommand.ExecuteNonQuery()
mykoneksi.close()
MsgBox("sukses")

Catch ex As Exception
MsgBox(ex.Message)
End Try

Tombol view diklik

Try
objdatatable.Clear()
strsql = "select * from [user]"
objcommand = New OleDbCommand
objcon.Open()
objcommand.Connection = objcon
objcommand.CommandType = CommandType.Text
objcommand.CommandText = strsql
objdataadapter = New OleDbDataAdapter(objcommand)
objdataadapter.Fill(objdatset, "user")
objdatatable = objdatset.Tables("user")
DataGrid1.DataSource = objdatatable
objcon.Close()

Catch ex As Exception
MsgBox(ex.Message)
End Try

Tombol delete diklik

Try
strsql = "delete from [user] where userid = '" & TextBox1.Text & "'"
objcommand = New OleDbCommand
objcommand.Connection = mykoneksi.open
objcommand.CommandType = CommandType.Text
objcommand.CommandText = strsql
objcommand.ExecuteNonQuery()
mykoneksi.close()
MsgBox("sukses")

Catch ex As Exception
MsgBox(ex.Message)
End Try

salah satu baris di datagrid di pilih untuk menampilkan di textbox
Ini berfungsi untuk edit dan delete

t = DataGrid1.CurrentRowIndex
With objdatatable.Rows(t)
TextBox1.Text = .Item(0).ToString
TextBox2.Text = .Item(1).ToString
End With
TextBox1.ReadOnly = True

Pertama kali program dijalankan

strsql = "select * from [user]"
objcommand = New OleDbCommand
objcommand.Connection = objcon
objcommand.CommandType = CommandType.Text
objcommand.CommandText = strsql
objdataadapter = New OleDbDataAdapter(objcommand)
objdataadapter.Fill(objdatset, "user")
objdatatable = objdatset.Tables("user")
DataGrid1.DataSource = objdatatable
objcon.Close()