Imports System.Data.OleDb
Imports System.IO
Public Class Form1
Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\MyDB.accdb")
Dim cmd As OleDbCommand
Dim da As OleDbDataAdapter
Dim ds As DataSet
Dim bs As BindingSource
Dim sqlstr As String
Private Sub ShowPosition()
txtPosition.Text = bs.Position + 1 & " " & "/" & bs.Count
End Sub
Sub EnableAddbtn()
If Not String.IsNullOrWhiteSpace(txtFname.Text) Then
btnInsert.Enabled = False
btnDelete.Enabled = True
Else
btnInsert.Enabled = True
btnDelete.Enabled = False
End If
End Sub
Sub EnableEditbtn()
If String.IsNullOrWhiteSpace(txtFname.Text) Then
btnUpdate.Enabled = False
Else
btnUpdate.Enabled = True
End If
End Sub
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Try
con.Open()
sqlstr = "Select * From Person"
cmd = New OleDbCommand(sqlstr, con)
da = New OleDbDataAdapter(cmd)
ds = New DataSet
ds.Clear()
da.Fill(ds, "Person")
bs = New BindingSource
bs.DataSource = ds.Tables("Person")
txtID.DataBindings.Clear()
txtFname.DataBindings.Clear()
txtLname.DataBindings.Clear()
txtAdrs.DataBindings.Clear()
Pic1.DataBindings.Clear()
txtID.DataBindings.Add("Text", bs, "ID")
txtFname.DataBindings.Add("Text", bs, "Fname")
txtLname.DataBindings.Add("Text", bs, "Lname")
txtAdrs.DataBindings.Add("Text", bs, "Adrs")
Pic1.DataBindings.Add("Image", bs, "MyImage", 2, 2)
con.Close()
ShowPosition()
EnableAddbtn()
Catch ex As Exception
If con.State = ConnectionState.Open Then con.Close()
MsgBox(ex.Message)
End Try
End Sub
Private Sub btnNew_Click(sender As Object, e As EventArgs) Handles btnNew.Click
txtID.Clear()
txtFname.Clear()
txtLname.Clear()
txtAdrs.Clear()
Pic1.Image = Nothing
EnableAddbtn()
EnableEditbtn()
End Sub
Private Sub btnInsert_Click(sender As Object, e As EventArgs) Handles btnInsert.Click
If (Pic1.Image) Is Nothing Then
MsgBox("Enter picture before saving")
Return
End If
Try
con.Open()
Dim arrImg As Byte()
Dim ms As New MemoryStream
Me.Pic1.Image.Save(ms, Pic1.Image.RawFormat)
arrImg = ms.GetBuffer
sqlstr = "Insert Into Person(Fname,Lname,Adrs,MyImage) Values(@Fname,@Lname,@Adrs,@MyImage)"
cmd = New OleDbCommand(sqlstr, con)
With cmd.Parameters
.Add("@Fname", OleDbType.VarWChar).Value = txtFname.Text
.Add("@Lname", OleDbType.VarWChar).Value = txtLname.Text
.Add("@Adrs", OleDbType.VarWChar).Value = txtAdrs.Text
.Add("@MyImage", OleDbType.Binary).Value = arrImg
End With
cmd.ExecuteNonQuery()
For Each ar As Byte In arrImg
ms.WriteByte(ar)
Next
Pic1.Image = Image.FromStream(ms, True)
MsgBox("Added Successfully")
con.Close()
Catch ex As Exception
If con.State = ConnectionState.Open Then con.Close()
MsgBox(ex.Message)
End Try
End Sub
Private Sub btnAddPic_Click(sender As Object, e As EventArgs) Handles btnAddPic.Click
Dim OFD As New OpenFileDialog
If OFD.ShowDialog = Windows.Forms.DialogResult.OK Then
Pic1.Image = Image.FromFile(OFD.FileName)
End If
End Sub
Private Sub btnFirst_Click(sender As Object, e As EventArgs) Handles btnFirst.Click
bs.Position = 0
ShowPosition()
End Sub
Private Sub btnPrev_Click(sender As Object, e As EventArgs) Handles btnPrev.Click
bs.Position -= 1
ShowPosition()
End Sub
Private Sub btnNext_Click(sender As Object, e As EventArgs) Handles btnNext.Click
bs.Position += 1
ShowPosition()
End Sub
Private Sub btnLast_Click(sender As Object, e As EventArgs) Handles btnLast.Click
bs.Position = bs.Count - 1
ShowPosition()
End Sub
Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
Try
con.Open()
sqlstr = "Select * From Person Where Fname=@Fname"
cmd = New OleDbCommand(sqlstr, con)
cmd.Parameters.Add("@Fname", OleDbType.VarWChar).Value = txtSearch.Text
da = New OleDbDataAdapter(cmd)
ds = New DataSet
ds.Clear()
da.Fill(ds, "Person")
bs = New BindingSource
bs.DataSource = ds.Tables("Person")
txtID.DataBindings.Clear()
txtFname.DataBindings.Clear()
txtLname.DataBindings.Clear()
txtAdrs.DataBindings.Clear()
Pic1.DataBindings.Clear()
txtID.DataBindings.Add("Text", bs, "ID")
txtFname.DataBindings.Add("Text", bs, "Fname")
txtLname.DataBindings.Add("Text", bs, "Lname")
txtAdrs.DataBindings.Add("Text", bs, "Adrs")
Pic1.DataBindings.Add("Image", bs, "MyImage", 2, 2)
con.Close()
ShowPosition()
EnableAddbtn()
Catch ex As Exception
If con.State = ConnectionState.Open Then con.Close()
MsgBox(ex.Message)
End Try
End Sub
Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
If (Pic1.Image) Is Nothing Then
MsgBox("Enter picture before saving")
Return
End If
Try
con.Open()
Dim arrImg As Byte()
Dim ms As New MemoryStream
Me.Pic1.Image.Save(ms, Pic1.Image.RawFormat)
arrImg = ms.GetBuffer
sqlstr = "Update Person Set Fname=@Fname,Lname=@Lname,Adrs=@Adrs,MyImage=@MyImage Where ID=@ID"
cmd = New OleDbCommand(sqlstr, con)
With cmd.Parameters
.Add("@Fname", OleDbType.VarWChar).Value = txtFname.Text
.Add("@Lname", OleDbType.VarWChar).Value = txtLname.Text
.Add("@Adrs", OleDbType.VarWChar).Value = txtAdrs.Text
.Add("@MyImage", OleDbType.Binary).Value = arrImg
.Add("@ID", OleDbType.Integer).Value = txtID.Text
End With
cmd.ExecuteNonQuery()
For Each ar As Byte In arrImg
ms.WriteByte(ar)
Next
Pic1.Image = Image.FromStream(ms, True)
MsgBox("Updated Successfully")
con.Close()
Catch ex As Exception
If con.State = ConnectionState.Open Then con.Close()
MsgBox(ex.Message)
End Try
End Sub
Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
Try
If MsgBox("Do you want to delete record ?", MsgBoxStyle.YesNo, "Caution") = MsgBoxResult.Yes Then
con.Open()
sqlstr = "Delete From Person Where ID=@ID"
cmd = New OleDbCommand(sqlstr, con)
cmd.Parameters.Add("@ID", OleDbType.Integer).Value = txtID.Text
cmd.ExecuteNonQuery()
MsgBox("Deleted successfully")
con.Close()
End If
ShowPosition()
Catch ex As Exception
If con.State = ConnectionState.Open Then con.Close()
MsgBox(ex.Message)
End Try
End Sub
End Class