In this Post you Can learn from here how to make Microsoft access insert search update delete with image vb.net here is the complete coding watch the video and follow the instruction
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Data.Sql
Imports System.IO
Imports System.Configuration
Public Class Form1
Sub filterrecords(ByVal sender As String)
Dim connection3 As OleDb.OleDbConnection = New OleDbConnection
connection3.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\Database16.accdb"
Dim da5 As OleDb.OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM Table3 ", connection3)
Dim ds5 As New DataSet
Dim dt5 As New DataTable
da5.Fill(dt5)
DataGridView1.DataSource = dt5
Dim imgc As New DataGridViewImageColumn
imgc = DataGridView1.Columns(5)
imgc.ImageLayout = DataGridViewImageCellLayout.Stretch
End Sub
Private Sub Button5_Click(sender As System.Object, e As System.EventArgs) Handles Button5.Click
OpenFileDialog1.FileName = ""
OpenFileDialog1.Filter = "All Picture Files|*.bmp;*.jpg;*.gif;*.png|JPEG (*.jpg)|*.jpg|Bitmap (*.bmp)|*.bmp|Graphics Interchange Format (*.gif)|*.gif|Portable Network Graphic (*.png)|*.png"
If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
PictureBox1.Image = Image.FromFile(OpenFileDialog1.FileName)
End If
End Sub
Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
If TextBox1.Text = "" Then
ElseIf TextBox2.Text = "" Then
ElseIf TextBox3.Text = "" Then
ElseIf TextBox4.Text = "" Then
Else
' this coding for search diplicate entry
Dim connection As OleDb.OleDbConnection = New OleDbConnection
connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database16.accdb"
connection.Open()
Dim da As OleDb.OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM Table3 WHERE Number_R = '" & TextBox1.Text & "';", connection)
Dim ds As New DataSet
da.Fill(ds, "FilteredDesc")
connection.Dispose()
connection = Nothing
If ds.Tables(0).Rows.Count > 0 Then
MessageBox.Show("Number Already Available! Go to Search Then Update")
Else
'this coding for insert
Dim sqlconn As New OleDb.OleDbConnection
Dim sqlquery As New OleDb.OleDbCommand
Dim connString As String
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database16.accdb"
sqlconn.ConnectionString = connString
sqlquery.Connection = sqlconn
sqlconn.Open()
sqlquery.CommandText = "INSERT INTO Table3 (Number_R,Name_E,F_Name,Address_S,Image_E) VALUES (@Number_R,@Name_E,@F_Name,@Address_S,@Image_E)"
Dim ms As New MemoryStream()
PictureBox1.Image.Save(ms, PictureBox1.Image.RawFormat)
sqlquery.Parameters.AddWithValue("@Number_R", TextBox1.Text)
sqlquery.Parameters.AddWithValue("@Name_E", TextBox2.Text)
sqlquery.Parameters.AddWithValue("@F_Name", TextBox3.Text)
sqlquery.Parameters.AddWithValue("@Address_S", TextBox4.Text)
sqlquery.Parameters.AddWithValue("@Image_E", ms.ToArray())
sqlquery.ExecuteNonQuery()
sqlconn.Close()
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
PictureBox1.Image = Nothing
filterrecords("")
Me.DataGridView1.Columns("Id").Visible = False
For i = 0 To DataGridView1.Rows.Count - 1
Dim r As DataGridViewRow = DataGridView1.Rows(i)
r.Height = 60
Next
MsgBox("Saved successfull")
End If
End If
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
If TextBox1.Text = "" Then
Else
Dim connection As OleDb.OleDbConnection = New OleDbConnection
connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\Database16.accdb"
connection.Open()
Dim da1 As OleDb.OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM Table3 WHERE Number_R = '" & TextBox1.Text & "';", connection)
Dim ds1 As New DataSet
da1.Fill(ds1, "FilteredDesc")
connection.Dispose()
connection = Nothing
If ds1.Tables(0).Rows.Count > 0 Then
Dim con As New OleDb.OleDbConnection
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim sql As String
con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\Database16.accdb"
con.Open()
sql = "SELECT * FROM Table3 WHERE Number_R = '" & TextBox1.Text & "'"
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "Name_E")
da.Fill(ds, "F_Name")
da.Fill(ds, "Address_S")
con.Close()
TextBox2.Text = ds.Tables("Name_E").Rows(0).Item(2)
TextBox3.Text = ds.Tables("F_Name").Rows(0).Item(3)
TextBox4.Text = ds.Tables("Address_S").Rows(0).Item(4)
Dim connection1 As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\Database16.accdb")
Dim command As New System.Data.OleDb.OleDbCommand("SELECT Image_E FROM Table3 WHERE Number_R = '" & TextBox1.Text & "'", connection1)
connection1.Open()
Dim pictureData As Byte() = DirectCast(command.ExecuteScalar(), Byte())
connection1.Close()
Dim pictures As Image = Nothing
Using stream As New System.IO.MemoryStream(pictureData)
pictures = Image.FromStream(stream)
End Using
PictureBox1.Image = pictures
MessageBox.Show("Record found!")
Else
MessageBox.Show("Record Not Found")
End If
End If
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim sqlconn As New OleDb.OleDbConnection
Dim sqlquery As New OleDb.OleDbCommand
Dim connString As String
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\Database16.accdb"
sqlconn.ConnectionString = connString
sqlquery.Connection = sqlconn
sqlconn.Open()
PictureBox1.Refresh()
sqlquery.CommandText = "UPDATE Table3 SET Number_R = @Number_R ,Name_E = @Name_E ,F_Name = @F_Name, Address_S = @Address_S,Image_E = @Image_E WHERE Number_R = @Number_R"
Dim ms As New MemoryStream()
PictureBox1.Image.Save(ms, PictureBox1.Image.RawFormat)
sqlquery.Parameters.AddWithValue("@Number_R", TextBox1.Text)
sqlquery.Parameters.AddWithValue("@Name_E", TextBox2.Text)
sqlquery.Parameters.AddWithValue("@F_Name", TextBox3.Text)
sqlquery.Parameters.AddWithValue("@Address_S", TextBox4.Text)
sqlquery.Parameters.AddWithValue("@Image_E", ms.ToArray())
sqlquery.ExecuteNonQuery()
sqlconn.Close()
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
PictureBox1.Image = Nothing
filterrecords("")
Me.DataGridView1.Columns("Id").Visible = False
For i = 0 To DataGridView1.Rows.Count - 1
Dim r As DataGridViewRow = DataGridView1.Rows(i)
r.Height = 60
Next
MsgBox("Update successfull")
End Sub
Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
If MessageBox.Show("Are you sure to delete this record", "Delete dialog box", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
Dim sqlconn As New OleDb.OleDbConnection
Dim sqlquery As New OleDb.OleDbCommand
Dim connString As String
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\Database16.accdb"
sqlconn.ConnectionString = connString
sqlquery.Connection = sqlconn
sqlconn.Open()
sqlquery.CommandText = ("Delete From Table3 where Number_R= @Number_R")
sqlquery.Parameters.AddWithValue("@Number_R", TextBox1.Text)
sqlquery.ExecuteNonQuery()
sqlconn.Close()
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
PictureBox1.Image = Nothing
filterrecords("")
Me.DataGridView1.Columns("Id").Visible = False
For i = 0 To DataGridView1.Rows.Count - 1
Dim r As DataGridViewRow = DataGridView1.Rows(i)
r.Height = 60
Next
MsgBox("Delete successfull")
Else
' MessageBox.Show("you clicked no")
End If
End Sub
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
filterrecords("")
Me.DataGridView1.Columns("Id").Visible = False
For s = 0 To DataGridView1.Rows.Count - 1
Dim r As DataGridViewRow = DataGridView1.Rows(s)
r.Height = 60
Next
End Sub
Private Sub Form1_Paint(sender As Object, e As System.Windows.Forms.PaintEventArgs) Handles Me.Paint
With TextBox1
Dim p As New Pen(If(TextBox1.Text <> "", Color.Green, Color.Red), 3)
e.Graphics.DrawRectangle(p, .Left - 1, .Top - 1, .Width + 2, .Height + 2)
p.Dispose()
End With
With TextBox2
Dim p As New Pen(If(TextBox2.Text <> "", Color.Green, Color.Red), 3)
e.Graphics.DrawRectangle(p, .Left - 1, .Top - 1, .Width + 2, .Height + 2)
p.Dispose()
End With
With TextBox3
Dim p As New Pen(If(TextBox3.Text <> "", Color.Green, Color.Red), 3)
e.Graphics.DrawRectangle(p, .Left - 1, .Top - 1, .Width + 2, .Height + 2)
p.Dispose()
End With
With TextBox4
Dim p As New Pen(If(TextBox4.Text <> "", Color.Green, Color.Red), 3)
e.Graphics.DrawRectangle(p, .Left - 1, .Top - 1, .Width + 2, .Height + 2)
p.Dispose()
End With
End Sub
Private Sub TextBox1_TextChanged(sender As System.Object, e As System.EventArgs) Handles TextBox1.TextChanged
Me.Invalidate()
End Sub
Private Sub TextBox2_TextChanged(sender As System.Object, e As System.EventArgs) Handles TextBox2.TextChanged
Me.Invalidate()
End Sub
Private Sub TextBox3_TextChanged(sender As System.Object, e As System.EventArgs) Handles TextBox3.TextChanged
Me.Invalidate()
End Sub
Private Sub TextBox4_TextChanged(sender As System.Object, e As System.EventArgs) Handles TextBox4.TextChanged
Me.Invalidate()
End Sub
End Class
Post a Comment