Misal : tanggal tertentu dikurangi tanggal tertentu dan ditambah 1 = tertentu :

SELECT datediff(dd, cast('4/11/2008' as datetime), cast('4/12/2008' as datetime)) + 1 = 2
---------------------------------------------------------------------------------------
Misal : tanggal tertentu ditambah 1 = tertentu :

SELECT dateadd(day, 2, cast('4/11/2008' as datetime)) = '04/13/2008'

Kita akan menyimpan file bitmap ke database Ms Acces dan kemudian menampilkannya ke form,scenarionya :

1. pada gambar paling bawah adalah pada saat form diload
2. kemudian kita klik ambil gambar maka tampilan gambar seperti pada form atas
3. klik save untuk menyimpannya ke database
4. file disimpan dalam type byte
5. klik open untuk menampilkan gambar yang disimpan

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

Open file dialog filter:Bmp Files(*.bmp)*.bmpGif Files(*.gif)*.gifJpg Files(*.jpg)*.jpg

Tombol Ambil gambar_clik :

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
OpenFileDialog1.ShowDialog()
PictureBox1.Image = Image.FromFile(OpenFileDialog1.FileName)
Exit Sub
End Sub

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

Tombol save_clik :

Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
Dim fs As FileStream = New FileStream(OpenFileDialog1.FileName, FileMode.Open, FileAccess.Read)
Dim s As String = TextBox1.Text
Dim r As BinaryReader = New BinaryReader(fs)
Dim FileByteArray() As Byte = r.ReadBytes(fs.Length)
Dim sql As String = "INSERT INTO Images (Filename,Photo) VALUES (?,?)"
Dim cmd As New System.Data.OleDb.OleDbCommand(sql, conn)
cmd.Parameters.Add("@Filename", System.Data.OleDb.OleDbType.VarChar).Value = TextBox1.Text
cmd.Parameters.Add("@Photo", System.Data.OleDb.OleDbType.Binary).Value = FileByteArray
conn.Open()
cmd.ExecuteNonQuery()
MessageBox.Show("Image Succesfully inserted !", "Image Save and Read", MessageBoxButtons.OK, MessageBoxIcon.Information)
conn.Close()
End Sub

-----------------------------------------------------------------
Tombol open_clik :

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim sql As String = "SELECT Photo FROM Images WHERE FileName='" & TextBox1.Text & "'"
conn.Open()
Dim objcom As New OleDbCommand(sql, conn)
rad = objcom.ExecuteReader
rad.Read()
Dim gambar() As Byte = CType(rad.Item("photo"), Byte())
Dim ms As MemoryStream = New MemoryStream(gambar)
Dim hslgambar As Drawing.Image = Bitmap.FromStream(ms)
PictureBox1.Image = New Bitmap(hslgambar)
Exit Sub
End Sub

----------------------------------------------------------
Tombol Delete_click :

Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
On Error GoTo fix
Dim fn As String = InputBox("Enter name to delete")
Dim sql As String = "DELETE FROM Images WHERE FileName='" & fn & "'"
conn.Open()
Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
cmd.ExecuteNonQuery()
MessageBox.Show("Image Succesfully deleted !", "Image Save and Read", MessageBoxButtons.OK, MessageBoxIcon.Information)
conn.Close()
fix:
Exit Sub
End Sub

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

String koneksi :

Imports System.Data.OleDb
Module Module1
Public Con As New ADODB.Connection
Public Sub Connection()
Con = New ADODB.Connection
Con.Open("Provider = MICROSOFT.JET.OLEDB.4.0;data Source=DataBase.MDB ")
End Sub
End Module

-------------------------------------------
dim RsSave as adodb.recordset
dim RsSearch as adodb.recordset
-------------------------------------------------
Save dan update record :


Private Sub SaveRecord()
RsSave.Fields("identitas").Value = txtserial.Text
RsSave.Fields("Nama").Value = txtname.Text
RsSave.Fields("UserID").Value = txtuser.Text
RsSave.Fields("Password").Value = txtpass.Text
RsSave.Fields("Label").Value = cmbstatus.Text
RsSave.Fields("tgl").Value = txtdate.Text
End Sub

-------------------------------------------------
Button save_click :

RsSave = New ADODB.Recordset
RsSave.Open("select * from Logon", Con, 1, 2)
RsSave.AddNew()
Call SaveRecord()
RsSave.Update()
MsgBox("Sukses menambahkan record")

------------------------------------------------
Button Edit_click :

RsSave = New ADODB.Recordset
ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
RsSave.Open("select * from Logon where SerialNo=" & (txtid.Text), Con, 1, 2)
Call SaveRecord()
RsSave.Update()
MsgBox("Record update successfull")

-----------------------------------------------
Button Delete_ click :

Con.Execute("delete from Logon where SerialNo=" & Trim(txtid.Text) & "")
txtserial.Text = ""
txtname.Text = ""
txtuser.Text = ""
txtpass.Text = ""
cmbstatus.Text = ""
txtdate.Text = ""
RsSearch.MoveNext()
Call DisplayRecord()

-----------------------------------------------
Prosedur tampil data :

Sub DisplayRecord()

txtid.Text = RsSearch.Fields("identitas").Value
txtnama.Text = RsSearch.Fields("nama").Value
txtuser.Text = RsSearch.Fields("user").Value
txtpassword.Text = RsSearch.Fields("Password").Value
cmbposisi.Text = RsSearch.Fields("Label").Value
txttgl.Text = RsSearch.Fields("tgl").Value

End Sub

------------------------------------------------------
Control Navigasi :

Last :

RsSearch = New ADODB.Recordset
RsSearch.Open("select * from Logon", Con, 1, 2)
RsSearch.MoveLast()
Call DisplayRecord()

Next :

RsSearch.MoveNext()
Call DisplayRecord()

Previous :

RsSearch.MovePrevious()
Call DisplayRecord()

Definisi string koneksi database :

Namespace accesdata
Public Class databaseconnection

Dim conect As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=spesies.mdb;")

Public Function open() As OleDbConnection
conect.Open()
Return conect
End Function

Public Function close() As OleDbConnection
conect.Close()
Return conect
End Function

End Class
end namespace

-----------------------------------------------------------
String koneksi dan insert table :

Public Class accesdatabase

Public Function insert(ByVal kelompok As String, ByVal spesies As String, ByVal kelamin As String, ByVal nama As String, ByVal photo As String)

Dim mykoneksi As New accesdata.databaseconnection
Dim objcommand As New OleDbCommand("insert into [nama](kelompok,spesies,kelamin,nama,photo) values ('" & kelompok & "','" & spesies & "','" & kelamin & "','" & nama & "','" & photo & "')")

objcommand.Connection = mykoneksi.open
objcommand.CommandType = CommandType.Text
objcommand.ExecuteNonQuery()
mykoneksi.close()

End Function
End Class

---------------------------------------------------------------------
String koneksi dan update table :

Public Class accesdatabase
Public Function update(ByVal kelompok As String, ByVal spesies As String, ByVal kelamin As String, ByVal nama As String, ByVal photo As String)
Dim mykoneksi As New accesdata.databaseconnection

Dim objcommand As New OleDbCommand("update [nama]set kelompok='" & kelompok & "',spesies='" & spesies & "',kelamin='" & kelamin & "',nama='" & nama & "',photo='" & photo & "' where kelomppk='"&kelompok&"'")

objcommand.Connection = mykoneksi.open
objcommand.CommandType = CommandType.Text
objcommand.ExecuteNonQuery()
mykoneksi.close()

End Function
End Class
----------------------------------------------------------------------
String koneksi dan delete table :

Public Class accesdatabase
Public Function delete(ByVal kelompok As String, ByVal spesies As String, ByVal kelamin As String, ByVal nama As String, ByVal photo As String)
Dim mykoneksi As New accesdata.databaseconnection

Dim objcommand As New OleDbCommand("delete from [nama] where kelomppk='"&kelompok&"'")

objcommand.Connection = mykoneksi.open
objcommand.CommandType = CommandType.Text
objcommand.ExecuteNonQuery()
mykoneksi.close()

End Function
End Class
---------------------------------------------------------------------
String koneksi dan Tampil table :

Public Class accesdatabase

'Public Function select(ByVal kelompok As String, ByVal spesies As String, ByVal kelamin As String, ByVal 'nama As String, ByVal photo As String)

Dim mykoneksi As New accesdata.databaseconnection
Dim objcommand As New OleDbCommand("select * from [nama] '")

objcommand.Connection = mykoneksi.open
objcommand.CommandType = CommandType.Text
objcommand.ExecuteNonQuery()
mykoneksi.close()

End Function
End Class
---------------------------------------------------------------------
memanggil clas koneksi dan insert database

mykoneksidata.insert(TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text, ComboBox1.Text)

---------------------------------------------------------------------
memanggil clas koneksi dan update database

mykoneksidata.update(TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text, ComboBox1.Text)